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 variable
CREATE 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 |
+-----------+---------+