golang 使用go-ora 连接数据库 示例代码

package main import ( "database/sql" "errors" "fmt" _ "github.com/sijms/go-ora/v2" "log" "os" "strings" ) func main() { err := InitConn("用户", "密码", "数据库的电脑ip", "oracle实例名") if err != nil { log.Fatal(err) } err = createTable() if err != nil { fmt.Println("新增dll失败", err) } err = insertData() if err != nil { fmt.Println(err) } err = batchInsertData() if err != nil { fmt.Println(err) } err = queryData() if err != nil { fmt.Println(err) } err = callStoredProcedure() if err != nil { fmt.Println(err) } err = callFunction() if err != nil { fmt.Println(err) } err = updateSql() if err != nil { fmt.Println(err) } } func insertData() error { var id int for i := 0; i < 10; i++ { //插入数据，可以直接返回id _, err := db.Exec(`insert into ora_test2(id,name,remark ) values (seq_ora_test2.nextval,&1,&2) RETURNING ID INTO :id`, fmt.Sprintf("名称%d", i), fmt.Sprintf("备注%d", i), sql.Out{Dest: &id}) if err != nil { fmt.Println("新增失败", err) return err } //查询结果 var name, remark string rs, err := db.Query("select name,remark from ora_test2 where id = :id ", sql.Named("id", id)) if err != nil { fmt.Println("新增失败", err) return err } if rs.Next() { _ = rs.Scan(&name, &remark) } _ = rs.Close() //打印 fmt.Println("新增成功 id为：", id, name, remark) } return nil } func batchInsertData() error { //开启事务 tx, err := db.Begin() if err != nil { return err } //定义执行的sql stmt, err := tx.Prepare(`insert into ora_test2(id,name,remark ) values (&1,&2,&3) `) //批发插入 RETURNING ID 有点问题 最好别用 defer func() { _ = stmt.Close() }() var id int for i := 0; i < 1000; i++ { //查询id rr, err := tx.Query("select seq_ora_test2.nextval from dual ") if err != nil { fmt.Println("新增失败，事务回滚", err) _ = tx.Rollback() return err } if rr.Next() { _ = rr.Scan(&id) } _ = rr.Close() //插入参数，执行新增 _, err = stmt.Exec(id, fmt.Sprintf("batch插入标题%d", i), fmt.Sprintf("batch插入备注%d", i)) //, sql.Out{Dest: &id} if err != nil { fmt.Println("新增失败，事务回滚", err) _ = tx.Rollback() //回滚事务 return err } //查询返回结果 var name, remark string rs, err := tx.Query("select name,remark from ora_test2 where id = :id ", sql.Named("id", id)) if err != nil { fmt.Println("新增失败，事务回滚", err) _ = tx.Rollback() //回滚事务 return err } if rs.Next() { _ = rs.Scan(&name, &remark) } _ = rs.Close() //打印 fmt.Println("新增成功 id为：", id, name, remark) } return tx.Commit() //提交事务 } func queryData() error { //按顺序 匹配参数查询 rs1, err := db.Query("select name,remark from ora_test2 where name = &1", "名称1") if err != nil { return err } defer func() { _ = rs1.Close() }() //打印结果 for rs1.Next() { var name, remark string _ = rs1.Scan(&name, &remark) fmt.Println("查询结果：", name, remark) } //按名称 匹配参数查询 rs2, err := db.Query("select name,remark from ora_test2 where name = :name", sql.Named("name", "名称1")) if err != nil { return err } defer func() { _ = rs2.Close() }() //打印结果 for rs2.Next() { var name, remark string _ = rs2.Scan(&name, &remark) fmt.Println("查询结果：", name, remark) } return nil } 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 } 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 } func updateSql() error { //更新sql _, err := db.Exec(`update ora_test2 t set t.remark=:1 where t.name = :2`, "更新后的备注", "名称1", ) if err != nil { return err } //删除 _, err = db.Exec(`delete from ora_test2 t where t.name = :1`, "名称3", ) if err != nil { return err } return nil } // 新增ddl func createTable() error { var err error _, err = db.Exec(`CREATE OR REPLACE function ora_test2_func(b_id IN NUMBER) return varchar2 AS BEGIN return '测试';END;`) if err != nil { return err } _, err = db.Exec(`CREATE OR REPLACE PROCEDURE ora_test2_pro(b_id IN NUMBER, r_msg OUT VARCHAR2) AS BEGIN r_msg:='测试'; return; END;`) if err != nil { return err } _, err = db.Exec(`create sequence seq_ora_test2 minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 5`) if err != nil { return err } _, err = db.Exec(`create table ora_test2(id number(10) primary key,name varchar2(80) not null,remark varchar2(80) )`) if err != nil { return err } return nil } var db *sql.DB func InitConn(user, pwd, host, database string) (err error) { err = os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8") if err != nil { return errors.New(fmt.Sprintf("数据库连接失败 : %v", err.Error())) } connStr := fmt.Sprintf("oracle://%s:%s@%s/%s", user, pwd, host, database) db, err = sql.Open("oracle", connStr) if err != nil { return errors.New(fmt.Sprintf("数据库连接失败 : %v", err.Error())) } err = db.Ping() if err != nil { return errors.New(fmt.Sprintf("数据库连接失败 : %v", err.Error())) } return err }