MySQL - 儲存過程

MySQL 儲存過程

MySQL 的儲存過程是一組預編譯的 SQL 語句，可以隨時重複使用。儲存過程可以用於執行不同的資料庫操作，例如插入、更新或刪除資料。

語法

在 MySQL 中建立儲存過程的基本語法如下：

DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name parameter_datatype)
BEGIN
    -- SQL statements to be executed
END //
DELIMITER;

其中，

• CREATE PROCEDURE 語句用於建立過程。
• 需要執行的 SQL 語句放置在 BEGIN 和 END 關鍵字之間。

建立過程

我們可以使用以下步驟在 MySQL 中建立儲存過程：

• 為過程選擇一個名稱。
• 編寫過程的 SQL 查詢。
• 使用不同的引數執行過程。

示例

為了理解儲存過程，讓我們考慮 CUSTOMERS 表，該表包含客戶的個人詳細資訊，包括他們的 ID、姓名、年齡、地址和薪水，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

現在使用 INSERT 語句將值插入此表，如下所示：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

獲得的 CUSTOMERS 表如下：

現在，讓我們建立一個名為 'GetCustomerInfo' 的過程，該過程沒有任何引數，用於檢索 CUSTOMERS 表中年齡大於 25 的所有記錄：

DELIMITER //
CREATE PROCEDURE GetCustomerInfo()
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE > 25;
END //

驗證

要驗證更改，我們使用 CALL 命令執行過程，如下面的查詢所示：

CALL GetCustomerInfo(); //

產生的結果如下：

儲存過程引數型別

儲存過程可以具有不同型別的引數，這些引數用於確定在執行期間將傳遞的值。以下是 SQL 中不同型別的儲存過程引數：

• 輸入引數 - 這些引數用於將值從呼叫程式或使用者傳遞到儲存過程。
• 輸出引數 - 這些引數用於將值從儲存過程返回到呼叫程式或使用者。
• 輸入/輸出引數 - 這些引數允許儲存過程接受輸入值並返回值。
• 表值引數 - 這些引數用於將表變數作為引數傳遞到儲存過程。
• 預設引數 - 這些引數用於指定如果未為引數傳遞任何值，則將使用的預設值。
• 遊標引數 - 這些引數用於將遊標傳遞到儲存過程。
• 輸出 XML 引數 - 這些引數用於從儲存過程返回值 XML 資料。

現在，讓我們看一下 SQL 中一些最常見的儲存過程引數型別：

帶 IN 引數的過程

IN 引數是預設引數，用於接收來自呼叫程式的輸入值。該值在過程執行時傳遞。

示例

在以下查詢中，我們正在建立一個儲存過程，該過程將客戶的 ID 作為輸入引數，並返回該客戶的詳細資訊。

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
END //

驗證

要執行儲存過程併為“CustomerAge”引數傳遞值，我們將使用 CALL 命令，如下所示：

CALL GetCustomerInfo(23); //

以下是上述程式碼的輸出：

帶 OUT 引數的過程

OUT 引數用於將輸出值傳送到呼叫程式。在建立過程時，必須為輸出引數指定OUT關鍵字。在呼叫時，以“@”為字首的變數用於儲存返回值。

然後，我們可以對變數使用 SELECT 語句來顯示過程的輸出。

示例

在以下查詢中，我們正在建立一個儲存過程，該過程將客戶的 ID 作為輸入引數，並使用輸出引數“Cust_Salary”返回該客戶的 SALARY。

DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID INT, OUT Cust_Salary DECIMAL(18,2))
BEGIN
SELECT Salary INTO Cust_Salary FROM CUSTOMERS WHERE ID = CustomerID;
END //
DELIMITER ;

驗證

要驗證建立的過程的工作原理，我們使用 CALL 命令呼叫它：

CALL GetCustomerSalary(3, @S);

輸出值將儲存在傳遞的“@S”引數中；使用 SELECT 語句進一步顯示，如下所示：

SELECT @S AS SALARY;

我們得到如下所示的輸出：

帶 INOUT 引數的過程

INOUT 引數是 IN 和 OUT 引數的組合，用於在一個引數中傳遞和接收來自儲存過程的資料。

INOUT關鍵字用於在儲存過程中宣告 INOUT 引數。

示例

在以下查詢中，我們使用 salary 作為 INOUT 引數來輸入薪水並儲存過程返回的輸出。

儲存過程使用 IN 引數 cust_id 檢索客戶的當前薪水。然後，它將薪水增加 10%，並將增加後的薪水儲存在 INOUT 引數 salary 中：

DELIMITER //
CREATE PROCEDURE increaseSalary(IN cust_id INT, INOUT salary DECIMAL(18, 2))
BEGIN
SELECT SALARY INTO salary FROM CUSTOMERS WHERE ID = cust_id;
SET salary = salary * 1.1;
UPDATE CUSTOMERS SET SALARY = @salary WHERE ID = cust_id;
END //
DELIMITER ;

驗證

在呼叫過程之前，我們必須使用以下 SET 命令設定 INOUT 引數的輸入值：

SET @salary = 50000.00;

現在，我們透過使用以下語句呼叫過程來檢查是否檢索了輸出值：

CALL increaseSalary(7, @salary);

要驗證結果，我們使用以下 SELECT 語句檢索儲存在 INOUT 引數中的最終增加後的薪水：

SELECT @salary as INCREASED_SALARY;

獲得的輸出如下：

請注意，這些查詢不會對原始表進行任何更改，因為我們將輸出儲存在變數中。

刪除儲存過程

我們可以使用**DROP PROCEDURE**語句刪除儲存過程。

語法

以下是刪除SQL中儲存過程的基本語法：

DROP PROCEDURE [IF EXISTS] procedure_name;

示例

在下面的示例中，我們刪除了一個名為“GetCustomerSalary”的儲存過程：

DROP PROCEDURE IF EXISTS GetCustomerSalary;

輸出

執行上述程式碼後，我們將獲得以下輸出：

Query OK, 0 rows affected (0.01 sec)

儲存過程的優點

以下是儲存過程的優點：

• **效能提升** - 儲存過程是預編譯的，因此它們可以比典型的SQL語句更快地執行。
• **程式碼重用** - 儲存過程可以從不同的客戶端應用程式中呼叫，從而實現程式碼的可重用性。
• **減少網路流量** - 儲存過程在伺服器上執行，只將結果返回給客戶端，從而減少網路流量並提高應用程式效能。
• **安全性增強** - 儲存過程可用於實施安全規則並防止未經授權訪問敏感資料。
• **簡化維護** - 儲存過程透過將SQL程式碼儲存在單個位置來簡化程式碼維護。

儲存過程的缺點

以下是儲存過程的缺點：

• **增加開銷** - 當頻繁使用或用於複雜操作時，儲存過程比簡單的SQL語句消耗更多的伺服器資源。
• **可移植性有限** - 儲存過程不能輕易地從一個數據庫管理系統(DBMS)遷移到另一個數據庫管理系統，因為它們通常特定於某個DBMS。
• **除錯挑戰** - 當涉及多層程式碼時，儲存過程很難除錯。
• **安全風險** - 如果儲存過程用於訪問敏感資料，則會帶來安全風險。

使用客戶端程式的儲存過程

我們還可以使用客戶端程式執行儲存過程。

語法

$sql = "DELIMITER &&
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
    BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
    END &&
 DELIMITER";
$mysqli->query($sql);

var sqlProcedure =
`CREATE PROCEDURE GetCustomerInfo()
BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE > 25;
END;`
con.query(sql)

String sql  "CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END";
statement.executeQuery(sql);

stored_procedure = "
CREATE PROCEDURE GetCustomerInfo()
BEGIN
SELECT * FROM CUSTOMERS WHERE AGE > 25;
END "
cursorObj.execute(literal_query)

示例

以下是程式：

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
//let's create table
$sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID))";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!\n");
}
//lets insert some records
$sql = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 )";
if($mysqli->query($sql)){
    printf("Second record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (3, 'kaushik', 23, 'Kota', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully....!\n");
}
$sql = "INSERT INTO CUSTOMERS VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 )";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully....!\n");
}
//display table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE %d, ADDRESS %s, SALARY %f", 
            $row['ID'],
            $row['NAME'], 
            $row['AGE'], 
            $row['ADDRESS'], 
            $row['SALARY']);
            printf("\n");
    }
}
//let's create procedure
$sql = "DELIMITER &&
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
    BEGIN
    SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
    END &&
 DELIMITER";
if($sql){
    printf("Stored procedure created successfully...!\n");
}
//now lets verify
$sql = "CALL GetCustomerInfo(25)";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Customers table created successfully...!
First record inserted successfully....!
Second record inserted successfully....!
Third record inserted successfully....!
Fourth record inserted successfully....!
Table records:
ID: 1, NAME: Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID: 2, NAME: Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID: 3, NAME: kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID: 4, NAME: Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
Stored procedure created successfully...!
Table records:
Array
(
    [0] => 2
    [ID] => 2
    [1] => Khilan
    [NAME] => Khilan
    [2] => 25
    [AGE] => 25
    [3] => Delhi
    [ADDRESS] => Delhi
    [4] => 1500.00
    [SALARY] => 1500.00
)
Array
(
    [0] => 4
    [ID] => 4
    [1] => Chaitali
    [NAME] => Chaitali
    [2] => 25
    [AGE] => 25
    [3] => Mumbai
    [ADDRESS] => Mumbai
    [4] => 6500.00
    [SALARY] => 6500.00
)       

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //Creating CUSTOMERS table
    sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
    con.query(sql);

    sql = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),(2, 'Khilan', 25, 'Delhi', 1500.00),(3, 'kaushik', 23, 'Kota', 2000.00 ),(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik', 27, 'Bhopal', 8500.00 ),(6, 'Komal', 22, 'MP', 4500.00 ),(7, 'Muffy', 24, 'Indore', 10000.00 );"
    con.query(sql);

    //Displaying records of CUSTOMERS table
    sql = "SELECT * FROM CUSTOMERS;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records of CUSTOMERS");
      console.log(result);
      console.log("--------------------------");
    });

    //Creating a Procedure
    var sqlProcedure = `
    CREATE PROCEDURE GetCustomerInfo()
    BEGIN
        SELECT * FROM CUSTOMERS WHERE AGE > 25;
    END;
    `
    con.query(sqlProcedure);

    sql = "CALL GetCustomerInfo();"
    con.query(sql, function(err, result){
      console.log("Verification");
      if (err) throw err
      console.log(result)
    });
});    

 
Connected!
--------------------------
Records of CUSTOMERS
[
  {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
  {ID: 2,NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'},
  {ID: 3,NAME: 'kaushik', AGE: 23,ADDRESS: 'Kota', SALARY: '2000.00'},
  {ID: 4, NAME: 'Chaitali', AGE: 25,ADDRESS: 'Mumbai',SALARY: '6500.00'},
  {ID: 5,NAME: 'Hardik',AGE: 27,ADDRESS: 'Bhopal',SALARY: '8500.00'},
  {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {ID: 7,NAME: 'Muffy',AGE: 24,ADDRESS: 'Indore',SALARY: '10000.00'}
]
--------------------------
Verification
[
  [
    {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'},
    {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'}
  ],
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    info: '',
    serverStatus: 34,
    warningStatus: 0,
    changedRows: 0
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StoredProcedure {
    public static void main(String[] args) {
        String url = "jdbc:mysql://:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //creating a table 
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID))";
            st.execute(sql);
            System.out.println("Table Customers created successfully....!");
            //let's insert some records into it...
            String sql1 = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ), (2, 'Khilan', 25, 'Delhi', 1500.00 ), (3, 'kaushik', 23, 'Kota', 2000.00 ), (4, 'Chaitali', 25, 'Mumbai', 6500.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //not let's create stored procedure
            String sql3 = "CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END";
            st.execute(sql3);
            System.out.println("Stored procedure created successfully....!");
            //verify by calling the procedure
            String sql4 = "CALL GetCustomerInfo(25)";
            rs = st.executeQuery(sql4);
            System.out.println("Procedure called successfully...!");
            rs = st.executeQuery(sql4);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Table Customers created successfully....!
Records inserted successfully....!
Table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Stored procedure created successfully....!
Procedure called successfully...!
Table records: 
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00    

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'CUSTOMERS'
create_table_query = '''
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25),
SALARY DECIMAL(18, 2),
PRIMARY KEY (ID)
);
'''
cursorObj.execute(create_table_query)
print("Table 'CUSTOMERS' is created successfully!")
# Inserting records into 'CUSTOMERS' table
sql = "INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
values = [
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'MP', 4500.00),
    (7, 'Muffy', 24, 'Indore', 10000.00)
]
cursorObj.executemany(sql, values)
# Printing the records from the 'CUSTOMERS' table
select_query = "SELECT * FROM CUSTOMERS;"
cursorObj.execute(select_query)
records = cursorObj.fetchall()
print("\nCustomers table records:")
for record in records:
    print(record)
# Creating a stored procedure
stored_procedure = """
CREATE PROCEDURE GetCustomerInfo()
BEGIN
SELECT * FROM CUSTOMERS WHERE AGE > 25;
END 
"""
cursorObj.execute(stored_procedure)
print("\nStored procedure 'GetCustomerInfo' created successfully!")
# Call the stored procedure
show_functions = "CALL GetCustomerInfo();"
cursorObj.execute(show_functions)
functions = cursorObj.fetchall()
# Print the information about stored functions
print("\nStored Procedure Results:")
for function in functions:
    print(function)
# Closing the cursor and connection
cursorObj.close()
connection.close()    

Table 'CUSTOMERS' is created successfully!

Customers table records:
(1, 'Ramesh', 32, 'Ahmedabad', Decimal('2000.00'))
(2, 'Khilan', 25, 'Delhi', Decimal('1500.00'))
(3, 'kaushik', 23, 'Kota', Decimal('2000.00'))
(4, 'Chaitali', 25, 'Mumbai', Decimal('6500.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))
(6, 'Komal', 22, 'MP', Decimal('4500.00'))
(7, 'Muffy', 24, 'Indore', Decimal('10000.00'))

Stored procedure 'GetCustomerInfo' created successfully!

Stored Procedure Results:
(1, 'Ramesh', 32, 'Ahmedabad', Decimal('2000.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))