1,golang 连接 oracle 数据库
2,增删改查
- /*
- * @Author: lmy
- * @Date: 2023-08-24 15:19:22
- * @LastEditors: lmy
- * @LastEditTime: 2023-08-24 16:23:58
- * @FilePath: \golangOracleDemo\main.go
- * @Description: golang oracle 增删改查 DEMO
- */
-
- package main
-
- import (
- "database/sql"
- "fmt"
- "strings"
- "time"
-
- go_ora "github.com/sijms/go-ora/v2"
- )
-
- var oracleDB *sql.DB //oracleDB oracle数据库
-
- func main() {
- var err error
-
- // 演示的数据库表DDL
- // CREATE TABLE "SYSTEM"."TABLE1"
- // ( "ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
- // "NAME" VARCHAR2(20 BYTE),
- // "AGE" NUMBER(*,0),
- // "PHONE" VARCHAR2(11 BYTE),
- // "BIRTHDAY" DATE
- // ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
- // NOCOMPRESS LOGGING
- // STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- // PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
- // BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- // TABLESPACE "SYSTEM" ;
-
- // 传入数据库连接字符串
- // username/password@host:port/service_name
- //system:123456@192.168.168.152:1521/orcl.docker.internal
- //system:123456@192.168.168.152:1521/?SID=orcl
- port := 1521 // 端口
- server := "192.168.168.152" // Host
- serviceName := "orcl.docker.internal" // 连接名|服务名
- username := "system" // 用户名
- password := "123456" // 密码
- sid := "orcl" // SID
- connStr := ""
- //连接数据库的方式
- //方法1:简单连接
- connStr = go_ora.BuildUrl(server, port, serviceName, username, password, nil)
- fmt.Println("数据库链接1=>", connStr)
- //方法2:使用SID连接
- urlOptions := map[string]string{
- "SID": sid,
- }
- connStr = go_ora.BuildUrl(server, port, "", username, password, urlOptions)
- fmt.Println("数据库链接2=>", connStr)
-
- oracleDB, err = CreateOracleSQLConnV2(connStr)
- if err != nil {
- fmt.Printf("连接数据库失败:%v \n", err)
- return
- }
- // 设置数据库链接 | 要数据库连链接创建成功 才能走下面的流程
-
- //增
- var info TableInfo
- info.Name = "小明"
- info.Age = 11
- info.Birthday, _ = time.Parse("2006-01-02", "2012-08-08")
- if isOK, _ := addTableInfo(info); isOK {
- fmt.Println("新增数据成功", info)
- } else {
- fmt.Println("新增数据失败", info)
- }
-
- //增
- info.Name = "大明"
- info.Age = 19
- var timeZero time.Time
- info.Birthday = timeZero
- //info.Birthday, _ = time.Parse("2006-01-02", "2004-09-08")
- isOK, err := addTableInfo(info)
- if err != nil {
- fmt.Println("新增数据异常", info, err)
- } else {
- if isOK {
- fmt.Println("新增数据成功", info)
- } else {
- fmt.Println("新增数据失败", info)
- }
- }
-
- //根据名称和年龄搜查询单条数据
- info, err = sqlGetOneTableDataInfo("明", 18)
- if err != nil {
- fmt.Printf("根据ID查询单条数据失败:%v \n", err)
- return
- }
- fmt.Println("根据名称和年龄搜查询单条数据", info)
- //查询所有数据
- datas, err := sqlGetAllTableDataInfo()
- if err != nil {
- fmt.Printf("查询所有数据失败:%v \n", err)
- return
- }
- fmt.Println("查询所有数据=>", datas)
-
- info.Name = "老明"
- info.Age = 20
- info.Birthday, _ = time.Parse("2006-01-02", "2003-05-06")
- //改
- if isOK := editTableInfo(info); isOK {
- fmt.Println("编辑数据成功", info)
- } else {
- fmt.Println("编辑数据失败", info)
- }
-
- //查询所有数据
- datas, err = sqlGetAllTableDataInfo()
- if err != nil {
- fmt.Printf("查询所有数据失败:%v", err)
- return
- }
- fmt.Println("查询所有数据=>", datas)
- fmt.Println("删除数据=>", info.ID)
- // 删除
- deleteTableInfoByID(info.ID)
- fmt.Println()
- //查询所有数据
- datas, err = sqlGetAllTableDataInfo()
- if err != nil {
- fmt.Printf("查询所有数据失败:%v", err)
- return
- }
- fmt.Println("查询所有数据=>", datas)
-
- //删除所有数据
- deleteTableInfo()
- fmt.Println("删除表所有数据")
-
- //查询所有数据
- datas, err = sqlGetAllTableDataInfo()
- if err != nil {
- fmt.Printf("查询所有数据失败:%v", err)
- return
- }
- fmt.Println("查询所有数据=>", datas)
- }
-
- // 添加表数据
- func addTableInfo(info TableInfo) (bool, error) {
- affect, err := ExecSQL(oracleDB, "INSERT INTO TABLE1( NAME,AGE,BIRTHDAY) VALUES(:name, :age,:brithday)", info.Name, info.Age, info.Birthday)
- if err != nil {
- fmt.Printf("添加数据失败:%v", err)
- return false, err
- }
- return affect > 0, nil
- }
-
- // 编辑表数据
- func editTableInfo(info TableInfo) bool {
- affect, err := ExecSQL(oracleDB, "UPDATE TABLE1 SET NAME=:name,AGE=:age,BIRTHDAY=:brithday WHERE ID=:ID", info.Name, info.Age, info.Birthday, info.ID)
- if err != nil {
- fmt.Printf("修改数据失败:%v", err)
- return false
- }
- return affect > 0
- }
-
- // 根据ID删除表数据
- func deleteTableInfoByID(id int) bool {
- affect, err := ExecSQL(oracleDB, "DELETE TABLE1 where ID = :id", id)
- if err != nil {
- fmt.Printf("根据ID删除数据失败:id:%v,%v", id, err)
- return false
- }
- return affect > 0
- }
-
- // 删除表所有数据
- func deleteTableInfo() bool {
- affect, err := ExecSQL(oracleDB, "DELETE TABLE1 ")
- if err != nil {
- fmt.Printf("删除所有数据失败:%v", err)
- return false
- }
- return affect > 0
- }
-
- // 表数据结构体
- type TableInfo struct {
- ID int //id,--ID
- Name string //NAME,--名称
- Age int //AGE,--年纪
- Birthday time.Time //BIRTHDAY,--生日
- }
-
- // 根据ID查询单条数据
- func sqlGetOneTableDataInfo(name string, age int) (TableInfo, error) {
- //根据id查询名称
- var info TableInfo
- sqlStr := `select ID, nvl(NAME,' ') NAME, nvl(AGE,0) AGE, nvl(BIRTHDAY,'') BIRTHDAY from TABLE1 where NAME LIKE :name AND AGE >= :p2 `
- err := oracleDB.QueryRow(sqlStr, "%"+name+"%", age).Scan(&info.ID, &info.Name, &info.Age, &info.Birthday)
- if err != nil {
- if err == sql.ErrNoRows { // 属于空查找
- fmt.Printf("查询 数据失败--没有符合条件的数据: \n ")
- }
- fmt.Printf("查询 数据失败: \nid=%v \nerr=%s", info.ID, err)
- return info, err
- }
- return info, err
- }
-
- // 查询所有数据
- func sqlGetAllTableDataInfo() ([]TableInfo, error) {
- var data []TableInfo
- rows, err := oracleDB.Query("SELECT ID, NAME,AGE,BIRTHDAY FROM TABLE1")
- if err != nil {
- fmt.Printf("查询数据失败:%v \n", err)
- return data, err
- }
- defer rows.Close()
- var info TableInfo
- for rows.Next() { // 游标读取
- info = TableInfo{}
- err = rows.Scan(&info.ID, &info.Name, &info.Age, &info.Birthday)
- if err != nil {
- fmt.Printf("查询数据失败:%v \n", err)
- } else {
- data = append(data, info)
- }
- }
- return data, nil
- }
-
- /*
- ExecSQL 通用语句
- 参数:
- 1. *sql.DB
- 2. SQL语句 例子:UPDATE WX_USER_INFO SET wui_status = 20 WHERE wui_openid = :openid
- 例子:INSERT INTO dbo.ALL_WEIXIN_MSG_LOG (awml_wxid,awml_send_openid,awml_msg_from,awml_msg_body,awml_addtime) VALUES(:wxid,:openid,:msgform,:msgbody,GETDATE())
- 3. SQL语句参数参数 os7Cm1EygxiRF3Tox6FM1z11HiMc ,.........
- 返回受影响行数affect和错误提示消息err
- */
- func ExecSQL(sqlBasis *sql.DB, sqlStr string, args ...interface{}) (int64, error) {
- affect := int64(-1)
- var result sql.Result
- var err error
- result, err = sqlBasis.Exec(sqlStr, args...)
- if err == nil {
- affect, _ = result.RowsAffected()
- }
- if affect == -1 { //执行失败
- err = fmt.Errorf("execSQLStr Err:%s\nsql:%s\ndata:%s", err, sqlStr, fmt.Sprintln(args...))
- }
- return affect, err
- }
-
- /*
- CreateOracleSQLConnV2 用于创建数据库链接
- */
- func CreateOracleSQLConnV2(dbconfigdatastr string) (*sql.DB, error) {
- // 建立 Oracle
- sqldbdata, err := sql.Open("oracle", dbconfigdatastr)
- if err != nil {
- fmt.Printf("sql application pool:%s\n", err)
- return nil, err
- }
- // 测试创建链接是否成功
- err = sqldbdata.Ping()
- dbName := strings.Split(dbconfigdatastr, "/")[2]
- if err != nil {
- fmt.Printf("%s DB Ping err : %s\n", dbName, err)
- defer sqldbdata.Close()
- } else {
- fmt.Printf("The Sql link is successful - %s. \n", dbName)
- }
- return sqldbdata, err
- }