mysql 基本操作 存储过程和函数详解 （第四章）

存储过程和函数

1.    什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，提高开发效率，减少数据传输，

存储过程： 返回值 没有  参数类型：INOUT INOUT

函数：     返回值 有 参数类型：IN

2.    操作权限

创建需要CREATE ROUTINE权限

修改或删除需要 ALTER ROUTINE权限

执行需要 EXECUTE 权限

3.    创建、修改存储过程或者函数的语法：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
    [characteristic ...] routine_body 
 
CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body 
     
    proc_parameter: 
    [ IN | OUT | INOUT ] param_name type 
     
    func_parameter:     param_name type 
 type: 
    Any valid MySQL data type 
 characteristic: 
    LANGUAGE SQL 
  | [NOT] DETERMINISTIC 
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
  | SQL SECURITY { DEFINER | INVOKER } 
  | COMMENT 'string' 
 routine_body: 
    Valid SQL procedure statement or statements 
 
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] 
 characteristic: 
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
  | SQL SECURITY { DEFINER | INVOKER } 
  | COMMENT 'string' 

调用过程如下：

CALL sp_name([parameter[,...]]) 
例子：CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count 
INT) 
    -> READS SQL DATA 
    -> BEGIN 
    ->      SELECT inventory_id 
    ->      FROM inventory 
    ->      WHERE film_id = p_film_id 
    ->      AND store_id = p_store_id 
    ->      AND inventory_in_stock(inventory_id); 
    ->  
    ->      SELECT FOUND_ROWS() INTO p_film_count; 
-> END $$ 
最后执行 DELIMITER ;

执行创建过程和函数之前，都会通过“DELIMITER $$”命令将语句的结束符从“;” 修改成其他符号，这里使用的是“$$”，这样在过程和函数中的“;”就不会被 MySQL 解释成语句的结束而提示错误。在存储过程或者函数创建完毕，通过“DELIMITER ;”命令再将结束符改回成“;”。

可以看到在这个过程中调用了函数 inventory_in_stock()，并且这个过程有两个输入参数和一个输出参数

好处在于：处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。

 

下面对 characteristic特征值的部分进行简单的说明。

 [NOT] DETERMINISTIC：DETERMINISTIC 确定的，即每次输入一样输出也一样的程序， NOTDETERMINISTIC 非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。

l  { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含 SQL 语句。READS SQL DATA 表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明确给定，默认使用的值是 CONTAINS SQL。

l  SQL SECURITY { DEFINER | INVOKER }：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。

COMMENT 'string'：存储过程或者函数的注释信息

删除存储过程或函数：DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储函数的定义：SHOW CREATE {PROCEDURE | FUNCTION} sp_name

 

变量的使用

存储过程和函数中可以使用变量，而且在 MySQL 5.1 版本中，变量是不区分大小写的

1.变量的定义：

DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END 块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用DEFAULT 赋默认值。

定义一个变量的语法如下：

DECLARE var_name[,...] type [DEFAULT value] 

例如，定义一个 DATE 类型的变量，名称是last_month_start：

DECLARE last_month_start DATE; 

2.变量的赋值

变量可以直接赋值，或者通过查询赋值。

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

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

给刚才定义的变量 last_month_start赋值，具体语法如下：

SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr 

通过查询将结果赋值给变量 v_payments：

CREATE FUNCTION get_customer_balance(p_customer_id INT,  p_effective_date DATETIME)  
RETURNS DECIMAL(5,2) 
DETERMINISTIC 
READS SQL DATA 
BEGIN 
   … 
   DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY 
   … 
   SELECT IFNULL(SUM(payment.amount),0) INTO v_payments 
   FROM payment 
   WHERE payment.payment_date <= p_effective_date 
   AND payment.customer_id = p_customer_id; 
   … 
		RETURN v_rentfees + v_overfees – v_payments;
		END $$;

定义的条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时响应的处理步骤

1.条件的定义

DECLARE condition_name CONDITION FOR condition_value 
 condition_value: 
    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 

光标的使用

1.	声明光标：
DECLARE cursor_name CURSOR FOR select_statement
2.	open光标
OPEN cursor_name
3.	FETCH 光标：
FETCH cursor_name INTO var_name [, var_name] ...
4.	CLOSE 光标： 
CLOSE cursor_name 

以下例子是一个简单的使用光标的过程，对 payment 表按照行进行循环的处理，按照 staff_id 值的不同累加 amount 的值，判断循环结束的条件是捕获NOT FOUND 的条件，当 FETCH 光标找不到下一条记录的时候，就会关闭光标然后退出过程

mysql> delimiter $$ mysql>  mysql> CREATE PROCEDURE payment_stat () 
    -> BEGIN 
    ->   DECLARE i_staff_id int; 
    ->   DECLARE d_amount decimal(5,2); 
    ->   DECLARE cur_payment cursor for select staff_id,amount from payment; 
    ->   DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment; 
    ->   set  @x1 = 0; 
    ->   set  @x2 = 0; 
    ->   OPEN cur_payment; 
    ->   REPEAT 
    ->     FETCH cur_payment INTO i_staff_id, d_amount; 
    ->        if i_staff_id = 2 then 
    ->           set @x1 = @x1 + d_amount;     ->        else  
    ->           set @x2 = @x2 + d_amount; 
    ->        end if; 
    ->   UNTIL 0 END REPEAT; 
    ->   CLOSE cur_payment; 
    -> END$$; 
Query OK, 0 rows affected (0.00 sec) 
 mysql> delimiter ; 
mysql>  
mysql> call payment_stat(); 
Query OK, 0 rows affected (0.11 sec) 
 mysql> select @x1,@x2; 
+----------+----------+ 
| @x1      | @x2      | 
+----------+----------+ 
| 33927.04 | 33489.47 | 
+----------+----------+ 
1 row in set (0.00 sec) 

注意：变量、条件、处理程序、光标都是通过 DECLARE定义的，它们之间是有先后顺序的要求的。变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明

流程控制

可以使用 IF、CASE、LOOP、LEAVE、ITERATE、REPEAT 及 WHILE 语句进行流程的控制，下面将一一说明

1.    If实现条件判断，满足不同的条件执行不同的语句列表：

IF search_condition THEN statement_list 
    [ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] END IF

2.    Case实现比 IF 更复杂一些的条件构造，具体语法如下

CASE case_value 
    WHEN when_value THEN statement_list 
    [WHEN when_value THEN statement_list] ... 
    [ELSE statement_list] 
END CASE Or:  
CASE 
    WHEN search_condition THEN statement_list 
    [WHEN search_condition THEN statement_list] ... 

3. loop语句

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOP    statement_list
ENDLOOP [end_label]

如果不在statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

4. LEAVE语句

用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。

下面是一个使用 LOOP 和 LEAVE 的简单例子，循环 100 次向 actor 表中插入记录，当插入 100 条记录后，退出循环：

mysql> CREATE PROCEDURE actor_insert () 
    -> BEGIN 
    ->   set @x = 0; 
    ->   ins: LOOP 
    ->      set @x = @x + 1;     ->      IF @x = 100 then 
    ->      leave ins; 
    ->      END IF; 
    ->      INSERT INTO actor(first_name,last_name) VALUES ('Test','201'); 
    ->   END LOOP ins; 
-> END$$; 
mysql> call actor_insert(); 
Query OK, 0 rows affected (0.01 sec) 
 mysql> select count(*) from actor where first_name='Test'; 
+----------+ 
| count(*) | 
+----------+ 
| 100      | 
+----------+ 

5.  ITERATE语句

ITERATE 语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。下面的例子使用了ITERATE 语句，

当@x 变量是偶数的时候，不再执行循环中剩下的语句，而直接进行下一轮的循环：

mysql> CREATE PROCEDURE actor_insert () 
    -> BEGIN 
    ->   set @x = 0; 
    ->   ins: LOOP 
    ->      set @x = @x + 1; 
    ->      IF @x = 10 then 
    ->      leave ins;  
    ->      ELSEIF mod(@x,2) = 0 then 
    ->      ITERATE ins; 
    ->      END IF; 
    ->      INSERT INTO actor(actor_id,first_name,last_name) VALUES (@x+200,'Test',@x); 
    ->   END LOOP ins; 
    -> END; 
    -> $$ 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call actor_insert(); 
Query OK, 0 rows affected (0.00 sec) 
 mysql> select actor_id,first_name,last_name from actor where first_name='Test'; 
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ 
| 201      | Test       | 1         | | 203      | Test       | 3         | 
| 205      | Test       | 5         | 
| 207      | Test       | 7         | 
| 209      | Test       | 9         | +----------+------------+-----------+ 
5 rows in set (0.00 sec)

6. REPEAT语句

有条件的循环控制语句，当满足条件的时候退出循环，具体语法如下：

[begin_label:] REPEAT     statement_list 
UNTIL search_condition 
END REPEAT [end_label]

在“12.2.6 光标的使用”小节中的例子就使用 REPEAT语句实现光标的循环获得，下面节选的代码就是其中使用 REPEAT 语句的部分

 REPEAT 
    ->     FETCH cur_payment INTO i_staff_id, d_amount; 
    ->        if i_staff_id = 2 then 
    ->           set @x1 = @x1 + d_amount;     ->        else  
    ->           set @x2 = @x2 + d_amount; 
    ->        end if; 
    ->   UNTIL 0 END REPEAT; 

7.  WHILE语句

WHILE 语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容，具体语法如下：

[begin_label:] WHILE search_condition DO     statement_list END WHILE [end_label]

 WHILE 循环和 REPEAT 循环的区别在于：WHILE 是满足条件才执行循环，REPEAT 是满足条件退出循环；WHILE 在首次循环执行之前就判断条件，所以循环最少执行 0 次，而 REPEAT 是在首次执行循环之后才判断条件，所以循环最少执行 1 次。

以下例子用来对比 REPEAT和 WHILE语句的功能：

mysql> delimiter $$ mysql> CREATE PROCEDURE loop_demo () 
 -> BEGIN 
 ->     set @x = 1 , @x1 = 1; 
->     REPEAT 
 ->        set @x = @x + 1; 
17.	    ->     until @x > 0 end repeat; 
    ->      
    ->     while @x1 < 0 do 
    ->        set @x1 = @x1 + 1; 
    ->     end while; 
    -> END; 
    -> $$ 
Query OK, 0 rows affected (0.00 sec) 
 mysql> delimiter ; mysql> call loop_demo(); 
Query OK, 0 rows affected (0.00 sec) 
 mysql> select @x,@x1; 
+------+------+ 
| @x   | @x1  | 
+------+------+ 
| 2    | 1    | 
+------+------+ 
1 row in set (0.00 sec) 

从判断的条件上看，初始值都是满足退出循环的条件的，但是REPEAT 循环仍然执行了一次以后才退出循环的，而 WHILE 循环则一次都没有执行。