MySQL 笔记7 存储过程和游标

傷城~ 2023-07-18 12:52 2阅读 0赞

参考：《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 =>

查看表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>