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;