golang Oracle数据库SQL操作驱动插件库go-oci8的使用

go-oci8简介

Golang Oracle数据库驱动，符合Go database/sql接口规范。

安装步骤

1. 安装Oracle完整客户端或Instant Client

2. 安装C/C++编译器

3. 安装pkg-config，编辑oci8.pc配置文件，然后设置环境变量PKG_CONFIG_PATH指向oci8.pc文件位置 (或者可以使用Go标签noPkgConfig，然后设置环境变量CGO_CFLAGS和CGO_LDFLAGS)

4. 使用Go 1.9或更高版本安装：

go get github.com/mattn/go-oci8

oci8.pc配置示例

Windows配置示例

prefix=/devel/target/XXXXXXXXXXXXXXXXXXXXXXXXXX
exec_prefix=${prefix}
libdir=C:/app/instantclient_12_2/sdk/oci/lib/msvc
includedir=C:/app/instantclient_12_2/sdk/include

glib_genmarshal=glib-genmarshal
gobject_query=gobject-query
glib_mkenums=glib-mkenums

Name: oci8
Description: oci8 library
Libs: -L${libdir} -loci
Cflags: -I${includedir}
Version: 12.2

Linux配置示例

prefix=/devel/target/XXXXXXXXXXXXXXXXXXXXXXXXXX
exec_prefix=${prefix}
libdir=/usr/lib/oracle/12.2/client64/lib
includedir=/usr/include/oracle/12.2/client64

glib_genmarshal=glib-genmarshal
gobject_query=gobject-query
glib_mkenums=glib-mkenums

Name: oci8
Description: oci8 library
Libs: -L${libdir} -lclntsh
Cflags: -I${includedir}
Version: 12.2

MacOS配置示例

prefixdir=/Users/<username>/Downloads/instantclient_12_2/
libdir=${prefixdir}
includedir=${prefixdir}/sdk/include

Name: OCI
Description: Oracle database driver
Version: 12.2
Libs: -L${libdir} -lclntsh
Cflags: -I${includedir}

MacOS还需要设置环境变量：

export LD_LIBRARY_PATH=/Users/<username>/Downloads/instantclient_12_2
export PKG_CONFIG_PATH=/Users/<username>/Downloads/instantclient_12_2

使用示例

基本查询示例

package main

import (
    "database/sql"
    "fmt"
    "log"
    
    _ "github.com/mattn/go-oci8"
)

func main() {
    // 连接Oracle数据库
    db, err := sql.Open("oci8", "user/password@host:port/service_name")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // 执行查询
    rows, err := db.Query("SELECT * FROM employees WHERE employee_id > :1", 100)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    // 处理查询结果
    for rows.Next() {
        var (
            id     int
            name   string
            salary float64
        )
        if err := rows.Scan(&id, &name, &salary); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("ID: %d, Name: %s, Salary: %.2f\n", id, name, salary)
    }
    
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

插入数据示例

package main

import (
    "database/sql"
    "log"
    
    _ "github.com/mattn/go-oci8"
)

func main() {
    db, err := sql.Open("oci8", "user/password@host:port/service_name")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // 准备插入语句
    stmt, err := db.Prepare("INSERT INTO employees (employee_id, name, salary) VALUES (:1, :2, :3)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    
    // 执行插入
    res, err := stmt.Exec(101, "John Doe", 50000.00)
    if err != nil {
        log.Fatal(err)
    }
    
    // 获取影响行数
    rowsAffected, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("Inserted %d rows", rowsAffected)
}

事务处理示例

package main

import (
    "database/sql"
    "log"
    
    _ "github.com/mattn/go-oci8"
)

func main() {
    db, err := sql.Open("oci8", "user/password@host:port/service_name")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // 开始事务
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }
    
    // 执行事务中的操作
    _, err = tx.Exec("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance + 100 WHERE account_id = 2")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }
    
    // 提交事务
    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }
    
    log.Println("Transaction completed successfully")
}

作者

Yasuhiro Matsumoto (a.k.a mattn)

特别感谢

Jamil Djadala