Mysql存储过程与游标

创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
   ...

END

如果在命令行客户端执行，需要先更改语句分割符，DELIMITER // (除\外，任何字符都可以用做语句分割符), 存储过程以END//结束，完成后再设置回来DELIMITER ;

调用存储过程
CALL productpricing();
查看存储过程创建语句
SHOW CREATE PROCEDURE productpricing;
查看数据库的所有存储过程
SHOW PROCEDURE STATUS;
删除存储过程

DROP PROCEDURE productpricing IF EXISTS;//存在时删除

使用参数，IN：输入  OUT：输出    INOUT：输入输出，对于参数的类型，表中使用的都可以，记录集不允许，不能通过一个参数返回多个行和列。所有Mysql局部变量需要使用@开头。如下：

DROP PROCEDURE productpricing IF EXISTS;//存在时删除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;调用CALL productpricing(@pricelow, @pricehigh, @priceaverage);查看结果：mysql> SELECT @pricelow, @pricehigh, @priceaverage;+-----------+------------+---------------+| @pricelow | @pricehigh | @priceaverage |+-----------+------------+---------------+|      2.50 |      55.00 |         16.13 |+-----------+------------+---------------+

再如以下例子：

ordertotal1:对某些合计增加营业稅，只有部分，需要1获得合计2把营业税有条件的添加到合计3返回合计（带或不带稅）--Name: ordertotal1--Params: onumber = order number--        taxable = 0 if not taxable, 1 if taxable--        ototal = order total variableCREATE PROCEDURE ordertotal1(   IN onumber INT,   IN taxable BOOLEAN,   OUT ototal DECIMAL(8,2)) COMMENT 'Obtain order total, optionally add tax'BEGIN   DECLARE total DECIMAL(8,2);   DECLARE taxrate INT DEFAULT 6;      SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;      IF taxable THEN      SELECT total + (total/100*taxrate) INTO total;   END IF;      SELECT total INTO ototal;END;调用：mysql> CALL ordertotal1(20005, 0, @total);Query OK, 1 row affected (0.00 sec)mysql> SELECT @total;+--------+| @total |+--------+| 149.87 |+--------+mysql> CALL ordertotal1(20005, 1, @total);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> SELECT @total;+--------+| @total |+--------+| 158.86 |+--------+

COMMIT值注释，不是必需的，在SHOW PROCEDURE STATUS显示

游标：

       游标是一个存储在MySQL服务器上的数据库查询，不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。Mysql中只能用于存储过程。

        使用游标的几个步骤：

        1 使用游标前，必须声明（定义）它，这个过程没有检索数据，只是定义要使用的select语句

        2 如果要使用，必须打开游标。打开时将实际数据检索出来

        3 对于填有数据的游标，根据需要检索出各行

        4 在结束游标使用时，必须关闭游标。

        游标只定义一次，但可以多次打开和关闭使用游标。 但如果关闭后没有重新打开则不能使用。如果在存储过程最后没有关闭游标，在到达END时mysql会关闭它。            

使用游标:Mysql只能用于存储过程声明：DECLARE cname CURSOR FOR SELECT ....打开游标：OPEN cname获取数据：FETCH cname INTO c关闭游标：CLOSE cname

         存储过程中DECLARE语句的声明顺序：局部变量， 游标，句柄，顺序不能错，否则会产生错误消息，以下为一个游标的使用与分析：

--构造一个新表ordertotals，从订单表中选出所有订单号，然后对于每个订单号，--调用存储过程计算出订单总价，将这些订单号与计算出的总价存入表中CREATE PROCEDURE processorders()BEGIN  --先声明局部变量  DECLARE done BOOLEAN;   --来表示循环是否完成  SET done=0;  DECLARE o INT;  DECLARE t DECIMAL(8,2);  --再声明游标表示取出的所有订单号  DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;  --再声明一个句柄进行错误处理，当游标循环到最后没有数据时，  --设置02000状态码表示没有数据，从而设置done=1,结束循环  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;  --新建一个表用来存储结果  CREATE TABLE IF NOT EXISTS ordertotals    (order_num INT, total DECIMAL(8,2));   OPEN ordernumbers;  --打开游标  REPEAT    --FETCH获取游标所指数据    FETCH ordernumbers INTO o;        --调用存储过程计算总价    CALL ordertotal1(o, 1, t);        --插入新建的表中    INSERT INTO ordertotals(order_num, total) VALUES(o,t);  UNTIL done END REPEAT; --结束循环  CLOSE ordernumbers;  --关闭游标END;

      注意，在客户端输入时不能有这些注释，而且要改变分隔符。以下为结果：

mysql> CALL processorders;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 |+-----------+---------+