参考:《MySQL必知必会》Ben Forta著,第23章 使用存储过程,第24章 使用游标
存储过程
考虑以下的情形:
为了处理订单,需要核对以保证库存中有相应的物品。
如果库存有物品,这些物品需要预定以便不将它们再卖给别的人,并且要减少可用的物品数量以反映正确的库存量。
库存中没有的物品需要订购,这需要与供应商进行某种交互。
关于哪些物品入库(并且可以立即发货)和哪些物品退订,需要通知相应的客户。
执行这些处理需要针对许多表的多条MySQL语句。此外,需要执行的具体语句及其次序也不是固定的,它们可能会(和将)根据哪些物品在库存中哪些不在而变化。 => 可以创建存储过程。
存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合,可将其视为批文件(作用不限于批处理)。
为什么要使用存储过程
简单、安全、高性能
1)通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。
2)由于不要求反复建立一系列处理步骤,这保证了数据的完整性。
=> 这一点的延伸就是防止错误,保证数据的一致性。
3)简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。
=> 这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据错误的机会。
4)提高性能。因为使用存储过程比使用单独的SQL语句要快。
使用存储过程
MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。存储过程可以显示结果,也可以不显示结果。 => 有点像函数~
1.创建存储过程
【例】一个返回产品平均价格的存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
-> BEGIN
-> SELECT AVG(prod_price) AS priceaverage FROM products;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
注意:如果使用的是mysql命令行实用程序,
默认的MySQL语句分隔符为";"。mysql命令行实用程序也使用";"作为语句分隔符。
如果命令行实用程序要解释存储过程自身内的";"字符,会出现如下句法错误。
mysql> CREATE PROCEDURE productpricing()
-> BEGIN
-> SELECT AVG(prod_price) AS priceaverage FROM products;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql>
解决办法是临时更改命令行实用程序的语句分隔符,除"\"符号外,任何字符都可以用作语句分隔符。
DELIMITER // => (注意有空格)告诉命令行实用程序使用//作为新的语句结束分隔符
这样,存储过程体内的 ";" 仍然保持不动,并且正确地传递给数据库引擎
最后,为恢复为原来的语句分隔符,可使用 "DELIMITER ;"
2.执行存储过程
mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.删除存储过程
mysql> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.00 sec)
mysql>
注意:DROP只给出存储过程名,没有使用后面的括号
4.使用参数
IN 后的变量类似函数传入的参数
OUT后的变量类似函数的返回值
【例】如下存储过程接受3个参数:pl存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。
mysql> CREATE PROCEDURE productpricing(
-> OUT pl DECIMAL(8,2),
-> OUT ph DECIMAL(8,2),
-> OUT pa DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Min(prod_price)
-> INTO pl
-> FROM products;
-> SELECT Max(prod_price)
-> INTO ph
-> FROM products;
-> SELECT Avg(prod_price)
-> INTO pa
-> FROM products;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql>
说明:每个参数必须具有指定的类型,这里使用十进制值。
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。
存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)。
调用该存储过程需要指定3个变量名,注意:MySQL变量以@开头
mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage)//
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT @pricelow, @pricehigh, @priceaverage//
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
| 2.50 | 55.00 | 16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)
【例】同时使用IN和OUT参数
mysql> CREATE PROCEDURE ordertotal(
-> IN onumber INT,
-> OUT ototal DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber
-> INTO ototal;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> CALL ordertotal(20005,@total)//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @total//
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
mysql>
mysql> DELIMITER ;
检查存储过程
为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句,
eg: SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PROCEDURE STATUS 列出所有存储过程。
可使用LIKE指定一个过滤模式限制输出,
eg: SHOW PROCEDURE STATUS LIKE 'ordertotal';
mysql> SHOW CREATE PROCEDURE ordertotal\G;
*************************** 1. row ***************************
Procedure: ordertotal
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber
INTO ototal;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> SHOW PROCEDURE STATUS LIKE 'ordertotal'\G;
*************************** 1. row ***************************
Db: supply
Name: ordertotal
Type: PROCEDURE
Definer: root@localhost
Modified: 2020-03-28 16:45:34
Created: 2020-03-28 16:45:34
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
建立智能存储过程
考虑这个场景 -> 获得订单合计,且需要对合计增加营业税,不过只针对某些顾客。那么,你需要做下面几件事情:
- 获得合计;
- 把营业税有条件地添加到合计;
- 返回合计(带或不带税)。
【例】ordertotal.sql内容如下,包含存储过程ordertotal,使用命令 ./mysql -uroot -p < ordertotal.sql 导入。
DELIMITER //
USE supply//
DROP PROCEDURE IF EXISTS ordertotal//
-- Name ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare variable for tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
-- Is this taxable?
IF taxable THEN
-- Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- Finally, save to out variable
SELECT total INTO ototal;
END//
DELIMITER ;
说明:
参数taxable是一个布尔值(如果要增加税则为真,否则为假)。
COMMENT关键字 不是必需的,如果给出,将在SHOW PROCEDURE STATUS的结果中显示。
在存储过程体中,用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的taxrate的默认被设置为6)。
IF语句检查taxable是否为真,如果为真,则用另一SELECT语句增加营业税到局部变量total。
最后,用另一SELECT语句将total(它增加或许不增加营业税)保存到ototal。
登录数据库验证,订单数为20005的商品的总计金额 不含营业税 vs 包含营业税 =>
mysql> CALL ordertotal(20005,0,@total);SELECT @total;
Query OK, 1 row affected (0.00 sec)
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
mysql> CALL ordertotal(20005,1,@total);SELECT @total;
Query OK, 1 row affected (0.00 sec)
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)
mysql>
游标
游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。
在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
MySQL游标只能用于存储过程(和函数)。
使用游标
DECLARE 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
OPEN 声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
FETCH 对于填有数据的游标,根据需要取出(检索)各行。
CLOSE 在结束游标使用时,必须关闭游标。
【例1】创建一个存储过程,在其中声明一个游标,打开游标,取一次数据,再关闭游标
=> 从结果可以看出取了第一行的数据
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS processorders//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE PROCEDURE processorders(
-> OUT o INT
-> )
-> BEGIN
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
->
-> OPEN ordernumbers;
-> FETCH ordernumbers INTO o;
-> CLOSE ordernumbers;
->
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> CALL processorders(@onum);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @onum;
+-------+
| @onum |
+-------+
| 20005 |
+-------+
1 row in set (0.00 sec)
mysql>
【例2】创建一个存储过程,在其中声明一个游标,打开游标,取三次数据,再关闭游标
=> 从结果可以看出从第一行开始,逐行取了三次数据
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS processorders//
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE processorders(
-> OUT o1 INT,
-> OUT o2 INT,
-> OUT o3 INT
-> )
-> BEGIN
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
->
-> OPEN ordernumbers;
-> FETCH ordernumbers INTO o1;
-> FETCH ordernumbers INTO o2;
-> FETCH ordernumbers INTO o3;
-> CLOSE ordernumbers;
->
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL processorders(@onum1,@onum2,@onum3);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @onum1,@onum2,@onum3;
+--------+--------+--------+
| @onum1 | @onum2 | @onum3 |
+--------+--------+--------+
| 20005 | 20009 | 20006 |
+--------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
6 rows in set (0.00 sec)
mysql>
【例3】processorders.sql内容如下,包含游标ordernumbers,使用命令 ./mysql -uroot -p < processorders.sql 导入。
DELIMITER //
USE supply//
DROP PROCEDURE IF EXISTS processorders//
CREATE PROCEDURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8,2));
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- Get the total for this order
CALL ordertotal(o,1,t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END//
DELIMITER ;
说明:
1.CONTINUE HANDLER 是在条件出现时被执行的代码。这里,它指出当SQLSTATE '02000’出现时,SET done=1。
SQLSTATE '02000’是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。
2.FETCH取每个order_num,然后用CALL执行另一个存储过程ordertotal来计算每个订单的带税的合计(结果存储到t)。
最后,用INSERT保存每个订单的订单号和合计。此存储过程不返回数据,但会创建新表并填充数据。
比较调用存储过程processorders前后,创建了新表ordertotals =>
mysql> SHOW TABLES;
+--------------------+
| Tables_in_supply |
+--------------------+
| customeremaillist |
| customers |
| orderitems |
| orderitemsexpanded |
| orders |
| productcustomers |
| productnotes |
| products |
| vendorlocations |
| vendors |
+--------------------+
10 rows in set (0.00 sec)
mysql> CALL processorders();
Query OK, 1 row affected (0.32 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_supply |
+--------------------+
| customeremaillist |
| customers |
| orderitems |
| orderitemsexpanded |
| orders |
| ordertotals |
| productcustomers |
| productnotes |
| products |
| vendorlocations |
| vendors |
+--------------------+
11 rows in set (0.00 sec)
查看表ordertotals的内容 =>
mysql> select * from ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
6 rows in set (0.00 sec)
mysql>