MySQL - 存储过程

MySQL - 存储过程

存储过程简介

MySQL 中的存储过程是一组存储在数据库中的预编译 SQL 语句。可以将其视为编程语言中的一个函数。您可以按名称调用它来执行其中的 SQL 代码，传递参数并接收结果。它们用于封装业务逻辑、提高安全性并减少网络流量。

创建存储过程

要创建存储过程，请使用 CREATE PROCEDURE 语句。逻辑代码放置在 BEGIN 和 END 块之间。

语法

DELIMITER //

CREATE PROCEDURE procedure_name([parameter_mode] parameter_name datatype, ...)
BEGIN
    -- 你的 SQL 语句写在这里
END //

DELIMITER ;

理解 DELIMITER

DELIMITER 命令是一个客户端指令。它将语句结束符从默认的分号（;）更改为其他字符（通常是 // 或 $$）。这是必要的，因为存储过程体本身包含分号。定义完存储过程后，我们需要将分隔符重置回 ;。

示例：一个简单的存储过程

让我们设置一个示例表。我们将使用带有 AUTO_INCREMENT 和适当数据类型的现代表定义。

CREATE TABLE Customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO Customers (name, age, address, salary) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

现在，让我们创建一个存储过程来获取所有年龄大于某个特定值的客户。

DELIMITER //

CREATE PROCEDURE GetCustomersByAge(IN min_age INT)
BEGIN
    SELECT id, name, age, address, salary
    FROM Customers
    WHERE age > min_age;
END //

DELIMITER ;

调用存储过程

您可以使用 CALL 语句执行存储过程。

CALL GetCustomersByAge(25);

这将返回一个结果集，包含年龄大于 25 的客户：

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

存储过程参数（IN, OUT, INOUT）

MySQL 支持存储过程的三种参数模式：

• IN：默认模式。调用程序将一个值传递给存储过程。存储过程可以修改参数的值，但在存储过程返回后，调用者看不到这些改变。
• OUT：存储过程将一个值传回给调用程序。它在存储过程内的初始值为 NULL。
• INOUT：IN 和 OUT 的组合。调用者传入一个值，存储过程可以修改它，修改后的值会传回给调用者。

示例：OUT 参数

此存储过程查找平均工资并通过 OUT 参数返回。

DELIMITER //

CREATE PROCEDURE GetAverageSalary(OUT avg_salary DECIMAL(10, 2))
BEGIN
    SELECT AVG(salary) INTO avg_salary FROM Customers;
END //

DELIMITER ;

要调用它，您必须提供一个会话变量（以 @ 开头）来接收输出值：

CALL GetAverageSalary(@average);
SELECT @average AS average_salary;

示例：INOUT 参数

此存储过程接收一个计数器，递增它，并返回新值。

DELIMITER //

CREATE PROCEDURE IncrementCounter(INOUT counter INT)
BEGIN
    SET counter = counter + 1;
END //

DELIMITER ;

要使用它，您首先需要初始化一个变量，然后调用存储过程：

SET @my_counter = 10;
CALL IncrementCounter(@my_counter);
SELECT @my_counter AS new_value; -- 结果将是 11

删除存储过程

要删除存储过程，请使用 DROP PROCEDURE 语句。最佳实践是包含 IF EXISTS 以避免在存储过程不存在时报错。

DROP PROCEDURE IF EXISTS GetCustomersByAge;

存储过程中的错误处理

健壮的存储过程应该处理潜在错误。您可以使用 DECLARE HANDLER 来捕获特定错误（如 ‘NOT FOUND’ 或 SQL 异常）并执行相应的代码。

示例：处理 ‘Not Found’ 错误

DELIMITER //

CREATE PROCEDURE GetCustomerName(IN cust_id INT, OUT cust_name VARCHAR(100))
BEGIN
    -- 声明一个处理 'NOT FOUND' 条件（SQLSTATE '02000'）的处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET cust_name = 'Customer Not Found';

    -- 尝试获取客户姓名
    SELECT name INTO cust_name FROM Customers WHERE id = cust_id;
END //

DELIMITER ;

现在，用一个不存在的 ID 调用它不会崩溃，而是会返回指定的消息：

CALL GetCustomerName(999, @name);
SELECT @name; -- 结果将是 'Customer Not Found'

优点和缺点

优点

• 网络流量减少：客户端无需发送多个冗长的 SQL 查询，只需发送一个 CALL 语句。只有结果会被传回。
• 安全性提高：您可以授予存储过程 EXECUTE 权限，而无需授予底层表的权限。这限制了直接数据访问并防止 SQL 注入。
• 封装与复用性：复杂的业务逻辑集中存储在数据库中，使其可以在多个应用程序中复用，并且更易于维护。
• 性能：存储过程在创建时会被解析和优化一次，这可能会导致后续调用执行得更快。

缺点

• 可移植性有限：存储过程的语法（特别是对于复杂逻辑）通常是特定于数据库供应商的（例如，MySQL 与 PostgreSQL 与 SQL Server 不同）。
• 调试挑战：调试存储过程可能比调试应用程序代码更困难。虽然有工具可用，但它们可能不够复杂。
• 增加数据库负载：存储过程中繁重的业务逻辑将 CPU 负载从应用服务器转移到数据库服务器。
• 版本控制复杂性：管理存储过程的更改需要一个严格的工作流程，通常使用像 Flyway 或 Liquibase 这样的数据库迁移工具。

安全与最佳实践

• 避免动态 SQL：在存储过程中构建 SQL 字符串可能会重新引入 SQL 注入风险。如果必须使用，请使用预处理语句 (PREPARE、EXECUTE、DEALLOCATE PREPARE)。
• 最小权限原则：仅授予需要 EXECUTE 权限的用户/角色。
• 使用版本控制：将 CREATE PROCEDURE 脚本存储在 Git 或其他版本控制系统 (VCS) 中以跟踪更改。
• 添加注释：在存储过程中记录复杂逻辑以帮助未来的维护。

在客户端应用程序中使用存储过程

现代数据库库提供了调用存储过程的清晰方式。关键是使用 CALL 语句。以下是常见语言的更新示例，展示了现代、安全的实践。

PHP
NodeJS
Java
Python

首先，创建一个用于示例的存储过程：

DELIMITER //
CREATE PROCEDURE CreateCustomer(IN p_name VARCHAR(100), IN p_age INT, OUT p_new_id INT)
BEGIN
    INSERT INTO Customers(name, age) VALUES(p_name, p_age);
    SET p_new_id = LAST_INSERT_ID();
END //
DELIMITER ;

PHP
NodeJS
Java
Python



// 使用 PDO 以获得更好的可移植性和安全性
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 调用存储过程
$name = 'New Customer';
$age = 30;

try {
    // 注意：PDO 不直接支持从单个 CALL 语句中获取 OUT 参数。
    // 一种常见且可靠的方法是使用第二个查询来获取值。
    $stmt = $pdo->prepare("CALL CreateCustomer(?, ?, @new_id)");
    $stmt->execute([$name, $age]);
    $stmt->closeCursor(); // 对某些驱动程序很重要

    $result = $pdo->query("SELECT @new_id AS new_id")->fetch();
    $newId = $result['new_id'];

    echo "Successfully created customer. New ID: {$newId}\n";

} catch (\PDOException $e) {
    echo "Error: " . $e->getMessage();
}




// 使用 mysql2 配合 promises 和 async/await
const mysql = require('mysql2/promise');

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        console.log("Connected to MySQL!");

        // 调用存储过程。mysql2 可以处理 OUT 参数。
        const name = 'Node Customer';
        const age = 42;

        // 结果是一个数组：[results, fields]
        // 对于带有 OUT 参数的 CALL 语句，results 数组包含结果集和 OUT 参数对象。
        const [result] = await connection.execute('CALL CreateCustomer(?, ?, @new_id)', [name, age]);
        const [[outParams]] = await connection.execute('SELECT @new_id as new_id');

        console.log(`Successfully created customer. New ID: ${outParams.new_id}`);

    } catch (error) {
        console.error('Database operation failed:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

main();




// 使用现代 JDBC 配合 try-with-resources
import java.sql.*;

public class StoredProcedureExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String name = "Java Customer";
        int age = 55;

        // 带有 IN 和 OUT 参数占位符的 CALL 语句的 SQL
        String sql = "{CALL CreateCustomer(?, ?, ?)}";

        // try-with-resources 确保连接和语句自动关闭
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             CallableStatement cstmt = conn.prepareCall(sql)) {

            // 设置 IN 参数
            cstmt.setString(1, name);
            cstmt.setInt(2, age);

            // 注册 OUT 参数
            cstmt.registerOutParameter(3, Types.INTEGER);

            // 执行存储过程
            cstmt.execute();

            // 检索 OUT 参数值
            int newId = cstmt.getInt(3);

            System.out.println("Successfully created customer. New ID: " + newId);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




# 使用 mysql-connector-python 配合上下文管理器
import mysql.connector
from mysql.connector import errorcode

def main():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }

    try:
        # `with` 语句确保连接被关闭
        with mysql.connector.connect(**config) as connection:
            print("Connected to MySQL!")
            # `with` 语句确保游标被关闭
            with connection.cursor() as cursor:
                name = "Python Customer"
                age = 28
                args = [name, age, 0] # 0 是 OUT 参数的占位符

                # 调用存储过程
                result_args = cursor.callproc('CreateCustomer', args)

                # 新 ID 在修改后的参数列表中返回
                new_id = result_args[2]
                print(f"Successfully created customer. New ID: {new_id}")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

if __name__ == "__main__":
    main()