mysql--存储过程

转载自：https://www.jianshu.com/p/5b058fbbda19

简介

• 存储过程是主动调用的，类似方法
• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤
• 默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀

查询数据库中的存储过程

select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程

查询存储过程创建代码

show create procedure proc_name;

用户变量

• 用户变量名一般以@开头

赋值

SET @变量名=值;  

创建存储过程

CREATE
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

存储体

局部变量定义

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

示例-变量定义

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

传参

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或用户变量），调用完毕不改变用户变量
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是用户变量）,调用过程改变用户变量
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是用户变量），调用过程改变用户变量

控制语句

if

create procedure mypro2(in type int)
begin
    declare c varchar(500);
    if type = 0 then
        set c = 'type is 0';
    elseif type =1 then
        set c = 'type is 1';
    else
        set c = 'type is not 0 also is not 1';
    end if;
    select c;
end;

case

create procedure mypro3(in type int)
begin
    declare c varchar(500);
    case type
    when 0 then
        set c = 'type is 0';
    when 1 then
        set c = 'type is 1';
    else
        set c = 'type is not 0 also is not 1';
    end case;
    select c;
end;

循环

drop procedure if exists mypro4;
create procedure mypro4(in number int)
begin
    declare i int default 0;
    declare j int default 0;
    while i <= number do
        set j = j + i;
        set i = i + 1;
    end while;
    select j;
end;

删除存储过程

drop procedure if exists test;