SQL 入门 13：SQL 存储过程与函数：封装逻辑与参数处理

讲存储过程和函数，帮你把 SQL 查询打包，省事还能复用。跟着注释走，边看边搞。

学习内容

1. 存储过程（Stored Procedure）
• 一堆 SQL 语句打包，存在数据库里，干特定活儿。
• 优点：编译好直接调，效率高，复用方便。
• 用 DELIMITER $$ 改分隔符，防止过程里的 ; 搞乱。BEGIN 和 END 包住主体，每条语句得加 ;。

 

1. 调用与删除
• 用 CALL 跑存储过程。
• 删除用 DROP PROCEDURE，加 IF EXISTS 防报错。
2. 带参数的存储过程
• 支持输入参数（IN）、输出参数（OUT）。
• 可以设默认值或用 IFNULL 处理空值。
3. 参数验证
• 用 IF 和 SIGNAL SQLSTATE 校验参数，扔自定义错误。
• 示例：检查付款金额 ≤ 0，报错代码 '22003'（数值超范围）。
4. 变量
• 用户会话变量：SET @var = value，整个会话有效。
• 局部变量：DECLARE 在存储过程内定义，仅过程内用。
5. 函数（FUNCTION）
• 类似存储过程，但返回单个值，用 RETURNS 指定类型。
• 特性声明：READS SQL DATA（只读）、DETERMINISTIC（同输入同输出，动态数据可不用）。
• 用在 SELECT 里，像内置函数。

---

示例代码与讲解

1. 创建存储过程

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END $$
DELIMITER ;

• 简单例子，查所有客户。
• DELIMITER $$ 改分隔符，防止 ; 误判。END $$ 结束定义，DELIMITER ; 恢复。
• 跑完没输出，但在数据库里多个存储过程（见图片，暂缺）。

CALL get_clients();

• 调用过程，输出客户表。

2. 删除存储过程

DROP PROCEDURE get_clients;

• 直接删。

DROP PROCEDURE IF EXISTS get_clients;

• 加 IF EXISTS，删不存在的也不报错。

3. 带参数的存储过程

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(p_state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.state = p_state;
END $$
DELIMITER ;

• 按州查客户，p_state 是输入参数。

CALL get_clients_by_state('CA');

• 查 CA 州的客户，必须传参，不然报错。

4. 带默认值的存储过程

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(p_state CHAR(2))
BEGIN
    IF p_state IS NULL THEN
        SET p_state = 'CA';
    END IF;
    SELECT * FROM clients c
    WHERE c.state = p_state;
END $$
DELIMITER ;

• 没传 p_state 就默认 CA。

CALL get_clients_by_state(NULL);

• 传 NULL，输出 CA 客户。

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(p_state CHAR(2))
BEGIN
    IF p_state IS NULL THEN
        SELECT * FROM clients;
    ELSE
        SELECT * FROM clients c
        WHERE c.client_id = p_state;
    END IF;
END $$
DELIMITER ;

• 空值查所有客户，不然按 client_id 查。

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(p_state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.client_id = IFNULL(p_state, client_id);
END $$
DELIMITER ;

• 用 IFNULL 简化，空值时 client_id 匹配自身，查所有。

5. 参数验证

DELIMITER $$
CREATE PROCEDURE make_payment(
    p_invoices_id INT,
    p_payment_amount DECIMAL(9,2),
    p_payment_date DATE
)
BEGIN
    IF p_payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003'
        SET MESSAGE_TEXT = 'Invalid payment amount';
    END IF;
    UPDATE invoices i
    SET i.payment_total = p_payment_amount,
        i.payment_date = p_payment_date
    WHERE i.invoice_id = p_invoices_id;
END $$
DELIMITER ;

• 录入付款，DECIMAL(9,2) 是 7 位整数 + 2 位小数。
• 金额 ≤ 0 就报错，22003 表示数值超范围

CALL make_payment(2, -100, '2019-01-01');

• 负金额触发错误。

6. 输出参数

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client(
    p_client_id INT,
    OUT invoices_count INT,
    OUT invoices_total DECIMAL(9,2)
)
BEGIN
    SELECT COUNT(*), SUM(i.invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = p_client_id AND i.payment_total = 0;
END $$
DELIMITER ;

• 查客户未付发票数量和总额，输出到 invoices_count 和 invoices_total。

SET @invoices_count = 0;
SET @invoices_total = 0;
CALL get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
SELECT @invoices_count, @invoices_total;

• 定义会话变量，调用过程，查 client_id = 3 的结果。

7. 变量

SET @variable_name = 0;

• 会话变量，全程可用。

DELIMITER $$
CREATE PROCEDURE get_risk()
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices;
    SET risk_factor = invoices_total / invoices_count * 5;
    SELECT risk_factor;
END $$
DELIMITER ;

• 局部变量 DECLARE 定义，算风险系数（平均发票额 * 5）。

8. 函数

DELIMITER $$
CREATE FUNCTION count_risk_for_client(client_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id;
    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);
END $$
DELIMITER ;

• 函数算客户风险值，返回整数。
• READS SQL DATA 表示只读，动态数据不需 DETERMINISTIC。

SELECT client_id, name, count_risk_for_client(client_id) AS risk
FROM clients;

• 像内置函数一样用，列出客户风险值。

---

作业

1. 视图存储过程

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
    SELECT * FROM invoices_with_balance
    WHERE Balance > 0;
END $$
DELIMITER ;

• 从 invoices_with_balance 视图查未付发票。

2. 带参数存储过程

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client(p_client INT)
BEGIN
    SELECT * FROM invoices
    WHERE invoice_id = p_client;
END $$
DELIMITER ;
CALL get_invoices_by_client(8);

• 查指定 invoice_id 的发票。

3. 默认值与多参数

DELIMITER $$
CREATE PROCEDURE get_payment(p_client_id INT, p_pay_method_id TINYINT)
BEGIN
    SELECT p.client_id, pm.name
    FROM payments p
    JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
    WHERE p.client_id = IFNULL(p_client_id, p.client_id)
    AND p.payment_method = IFNULL(p_pay_method_id, p.payment_method);
END $$
DELIMITER ;
CALL get_payment(5, 4);

• 查客户和支付方式，参数为空时返回所有。

---

总结

讲了存储过程的创建、调用、参数、验证、变量，还搞了个函数，方便复用逻辑。用的 sql_invoicing 数据库。接下来看触发器和事务。

常见的错误对应代码：