MySQL存储过程

存储过程是数据库的一个重要对象，可以封装SQL，完成一些复杂的业务逻辑。使用存储过程，最主要的特点是执行速度快，其经过编译后会比一条一条单独执行快，也能避免频繁的连接而消耗资源。

语法结构

创建的语法结构

create 
    [definer = user]                                   -- 用来指定用户的执行权限，默认所有用户可用；
    procedure  procedure_name([proc_parameter[,...]])  --  procedure_name存储过程的名字；
                         -- proc_parameter参数，类型有in（入参）、out（出参）、inout（入参和出参）；
   body                  -- body中写程序体，只要是合法的sql即可。以begin开始，以end结束，必须成对出现，通常一对即可。

call 调用存储过程

call  procedure_name   -- 使用call关键字调用存储过程

实例：创建存储过程并调用

-- 创建存储过程
create procedure proc_test()
begin
    select now() from dual;  -- 查询当前时间
end;
-- 调用存储过程
call proc_test();  -- 执行结果是当前时间

删除的语法结构

drop procedure procedure_name  -- 删除时指定存储过程名称即可，不能加括号。

实例：删除存储过程

drop procedure proc_test;

变量及赋值

局部变量

用户自定义的变量，尽在begin/end块中有效。

-- 使用declare关键字声明变量名和类型，可指定默认值。 [] 表示可选参数
declare var_name type [default var_value];

使用set赋值

给变量赋值，可以使用set和into关键字，对所有变量都适用。

create procedure proc_var01()
begin
    declare user_name varchar(50);#直接声明，无默认值
    declare age int(11) default 20;#声明的同时指定默认值
    set user_name = '张三';#赋值
    select user_name,age; #查询
end;
-- 调用存储过程 
call proc_var01

若有多个set对其赋值，则最终值是最后一个set的结果。

使用into赋值

create procedure proc_var02()
begin
    declare create_time datetime;
    select now() into create_time;
    select create_time;
end;
-- 调用存储过程 
call proc_var02

若在select中使用into给多个变量赋值，则使用逗号分隔即可，但前面的值和后面的变量必须一一对应。

用户变量

用户自定义的变量，在当前会话有效。

@var_name  #无需提前声明，使用即声明

赋值

create procedure proc_var03()begin    set @create_time = now(); # 并未声明就直接赋值，但变量名前面必须带@符号，表示它是用户变量。    select @create_time;end;-- 调用存储过程 call proc_var03

会话变量与全局变量

会话变量和全局变量都是系统提供的变量。

会话变量：当前会话有效。由于用的不多，在此略。

全局变量：整个MySQL服务有效。由于用的不多，在此略。

入参和出参

in | out | inout param_name type  # in（入参）、out（出参）、inout（入参和出参） typ参数的类型

入参

create procedure proc_var04(in name varchar(50))begin    select name;end;-- 调用存储过程 传入名字进行查询：call proc_var04('root');

出参

create procedure proc_var05(in phone varchar(11),out phone_suffix varchar(4))begin    set phone_suffix = right(phone,4);  -- 截取后4位：end;-- 调用存储过程 传入手机号，call proc_var05('15623524651',@suffix);-- 执行结果是4651。需要注意的是，在接收输出参数时，必须使用用户变量，否则接收不到，调用时还会出错。select @suffix;

入参和出参

create procedure proc_var06(inout name varchar(100))begin    set name = concat('hello,',name); #将hello拼接用户名end;-- 设置传入的参数set @username='张三';-- 调用存储过程 传入参数用户名call proc_var06(@username);-- 查询用户名select @username;

判断（流程控制）

if

if判断有if、if-else、if-elseif-else等。其条件在判断等于时用一个等号，条件后使用then，且以 if 开头 end if 成对结尾。

仅有if

create procedure proc_var07(in age int)begin    declare msg varchar(20) default '';    if age = 20 then                            -- 满足条件进入then        set msg = '年龄为20';    end if;                                     -- 表示if 结束了    select msg;end;-- 调用存储过程 传入参数用户名call proc_var07(20);

if-else

create procedure proc_var08(in age int)begin    declare msg varchar(20) default '';    if age = 20 then                            -- 满足条件进入then        set msg = '年龄为20';    else                                        -- 不满足条件进入else        set msg = '年龄不为20';    end if;                                     -- 表示if 结束了    select msg;end;-- 调用存储过程 传入参数用户名call proc_var07(21);

if-elseif-else

create procedure proc_var09(in age int)begin    declare msg varchar(20) default '';    if age > 20 then                           -- 满足条件进入then        set msg = '年龄大于20';    elseif age < 20 then                       -- 满足条件进入then        set msg = '年龄小于20';    else                                       -- 不满足条件进入else        set msg = '年龄等于20';    end if;                                    -- 表示if 结束了    select msg;end;-- 调用存储过程 传入参数用户名call proc_var09(18);

case分支

case的语法有两种

第一种：值匹配

create procedure proc_var10(in season int)begin    declare msg varchar(20) default '';    case season                             -- 值匹配 season为3 就进入3这个分支        when 1 then            set msg = 'spring';        when 2 then            set msg = 'summer';        when 3 then            set msg = 'autumn';        else            set msg = 'winter';    end case;    select msg;end;-- 调用存储过程 传入3 执行的结果是autumncall  proc_var10(3);

第二种：表达式和if一样，哪个满足条件进入哪个

create procedure proc_var10(in age int)begin    declare msg varchar(20) default '';    case        when age > 20 then            set msg = '年龄大于20';        when age < 20 then            set msg = '年龄小于20';        else            set msg = '年龄等于20';    end case;    select msg;end;-- 调用存储过程 传入18 执行的结果是年龄小于20call  proc_var10(18);

循环（流程控制）

**leave：**用于结束循环。

iterate：用于结束本次循环，进入下一次循环。

loop循环

[begin_label:] loop    statement_listend loop [begin_label]

begin_label是给该循环起个别名，当多个loop嵌套时结束循环需根据别名。另外loop是死循环，必须使用leave结束循环.

实例： 打印1到10：

create procedure proc_var11(in count int )begin    declare msg varchar(200) default '1';    declare curr int default 1;    cnt:loop                        -- cnt是loop循环的别名      if curr >= count then        leave cnt;                   -- 当curr>10时，结束cnt循环             end if;      set curr = curr + 1;      set msg = concat(msg,',',curr);      end loop cnt;    select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call  proc_var11(10);

repeat循环

[begin_label:] repeat    statement_listuntil conditionend repeat [begin_label]

当until后面的条件符合时结束循环，否则会一直循环。

实例： 打印1到10：

create procedure proc_var12(in count int)begin    declare msg varchar(200) default '1';    declare curr int default 1;         cnt:repeat      set curr = curr + 1;      set msg = concat(msg,',',curr);                until curr >= count #符合条件结束循环 ，注意until后不能加分号。     end repeat cnt;    select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call  proc_var12(10);

注意：until后不能加分号。

while循环

[begin_label:] while condition do        statement_listend while [begin_label]

实例： 打印1到10：

create procedure proc_var13(in count int)begin    declare msg varchar(100) default '';    declare curr int default 1;    cnt:while curr<=count do               -- 符合条件进入循环体，不符合条件退出循环        set msg = concat(msg,',',curr);        set curr = curr + 1;    end while;    select msg;end;-- 调用存储过程 传入10 执行的结果是打印1到10call  proc_var13(10);

游标

数据准备

# 创建员工表empCREATE TABLE `emp` (  `id` int(11) NOT NULL,  `name` varchar(200) DEFAULT NULL COMMENT '姓名',  `dept_no` varchar(32) DEFAULT NULL COMMENT '部门编号',  `sal` decimal(10,2) DEFAULT NULL COMMENT '工资',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 创建部门表deptCREATE TABLE `dept` (  `dept_no` varchar(32) NOT NULL COMMENT '部门编号',  `dept_name` varchar(200) DEFAULT NULL COMMENT '部门名称',  PRIMARY KEY (`dept_no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;#添加数据INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A001', '策划部');INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A002', '开发部');INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A003', '人事部');INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (1, '张飒', 'A002', 5000.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (2, '李敏', 'A003', 4500.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (3, '赵虹', 'A001', 4350.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (4, '赵敏敏', 'A002', 6530.00);INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (5, '孙慧', 'A002', 7150.00);

游标语法

游标的作用主要是遍历查询的结果集。故在声明游标时for后面就是select的查询语句。

#声明游标declare cursor_name cursor for select_statement#声明句柄declare continue handler for 1329 set finished = 1;#打开游标open cursor_name #遍历取值 使用fetch遍历，当结果集遍历到最后一条时，它会报错，错误码是1329，错误状态是02000，需要用Handler处理异常fetch cursor_name into var_name ...#使用Handler（句柄）来判断异常进行处理。if finished = 1 then    具体处理#关闭游标，使用完后需关闭close cursor_name 

实例： 给开发部的员工涨工资的存储过程：

create procedure proc_var14(in name varchar(200),in add_sal decimal)begin    declare user_id int;    declare finished int default 0;    #声明游标    declare cur_emp cursor for         select id from emp where dept_no =(select dept_no from dept where dept_name =name) ;     #声明句柄    declare continue handler for 1329 set finished = 1;    #打开游标    open cur_emp;    emp_loop:loop        #遍历取值        fetch cur_emp into user_id;        #使用Handler（句柄）来判断异常进行处理。        if finished = 1 then             leave emp_loop;  #退出循环        else            update  emp e set sal = e.sal + add_sal where e.id = user_id;  #开发部的员工涨工资        end if;     end loop emp_loop;    #关闭游标，使用完后需关闭    close cur_emp;end;-- 调用存储过程 给开发部的员工1000涨工资call  proc_var14('开发部',1000);

声明句柄的注意事项：

除了使用错误码外，还可以使用错误状态，声明如下，二选一即可：

declare continue handler for sqlstate '02000' set finished = 1;