SQL存储过程和函数

SQL存储过程和函数

  存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。

与函数的区别

（1）函数必须有返回值，而存储过程没有

（2）存储过程的参数可以使用 IN、OUT、INOUT 类型，而函数的参数只能是 IN 类型的。

相关操作

语句	定义
CREATE PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic …] routine_body	创建存储过程
CREATE FUNCTION sp_name ([func_parameter[,…]] RETURNS type [characteristic …] routine_body	创建函数
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]	修改 存储过程/函数
CALL sp_name([parameter[,…]])	调用过程
DROP PROCEDURE name	删除存储过程
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’]	查看存储过程或者函数的状态
SHOW CREATE {PROCEDURE |FUNCTION} sp_name	查看存储过程或者函数的定义
select * from routines where ROUTINE_NAME = ‘film_in_stock’ \G	获取存储过程和函数的包括名称、类型、语法、创建人 等信息。
DECLARE var_name[,…] type [DEFAULT value]	变量的定义{用于begin…end块中}
SET var_name = expr [, var_name = expr]	变量的赋值
DECLARE condition_name CONDITION FOR condition_value	条件的定义
DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement	条件的处理
DECLARE cursor_name CURSOR FOR select_statement	声明光标
OPEN cursor_name	打开光标
FETCH cursor_name INTO var_name [, var_name] …	fetch光标
CLOSE cursor_name	关闭光标
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] … [ELSE statement_list] END IF	IF语句
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] … [ELSE statement_list] END CASE	CASE语句
[begin_label:] LOOP statement_list END LOOP [end_label]	LOOP语句
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]	REPEAT语句
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]	WHILE语句

参数说明：

创建存储过程的proc_parameter:

• [ IN | OUT | INOUT ] param_name type

创建函数的func_parameter:

• param_name type

创建函数的type:

• Any valid MySQL data type

创建存储过程/函数的characteristic:

• (1) | LANGUAGE SQL //说明下面过程的BODY是使用SQL语言
• (2) | [NOT] DETERMINISTIC //DEDETERMINISTIC 确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC 非确定的，默认是非确定的。
• (3)| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } //CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含 SQL 语句。READS SQL DATA 表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。
• (4)| SQL SECURITY { DEFINER | INVOKER } //可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是 DEFINER
• (5) | COMMENT ‘string’ //存储过程或者函数的注释信息。

创建存储过程/函数的 routine_body:

• Valid SQL procedure statement or statements

修改存储过程/函数中的characteristic:

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string

条件的定义condition_value:

• SQLSTATE [VALUE] sqlstate_value <

  |mysql_error_code

条件的处理参数handler_type:

• CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value  

| condition_name  

| SQLWARNING  //对所有以 01 开头的 SQLSTATE 代码的速记

| NOT FOUND  //对所有以 02 开头的 SQLSTATE 代码的速记

| SQLEXCEPTION  //对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记。

| mysql_error_code

例子

mysql> DELIMITER $$   //delimiter将sql的结束标志设为$$

mysql>
mysql> 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);  调用了函数inventory_in_stock()

    ->

    -> SELECT FOUND_ROWS() INTO p_film_count;

    -> END $$

Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;  //delimiter将sql的结束标志设为;