golang calls stored procedure

In Golang, we can use the database/sql package to connect and operate the database. When we need to call a stored procedure, unlike calling ordinary SQL statements, we need to use some special functions provided by the database/sql package to call the stored procedure.

A stored procedure is a precompiled set of SQL statements that can accept parameters. They are usually used to perform some complex data operations, which can improve efficiency and security.

In Golang, the database connection can be established through the Open() method. Then we can use the Exec() or Query() method to execute SQL statements, including stored procedures. However, calling stored procedures requires some special handling.

Below we will introduce how to call stored procedures in Golang.

First, we need to create a connection to the database in Golang. We can use the Open() method provided by the database/sql package to create a connection to the MySQL database, as shown below:

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func main() {

    // Establish a database connection
    db, err := sql.Open("mysql", "user:password@/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Other database code goes here...

}

Next, we need to prepare the SQL statement that calls the stored procedure. In MySQL, we can use the CALL statement to call a stored procedure as follows:

CALL stored_proc(?, ?)

"stored_proc" is the name of the stored procedure, and the question mark (?) represents the parameter. We can not specify the specific value of the parameter here, but specify it later in the code.

After we prepare the SQL statement, we can use the Prepare() method to create a prepared statement object. A prepared statement object is a compiled statement that can be passed in parameters and executed when needed. As shown below:

stmt, err := db.Prepare("CALL stored_proc(?, ?)")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

Next, we need to specify values ​​for the parameters. We can use the Exec() or Query() method of the prepared statement object to execute the SQL statement, as shown below:

res, err := stmt.Exec("value1", "value2")
if err != nil {
    log.Fatal(err)
}

This method executes the stored procedure with the specified parameters and returns the result. The Exec() method returns a sql.Result object, which can be used to check information such as the number of affected rows.

If the stored procedure returns a result set, we can use the Query() method of the prepared statement object, as shown below:

rows, err := stmt.Query("value1", "value2")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

// Process the result set here...

This method executes the stored procedure and returns a result set. We can process the result set as needed.

At the end, we need to close resources such as database connections, prepared statement objects, and result sets as needed, as shown below:

stmt.Close()
rows.Close()
db.Close()

The above are the basic steps for calling stored procedures in Golang. Using prepared statement objects can effectively avoid security issues such as SQL injection, and can also improve efficiency.

In general, calling stored procedures in Golang is not complicated. We can use the prepared statement objects provided by the database/sql package to execute stored procedures. For some situations that require complex data operations, stored procedures can greatly improve efficiency and security.

The above is the detailed content of golang calls stored procedure. For more information, please follow other related articles on the PHP Chinese website!