4 Mysql存储过程

1 存储过程的定义

存储过程是存储在数据库目录中的一段声明性SQL语句。应用程序(触发器,JAVA,PHP)可以调用存储过程。

使用存储过程带来的好处：

（1）mysql的存储过程编译之后放到缓存中，以后每次执行存储过程都不需要重新编译，而一般SQL语句每执行一次就需编译一次，所以使用存储过程可提高数据库的执行速度。

（2）存储过程可以重复使用，提高了可重用性，减少数据库开发人员的工作量。

（3）存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

（4）安全性高，可设定只有某用户才能对指定存储过程的使用权

带来了好处，那肯定也带来了一些坏处：

（1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。

（2）很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。

（3）开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。

 

2 存储过程的创建

接下来的实验中使用到了数据库，数据库的sql文件下载地址为：http://www.yiibai.com/downloads/yiibaidb.zip

我们就通过最常使用的navicat进行存储过程的创建

（1）首先，点击函数 ----新建函数---过程

（2）输入参数，参数具有IN、OUT、INOUT类型，关于参数我们下面会介绍

（3）编写存储过程

我们就编写一个查询：查询product表的商品列表

（4）点击运行，产生实验结果

一个简单的过程创建完成了。

 

3 存储过程的变量

存储过程中的变量和函数中的变量非常的像

3.1 变量的声明

DECLARE variable_name datatype(size) DEFAULT default_value;

• 首先，在DECLARE关键字后面要指定变量名。
• 其次，指定变量的数据类型及其大小。
• 最后，当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。

变量声明举例

DECLARE total_sale INT DEFAULT 0;

3.2 变量的赋值

（1）SET

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;

（2）SELECT INTO

DECLARE total_products INT DEFAULT 0

SELECT COUNT(*) INTO total_products
FROM products

3.3 变量的作用域

如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

 

4 存储过程参数

4.1 参数类型

IN：存储过程接收的参数

OUT：存储过程返回的参数

INOUT：INOUT参数是IN和OUT参数的组合。既可以作为接收的参数，又可以作为返回参数并将新值传递回调用程序

4.2 IN参数示例

以下示例说明如何使用GetOfficeByCountry存储过程中的IN参数来查询选择位于特定国家/地区的办公室。

我们通过navicat，定义存储过程使用的参数

在这里我们没有办法设置varchar的长度，我们可以到存储过程编辑 界面设置。

点击运行，输入参数

结果：

4.3 OUT参数示例

以下存储过程通过订单状态返回订单数量。它有两个参数：

• orderStatus：IN参数，它是要对订单计数的订单状态。
• total：存储指定订单状态的订单数量的OUT参数。

我们使用total接受参数，这里的total必须使用@修饰

4.4 INOUT参数示例

• set_counter存储过程接受一个INOUT参数(count)和一个IN参数(inc)。
• 在存储过程中，通过inc参数的值增加计数器(count)。

由于要多次的调用存储过程，因此，我们使用查询语句来调用存储过程，调用的方法是call +存储过程。

 

5 IF和CASE语句

5.1 IF语法规则

IF expression THEN 
   statements;
END IF;

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

5.2 CASE语法规则

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

5.3 IF 示例

以下示例说明如何使用IF ESLEIF ELSE语句，GetCustomerLevel()存储过程接受客户编号和客户级别的两个参数。

首先，它从customers表中获得信用额度。然后，根据信用额度，它决定客户级别：PLATINUM , GOLD 和 SILVER 。

 

 5.4 CASE 示例

• GetCustomerShipping存储过程接受客户编号作为IN参数，并根据客户所在国家返回运送时间。

• 在存储过程中，首先，我们根据输入的客户编号得到客户的国家。然后使用简单CASE语句来比较客户的国家来确定运送期。如果客户位于美国(USA)，则运送期为2天。 如果客户在加拿大，运送期为3天。 来自其他国家的客户则需要5天的运输时间。

 

5.5 IF和CASE的区别

• 当将单个表达式与唯一值的范围进行比较时，简单CASE语句比IF语句更易读。另外，简单CASE语句比IF语句更有效率。
• 当您根据多个值检查复杂表达式时，IF语句更容易理解。
• 如果您选择使用CASE语句，则必须确保至少有一个CASE条件匹配。否则，需要定义一个错误处理程序来捕获错误。IF语句则不需要处理错误。

6 存储过程循环

6.1 语法规则

WHILE expression DO
   statements
END WHILE

REPEAT
 statements;
UNTIL expression
END REPEAT

 

 LOOP，LEAVE和ITERATE语句

有两个语句允许您用于控制循环：

• LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP，C/C++，Java等其他语言的break语句一样。
• ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP，C/C++，Java等中的continue语句。

MySQL还有一个LOOP语句，它可以反复执行一个代码块，另外还有一个使用循环标签的灵活性。

6.1 while do

• 首先，重复构建str字符串，直到x变量的值大于5。
• 然后，使用SELECT语句显示最终的字符串。

BEGIN
	#Routine body goes here...
 DECLARE x  INT;
 DECLARE str  VARCHAR(255);

 SET x = 1;
 SET str =  '';

 WHILE x  <= 5 DO
 SET  str = CONCAT(str,x,',');
 SET  x = x + 1; 
 END WHILE;

 SELECT str;

END

6.2 REPEAT

repeat和while do类似

6.3 LOOP，LEAVE,ITERATE

• 以上存储过程仅构造具有偶数字符串的字符串，例如2,4,6等。
• 在LOOP语句之前放置一个loop_label循环标签。
• 如果x的值大于10，则由于LEAVE语句，循环被终止。
• 如果x的值是一个奇数，ITERATE语句忽略它下面的所有内容，并开始一个新的迭代。
• 如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串。

  BEGIN
  	#Routine body goes here...
   DECLARE x  INT;
   DECLARE str  VARCHAR(255);
  
   SET x = 1;
   SET str =  '';
  
   loop_label:  LOOP
   IF  x > 10 THEN 
   LEAVE  loop_label;
   END  IF;
  
   SET  x = x + 1;
   IF (x mod 2) THEN
       ITERATE  loop_label;
   ELSE
      SET  str = CONCAT(str,x,',');
   END IF;
      END LOOP;    
      SELECT str;
  END

  

7 游标

游标允许您迭代查询返回的一组行，并相应地处理每行。

由于游标的使用用到了声明处理程序。因此，我们先介绍声明处理程序。

7.1 声明处理程序

DECLARE action HANDLER FOR condition_value statement;

如果条件的值与condition_value匹配，则MySQL将执行statement，并根据该操作继续或退出当前的代码块。
各个字段的解释

7.2 使用MySQL游标

（1）声明一个游标

DECLARE cursor_name CURSOR FOR SELECT_statement;

（2）打开游标

OPEN cursor_name;

（3）使用FETCH语句来检索光标指向的下一行，并将光标移动到结果集中的下一行。之后，可以检查是否有任何行记录可用，然后再提取它。

FETCH cursor_name INTO variables list;

（4）关闭游标

CLOSE cursor_name;

（5） 还必须声明一个NOT FOUND处理程序来处理当游标找不到任何行时的情况

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

7.3 示例

使用游标遍历查询出的employees表中所有员工的电子邮件列表。

BEGIN
	#Routine body goes here...
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_email varchar(100) DEFAULT "";

 -- declare cursor for employee email
 DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

 OPEN email_cursor;

 get_email: LOOP

 FETCH email_cursor INTO v_email;

 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;

 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);

 END LOOP get_email;

 CLOSE email_cursor;
END

8 存储过程错误处理

8.1 声明一个错误处理程序

      如果发生重复的键错误，则会发出MySQL错误1062

  我们对应一个1062的声明处理程序

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

往这个表中插入重复的主键值，就会报错。

BEGIN
	#Routine body goes here...
   DECLARE CONTINUE HANDLER FOR 1062
 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;

 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);

 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);
CALL insert_article_tags(1,3);#插入重复的键，会调用声明处理程序

8.2 使用命名错误条件

上面的例子中使用了1062作为触发声明处理程序的条件，想象一下，你有一个大的存储过程代码使用了好多类似这样的数字; 这将成为维护代码的噩梦。

解决方法：重命名

DECLARE condition_name CONDITION FOR condition_value;

DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR  table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;

9 存储函数

储存函数和储存过程比较相似，但是，两者的使用场景不同。储存过程是暴露给外部应用的（JAVA,PHP）,而存储函数使用在您SQL语句中或者表达式中。

9.1 语法规则

CREATE FUNCTION function_name(param1,param2,…)
    RETURNS datatype
   [NOT] DETERMINISTIC
 statements

存储函数和存储过程的创建是类似，但是存储函数在主体部分中，必须至少指定一个RETURN语句。RETURN语句用于返回一个值给调用者。 每当到达RETURN语句时，存储的函数的执行将立即终止。

9.2 示例

我们改写5.3 中IF 示例

（1）输入函数的参数

（2）输入返回值

BEGIN
	#Routine body goes here...
    DECLARE lvl varchar(10);

    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;

 RETURN (lvl);
END;

 现在，我们在SELECT语句中调用CustomerLevel()存储函数

请注意，存储函数仅返回单个值。 如果没有包含INTO子句的SELECT语句，则将会收到错误