MySQL - 存储过程

• 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] procedures_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...!
");
}
//lets insert some records
$sql = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully....!
");
}
$sql = "INSERT INTO CUSTOMERS VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 )";
if($mysqli->query($sql)){
    printf("Second record inserted successfully....!
");
}
$sql = "INSERT INTO CUSTOMERS VALUES (3, 'kaushik', 23, 'Kota', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully....!
");
}
$sql = "INSERT INTO CUSTOMERS VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 )";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully....!
");
}
//display table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Table records: 
");
    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("
");
    }
}
//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...!
");
}
//now lets verify
$sql = "CALL GetCustomerInfo(25)";
if($result = $mysqli->query($sql)){
    printf("Table records: 
");
    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"
});

// 连接到 MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
   // 创建新数据库
    sql = "Create Database TUTORIALS";
    con.query(sql);
   sql = "USE TUTORIALS";
    con.query(sql);
   //创建 CUSTOMERS 表
    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);
   //显示CUSTOMERS表的记录
    sql = "SELECT * FROM CUSTOMERS;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records of CUSTOMERS");
      console.log(result);
      console.log("--------------------------");
    });
   //创建一个过程
    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://localhost: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....!");
            //让我们在其中插入一些记录...
            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....!");
            //打印表记录
            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);
            }
            //不要创建存储过程
            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....!");
            //通过调用过程进行验证
            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
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 创建表"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!")
# 将记录插入"CUSTOMERS"表
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)
# 打印"CUSTOMERS"表中的记录
select_query = "SELECT * FROM CUSTOMERS;"
cursorObj.execute(select_query)
records = cursorObj.fetchall()
print("
Customers table records:")
for record in records:
    print(record)
# 创建存储过程
stored_procedure = """
CREATE PROCEDURE GetCustomerInfo()
BEGIN
SELECT * FROM CUSTOMERS WHERE AGE > 25;
END
"""
cursorObj.execute(stored_procedure)
print("
Stored procedure 'GetCustomerInfo' created successfully!")
# 调用存储过程
show_functions = "CALL GetCustomerInfo();"
cursorObj.execute(show_functions)
functions = cursorObj.fetchall()
# 打印存储函数的信息
print("
Stored Procedure Results:")
for function in functions:
    print(function)
# 关闭游标和连接
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'))