golang executes stored procedure

Golang is a static programming language for building simple, efficient, and scalable software. It is increasingly popular in enterprise application development due to its simplicity, readability, and fast performance. However, like other languages, Golang needs to be connected to the database. The addition, deletion, modification and query of data itself is not a problem, but the call of stored procedures may become a challenge. In this article, we will introduce how to call stored procedures in Golang.

A stored procedure is a set of SQL statements stored and managed in a relational database. It has input parameters, output parameters and return values, and is a simple database function. In order to execute a stored procedure, we need to understand the basic structure of a stored procedure and its calling method in Golang. Next, we will show how to connect to a MySQL database and call stored procedures in Golang.

1. Install go-sql-driver/mysql package

To start using Golang to connect to the MySQL database, we need to import the necessary packages from the database driver. Here, we have used the go-sql-driver/mysql package as the MySQL database driver. We can install the go-sql-driver/mysql package using the following command:

go get -u github.com/go-sql-driver/mysql

2. Connect to MySQL database

Before calling the stored procedure from Golang, we must connect to the MySQL database. The following is a sample program that uses the go-sql-driver/mysql package to connect to a MySQL database:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/testdb")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err.Error())
    }

    fmt.Println("Connected to MySQL database.")
}

This sample program uses the sql.Open() function to open a MySQL database connection. The second parameter is the connection string, which includes username, password, IP address and port, and the name of the database that needs to be connected. Use the defer db.Close() statement to close the database connection after the code block ends.

After the connection is successful, we should use the db.Ping() function to verify whether the database connection is normal.

3. Execute a stored procedure with input parameters and return values

The following is a sample program that uses golang and MySQL to execute a stored procedure together. In this sample program, we will call a stored procedure that takes input parameters and output parameters with return values.

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {

    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/testdb")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    storedProc := "CALL sp_get_customer_by_email(?, ?)"
    email := "test@test.com"
    var name string

    rows, err := db.Query(storedProc, email, &name)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&name)
        if err != nil {
            log.Fatal(err)
        }
    }

    fmt.Println("Name of the customer with given email is ", name)
}

In the above example, we first open the MySQL database connection and then declare the variables and input parameters for calling the stored procedure. In this example, the stored procedure name is sp_get_customer_by_email, with two input parameters (i.e. email address and name variable of string type) and an output variable (i.e. return value). We pass the email address as an input variable to the stored procedure through the function db.Query().

After the stored procedure is started, the stored procedure will return the query results in the name variable. Finally, we print the value returned by the function and close the database connection. In this example, we can see that the return value of the stored procedure is stored in the variable name.

Summary

Calling stored procedures is a necessary step, and it is also indispensable for enterprise applications developed through Golang. The sample program we introduced shows how to use the go-sql-driver/mysql package to connect to a MySQL database, and how to interact with Golang using stored procedures with input parameters and return values. Now that we have a better understanding of the basics of executing stored procedures in Golang, we can apply it to the next project we develop.

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