【REST2SQL】02 GO连接Oracle数据库

【REST2SQL】01RDB关系型数据库REST初设计

Oracle数据库我用的最多，先研究Oracle,Go连接Oracle并实现REST和SQL服务。

1 Oracle数据库的安装

我这里安装使用的是Oracle 11g , 安装过程省略5217字。

2 安装Go-ora依赖

 go get github.com/sijms/go-ora/v2

安装成功后在GOPATH目录可见：
在这里插入图片描述

3 创建一个Gooracle控制台程序测试

全部代码如下：

// gooracle project main.go
package mainimport ("database/sql/driver""encoding/json""fmt""io""log""os"go_ora "github.com/sijms/go-ora/v2" // 1 go get github.com/sijms/go-ora/v2
)// Oracle连接字符串
const connStr string = "oracle://blma:5217@127.0.0.1:1521/CQYH"func main() {var (sqls   string //sql语句result string //sql执行后返回的结果)// select查询数据sqls = "select sysdate from dual"result = selectData(sqls)fmt.Println(result)// delete 删除数据sqls = "delete from atop where p_id = -5"result = deleteData(sqls)fmt.Println(result)// update 更新数据sqls = "update atop set f_dm = '005217' where p_id = -5217"result = updateData(sqls)fmt.Println(result)// insert 插入一行数据sqls = "insert into atop (p_id) values (FLOOR(DBMS_RANDOM.value(0, 100)))"result = insertData(sqls)fmt.Println(result)
}// 连接Oracle数据库
func connDB(connStr string) *go_ora.Connection {//创建连接DB, err := go_ora.NewConnection(connStr)dieOnError("Can't open the driver:", err)//打开连接err = DB.Open()dieOnError("Can't open the connection:", err)return DB
}// delete
func deleteData(deleteSql string) string {result, _ := execSQL(deleteSql)rows, err := result.RowsAffected()dieOnError("Can't delete", err)ret := map[string]int{"Delete rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 转 json失败：", err)return string(jsonBytes)
}// update
func updateData(updateSql string) string {result, _ := execSQL(updateSql)rows, err := result.RowsAffected()dieOnError("Can't update", err)ret := map[string]int{"Update rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 转 json失败：", err)return string(jsonBytes)
}// insert
func insertData(insertSql string) string {result, _ := execSQL(insertSql)rows, err := result.RowsAffected()dieOnError("Can't insert", err)ret := map[string]int{"Insert rowsAffected": int(rows),}jsonBytes, err := json.Marshal(ret)dieOnError("map 转 json失败：", err)return string(jsonBytes)
}// 执行SQL， execute stmt (INSERT, UPDATE, DELETE, DML, PLSQL) and return driver.Result object
func execSQL(sqls string) (result driver.Result, err error) {//连接数据库DB := connDB(connStr)//延迟关闭连接defer DB.Close()//准备sql语句stmt := go_ora.NewStmt(sqls, DB)//延迟关闭SQLdefer stmt.Close()//执行SQL， execute stmt (INSERT, UPDATE, DELETE, DML, PLSQL) and return driver.Result objectresult, err = stmt.Exec(nil)dieOnError("Can't execSql() ", err)return result, err
}// select查询，结果为json
func selectData(sqls string) string {//连接数据库DB := connDB(connStr)//延迟关闭连接defer DB.Close()//准备sql语句stmt := go_ora.NewStmt(sqls, DB)//延迟关闭SQLdefer stmt.Close()rows, err := stmt.Query(nil)dieOnError("Can't query", err)defer rows.Close()//fmt.Println(rows)columns := rows.Columns()//fmt.Println("columns:", columns)values := make([]driver.Value, len(columns))var dataset []map[string]interface{} //元素为map的切片//Header(columns)for {err = rows.Next(values)if err != nil {break}//fmt.Println("values:", values)row1 := Record(columns, values)dataset = append(dataset, row1)}if err != io.EOF {dieOnError("Can't Next", err)}//切片转jsonjsonBytes, err := json.Marshal(dataset)dieOnError("slice 转 json失败：", err)//fmt.Println(string(jsonBytes))return string(jsonBytes)
}// 发生错误退出1
func dieOnError(msg string, err error) {if err != nil {//fmt.Println(msg, err)log.Fatal(msg, err)os.Exit(1)}
}// func Header(columns []string) {// }// 一行记录加入 map
func Record(columns []string, values []driver.Value) map[string]interface{} {mc := make(map[string]interface{}) //一行记录信息放入 mapfor i, c := range values {//fmt.Printf("\"%s\":%v,", columns[i], c)mc[columns[i]] = c}//fmt.Println(mc)return mc //返回一行记录的信息map
}// func returnErr(err error) error {
// 	if err != nil {
// 		return err
// 	}
// 	return nil
// }// // 7调用存储过程
// func callStoredProcedure() error {
// 	var (
// 		id  int
// 		msg string = strings.Repeat(" ", 2000) //先赋值内容
// 	)
// 	//执行存储过程，
// 	_, err := db.Exec(`BEGIN ora_test2_pro(:1, :2 ); END;`,
// 		id,
// 		sql.Out{Dest: &msg},
// 	)
// 	if err != nil {
// 		return err
// 	}
// 	//输出结果
// 	fmt.Println(msg)
// 	return nil
// }// // 8.调用函数
// func callFunction() error {
// 	var (
// 		id  int
// 		msg string = strings.Repeat(" ", 2000) //先赋值内容
// 	)
// 	//执行存储过程，
// 	_, err := db.Exec(`BEGIN :1 := ora_test2_func(:2 ); END;`,
// 		sql.Out{Dest: &msg},
// 		id,
// 	)
// 	if err != nil {
// 		return err
// 	}
// 	//输出结果
// 	fmt.Println(msg)
// 	return nil
// }

运行输出结果如下：