golang 连接oracle（纯golang的方式）

git地址：

GitHub - sijms/go-ora: Pure go oracle clientPure go oracle client. Contribute to sijms/go-ora development by creating an account on GitHub.https://github.com/sijms/go-ora

优点

无效安装 mingw，也不用 oracle的客户端，使用非常方便

简单操作示例

1. 安装 go-ora。

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

2. 连接数据库示例代码

package main

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/sijms/go-ora/v2"
	"log"
	"os"
	"time"
)

func main() {
	err := InitConn(
		"user",      //数据库用户名
		"pwd",       //数据库用户密码
		"localhost", //数据库的电脑ip
		1521,        //oracle端口
		"orcl",      //oracle实例名
	)
	if err != nil {
		log.Fatal(err)
	}
	

}

var db *sql.DB

func InitConn(user, pwd, server string, port int, database string) (err error) {
	err = os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8")
	if err != nil {
		return errors.New(fmt.Sprintf("数据库连接失败 : %v", err.Error()))
	}
	connStr := go_ora.BuildUrl(server, port, database, user, pwd, nil)
	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
}

3. 新增表

func createTable() error {
	var err error
	_, err = db.Exec(`CREATE OR REPLACE function ora_test2_func(b_id IN NUMBER) return varchar2 AS BEGIN 'ora_test2_pro' || p_id; 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 'ora_test2_func-' || b_id;  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
}

4、插入数据，并获取id。

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
}

5、批量插入 

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() //提交事务
}

6. 查询sql

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
}

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
}

9. 执行sql

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
}

实操视频

golang 连接oracle（go-ora）