存储过程是一个包含一堆SQL代码的数据库对象

修改默认分隔符的原因：
在MySQL中创建存储过程时，我们通常需要修改默认的分隔符。原因是MySQL默认使用分号（;）作为SQL语句的结束标志。但在存储过程中，我们可能需要使用多条SQL语句，并且这些语句也是以分号结束的。
如果不修改默认的分隔符，MySQL会在遇到第一个分号时就认为存储过程的定义结束了，从而导致存储过程的创建失败。因此，我们通常在创建存储过程前先设置一个临时的分隔符（例如 $$)，然后在存储过程结束后再将分隔符改回分号。

创建存储过程

1
2
3
4
5
6
7
8
9
10
DELIMITER $$ -- 修改默认分隔符
CREATE PROCEDURE get_invoices_with_balance()
BEGIN 
	SELECT *
    FROM invoices_with_balance; -- 在begin-end中，每一次的查询语句都用；分隔
END$$ -- 使用修改后的默认分隔符来将begin-end中的内容视为一个整体

DELIMITER ;

CALL get_invoices_with_balance() -- 使用call命令调用存储过程

删除存储过程

1
DROP PROCEDURE IF EXISTS get_invoices_with_balance;

创建带参数的存储过程

  • 不带默认值
1
2
3
4
5
6
7
8
9
10
DELIMITER $$

CREATE PROCEDURE get_invoices_by_client(id INT(11))
BEGIN
	SELECT *
    FROM invoices i
    WHERE i.client_id = id;
END$$

DELIMITER ;
  • 带默认值
1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_payments`(
	client_id INT,
    payment_method_id TINYINT
)
BEGIN 
	SELECT *
    FROM payments p
    WHERE p.client_id = IFNULL(client_id,p.client_id) 
    -- 如果提供的client_id为空，则返回所有client_id
    AND p.payment_method = IFNULL(payment_method_id,p.payment_method);
END$$
DELIMITER ;

参数验证

在创建存储过程时加入判断语句，避免更新数据库时插入不合理的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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' -- sqlstate的错误代码，网上可搜
			SET MESSAGE_TEXT = 'Invalid payment amount'
	END IF;
	UPDATE invoices i
	SET 
		i.payment_total = payment_amount,
		i.payment_data = payment_date
	WHERE i.invoice_id = invoice_id;
END $$

DELIMITER ;

输出参数

通过程序得到返回值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER $$
CREATE PROCEDURE get_unpaid_invoice_for_client
(
	client_id INT,
	OUT invoices_count INT, -- 作为输出参数，需要使用OUT进行标记
	OUT invoices_total DECIMAL(9,2)
)
BEGIN
	SELECT COUNT(*),SUM(invoices_total)
	INTO invoices_count,invoices_total -- 将查询值传入输出参数
	FROM invoices i
	WHERE i.client_id = client_id AND payment_total = 0 
END $$
DELIMITER ;

-- 查询
CALL get_unpaid_invoice_for_client(3,@invoices_count,@invoices_total)
-- 查询客户未支付的发票数和发票总金额
SELECT @invoices_count,@invoices_total

变量

变量在mysql中分为两种：

  1. 用户变量：在用户使用mysql期间会被保存，当用户下线时会被清除
    格式：SET @变量名
  2. 本地变量：在存储过程和函数中定义的变量，在执行完毕后会被清除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN 
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0; -- DECLARE 声明变量
	DECLARE invoices_total DECIMAL(9,2);
	DECLARE invoice_count INT;

	SELECT COUNT(*),SUM(invoice_total)
	INTO invoice_count,invoices_total
	FROM invoices;

	SET risk_factor = invoices_total / invoice_count * 5;

	SELECT risk_factor;
END$$
DELIMITER ;

函数

函数不同于存储过程，它只能返回一列
函数的属性：

  1. DETERMINISTIC 确定性，相同的输入对象返回值相同
  2. READ SQL DATA 读取SQL的数据
  3. MODIFIES SQL DATA 修改SQL的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DELIMITER $$
CREATE FUNCTION get_risk_factor_for_client
(
	client_id INT
)
RETURNS INT -- 确定函数返回值的类型
READS SQL DATA -- 确定函数的属性
BEGIN
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0; -- DECLARE 声明变量
	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 ;