MySQL 实践之存储过程(下)

上一篇文章中,我们只存储了查询数据的过程,也可以用存储过程来插入、删除、更新数据。

mysql1@2x.jpg

参数验证

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE make_payment
(
  invoice_id INT,
  payment_amount DECIMAL(9, 2),    -- decimal带小数点的数,参数1表示总的数字位数,参数2表示保留小数位
  payment_date DATE
)
BEGIN
  IF payment_amount <= 0 THEN
    SIGNAL SQLSTATE '22003'
    SET MESSAGE_TEXT = 'Invalid payment amount.';
  END IF;
  
  UPDATE invoices i
  SET 
    i.payment_total = payment_amount,
    i.payment_date = payment_date
  WHERE i.invoice_id = invoice_id;
END
1
CALL PROCEDURE make_payment(2, -100, '2019-01-01')

22003是一个标准代码,代表一个数值类型数据超出了范围。👉ibm-sqlstate

当我们调用过程,传入非法的值,会在输出面板看到异常信息。最好在用户输入的时候提示错误,数据库数据验证是最后一道防线,为防止有人绕过了应用程序直接调用存储过程。

输出参数

上面的实践是通过参数向过程提供参数,那么我们如何让在过程外部获得数据(指定参数)?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE PROCEDURE get_invoices_for_client
(
  client_id INT,  -- 默认是输入参数
  OUT invoice_count INT,
  OUT invoices_total DECIMAL(9, 2)  -- 总位数最多为9,保留两位小数
)
BEGIN
  SELECT COUNT(*),SUM(invoice_total)
  INTO invoices_count, invoices_total
  FROM invoices i
  WHERE i.client_id = client_id AND payment_total = 0;
END

调用过程.png

使用输出参数读取数据有点复杂,不推荐使用。

变量

如上图,存储过程有输出变量的时候,通常是这样定义变量:

1
SET @invoices_total = 0

这些变量在整个客户端的生命周期中都会存在于内存中,当客户端中止与数据库的连接,变量会释放,称为用户变量(user or session variables)。

还有一种变量称为本地变量(local variables),通常在存储过程或者函数中定义,只要存储过程运行结束,变量就会被释放了。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE PROCEDURE get_risk_factor
BEGIN
  DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
  DECLARE invoices_count INT;
  DECLARE invoices_total DECIMAL(9, 2);
  
  SELECT COUNT(*), SUM(invoice_total)
  INTO invoices_count, invoices_total
  FROM invoices;
  
  SET risk_factor = invoices_total / invoices_count * 5;
  SELECT risk_factor;
END

这些变量一旦声明就可以使用,一旦执行结束就会被清除。

函数

函数不同于存储过程的是,函数不能返回带行列的结果集,而是返回单一值。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE FUNCTION get_risk_for_client
(
  client_id INT
)
RETURN INTEGER
READ SQL DATA  -- 从数据库读取数据;  MODIFES SQL DATA修改数据
BEGIN
  DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
  DECLARE invoices_count INT;
  DECLARE invoices_total DECIMAL(9, 2);
  
  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

调用

1
SELECT client_id, name, get_risk_for_client(client_id) AS risk_factor FROM client;

删除

1
DROP FUNCTION IF EXISTS get_risk_for_client;
Licensed under CC BY-NC-SA 4.0
Built with Hugo
Theme Stack designed by Jimmy