• MySQL的存储过程


    存储过程

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

    创建存储过程

    基本语法如下:

    CREATE PROCEDURE sp_name([proc_parameter])
    [characteristics ...] routine_body
    
    • 1
    • 2
    参数含义备注
    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 ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

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

    创建存储函数

    基本语法:

    CREATE FUNCTION func_name([func_parameter])
    RETURNS type
    [characteristic ...] routine_body
    
    • 1
    • 2
    • 3
    参数含义备注
    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 ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

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

    变量
    定义变量

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

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

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

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

    例:

    DECLARE var1,var2,var3 INT;
    SET var1=10,var2=20;
    SET var3=var1+var2;
    
    • 1
    • 2
    • 3

    SELECT ... INTO赋值

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

    例:

    DECLARE fruitname CHAR(50);
    DECLARE fruitprice DECIMAL(8,2);
    
    SELECT f_name,f_price INTO fruitname,fruitprice
    FROM fruits WHERE f_id='a1';
    
    • 1
    • 2
    • 3
    • 4
    • 5
    定义条件和处理程序
    定义条件
    DECLARE condition_name CONDITION FOR [condition_type]
    
    [condition_type]:
    SQLSTATE [VALUE] sqlstate_value | mysql_error_code
    
    • 1
    • 2
    • 3
    • 4
    参数含义
    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;
    
    • 1
    • 2
    • 3
    • 4
    定义处理程序
    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
    • 2
    • 3
    • 4
    • 5
    参数含义备注
    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';
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    定义条件和处理过程,例:

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    光标的使用
    声明光标
    DECLARE cursor_name CURSOR FOR select_statement
    
    • 1
    参数含义
    cursor_name光标名称
    select_statementSELECT语句返回一个用于创建光标的结果集
    打开光标
    OPEN cursor_name{光标名称}
    
    • 1
    使用光标
    FETCH cursor_name INTO var_name[,var_name]...{参数名称}
    
    • 1
    关闭光标
    CLOSE cursor_name{光标名称}
    
    • 1
    流程控制语句
    IF语句
    IF expr_condition THEN statement_list
        [ELSEIF expr_condition THEN statement_list]...
        [ELSE statement_list]
    END IF
    
    • 1
    • 2
    • 3
    • 4

    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;
    
    • 1
    • 2
    • 3
    • 4
    CASE语句
    CASE case_expr
        WHEN when_value THEN statement_list
        [WHEN when_value THEN statement_list] ...
        [ELSE statement_list]
    END CASE
    
    • 1
    • 2
    • 3
    • 4
    • 5
    参数含义
    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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    参数含义
    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    LOOP语句

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

    [loop_label:] LOOP
        statement_list
    END LOOP [loop_label]
    
    • 1
    • 2
    • 3

    例:

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    LEAVE语句

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

    LEAVE label
    
    • 1

    例:

    add_num: LOOP
    SET @count=@count+1;
    IF @count=50 THEN LEAVE add_num;
    END LOOP add_num;
    
    • 1
    • 2
    • 3
    • 4
    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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    REPEAT语句

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

    [repeat_label:] REPEAT
        statement_list
    UNTIL expr_condition
    END REPEAT [repeat_label]
    
    • 1
    • 2
    • 3
    • 4

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

    DECLARE id INT DEFAULT 0;
    REPEAT
    SET id=id+1;
    UNTIL id>=10
    END REPEAT;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    WHILE语句

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

    [while_label:] WHILE expr_condition DO
        statement_list
    END WHILE [while_label]
    
    • 1
    • 2
    • 3

    例:

    DECLARE i INT DEFAULT 0;
    WHILE i<10 DO
    SET i=i+1;
    END WHILE;
    
    • 1
    • 2
    • 3
    • 4
    查看存储过程和函数
    SHOW STATUS
    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
    
    • 1
    SHOW CREATE
    SHOW CREATE {PROCEDURE | FUNCTION} sp_name
    
    • 1
    information_schema.Routines表中查看
    SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
    
    • 1
    修改存储过程和函数
    ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
    
    • 1
    参数含义备注
    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;
    
    • 1
    • 2
    • 3

    例:

    ALTER FUNCTION CountProc
    READS SQL DATA
    COMMENT 'FIND NAME';
    
    • 1
    • 2
    • 3
    删除存储过程和函数
    DROP [PROCEDURE|FUNCTION] [IF EXISTS] sp_name
    
    • 1

    例:

    DROP PROCEDURE CountProc;
    DROP FUNCTION CountProc;
    
    • 1
    • 2
  • 相关阅读:
    工厂模式和抽象工厂的区别总是绕晕初学者!!?欢迎来讨论
    如何利用播放器节省20%点播成本
    [Linux] 下载工具 aria2 的使用
    Casein-PEG-Rhodamine B 络蛋白-聚乙二醇-罗丹明B Casein-RB
    苍穹外卖技术栈
    左倾红黑树的go语言实现
    见面礼——图论
    某今日头条_signature解析
    设计模式之桥接模式--连接抽象与实现(你想知道的问题都有)
    Doc as Code (4):使用Git做版本管理,而不是使用目录做版本管理
  • 原文地址:https://blog.csdn.net/weixin_41489136/article/details/127615722