MySQL 学习笔记（六） 存储过程

MySQL学习笔记 存储过程

Stored Procedure 存储过程

存储过程就是在数据库中保存代码块的对象。在应用代码中，我们可以直接调用存储过程来对数据进行增删改。其好处是：

1. 存储并组织 SQL 语句
2. 有更快的执行能力
3. 有一定的数据安全性
4. 能限制用户对数据的操作

**如果没有选择输出，存储过程将会选定一条 SELECT 语句输出，也就是选最后一条 SELECT 语句。**接下来我们开始学习√

CREATE PROCEDURE 创建存储过程

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

DELIMITER ;

注意：

1. 在MySQL的 BEGIN 和 END 代码块中无论有几条语句都要使用 ; 来结束语句。
2. 如果使用了 ; 作为 BEGIN 和 END 代码块中的结束语句的符号，就不得不改变整个代码的结束语句的符号。所以我们在开头使用 DELIMITER 关键词改变结束语句的符号为 $$ (推荐，改成其他符号也没问题，只要不冲突)。
3. 最后还要再把符号改回去。
4. 综上所述的只是针对MySQL，其他管理系统比如 SQL Server 中，我们是不用修改分隔符的。

DROP PROCEDURE 删除存储过程

DROP PROCEDURE IF EXISTS get_clients;

CALL Procedure 调用存储过程

CALL get_clients();

这是在 MySQL 中调用的办法，但是我们一般在程序中调用（C#、Java、Python等）。

Parameters 在创建存储过程中传递参数

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(
	state CHAR(2) -- 两个长度的字符类型
)
BEGIN
	SELECT * FROM clients c
	WHERE c.state = state;
END$$

DELIMITER ;

-- 调用
CALL get_clients_by_state('CA');

1. 参数格式：名字 + 参数类型 。
2. 如果重名，一般给表一个别名。

使用案例

DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment(
	invoice_id INT,
    payment_amount DECIMAL(9, 2),
    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$$

DELIMITER ;

IF 在存储过程中写逻辑

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; -- 告诉 MySQL IF 语句结束了
		
	SELECT * FROM clients c
	WHERE c.state = state;
END$$

DELIMITER ;

SIGNAL SQLSTATE 在存储过程中验证数据

DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment(
	invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
	IF payment_amount <= 0 THEN
		SIGNAL SQLSTATE '22003' -- 可以到 MySQL 官网查看错误代码大全
			SET MESSAGE_TEXT = 'Invalid payment amount';
			
	UPDATE invoices i
	SET
		i.payment_total = payment_amount,
		i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END$$

DELIMITER ;

OUT 在存储过程中选择返回结果 (不建议使用，除非有明确理由)

DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client(
	client_id INT,
	OUT invoices_count INT,
    OUT invoices_total DECIMAL(9, 2)
)
BEGIN
	SELECT COUNT(*), SUM(invoice_total)
	INTO invoices_count, invoices_total -- 读取了SELCET的数据并存储在两个参数中
	FROM invoices i 
	WHERE i.client_id = client_id
		AND payment_total = 0;
END$$

DELIMITER ;

1. 使用了 OUT 关键词后，存储过程只会输出带有 OUT 关键词的数据。
2. 同时，还得在 SELECT 语句中使用 INTO 关键词把要输出的参数传递进去（按顺序来赋值）。
3. 要接受带有 OUT 关键词的存储过程的结果，要新建变量来接受。
4. 这些变量在整个客户端 session 周期中都存在于内存中，当客户端终止与数据库的连接，这些变量也会释放。
5. 通常这些变量被称为 User or Session Variable，用 SET 语句来定义，前面前缀 @ 符号。
6. 相对于还有 Local Variable，用 DECLARE 语句来定义，是在存储过程或函数中定义的，这些本地变量是不会在 session 周期中保存在内存中的，随着存储过程或函数的结束而释放。

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

DROP PROCEDURE IF EXISTS get_risk_factor;

DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN
	DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0; -- 设置默认值为0，否则将是NULL
	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; -- 只会输出这一条，因为存储过程只会选定一条 SELECT 语句输出，也就是选最后一条 SELECT 语句
END