go链接Oracle

1、先安装gcc，可以参考   超详细教程：windows安装MSYS2（mingw && gcc）

2、go代码

 

package main

import (
	"database/sql"
	"fmt"
	"time"

	"github.com/godror/godror"
)

//全局链接变量
var db *sql.DB

const (
	host        string = "127.0.0.1"
	port        string = `1521`
	user        string = "user"
	sqlpassword string = "sqlpassword"
	dbname      string = "orcl"
)

//初始化数据库链接
func init() {
	// 用户名/密码@IP:端口/实例名
	//osqlInfo := fmt.Sprintf("%s/%s@%s:%d/%s?timezone=local", user, sqlpassword, host, port, dbname)

	//	db, err := sql.Open("godror", osqlInfo)

	var P godror.ConnectionParams
	P.Username, P.Password = user, godror.NewPassword(sqlpassword)
	P.ConnectString = host + ":" + port + "/" + dbname + "?connect_timeout=5"
	P.SessionTimeout = 32 * time.Second
	//	P.SetSessionParamOnInit("NLS_NUMERIC_CHARACTERS", ",.")
	// loc, _ := time.LoadLocation("Asia/Shanghai")
	// P.Timezone = loc

	//fmt.Println(P.String()) //打印全部参数

	db = sql.OpenDB(godror.NewConnector(P))
	if y := db.Ping(); y != nil {
		fmt.Println(`y is ====?`, y)
		return
	} else {
		fmt.Println(`链接数据库成功!`)
	}

}

func main() {

	sqlStatement := `SELECT * from(		SELECT rownum as orderid,e.* FROM wms.jc_jgm e where djbm =:1 ) f where f.orderid>=:2 and f.orderid<:3	`
	stmt, err := db.Prepare(sqlStatement)

	if err != nil {
		fmt.Println(err)
		panic(err)
	}
	defer stmt.Close()

	rows, err := stmt.Query("YYXX2204210033", 3, 5) //输入sql中对应参数的值
	if err != nil {
		panic(err)
	}

	defer rows.Close() //defer关闭查询连接
	a, _ := sql_rows(rows)
	for i := 0; i < len(a); i++ {

		fmt.Println(i, `行结果集==>`)
		d := a[i]
		map_sort(d)
		for y, d1 := range d {

			if x, ok := d1.([]byte); ok && len(x) > 0 {
				fmt.Println(y, `==>OK==>`, string(x))
			}

		}
	}

}

 

工具函数:

 

package main

import (
	"database/sql"
	"fmt"
	"sort"
)

/*返回有序map*/
func sql_rows(rows *sql.Rows) (map[int]map[string]interface{}, error) {

	//读出查询出的列字段名
	cols, _ := rows.Columns()
	//	fmt.Println(cols)
	//values是每个列的值，这里获取到byte里
	values := make([][]byte, len(cols))
	//query.Scan的参数，因为每次查询出来的列是不定长的，用len(cols)定住当次查询的长度
	scans := make([]interface{}, len(cols))
	//让每一行数据都填充到[][]byte里面
	for i := range values {
		scans[i] = &values[i]
	}

	//最后得到的map
	results := make(map[int]map[string]interface{})
	i := 0
	for rows.Next() { //循环，让游标往下推
		if err := rows.Scan(scans...); err != nil { //query.Scan查询出来的不定长值放到scans[i] = &values[i],也就是每行都放在values里
			//fmt.Println(err)
			return nil, err
		}

		row := make(map[string]interface{}) //每行数据

		for k, v := range values { //每行数据是放在values里面，现在把它挪到row里
			//key := strings.ToLower(cols[k])
			key := cols[k]
			row[key] = v

			//fmt.Println("row[key]======>", row[key])
		}
		results[i] = row //装入结果集中
		i++
	}

	//return json.Marshal(results)
	return results, nil

}

//有序打印  map[string]interface{}
func map_sort(map_list map[string]interface{}) {

	var names []string
	for name := range map_list {
		names = append(names, name)
	}
	sort.Strings(names)
	for _, name := range names {
		//fmt.Printf("%s\t%v\n", name, map_list[name])

		if x, ok := map_list[name].([]byte); ok && len(x) > 0 {
			fmt.Println(name, `==>map_sort OK==>`, string(x))
		}
	}

}