?1，golang 連接 oracle 數(shù)據(jù)庫(kù)

?2，增刪改查文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-698759.html

/*
 * @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數(shù)據(jù)庫(kù)

func main() {
	var err error

	// 演示的數(shù)據(jù)庫(kù)表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" ;

	// 傳入數(shù)據(jù)庫(kù)連接字符串
	// 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" // 連接名|服務(wù)名
	username := "system"                  // 用戶名
	password := "123456"                  // 密碼
	sid := "orcl"                         // SID
	connStr := ""
	//連接數(shù)據(jù)庫(kù)的方式
	//方法1:簡(jiǎn)單連接
	connStr = go_ora.BuildUrl(server, port, serviceName, username, password, nil)
	fmt.Println("數(shù)據(jù)庫(kù)鏈接1=>", connStr)
	//方法2:使用SID連接
	urlOptions := map[string]string{
		"SID": sid,
	}
	connStr = go_ora.BuildUrl(server, port, "", username, password, urlOptions)
	fmt.Println("數(shù)據(jù)庫(kù)鏈接2=>", connStr)

	oracleDB, err = CreateOracleSQLConnV2(connStr)
	if err != nil {
		fmt.Printf("連接數(shù)據(jù)庫(kù)失?。?v \n", err)
		return
	}
	// 設(shè)置數(shù)據(jù)庫(kù)鏈接 |  要數(shù)據(jù)庫(kù)連鏈接創(chuàng)建成功 才能走下面的流程

	//增
	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("新增數(shù)據(jù)成功", info)
	} else {
		fmt.Println("新增數(shù)據(jù)失敗", 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("新增數(shù)據(jù)異常", info, err)
	} else {
		if isOK {
			fmt.Println("新增數(shù)據(jù)成功", info)
		} else {
			fmt.Println("新增數(shù)據(jù)失敗", info)
		}
	}

	//根據(jù)名稱和年齡搜查詢單條數(shù)據(jù)
	info, err = sqlGetOneTableDataInfo("明", 18)
	if err != nil {
		fmt.Printf("根據(jù)ID查詢單條數(shù)據(jù)失?。?v \n", err)
		return
	}
	fmt.Println("根據(jù)名稱和年齡搜查詢單條數(shù)據(jù)", info)
	//查詢所有數(shù)據(jù)
	datas, err := sqlGetAllTableDataInfo()
	if err != nil {
		fmt.Printf("查詢所有數(shù)據(jù)失?。?v \n", err)
		return
	}
	fmt.Println("查詢所有數(shù)據(jù)=>", datas)

	info.Name = "老明"
	info.Age = 20
	info.Birthday, _ = time.Parse("2006-01-02", "2003-05-06")
	//改
	if isOK := editTableInfo(info); isOK {
		fmt.Println("編輯數(shù)據(jù)成功", info)
	} else {
		fmt.Println("編輯數(shù)據(jù)失敗", info)
	}

	//查詢所有數(shù)據(jù)
	datas, err = sqlGetAllTableDataInfo()
	if err != nil {
		fmt.Printf("查詢所有數(shù)據(jù)失?。?v", err)
		return
	}
	fmt.Println("查詢所有數(shù)據(jù)=>", datas)
	fmt.Println("刪除數(shù)據(jù)=>", info.ID)
	// 刪除
	deleteTableInfoByID(info.ID)
	fmt.Println()
	//查詢所有數(shù)據(jù)
	datas, err = sqlGetAllTableDataInfo()
	if err != nil {
		fmt.Printf("查詢所有數(shù)據(jù)失?。?v", err)
		return
	}
	fmt.Println("查詢所有數(shù)據(jù)=>", datas)

	//刪除所有數(shù)據(jù)
	deleteTableInfo()
	fmt.Println("刪除表所有數(shù)據(jù)")

	//查詢所有數(shù)據(jù)
	datas, err = sqlGetAllTableDataInfo()
	if err != nil {
		fmt.Printf("查詢所有數(shù)據(jù)失?。?v", err)
		return
	}
	fmt.Println("查詢所有數(shù)據(jù)=>", datas)
}

// 添加表數(shù)據(jù)
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("添加數(shù)據(jù)失?。?v", err)
		return false, err
	}
	return affect > 0, nil
}

// 編輯表數(shù)據(jù)
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("修改數(shù)據(jù)失?。?v", err)
		return false
	}
	return affect > 0
}

// 根據(jù)ID刪除表數(shù)據(jù)
func deleteTableInfoByID(id int) bool {
	affect, err := ExecSQL(oracleDB, "DELETE TABLE1 where ID = :id", id)
	if err != nil {
		fmt.Printf("根據(jù)ID刪除數(shù)據(jù)失?。篿d:%v,%v", id, err)
		return false
	}
	return affect > 0
}

// 刪除表所有數(shù)據(jù)
func deleteTableInfo() bool {
	affect, err := ExecSQL(oracleDB, "DELETE TABLE1 ")
	if err != nil {
		fmt.Printf("刪除所有數(shù)據(jù)失?。?v", err)
		return false
	}
	return affect > 0
}

// 表數(shù)據(jù)結(jié)構(gòu)體
type TableInfo struct {
	ID       int       //id,--ID
	Name     string    //NAME,--名稱
	Age      int       //AGE,--年紀(jì)
	Birthday time.Time //BIRTHDAY,--生日
}

// 根據(jù)ID查詢單條數(shù)據(jù)
func sqlGetOneTableDataInfo(name string, age int) (TableInfo, error) {
	//根據(jù)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("查詢 數(shù)據(jù)失敗--沒(méi)有符合條件的數(shù)據(jù): \n ")
		}
		fmt.Printf("查詢 數(shù)據(jù)失敗: \nid=%v \nerr=%s", info.ID, err)
		return info, err
	}
	return info, err
}

// 查詢所有數(shù)據(jù)
func sqlGetAllTableDataInfo() ([]TableInfo, error) {
	var data []TableInfo
	rows, err := oracleDB.Query("SELECT ID, NAME,AGE,BIRTHDAY FROM TABLE1")
	if err != nil {
		fmt.Printf("查詢數(shù)據(jù)失?。?v \n", err)
		return data, err
	}
	defer rows.Close()
	var info TableInfo
	for rows.Next() { // 游標(biāo)讀取
		info = TableInfo{}
		err = rows.Scan(&info.ID, &info.Name, &info.Age, &info.Birthday)
		if err != nil {
			fmt.Printf("查詢數(shù)據(jù)失?。?v \n", err)
		} else {
			data = append(data, info)
		}
	}
	return data, nil
}

/*
ExecSQL 通用語(yǔ)句

	參數(shù)：
	1. *sql.DB
	2. SQL語(yǔ)句  例子：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語(yǔ)句參數(shù)參數(shù)  os7Cm1EygxiRF3Tox6FM1z11HiMc ,.........
	返回受影響行數(shù)affect和錯(cuò)誤提示消息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 { //執(zhí)行失敗
		err = fmt.Errorf("execSQLStr Err:%s\nsql:%s\ndata:%s", err, sqlStr, fmt.Sprintln(args...))
	}
	return affect, err
}

/*
CreateOracleSQLConnV2 用于創(chuàng)建數(shù)據(jù)庫(kù)鏈接
*/
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
	}
	// 測(cè)試創(chuàng)建鏈接是否成功
	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
}

到了這里，關(guān)于golang 連接 oracle 數(shù)據(jù)庫(kù) 增刪改查的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！