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://127.0.0.1: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'))