golang开发oracle利器godror

golang开发oracle利器godror

godror是golang中连接oracle进行程序开发的一个驱动库，解决了长期以来go对oracle支持不友好的问题。github项目地址

尽管编译不需要 Oracle 客户端库，但在运行时需要它们,需要在生产环境里安装oracle客户端。从 https://www.oracle.com/database/technologies/instant-client/downloads.html 下载免费的 Basic 或 Basic Light 软件包。

Linux下安装oracle客户端参见：文档最后部分。

简单查询示例:
将查询结果转换为json字符串输出

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"

	_ "github.com/godror/godror"
)

func main() {
	db, err := sql.Open("godror", `user="testq" password="123456" connectString="192.169.123.72:1521/orcl"`)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}

	//查询
	sqlStatement := "SELECT * FROM BAJG where ID = :1"
	stmt, err := db.Prepare(sqlStatement)
	defer stmt.Close()
	if err != nil {
		panic(err)
	}
	rows, err := stmt.Query("479") //输入sql中对应参数的值
	//defer rows.Close()
	if err != nil {
		panic(err)
	}
	//rows, _ := db.Query("SELECT * FROM BAJG")

	defer rows.Close() //defer关闭查询连接
	//获取列相关信息
	strings, _ := rows.Columns()

	for i := 0; i < len(strings); i++ {
		fmt.Print(" ", strings[i])
	}
	fmt.Print("\n")
	//构造切片存储json
	var slice []map[string]interface{}
	var m1 map[string]interface{}
	m1 = make(map[string]interface{})

	var ID, XM, SFZH, ZL, MJ string
	for rows.Next() {
		rows.Scan(&ID, &XM, &SFZH, &ZL, &MJ) //写入查询数据集的所有列名称
		fmt.Printf("username is %s, department is %s\n", XM, ZL)
		m1["ID"] = ID
		m1["XMA"] = XM
		m1["SFZHB"] = SFZH
		m1["ZLC"] = ZL
		m1["MJD"] = MJ
		slice = append(slice, m1) //分片中追加信息
	}
	if err = rows.Err(); err != nil {
		// handle the error here
	}
	defer stmt.Close()

	data, err := json.Marshal(slice)
	if err != nil {
		fmt.Printf("序列化错误 err = %v\n", err)
	}
	//输出序列化后的结果 json字符串
	fmt.Printf("slice 序列化后 = %v\n", string(data))

	fmt.Printf("ok")
}

存储过程调用：

	        var po_out, po_fhz, po_msg string      //存储过程输出参数
		var loginid2, password2, gmsfhm string //存储过程输入参数
		loginid2 = "aaa"
		password2 = "aaa123"
		gmsfhm = b.SFZH //"41234567890000000"		
		query := `BEGIN pro.abcproduce(:1,:2,:3,:4,:5,:6); END;`
		ctx, _ := context.WithTimeout(context.Background(), 10*time.Second)
		//ctx, _ := context.WithCancel(context.Background())
		if _, err := db.ExecContext(ctx, query, loginid2, password2, gmsfhm, sql.Out{Dest: &po_out}, sql.Out{Dest: &po_fhz}, sql.Out{Dest: &po_msg}); err != nil {
			log.Printf("Error running %q: %+v", query, err)
			panic(err)
			//return
		}

调用完成后，可以在输出参数中获取存储过程执行后的输出值。