《MySQL必知必会》[05] 存储过程和游标

2022-03-02 05:20:44


1、存储过程

存储过程是什么，简单来讲，就像Java中的方法（函数），不过它是SQL世界中的方法。

大部分时候，我们所使用都是单条SQL，用来针对一个或多表连接。但是也有情况，是据判断先对表A执行操作，变动后再结合表B进行操作。即SQL的执行可能需要考虑包含业务规则在内的智能处理。封装操作的好处就不过多说明，无非是简化，复用，降低耦合等，同时，它还具有更高的性能。

考虑这种业务情况，现在你需要获得订单总额，但同时需要增加营业税，且只针对某些顾客，那么你需要：
  • 获得基本的订单总额
  • 将营业税有条件地添加到合计中
  • 返回合计

1.1 基本语句

先看基本的语句，然后再看示例，就豁然开朗了：
--创建存储过程

CREATE PROCEDURE <存储过程的名称>(<变量的类型定义>)

BEGIN

  <执行操作>

END;

--执行存储过程

CALL <存储过程的名称>(<@变量名>);

--删除存储过程

DROP PROCEDURE <存储过程的名称>;
11
 
1
--创建存储过程
2
CREATE PROCEDURE <存储过程的名称>(<变量的类型定义>)
3
BEGIN
4
  <执行操作>
5
END;
6








7




--执行存储过程








8




CALL <存储过程的名称>(<@变量名>);








9












10




--删除存储过程








11




DROP PROCEDURE <存储过程的名称>;































1.2 创建


然后，根据刚才我们说到的返回包含营业税的订单总额，创建如下存储过程：





-- 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 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

    SELECT total+(total/100*taxrate) INTO total;

  END IF;

  SELECT total INTO ototal;

END;























29










 












1




-- Name: ordertotal








2




-- Parameters: onumber = order number








3




--             taxable = 0 if not taxable, 1 if taxable








4




--             ototal  = order total variable








5












6




CREATE PROCEDURE ordertotal(








7




  IN  onumber INT,








8




  IN  taxable BOOLEAN,








9




  OUT ototal DECIMAL(8, 2)








10




) COMMENT 'Obtain order total, optionally adding tax'








11




BEGIN








12




  --Declare variable for total








13




  DECLARE total DECIMAL(8, 2);








14




  --Declare tax percentage








15




  DECLARE taxrate INT DEFAULT 6;








16




  








17




  --GET the order total








18




  SELECT Sum(item_price*quantity)








19




  FROM orderitems








20




  WHERE order_num = onumber








21




  INTO total;








22












23




  --Is this taxable








24




  IF taxable THEN








25




    SELECT total+(total/100*taxrate) INTO total;








26




  END IF;








27












28




  SELECT total INTO ototal;








29




END;































看起来这么长好像挺唬人，其实很清楚：




CREATE PROCEDURE ordertotal(

  IN  onumber INT,

  IN  taxable BOOLEAN,

  OUT ototal DECIMAL(8, 2)

) COMMENT 'Obtain order total, optionally adding tax'























5










 












1




CREATE PROCEDURE ordertotal(








2




  IN  onumber INT,








3




  IN  taxable BOOLEAN,








4




  OUT ototal DECIMAL(8, 2)








5




) COMMENT 'Obtain order total, optionally adding tax'






























    

  • 使用CREATE PROCEDURE关键词创建了名为ordertotal的存储过程
    • 

  • 该存储过程定义了三个变量，IN表示要求输入的参数，OUT表示输出的结果。INT、BOOLEAN等表示变量的数据类型
    • 

  • COMMENT非必需，如果有，那么在SHOW PROCEDURE STATUS的结果时会显示（简单说，类似于方法的说明）
    • 











BEGIN

  ...

END;























3










 












1




BEGIN








2




  ...








3




END;






























  • BEGIN和END用来界定存储过程操作执行的语句










  --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;























10










 












1




  --Declare variable for total








2




  DECLARE total DECIMAL(8, 2);








3




  --Declare tax percentage








4




  DECLARE taxrate INT DEFAULT 6;








5




  








6




  --GET the order total








7




  SELECT Sum(item_price*quantity)








8




  FROM orderitems








9




  WHERE order_num = onumber








10




  INTO total;






























    

  • DECLARE用来定义存储过程中的局部变量
    • 

  • INTO表示赋值到变量
    • 











  --Is this taxable

  IF taxable THEN

    SELECT total+(total/100*taxrate) INTO total;

  END IF;























4










 












1




  --Is this taxable








2




  IF taxable THEN








3




    SELECT total+(total/100*taxrate) INTO total;








4




  END IF;






























  • IF <boolean> THEN <do something> END IF 为条件执行语句，记得END IF结尾







假如用Java来写的话，大概是这么个意思：





public void ordertotal(int onumber, boolean taxable, double ototal) {

    double total;

    int taxrate = 6;

    total = getOrderTotal(onumber);

    if (taxable) {

        total += total / (100 * taxrate);

    }

    ototal = total;

}























12










 












1




public void ordertotal(int onumber, boolean taxable, double ototal) {








2












3




    double total;








4




    int taxrate = 6;








5




    








6




    total = getOrderTotal(onumber);








7




    if (taxable) {








8




        total += total / (100 * taxrate);








9




    }








10












11




    ototal = total;








12




}































1.3 执行


在1.2我们定义了存储过程ordertotal()，则执行方式为：





--不含营业税

CALL ordertotal(20005, 0, @total);

SELECT @total

+----------+

|  @total  |

+----------+

|  149.87  |

+----------+

--包含营业税

CALL ordertotal(20005, 1, @total);

SELECT @total

+-----------------+

|      @total     |

+-----------------+

|  158.862200000  |

+-----------------+























19










 












1




--不含营业税








2




CALL ordertotal(20005, 0, @total);








3




SELECT @total








4












5




+----------+








6




|  @total  |








7




+----------+








8




|  149.87  |








9




+----------+








10












11




--包含营业税








12




CALL ordertotal(20005, 1, @total);








13




SELECT @total








14












15




+-----------------+








16




|      @total     |








17




+-----------------+








18




|  158.862200000  |








19




+-----------------+































定义时我们说过，IN表示定义输入，OUT表示定义输出，所以这里的三个变量中，前两者由调用者传入，而第三个变量，则作为返回结果的变量。


（调用存储过程时，用于临时存储返回数据的变量必须以@开头）





1.4 检查


用来显示“创建某个存储过程的CREATE语句”，使用SHOW CREATE PROCEDURE语句：





SHOW CREATE PROCEDURE ordertotal;























1










 












1




SHOW CREATE PROCEDURE ordertotal;































1.5 删除





DROP PROCEDURE ordertotal;























1










 












1




DROP PROCEDURE ordertotal;




























注意，检查和删除存储过程，都不用加后面的()，只需要给出存储过程的名称即可。














2、游标


在检索出来的行中，前进或者后退一行或多行，就需要用到所谓的“游标”。游标不是某个SELECT语句，但是它是被该语句检索出来的结果集，另外，MySQL游标只能用于存储过程（和函数）。





2.1 创建游标


使用DECLARE和CURSOR关键字：





CREATE PROCEDURE processorders()

BEGIN

  DECLARE ordernumbers CURSOR

  FOR

  SELECT order_num FROM orders;

END;























6










 












1




CREATE PROCEDURE processorders()








2




BEGIN








3




  DECLARE ordernumbers CURSOR








4




  FOR








5




  SELECT order_num FROM orders;








6




END;































2.2 打开和关闭游标


因为游标局限于存储过程，所以如果存储过程处理完成后，游标就会消失。所以往往在存储过程中要关键字OPEN进行打开。另，游标相关的SELECT查询语句，在定义时是不执行的，在OPEN时才执行查询，存储检索出的数据以供浏览和滚动。在游标使用完成后，使用CLOSE进行关闭：





CREATE PROCEDURE processorders()

BEGIN

  --Declare

  DECLARE ordernumbers CURSOR

  FOR

  SELECT order_num FROM orders;

  --Open

  OPEN ordernumbers;

  --Close

  CLOSE ordernumbers;

END;























13










 












1




CREATE PROCEDURE processorders()








2




BEGIN








3




  --Declare








4




  DECLARE ordernumbers CURSOR








5




  FOR








6




  SELECT order_num FROM orders;








7












8




  --Open








9




  OPEN ordernumbers;








10




  








11




  --Close








12




  CLOSE ordernumbers;








13




END;































2.3 使用游标数据


打开游标之后，我们就可以使用关键字FETCH访问数据了，FETCH是从第一行开始，获取当前行的数据，每次执行后会移动内部行指针，再次调用FETCH则会检索到下一行（不会重复读取同一行）：





CREATE PROCEDURE processorders()

BEGIN

  --Declare

  DECLARE o INT;

  DECLARE ordernumbers CURSOR

  FOR

  SELECT order_num FROM orders;

  --Open

  OPEN ordernumbers;

  --Get

  FETCH ordernumbers INTO o;  

  --Close

  CLOSE ordernumbers;

END;























17










 












1




CREATE PROCEDURE processorders()








2




BEGIN








3




  --Declare








4




  DECLARE o INT;








5




  DECLARE ordernumbers CURSOR








6




  FOR








7




  SELECT order_num FROM orders;








8




 








9




  --Open








10




  OPEN ordernumbers;








11




 








12




  --Get








13




  FETCH ordernumbers INTO o;  








14




 








15




  --Close








16




  CLOSE ordernumbers;








17




END;































看一个复杂些的例子：





CREATE PROCEDURE processorders()

BEGIN

  --Declare local variables

  DELCARE done BOOLEAN DEFAULT 0;

  DECLARE o    INT;

  DECLARE t    DECIMAL(8,2);

  --Declare 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

    FETCH ordernumbers INTO o;

    CALL ordertotal(o, 1, t);

    INSERT INTO ordertotals(order_num, total) VALUES(o, t);

  UNTIL done END REPEAT;

  --Close the cursor

  CLOSE ordernumbers;

END;























31










 












1




CREATE PROCEDURE processorders()








2




BEGIN








3




  --Declare local variables








4




  DELCARE done BOOLEAN DEFAULT 0;








5




  DECLARE o    INT;








6




  DECLARE t    DECIMAL(8,2);








7












8




  --Declare cursor








9




  DECLARE ordernumbers CURSOR








10




  FOR








11




  SELECT order_num FROM orders;








12




 








13




  --Declare continue handler








14




  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;








15












16




  --Create a table to store the results








17




  CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));  








18












19




  --Open the cursor








20




  OPEN ordernumbers;








21




 








22




  --Loop through all rows








23




  REPEAT








24




    FETCH ordernumbers INTO o;








25




    CALL ordertotal(o, 1, t);








26




    INSERT INTO ordertotals(order_num, total) VALUES(o, t);








27




  UNTIL done END REPEAT;








28












29




  --Close the cursor








30




  CLOSE ordernumbers;








31




END;






























    

  • 以上存储过程，游标不断读取订单号，并以此为参调用另一个存储过程，将最终的值填入到表ordertotals中
    • 

  • CONTINUE HANDLER 是在条件出现时执行的代码，SQLSTATE '02000' 表没有找到更多的行（MySQL错误代码）
    • 











SELECT * FROM ordertotals;

+---------+---------+

|  20005  |  158.86 |

|  20006  |   58.30 |

|  20007  | 1060.00 |

|  20008  |  132.50 |

|  20009  |   40.78 |

+---------+---------+



















x






 












1




SELECT * FROM ordertotals;








2












3




+---------+---------+








4




|  20005  |  158.86 |








5




|  20006  |   58.30 |








6




|  20007  | 1060.00 |








7




|  20008  |  132.50 |








8




|  20009  |   40.78 |








9




+---------+---------+




















































上一篇：cache(缓存)和buffer(缓冲)的区别

下一篇：jquery validate 插件使用小结