MySQL知识（十四）——存储过程的创建

1 创建存储过程和函数

　　存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（即通过引用函数名），也能返回标量值。存储过程也可以调用其它存储过程。
　　说白了，存储过程和函数就是我们一般编程语言的自定义函数。

1.1 创建存储存储过程

　　基本语法：

CREATE PROCEDURE sp_name([proc_parameter])
　　[characteristics] routine_body

　　（1）CREATE PROCEDURE：创建存储过程的关键字
　　（2）sp_name：存储过程的名字
　　（3）proc_parameter：指定存储过程的参数列表。
IN表示输入参数；OUT表示输出参数；INOUT表示即可以输入也可以输出；param_name表示参数名；type表示参数类型
　　（4）characteristics指定存储过程的特性，可以有如下取值：

• LANGUAGE SQL：说明routine body部分是由SQL语句组成
• [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确
• {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}：指明子程序使用SQL语句的限制
• SQL SECURITY {DEFINER|INVOKER}：指明谁有权限来执行

• COMMENT ‘string’：注释信息

  　　示例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE Pro()
    -> BEGIN
    -> SELECT * FROM fruits;
    -> END //
Query OK, 0 rows affected

mysql> CREATE PROCEDURE CountProc(OUT param1 INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO param1 FROM fruits;
    -> END //
Query OK, 0 rows affected

mysql> DELIMITER ;

　　“DELIMITER //”表示将结束符设置为//

1.2 变量的使用

　　变量可以在子程序中声明并使用，作用范围是咋BEGIN…END程序中。
　　（1）定义变量

DECLARE var_name[,varname]...data_type[DEFAULT value];

　　例如：

DECLARE myparam INT DEFAULT 100;

　　（2）为变量赋值

SET var_name=expr[,var_name = expr]...;

　　示例：

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

1.3 定义条件和处理程序

　　特定条件需要特定处理，这些条件可以联系到错误，以及子程序中的一般流程控制。
　　定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数咋iyudao警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
　　（1）定义条件

DECLARE condition_name CONDITION FOR[condition_type]
[condition_type]:
　　SQLSTATE [VALUE] sqlstate_value|mysql_error_code

　　（2）定义处理程序

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
　　CONTINUE|EXIT|UNDO


condition_value:
　　SQLSTATE[VALUE]sqlstate_value
|condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code

①handler_type：错误处理方式，取值：CONTINUE、EXIT和UNDO。
- CONTINUE表示遇到错误不处理，继续执行
- EXIT表示遇到错误马上退出
- UNDO表示遇到错误后撤回之前的操作。
②condition_value：表示错误类型

　　（3）示例：

mysql> CREATE TABLE t(s1 int,primary key(s1));
Query OK, 0 rows affected

mysql> DELIMITER //
mysql> CREATE PROCEDURE handlerdemo()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
    -> SET @x=1;
    -> INSERT INTO t VALUES(1);
    -> SET @x=2;
    -> INSERT INTO t VALUES(1);
    -> SET @x=3;
    -> END;
    -> //
Query OK, 0 rows affected

mysql> DELIMITER ;
mysql> CALL handlerdemo();
Query OK, 0 rows affected

mysql> SELECT @x;
+----+
| @x |
+----+
|  3 |
+----+
1 row in set

　　“@var_name”表示用户变量，使用SET语句赋值，用户变量与连接有关。一个客户端定义的变量不能被其他客户端使用。客户端退出时，变量自动释放。

1.4 光标的使用

　　查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
　　光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。
　　（1）声明光标

DECLARE cursor_name CURSOR FOR selec_statement

例如：

DECLARE cursor_fruits CURSOR FOR SELECT f_name,f_price FROM fruits;

　　（2）打开光标

OPEN cursor_name{光标名称}

例如：

OPEN cursor_fruits;

　　（3）使用光标

FETCH cursor_name INTO var_name[,var_name]...{参数名称}

例如：

FETCH cursor_fruits INTO fruits_name,fruits_price;

　　（4）关闭光标

CLOSE cursor_name{光标名称}

例如：

CLOSE cursor_fruits;

　　MySQL中光标只能在存储过程和函数中使用。

1.5 流程控制的使用

　　流程控制语句用来根据条件控制语句的执行。
（1）IF语句

IF val IS NULL
  THEN SELECT 'val is NULL';
  ELSE SELECT 'val is not NULL';
END IF;

（2）CASE语句

CASE val
  WHEN 1 THEN SELECT 'val is 1';
  WHEN 2 THEN SELECT 'val is 2';
  ELSE SELECT 'val is not 1 or 2';
END CASE;

（3）LOOP语句

DECLARE id INT DEFAULT 0;
add_loop: LOOP  
SET id = id + 1;
  IF id >= 10 THEN  LEAVE add_loop;
  END IF;
END LOOP add_ loop; 

（4）LEAVE语句

add_num:LOOP      
　SET @count=@count+1;
  IF @count=50 THEN LEAVE add_num;
  END IF;
END LOOP add_num; 

（5）ITERATE语句

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
  SET p1= p1 + 1;
  IF p1 < 10 THEN ITERATE my_loop;
  ELSEIF p1 > 20 THEN LEAVE my_loop;
  END IF;
  SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

（6）REPEAT语句

DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTIL  id >= 10
END REPEAT; 

（7）WHILE语句

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;

说明

阅读《MySQL5.5 从零开始学》摘抄。