Using Oracle in Go: A Complete Guide

Using Oracle in Go Language: A Complete Guide

Oracle database is one of the industry's leading relational databases and is highly praised for its data security and reliability. Many companies are using Oracle databases to save and manage massive amounts of data. Now, with the rise and application of Go language, more and more people are beginning to explore how to use Oracle in Go language.

Oracle officially provides a driver suitable for the Go language, namely "go-oci8". In this article, we'll cover how to install the driver, initialize the connection, and perform query, delete, and insert operations.

Install Oracle driver

First, we need to install the go-oci8 driver. The driver calls the Oracle database client library through CGO, so you need to check whether the Oracle client library is installed before installation.

Download the installation package: https://oracle.github.io/odpi/doc/installation.html#linux

The installation process is very simple, just unzip and run the installation script. After completing the installation, you can install the go-oci8 driver. Use the following command:

go get -v -u gopkg.in/goracle.v2

Initialize the connection

After the driver is installed and configured, we can connect to the Oracle database in the Go language. You can use the following code to initialize the connection:

package main

import (
    "database/sql"
    "fmt"
    _ "gopkg.in/goracle.v2"
)

func main() {
    // 格式：[账号]/[密码]@[主机名]:[端口号]/[SID]
    connectionString := "scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
    db, err := sql.Open("goracle", connectionString)
    if err != nil {
        fmt.Println("Connection Failed : ", err)
        return
    }
    defer db.Close()
    fmt.Println("Connected successfully")
}

We need to provide the connection string of the Oracle database. The connection string contains the following: username, password, host (or IP address), port, and database SID. If you are not familiar with Oracle connection strings, you can refer to the official Oracle documentation for more information.

After running the above code, if the connection is successful, "Connected successfully" will be output.

Query operation

The following is a sample code for querying the Oracle database:

package main

import (
    "database/sql"
    "fmt"
    _ "gopkg.in/goracle.v2"
)

func main() {
    // Initialize connection
    connectionString := "scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
    db, err := sql.Open("goracle", connectionString)
    if err != nil {
        fmt.Println("Connection Failed : ", err)
        return
    }
    defer db.Close()

    // Execute SQL query
    rows, err := db.Query("SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = :1", 10)
    if err != nil {
        fmt.Println("Error executing SQL query: ", err)
        return
    }
    defer rows.Close()

    // Process rows
    for rows.Next() {
        var empno int
        var ename string
        var sal int
        if err := rows.Scan(&empno, &ename, &sal); err != nil {
            fmt.Println("Error scanning row: ", err)
            return
        }
        fmt.Println(empno, ename, sal)
    }
}

This code queries all employee information with department number 10 in the employee table. Note that named parameters are used in the query. If you need to use positional parameters, use "?" instead of ":1".

Update operation

The following is a sample code to update the Oracle database:

package main

import (
    "database/sql"
    "fmt"
    _ "gopkg.in/goracle.v2"
)

func main() {
    // Initialize connection
    connectionString := "scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
    db, err := sql.Open("goracle", connectionString)
    if err != nil {
        fmt.Println("Connection Failed : ", err)
        return
    }
    defer db.Close()

    // Execute SQL update
    result, err := db.Exec("UPDATE EMP SET SAL = :1 WHERE EMPNO = :2", 10000, 7369)
    if err != nil {
        fmt.Println("Error executing SQL update: ", err)
        return
    }
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        fmt.Println("Error getting affected rows: ", err)
        return
    }
    fmt.Println("Rows affected:", rowsAffected)
}

This code updates the salary of employee number 7369 in the employee table to 10000.

Insert operation

The following is a sample code to insert a record in the Oracle database:

package main

import (
    "database/sql"
    "fmt"
    _ "gopkg.in/goracle.v2"
)

func main() {
    // Initialize connection
    connectionString := "scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
    db, err := sql.Open("goracle", connectionString)
    if err != nil {
        fmt.Println("Connection Failed : ", err)
        return
    }
    defer db.Close()

    // Execute SQL insert
    result, err := db.Exec("INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)", 1000, "Test", "Test", 7369, "01-JAN-00", 10000, 0, 10)
    if err != nil {
        fmt.Println("Error executing SQL insert: ", err)
        return
    }
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        fmt.Println("Error getting affected rows: ", err)
        return
    }
    fmt.Println("Rows affected:", rowsAffected)
}

This code inserts a new record into the employees table.

Summary

This article introduces how to use Oracle database in Go language. We learned how to initialize a connection, perform queries, updates, and insert operations. Additionally, we cover how to install the necessary drivers. The above method is useful if your application needs to interact with Oracle database.

The above is the detailed content of Using Oracle in Go: A Complete Guide. For more information, please follow other related articles on the PHP Chinese website!