|
本学习笔记参考《MySQL必知必会》和官方手册MySQL 5.6 Reference Manual
MySQL入门（一）
MySQL入门（二）
本文内容：
- MySQL存储过程
- MySQL游标
- MySQL触发器
六、MySQL存储过程
6.1 什么是存储过程
简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
6.2 使用存储过程
(1) 创建存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing( )
-> BEGIN
-> SELECT Avg(prod_price) AS priceaverage
-> FROM products;
-> END //
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;
上面的语句创建了一个名为 productpricing 的存储过程，productpricing( ) 的括号里可以加入参数列表，BEGIN 和 END 之间为过程体。由于 MySQL 语句的分隔符为 ; ，而 mysql 命令行实用程序的分隔符也为 ; ，为了避免存储过程体里的 ; 不被 mysql 实用程序解释，解决办法是临时更改命令行实用程序的语句分隔符。DELIMITER // 语句重新定义分隔符为 // ，在创建完存储过程后再用 DELIMITER ; 把分隔符改回来。 (2) 使用存储过程 mysql> CALL productpricing( );
+――――?+
| priceaverage |
+――――?+
| 16.133571 |
+――――?+ (3) 删除存储过程 存储过程在创建之后，就被保存在服务器上以供使用，直至被删除，删除命令如下： mysql> DROP PROCEDURE productpricing;
注意：存储过程名后面没有括号。
如果指定要删除的存储过程存在则删除，如果不存在就会出错。为了使在不存在时也不至于出错可使用这样的语句： mysql> DROP PROCEDURE IF EXISTS productpricing;
(4) 使用参数 mysql> DELIMITER //
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.07 sec)
mysql> DELIMITER ;
此存储过程接收3个参数：pl 存储产品的最低价格，ph 存储产品的最高价格，pa 存储产品的平均价格。每个参数必须有指定的类型，这里使用十进制。关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL 支持 IN （传递给存储过程）、OUT （从存储过程传出）和 INOUT （对存储过程传入和传出）类型的参数。
调用这个存储过程： mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> SELECT @pricelow, @pricehigh, @priceaverage;
+―――?+――――+――――――?+
| @pricelow | @pricehigh | @priceaverage |
+―――?+――――+――――――?+
| 2.50 | 55.00 | 16.13 |
+―――?+――――+――――――?+
所有 MySQL 变量都必须以 @ 开始。 另外一个例子： mysql> DELIMITER //
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 //
onumber 定义为 IN ，因为订单号被传入存储过程。ototal 定义为 OUT ，因为要从存储过程返回合计。 mysql> CALL ordertotal(20005, @total);
mysql> SELECT @total;
+――?+
| @total |
+――?+
| 149.87 |
+――?+ (5) 建立智能存储过程 -- create_procedure.sql
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
DELIMITER //
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 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;
-- And finally, save to out variable
SELECT total INTO ototal;
END//
DELIMITER ;
此存储过程有很大的变动。首先，增加了注释（前面放置 ?）。添加了另外一个参数 taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用 DECLARE 语句定义了两个局部变量。DECLARE 要求指定变量名和数据类型，它也支持可选的默认值。
COMMENT 关键字是可选的，如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示。 mysql> CALL ordertotal(20005, 0, @total);
mysql> SELECT @total;
+――?+
| @total |
+――?+
| 149.87 |
+――?+ mysql> CALL ordertotal(20005, 0, @total);
mysql> SELECT @total;
+――?+
| @total |
+――?+
| 158.86 |
+――?+ (6) 检查存储 |