9. 存储过程

俄罗斯刺沙蓬
95 阅读7分钟

什么是存储过程

如果将SQL语句内嵌在应用程序的代码里，将使其混乱且难以维护（修改后需要重新编译），所以应该将SQL代码和应用程序代码分开，将SQL代码储存在所属的数据库中，具体来说，是放在储存过程（stored procedure）和函数中。

存储过程三大作用：

  1. 储存和管理SQL代码 Store and organize SQL
  2. 性能优化 Faster execution
  3. 数据安全 Data security

创建存储过程

DELIMITER $$
create procedure get_clients()
begin
	select * from clients;
end $$
DELIMITER ;

上面就是创建一个存储过程的样例

BEGIN 和 END 之间包裹的是此过程（PROCEDURE）的内容（body），内容里可以有多个语句，但每个语句都要以 ; 结束，包括最后一个。

为了将过程内容内部的语句分隔符与SQL本身执行层面的语句分隔符 ; 区别开，要先用 DELIMITER(分隔符) 关键字暂时将SQL语句的默认分隔符改为其他符号，一般是改成双美元符号 $$ ，创建过程结束后再改回来。注意创建过程本身也是一个完整SQL语句，所以别忘了在END后要加一个暂时语句分隔符 $$

call get_clients()

调用此程序使用call或者use

视图和存储过程结合使用

delimiter $$
create procedure get_invoices_with_balance()
begin
	select *
	from invoices_with_balance		-- 使用之前创建的视图
	where balance > 0;
end$$
delimiter ;

MySQL工作台创建存储过程

也可以用点击的方式创造过程，右键选择 Create Stored Procedure，填空，Apply。这种方式 Workbench 会帮你处理暂时修改分隔符的问题。

删除存储过程

drop procedure if exists get_clients

可以把创建和删除的sql文件放在一起，便于使用。

带参数的存储过程

DELIMITER $$

    CREATE PROCEDURE get_clients_by_state(
		state char(2)
    )
        BEGIN
            SELECT * FROM clients c
            where c.state = state;
        END$$

DELIMITER ;

在创建 存储过程 后面的加上括号，里面写上需要用到的参数，用逗号隔开。 注意，在语句中执行时，调用到的参数不能有命名冲突。

drop procedure if exists get_invoices_by_client;

DELIMITER $$

    CREATE PROCEDURE get_invoices_by_client(
		id int
    )
        BEGIN
            SELECT * FROM clients c
            where c.client_id = id;
        END$$

DELIMITER ;

CALL get_invoices_by_client(2);

带默认参数

drop procedure if exists get_clients_by_state;

DELIMITER $$

    CREATE PROCEDURE get_clients_by_state(
		state char(2)
    )
        BEGIN
			if state is null then
				set state = 'CA';
			end if;
			
            SELECT * FROM clients c
            where c.state = state;
        END$$

DELIMITER ;

CALL get_clients_by_state(null);

可以使用

if state is null then
	set state = 'CA';
end if;

来设置默认参数

但是需要注意，即使这样，也需要在 call 时给实参带上 null 值才可以

这只是一种用 if 实现的方法，if 里面的语句还可以是任何别的语句

还可以使用 ifnull 来实现，更简洁

 BEGIN
	SELECT * FROM clients c
	where c.state = ifnull(state,c.state);
END$$

drop procedure if exists get_payments;

DELIMITER $$

    CREATE PROCEDURE get_payments(
		client_id int(4),
        payment_method_id tinyint(1)
    )
        BEGIN
            SELECT * FROM payments p
            where p.client_id = ifnull(client_id,p.client_id) and 
				p.payment_method = ifnull(payment_method_id,p.payment_method);
        END$$

DELIMITER ;

CALL get_payments(null,null);

参数验证

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
	invoice_id int,
    payment_amount decimal(6,2),
        /*
    9是精度， 2是小数位数。
    精度表示值存储的有效位数，
    小数位数表示小数点后可以存储的位数
    见：
    https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html 
    */
    payment_date date
)
BEGIN
UPDATE invoices i
    SET 
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END

为了防止传入像 -100 的 payment_total 这样不合理的参数，要在增加一段参数验证语句，利用的是条件语句块加SIGNAL关键字，和其他编程语言中的抛出异常等类似。

注意还添加了 MESSAGE_TEXT 以提供给用户参数错误的更具体信息。现在传入 负数的 payment_amount 就会报错 'Error Code: 1644. Invalid payment amount '

……
BEGIN
    IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' 
            SET MESSAGE_TEXT = 'Invalid payment amount';    
    END IF;

    UPDATE invoices i
    ……
END

过犹不及（"Too much of a good thing is a bad thing"），加入过多的参数验证会让代码过于复杂难以维护，像 payment_amount 非空这样的验证就不需要添加因为 payment_amount 字段本身就不允许空值因此MySQL会自动报错，。

参数验证工作更多的应该在应用程序端接受用户输入数据时就检测和报告，那样更快也更有效。储存过程里的参数验证只是在有人越过应用程序直接访问储存过程时作为最后的防线。这里只应该写那些最关键和必要的参数验证。

输出参数

Output Parameters (3:55)

小结

输入参数是用来给过程传入值的，我们也可以用输出参数来获取过程的结果值

具体是在参数的前面加上 OUT 关键字，然后再 SELECT 后加上 INTO……

调用麻烦，如无需要，不要多此一举

案例

创造 get_unpaid_invoices_for_client 过程，获取特定顾客所有未支付过的发票记录（即 payment_total = 0 的发票记录）

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
        client_id INT
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END

调用

call sql_invoicing.get_unpaid_invoices_for_client(3);

得到3号顾客的 COUNT(*) 和 SUM(invoice_total) （未支付过的发票数量和总金额）分别为2和286

我们也可以通过输出参数（变量）来获取这两个结果值，修改过程，添加两个输出参数 invoice_count 和 invoice_total：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
        client_id INT,
        OUT invoice_count INT,
        OUT invoice_total DECIMAL(9, 2)
        -- 默认是输入参数，输出参数要加【OUT】前缀
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoice_count, invoice_total
    -- SELECT后跟上INTO语句将SELECT选出的值传入输出参数（输出变量）中
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END

调用：单击闪电按钮调用，只用输入client_id，得到如下语句结果

set @invoice_count = 0;
set @invoice_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoice_count, @invoice_total);
select @invoice_count, @invoice_total;

先定义以@前缀表示用户变量，将初始值设为0。（变量（variable）简单讲就是储存单一值的对象）再调用过程，将过程结果赋值给这两个输出参数，最后再用SELECT查看。

很明显，通过输出参数获取并读取数据有些麻烦，若无充足的原因，不要多此一举。

本地变量

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`()
BEGIN
    -- 声明三个本地变量，可设默认值，不设置为 null
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    -- 用SELECT得到需要的值并用INTO传入invoices_total和invoices_count
    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices;

    -- 用SET语句给risk_factor计算赋值
    SET risk_factor = invoices_total / invoices_count * 5;

    -- 展示最终结果risk_factor
    SELECT risk_factor;        
END

函数

创建函数和创建过程的两点不同

1. 参数设置和body内容之间，有一段确定返回值类型以及函数属性的语句段
RETURNS INTEGER
DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
……
2. 最后是返回（RETURN）值而不是查询（SELECT）值
RETURN IFNULL(risk_factor, 0);

删除

DROP FUNCTION [IF EXISTS] 函数名

导航

现在已经学了很多内置函数，包括聚合函数和处理数值、文本、日期时间的函数，这一节学习如何创建函数

函数和储存过程的作用非常相似，唯一区别是函数只能返回单一值而不能返回多行多列的结果集，当你只需要返回一个值时就可以创建函数。

案例

在上一节的储存过程 get_risk_factor 的基础上，创建函数 get_risk_factor_for_client，计算特定顾客的 risk_factor

还是用右键 Create Function 来简化创建

创建函数的语法和创建过程的语法极其相似，区别只在两点：

  1. 参数设置和body内容之间，有一段确定返回值类型以及函数属性的语句段

  2. 最后是返回（RETURN）值而不是查询（SELECT）值

另外，关于函数属性的说明：

  1. DETERMINISTIC 决定性的：唯一输入决定唯一输出，和数据的改动更新无关，比如税收是订单总额的10%，则以订单总额为输入税收为输出的函数就是决定性的（？），但这里每个顾客的 risk_factor 会随着其发票记录的增加更新而改变，所以不是DETERMINISTIC的

  2. READS SQL DATA：需要用到 SELECT 语句进行数据读取的函数，几乎所有函数都满足

  3. MODIFIES SQL DATA：函数中有 增删改 或者说有 INSERT DELETE UPDATE 语句，这个例子不满足

CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`
(
    client_id INT
) 
RETURNS INTEGER
-- DETERMINISTIC
READS SQL DATA
-- MODIFIES SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices i
    WHERE i.client_id = client_id;
    -- 注意不再是整体risk_factor而是特定顾客的risk_factor

    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);       
END

有些顾客没有发票记录，NULL乘除结果还是NULL，所以最后用 IFNULL 函数将这些人的 risk_factor 替换为 0

调用案例：

SELECT 
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
    -- 函数当然是可以处理整列的，我第一时间竟只想到传入具体值
    -- 不过这里更像是一行一行的处理，所以应该每次也是传入1个client_id值
FROM clients

删除，还是用DROP

DROP FUNCTION [IF EXISTS] get_risk_factor_for_client

注意

和视图和过程一样，也最好存入SQL文件并加入源码控制，老生常谈了。

avatar
文章
阅读
粉丝