MySQL存储过程

存储过程简介

到目前为止，我们已经了解了如何写简单和复杂的查询，了解了如何让通过将查询存储到视图来简化查询，了解了怎么使用sql语句插入、更新和删除数据。

那么，现在假设我们在开发一个应用，应用一个数据库，我们要在哪里写sql语句？首先，我们不会在我们应用的代码里编写sql语句，因为这会使得我们的应用代码很混乱而且难以维护，比如你在使用C#、Python、Java开发应用，你不会想把代码和sql混在一起，sql代码的介入使得我们应用的代码很混乱且难以维护。

此外，一些C#、Java这样的编程语言需要编译工作，所以如果你在应用里写sql查询代码，还需要重新将程序编译部署才能生效，过程十分繁杂且容易出错，每次更新sql代码都需要重新编译。由于以上原因，我们不能将sql代码和应用代码混在一起。

应该将sql代码存储在它应属的数据库里，即在存储过程和函数里去存储sql代码。存储过程是一个包含一堆sql代码的数据库对象，在我们的应用代码里，我们调用这些存储过程里的sql代码来获取和保存数据，所以我们使用存储过程来存储和管理sql代码。

此外，大部分的DBMS可以对存储过程里的代码做优化，因此存储在存储过程里的代码有时候执行起来会更快。

和视图一样，存储过程能加强数据的安全性。例如，我们取消对所有表的直接访问权限，并让很多操作比如插入、更新和删除数据由存储过程来完成，然后指定可以执行存储过程的某一人，这样能够限制用户对我们的数据的操作范围。比如，我们可以防止一些用户删除我们的数据，所以存储过程很强大。

存储过程的创建

CREATE PROCEDURE语句来创建一个存储过程，如果要给存储过程命名，只采用小写英文字母和下划线分割进行命名，这是mysql开发者间的共识。具体如下代码所示：

1
2
3
4
5
6
7
8
DELIMITER $$ -- 这里将分隔符从;换为$$
CREATE PROCEDURE get_clients()
-- BEGIN和END之间的内容称为存储过程的主体(body)，主体中每条sql语句都要用";"分隔，这时mysql指定的，其他dbms就不需要管了。
BEGIN
	SELECT * FROM clients;
END$$
-- $$之间引起来的整体就是要上传给mysql的存储过程
DELIMITER ; -- 这里表示换回去

当然，我们也可以这样写存储过程，这样更简便些，上面的就当是拓展了。

1
2
3
4
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END;

我们可以通过这个语句来调用对应的存储过程来执行：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> CALL get_clients();
+-----------+-------------+--------------------------+---------------+-------+--------------+
| client_id | name        | address                  | city          | state | phone        |
+-----------+-------------+--------------------------+---------------+-------+--------------+
|         1 | Vinte       | 3 Nevada Parkway         | Syracuse      | NY    | 315-252-7305 |
|         2 | Myworks     | 34267 Glendale Parkway   | Huntington    | WV    | 304-659-1170 |
|         3 | Yadel       | 096 Pawling Parkway      | San Francisco | CA    | 415-144-6037 |
|         4 | Kwideo      | 81674 Westerfield Circle | Waco          | TX    | 254-750-0784 |
|         5 | Topiclounge | 0863 Farmco Road         | Portland      | OR    | 971-888-9129 |
+-----------+-------------+--------------------------+---------------+-------+--------------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

通常，在开发中，我们会用C#、Java、Python来调用对应的存储过程。

练习

1.通过(invoice_total - payment_total)我们可以得到结余balance，选取balance大于0的发票，并将这个过程存储到存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from invoices;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |          0.00 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
|          2 | 03-898-6735 |         5 |          8.18 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-16   |
|          3 | 20-228-0335 |         5 |          0.00 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |          0.00 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-07   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |          0.00 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-15   |
|         13 | 41-666-1035 |         5 |         87.44 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-30   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-19   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-19   |
|         18 | 52-269-9803 |         5 |         42.77 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-12   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
17 rows in set (0.00 sec)

Result

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance1()
BEGIN
	SELECT *
	FROM
		(SELECT * 
			, invoice_total - payment_total as balance
		FROM invoices) B
	WHERE balance > 0;
END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> CALL get_invoices_with_balance();
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+---------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date | balance |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+---------+
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |  152.21 |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-07   |   83.23 |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |  133.87 |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |  189.12 |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |  159.50 |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-15   |  126.12 |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-19   |   86.98 |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |  162.02 |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-19   |   58.28 |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |  134.47 |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+---------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

使用Mysql工作台创建存储过程

有时，你可能会觉得通过sql代码来写存储过程过于繁琐，我们还可以通过Mysql的工作台来创建，这里我们使用的是Navicate，接下来看步骤：

1.第一步

2.第二步

3.第三步，直接在里面写查询语句即可，写完后保存运行即可。

删除存储过程

我们可以通过如下语句删除存储过程，语句中的IF EXISTS是为了避免我们要删除的存储过程不存在而报错，达到只有在要删除的存储过程存在才会删除的效果，使得我们的程序更加安全。

1
DROP PROCEDURE IF EXISTS (get_invoices_with_balance);

带参数的存储过程

如何在存储过程中添加参数，我们一般使用参数为存储过程传递值，但我们也可以使用参数为调用程序赋值。下面写一个传参的存储过程案例。

演示代码

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state
(
	state CHAR(2), -- CA/NY
	parameters type, -- delete when exec
	... -- delete when exec
) -- 创建一个返回传入参数条件对应的用户的存储过程
BEGIN
	SELECT * 
	FROM clients c
	WHERE c.state = state; -- 要注意传入参数state和表中本身字段state不要重复，可以给表加别名或者修改参数名来区分
END$$
DELIMITER ;

练习1

原表

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM clients LIMIT 5;
+-----------+-------------+--------------------------+---------------+-------+--------------+
| client_id | name        | address                  | city          | state | phone        |
+-----------+-------------+--------------------------+---------------+-------+--------------+
|         1 | Vinte       | 3 Nevada Parkway         | Syracuse      | NY    | 315-252-7305 |
|         2 | Myworks     | 34267 Glendale Parkway   | Huntington    | WV    | 304-659-1170 |
|         3 | Yadel       | 096 Pawling Parkway      | San Francisco | CA    | 415-144-6037 |
|         4 | Kwideo      | 81674 Westerfield Circle | Waco          | TX    | 254-750-0784 |
|         5 | Topiclounge | 0863 Farmco Road         | Portland      | OR    | 971-888-9129 |
+-----------+-------------+--------------------------+---------------+-------+--------------+
5 rows in set (0.00 sec)

存储过程

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state
(
	state CHAR(2)-- CA/NY
) -- 创建一个返回传入参数条件对应的用户的存储过程
BEGIN
	SELECT * 
	FROM clients c
	WHERE c.state = state; -- 要注意传入参数state和表中本身字段state不要重复，可以给表加别名或者修改参数名来区分
END$$
DELIMITER ;

结果

1
2
3
4
5
6
7
8
9
10
mysql> CALL get_clients_by_state("NY")
    -> ;
+-----------+-------+------------------+----------+-------+--------------+
| client_id | name  | address          | city     | state | phone        |
+-----------+-------+------------------+----------+-------+--------------+
|         1 | Vinte | 3 Nevada Parkway | Syracuse | NY    | 315-252-7305 |
+-----------+-------+------------------+----------+-------+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

如果我们不给带参数的存储过程传递参数，程序就会报错，因为在mysql中，所有系数都是必填的！

练习2

创建带参数存储过程

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$ 
CREATE PROCEDURE get_invoices_by_clientid
(
	id INT
) 
BEGIN
	SELECT * 
	FROM invoices i
	WHERE i.client_id = id;
END$$
DELIMITER ;

查询结果

1
2
3
4
5
6
7
8
9
mysql> CALL get_invoices_by_clientid(2);
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |          0.00 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

带默认值的参数存储过程

这里用代码来展示更好理解，代码实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state_default
(
	state CHAR(2)-- CA/NY
) -- 创建一个返回传入参数条件对应的用户的存储过程
BEGIN

	IF state IS NULL THEN 
		SET state = 'CA'; -- 如果参数未传递，则用默认参数CA
	END IF; -- IF...END IF 语句
	 
	SELECT * 
	FROM clients c
	WHERE c.state = state; -- 要注意传入参数state和表中本身字段state不要重复，可以给表加别名或者修改参数名来区分
END$$
DELIMITER ; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> use sql_invoicing;
Database changed
mysql> CALL get_clients_by_state_default(); -- 这里要注意，mysql所有参数都是必填的！
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE sql_invoicing.get_clients_by_state_default; expected 1, got 0

mysql> CALL get_clients_by_state_default(NULL);
+-----------+-------+---------------------+---------------+-------+--------------+
| client_id | name  | address             | city          | state | phone        |
+-----------+-------+---------------------+---------------+-------+--------------+
|         3 | Yadel | 096 Pawling Parkway | San Francisco | CA    | 415-144-6037 |
+-----------+-------+---------------------+---------------+-------+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

我们还可以这样写这段存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state_default
(
	state CHAR(2)-- CA/NY
) -- 创建一个返回传入参数条件对应的用户的存储过程
BEGIN

	IF state IS NULL THEN 
		SELECT * FROM clients c;
	ELSE
		SELECT * 
		FROM clients c
		WHERE c.state = state;
	END IF;
	
END$$
DELIMITER ;

当然，还能更简化一点:

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state_default2
(
	state CHAR(2)-- CA/NY
) -- 创建一个返回传入参数条件对应的用户的存储过程
BEGIN

		SELECT * FROM clients c
		-- IFNULL函数
		WHERE c.state = IFNULL(state, c.state); -- 当查询的state为null时，我们用c.state代替，即返回所有数据
	
END$$
DELIMITER ; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CALL get_clients_by_state_default2(null);
+-----------+-------------+--------------------------+---------------+-------+--------------+
| client_id | name        | address                  | city          | state | phone        |
+-----------+-------------+--------------------------+---------------+-------+--------------+
|         1 | Vinte       | 3 Nevada Parkway         | Syracuse      | NY    | 315-252-7305 |
|         2 | Myworks     | 34267 Glendale Parkway   | Huntington    | WV    | 304-659-1170 |
|         3 | Yadel       | 096 Pawling Parkway      | San Francisco | CA    | 415-144-6037 |
|         4 | Kwideo      | 81674 Westerfield Circle | Waco          | TX    | 254-750-0784 |
|         5 | Topiclounge | 0863 Farmco Road         | Portland      | OR    | 971-888-9129 |
+-----------+-------------+--------------------------+---------------+-------+--------------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL get_clients_by_state_default2('CA');
+-----------+-------+---------------------+---------------+-------+--------------+
| client_id | name  | address             | city          | state | phone        |
+-----------+-------+---------------------+---------------+-------+--------------+
|         3 | Yadel | 096 Pawling Parkway | San Francisco | CA    | 415-144-6037 |
+-----------+-------+---------------------+---------------+-------+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

练习

写一个传入两个参数的存储过程进行查询。

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 PROCEDURE get_payments
(
	id INT,-- client id
	payment_method_id TINYINT -- payment_method_id
) 
BEGIN

		SELECT
			c.name,
			c.client_id,
			`address`,
			`city`,
			`state`,
			pm.`name` as payment_type,
			pm.payment_method_id
		FROM clients c
		JOIN payments p USING (client_id)
		JOIN payment_methods pm ON pm.payment_method_id = p.payment_method
		WHERE client_id = IFNULL(id,c.client_id) AND pm.payment_method_id = IFNULL(payment_method_id,pm.payment_method_id);
	
END$$
DELIMITER ;

结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> CALL get_payments(2, 1);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_payments(2, 2);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_payments(3, 2);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_payments(3, 1);
+-------+-----------+---------------------+---------------+-------+--------------+-------------------+
| name  | client_id | address             | city          | state | payment_type | payment_method_id |
+-------+-----------+---------------------+---------------+-------+--------------+-------------------+
| Yadel |         3 | 096 Pawling Parkway | San Francisco | CA    | Credit Card  |                 1 |
| Yadel |         3 | 096 Pawling Parkway | San Francisco | CA    | Credit Card  |                 1 |
| Yadel |         3 | 096 Pawling Parkway | San Francisco | CA    | Credit Card  |                 1 |
+-------+-----------+---------------------+---------------+-------+--------------+-------------------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL get_payments1(NULL, 2);
+-------------+-----------+------------------+----------+-------+--------------+-------------------+
| name        | client_id | address          | city     | state | payment_type | payment_method_id |
+-------------+-----------+------------------+----------+-------+--------------+-------------------+
| Topiclounge |         5 | 0863 Farmco Road | Portland | OR    | Cash         |                 2 |
+-------------+-----------+------------------+----------+-------+--------------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

参数验证

用存储过程进行插入、更新、删除数据的时候，我们需要对传入参数进行验证，确保不会传入错误的数据。

现在来看一个练习，对Invoices进行更新，数据展示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> use sql_invoicing;
Database changed
mysql> select * from invoices;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |          0.00 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
|          2 | 03-898-6735 |         5 |          8.18 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-16   |
|          3 | 20-228-0335 |         5 |          0.00 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |          0.00 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-07   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |          0.00 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-15   |
|         13 | 41-666-1035 |         5 |         87.44 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-30   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-19   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-19   |
|         18 | 52-269-9803 |         5 |         42.77 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-12   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
17 rows in set (0.00 sec)

具体代码实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE make_payment
(
	invoice_id INT,
	payment_amount DECIMAL(9, 2), -- 9位数，包括两位小数
	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;

对invoice id为2的发票进行修改，这里先查询：

1
2
3
4
5
6
7
mysql> SELECT * FROM invoices WHERE invoice_id = 2;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          2 | 03-898-6735 |         5 |          8.18 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-16   |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
1 row in set (0.00 sec)

如果你是在终端进行修改，是不会提示传入参数。如果是在mysql工作台运行存储过程，会跳出提示传入参数，手动填入即可：

提示窗口

运行返回结果

1
2
3
4
5
6
7
mysql> SELECT * FROM invoices WHERE invoice_id = 2;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          2 | 03-898-6735 |         5 |          8.18 |        100.00 | 2019-06-11   | 2019-07-01 | 2020-09-10   |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
1 row in set (0.00 sec)

可以看到修改成功。

但是以上修改并没有进行对传入参数的验证，如果我传入-100，显然这是不合理的，具体修改加入验证的代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE make_payment1
(
	invoice_id INT,
	payment_amount DECIMAL(9, 2), -- 9位数，包括两位小数
	payment_date DATE
)
BEGIN
	IF payment_amount <= 0 THEN
		SIGNAL SQLSTATE '22003' -- signal在这里相当于抛出异常，返回对应的异常状态sqlstate代码值
			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;

具体sql报错码可以在IBM的对应网址里查看：

返回参数

要想让存储过程将查询到的结果返回赋值给两个变量，我们可以通过如下代码逻辑来实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE get_payment2
(
	invoice_id INT,
	OUT payment_amount DECIMAL(9, 2), -- OUT标记代表输出
	OUT payment_date DATE
)
BEGIN
	IF invoice_id <= 0 THEN
		SIGNAL SQLSTATE '22003' -- signal在这里相当于抛出异常，返回对应的异常状态sqlstate代码值
			SET MESSAGE_TEXT = 'INVALID INVOICE ID'; -- 设置返回提醒信息
	END IF;
	
	SELECT 
		i.payment_total,
		i.payment_date
	INTO -- 将查询结果赋给要返回的变量值中
		payment_amount,
		payment_date
	FROM invoices i
	WHERE i.invoice_id= invoice_id;
END;

为了执行上面的存储过程，我们需要定义两个变量来存储查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SET @payment_amount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @payment_date = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_payment2(2, @payment_amount, @payment_date);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @payment_amount, @payment_date;
+-----------------+---------------+
| @payment_amount | @payment_date |
+-----------------+---------------+
|          100.00 | 2020-09-10    |
+-----------------+---------------+
1 row in set (0.00 sec)

我们定义了两个变量来存储存储过程给我们返回的查询结果，通过select将变量中的值给查询出来。

用户定义变量，是用来存储单一值的对象，用@符号作为前缀来定义变量。

变量

在上面内容中我们已经涉及到了变量，上面涉及到的变量称为用户定义变量或会话变量，这些变量会在会话整个过程中一直保存，丢失会话后就丢失，比如mysql窗口关闭后，这些变量就丢失了。当然，mysql还有一种叫做本地变量的变量，存储在存储过程或函数中的变量，存储过程或函数执行完毕，这些变量就会丢失，我们常用这类变量存储存储过程或函数计算过程中的中间变量。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE risk_factor()
BEGIN
	DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0; -- 申明本地变量
	DECLARE invoices_total DECIMAL(9, 2);
	DECLARE invoice_count INT;
	
	SELECT 
		COUNT(*),
		SUM(invoice_total)
	INTO 
		invoice_count,
		invoices_total
	FROM invoices i;
	
	SET risk_factor = invoices_total/invoice_count * 5;
	
	SELECT risk_factor;
	
END;

结果：

1
2
3
4
5
6
7
8
9
mysql> CALL risk_factor();
+-------------+
| risk_factor |
+-------------+
|      484.46 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

函数

前面我们介绍了日期、字符串、聚合（MIN、MAX）函数，现在开始介绍如何创建自己的函数。函数和存储过程的主要区别在于函数只能返回单一值。

函数的属性：（returns后面要紧跟着设置函数属性每个mysql函数必须有至少一个属性）

  • DETERMINISTIC : 给函数相同值他永远返回一样的值（mysql中的data会变）
  • READS SQL DATA : 函数中会配置选择语句用以读取数据
  • MODIFIES SQL DATA :函数中有对数据进行更新删除插入的操作需要定义属性

函数可以有多种属性并存。

函数申明代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE FUNCTION get_client_risk
(
	-- 括号里定义我们的参数，比如客户id，类型INT
	client_id INT
	
)
RETURNS integer -- return明确了
-- returns后面要紧跟着设置函数属性每个mysql函数必须有至少一个属性
-- DETERMINISTIC -- 给函数相同值他永远返回一样的值（mysql中的data会变）
READS SQL DATA -- : 另一个属性，函数中会配置选择语句用以读取数据
 -- MODIFIES SQL DATA :函数中有对数据进行更新删除插入的操作
 -- 函数可以有多种属性并存。
BEGIN
	#Routine body goes here...
	DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0; -- 申明本地变量
	DECLARE invoices_total DECIMAL(9, 2);
	DECLARE invoice_count INT;
	
	SELECT 
		COUNT(*),
		SUM(invoice_total)
	INTO 
		invoice_count,
		invoices_total
	FROM invoices i
	WHERE i.client_id = client_id;
	
	SET risk_factor = invoices_total/invoice_count * 5;
	
	RETURN risk_factor;
	
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP FUNCTION IF EXISTS get_client_risk

DELIMITER $$ 
CREATE FUNCTION get_client_risk
(
	client_id INT
)
RETURNS INTEGER
READS SQL DATA
BEGIN
	DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
	DECLARE invoices_total DECIMAL(9, 2);
	DECLARE invoice_count INT;
	
	SELECT 
		COUNT(*),
		SUM(i.invoice_total)
	INTO 
		invoice_count,
		invoices_total
	FROM invoices i
	WHERE i.client_id = client_id;
	
	SET risk_factor = invoices_total/invoice_count * 5;
	
	RETURN risk_factor;
END $$
DELIMITER ;

执行函数：

1
2
3
4
5
SELECT
	client_id,
	name,
	get_client_risk(client_id)
FROM clients;

结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT
    ->  client_id,
    ->  name,
    ->  get_client_risk(client_id)
    -> FROM clients;
+-----------+-------------+----------------------------+
| client_id | name        | get_client_risk(client_id) |
+-----------+-------------+----------------------------+
|         1 | Vinte       |                        803 |
|         2 | Myworks     |                          0 |
|         3 | Yadel       |                        706 |
|         4 | Kwideo      |                       NULL |
|         5 | Topiclounge |                        115 |
+-----------+-------------+----------------------------+
5 rows in set (0.00 sec)