[大数据]MySQL的存储过程

存储过程

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

创建存储过程

基本语法如下：

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

例：

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

例：

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]

为变量赋值

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

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

例：

//方法一：使用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

例:

//方法一：捕获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

打开光标

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
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

例：

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 ...]

例：

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;