MySQL的存储过程

存储过程

存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句调用存储过程智能用输出变量返回值。函数可以从语句外调用(通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

创建存储过程

基本语法如下：

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

参数	含义	备注
CREATE PROCEDURE	用来创建存储函数的关键字
sp_name	为存储过程的名称
proc_parameters	指定存储过程的参数列表	[IN|OUT|INOUT] param_name type 参数 含义 IN 表示输入参数 OUT 表示输出参数 INOUT 表示既可以输入，也可以输出 param_name 表示参数名称 type 表示参数的类型
characteristics	指定存储过程的特性	取值 作用 LANGUAGE SQL 说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值 [NOT]DETERMINISTIC 指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC {CONTAINS SQL|NOSQL|READS SQL DATA|MODIFIES SQL DATA} 指明子程序使用SQL语句的限制。 SQL SECURITY{DEFINER|INVOKER} 指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER COMMENT'string' 注释信息，可以用来描述存储过程或函数
routine_body	是SQL代码的内容，可以用BEGIN...END表示SQL代码的开始和结束

例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE Proc()
        BEGIN
        SELECT * FROM fruits;
        END;
        //
mysql> DELIMITER ;

注："DELIMITER //“语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号”;"为了避免与存储过程中的SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。存储过程结束后再用DELIMITER;恢复默认结束符。

创建存储函数

基本语法：

CREATE FUNCTION func_name([func_parameter])
RETURNS type
[characteristic ...] routine_body

参数	含义	备注
CREATE FUNCTION	用来创建存储函数的关键字
func_name	表示存储函数名称
func_parameter	存储过程的参数列表	[IN|OUT|INOUT] param_name type 参数 含义 IN 表示输入参数 OUT 表示输出参数 INOUT 表示既可以输入，也可以输出 param_name 表示参数名称 type 表示参数类型，该类型可以是MySQL数据库中的任意类型
RETURN type	表示函数返回数据的类型
characteristic	指定存储函数的特性，取值与创建存储过程时相同

例：

mysql> DELIMITER //
mysql> CREATE FUNCTION NameByZip()
        RETURNS CHAR(50)
        RETURN (SELECT s_name FROM suppliers WHERE s_call='48075');
        //
mysql> DELIMITER ;

注：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。(FUNCTION中总是默认为IN参数)。RETURNS子句只能对FUNCTION指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

变量

定义变量

在存储过程中使用DECLARE语句定义变量：

DELARE var_name[,varname]... date_type [DEFAULT value]

参数	含义
var_name	为局部变量的名称
DEFAULT value	变量的默认值。值除了可以被声明为一个常数外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL

为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值。

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

例:

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

SELECT ... INTO赋值

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

例:

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name,f_price INTO fruitname,fruitprice
FROM fruits WHERE f_id='a1';

定义条件和处理程序

定义条件

DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

参数	含义
condition_name	表示条件的名称
condition_type	表示条件的类型
sqlstate_value	长度为5的字符串类型错误代码
mysql_error_code	数值类型错误代码

例：

//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二：
DECLARE command_not_allowed CONDITION FOR 1148;

定义处理程序

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 表示遇到错误撤回之前操作，MySQL中暂不支持这样的操作
condition_value	表示错误类型	取值 含义 SQLSTATE[VALUE]sqlstate_value 包含5个字符的字符串错误值 condition_name 表示DECLARE CONDITION定义的错误条件名称 SQL WARNING 匹配所有以01开头的SQLSTATE错误代码 NOT FOUND 匹配所有以02开头的SQLSTATE错误代码 SQLEXCEPTION 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
sp_statement	参数为程序语句段，表示在遇到定义错误时，需要执行的存储过程或函数

例:

//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
//方法三：先定义条件，然后调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
//方法六：使用SQLEXCPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

定义条件和处理过程，例：

mysql> CREATE TABLE test.t(s1 int,primary key (s1));

mysql> DELIMITER //
mysql>CREATE PROCEDURE handlerdemo()
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
            SET @x=1;
            INSERT INTO test.t VALUES(1);
            SET @x=2;
            INSERT INTO test.t VALUES(1);
            SET @x=3;
            END;
            //
mysql> DELIMITER ;

mysql> CALL handlerdemo();
mysql> SELECT @x;

光标的使用

声明光标

DECLARE cursor_name CURSOR FOR select_statement

参数	含义
cursor_name	光标名称
select_statement	SELECT语句返回一个用于创建光标的结果集

打开光标

OPEN cursor_name{光标名称}

使用光标

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

关闭光标

CLOSE cursor_name{光标名称}

流程控制语句

IF语句

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

IF实现了一个基本的条件构造，如果expr_condition求值为真(TRUE)，相应的SQL语句列表被执行；如果没有expr_condition匹配，则ELSE子句里的语句被执行
例:

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

CASE语句

CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

参数	含义
case_expr	参数表示条件判断的表达式
when_value	表示表达式可能的值
statement_list	表示不同when_value值得执行语句

CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

参数	含义
expr_condition	表示条件判断语句
statement_list	表示不同条件的执行语句

例：

CASE
    WHEN val is NULL THEN SELECT 'val is NULL';
    WHEN val<0 THEN SELECT 'val is less then 0';
    WHEN val>0 THEN SELECT 'val is greater then 0';
    ELSE SELECT 'val is 0';
END CASE;

LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。

[loop_label:] LOOP
    statement_list
END LOOP [loop_label]

例：

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;

LEAVE语句

LEAVE语句用来退出任何被标注的流程控制构造：

LEAVE label

例：

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

ITERATE语句

ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。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

REPEAT语句

REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，就结束循环；否则重复执行循环中的语句。

[repeat_label:] REPEAT
    statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句货语句群被重复，直至expr_condition为真
例：

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

WHILE语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE语句执行时，先对指定的表达式进行判断。

[while_label:] WHILE expr_condition DO
    statement_list
END WHILE [while_label]

例：

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

查看存储过程和函数

SHOW STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

SHOW CREATE

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

information_schema.Routines表中查看

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';

修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]

参数	含义	备注
sp_name	参数表示存储过程或函数的名称
characteristic	指定存储函数特性	取值 含义 CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句 NO SQL 表示子程序中不包含SQL语句 READS SQL DATA 表示子程序中包含读数据的语句 MODIFIED SQL DATA 表示子程序中包含写数据的语句 SQL SECURITY{DEFINER|INVOKER} 指明谁有权限来执行 DEFINER 表示只有定义者才能够执行 INVOKER 表示调用者可以执行 COMMENT 'string' 表示注释信息

例：

ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER;

例：

ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME';

删除存储过程和函数

DROP [PROCEDURE|FUNCTION] [IF EXISTS] sp_name

例：

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;