MySQL存储过程

2023-08-12 17:09:10

1.引言

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

2.语法结构

2.1创建的语法结构

语法结构如下：

create 
    [definer = user]
    procedure  procedure_name([proc_parameter[,...]])
    body

[definer = user] 用来指定用户的执行权限，默认所有用户可用；

procedure_name 指定存储过程的名字；

proc_parameter 指定存储过程的参数，类型有 in（入参）、out（出参）、inout（同时作为入参和出参）；

body 中写程序体，只要是合法的sql即可。一般会以begin开始，以end结束，必须成对出现，通常一对即可。

在调用时，根据入参和出参，使用call关键字和存储过程名调用即可。

2.2删除的语法结构

drop procedure procedure_name

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

2.3示例演示

1）创建存储过程

create procedure proc_test()
begin
    select now() from dual;
end;

2）调用存储过程

call proc_test();

上述的存储过程仅是用来查询时间的，故执行结果是当前时间。

3）删除存储过程

drop procedure proc_test;

3.变量及赋值

3.1局部变量

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

1）语法

declare var_name type [default var_value];

使用declare关键字声明变量名和类型，可指定默认值。

2）使用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;

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

2）使用into赋值

create procedure proc_var02()
begin
    declare create_time datetime;
    select now() into create_time;
    select create_time;
end;

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

3.2用户变量

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

1）语法

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

2）赋值

create procedure proc_var03()
begin
    set @create_time = now();
    select @create_time;
end;

可看出，并未声明就直接赋值，但变量名前面必须带@符号。

3.3会话变量

系统提供的变量，当前会话有效。由于用的不多，在此略。

3.4全局变量

系统提供的变量，整个MySQL服务有效。由于用的不多，在此略。

4.入参和出参

4.1语法

in | out | inout param_name type

#in表示传入的参数
#out表示返回的参数
#inout表示传入的参数还作为返回的参数
#type表示参数的类型

4.2入参

1）创建存储过程

传入名字进行查询：

create procedure proc_var04(in name varchar(50))
begin
    select name;
end;

参数传入进来后就是局部变量，在整个存储过程内部可用，相当于方法的参数。若有多个参数，使用逗号分隔。

2）调用存储过程

call proc_var04('root');

4.3出参

1）创建存储过程

根据传入的手机号，截取后4位返回：

create procedure proc_var05(in phone varchar(11),out phone_suffix varchar(4))
begin
    set phone_suffix = right(phone,4);
end;

2）调用存储过程

call proc_var05('15623524651',@suffix);
select @suffix;

执行结果是4651。需要注意的是，在接收输出参数时，必须使用用户变量，否则接收不到，调用时还会出错。

4.4入参和出参

1）创建存储过程

create procedure proc_var06(inout name varchar(100))
begin
    set name = concat('hello,',name);
end;

对结果处理后返回。

2）调用存储过程

set @username='张三';
call proc_var06(@username);
select @username;

5.判断（流程控制）

5.1 if判断

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

1）仅有if

create procedure proc_var07(in age int)
begin
    declare msg varchar(20) default '';
    if age = 20 then
        set msg = '年龄为20';
    end if;
    select msg;
end;

2）if-else

create procedure proc_var08(in age int)
begin
    declare msg varchar(20) default '';
    if age = 20 then
        set msg = '年龄为20';
    else
        set msg = '年龄不为20';
    end if;
    select msg;
end;

3）if-elseif-else

create procedure proc_var09(in age int)
begin
    declare msg varchar(20) default '';
    if age > 20 then
        set msg = '年龄大于20';
    elseif age < 20 then
        set msg = '年龄小于20';
    else
        set msg = '年龄等于20';
    end if;
    select msg;
end;

5.2 case分支

1）case语法

case的语法有两种，如下，MySQL的查询使用case和存储过程使用case类似：

第一种：

case value
    when value1 then result1;
    when value2 then result2;
    when value3 then result3;
    #...可有多个
    else resultn;
end case;

第二种：

case
    when expr1 then result1;
    when expr2 then result2;
    when expr3 then result3;
    #...可有多个
    else resultn
end case;

2）存储过程使用

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;

6.循环（流程控制）

6.1继续、结束循环

由于继续或结束循环常用在循环中，先行说明。

1）leave结束循环

leave用于结束循环，其后的语句不会执行。

2）iterate继续循环

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

6.2 loop循环

1）语法

[begin_label:] loop
    statement_list
end loop [begin_label]

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

2）存储过程使用

打印1到10：

create procedure proc_var11()
begin
    declare msg varchar(200) default '1';
    declare curr int default 1;

    cnt:loop
    
      if curr >= 10 then
        leave cnt;#结束循环
      end if;

      set curr = curr + 1;
      set msg = concat(msg,',',curr);
        
    end loop cnt;
    
    select msg;
end;

leave在不符合条件时结束循环。

打印1到10中偶数：

create procedure proc_var11()
begin
    declare msg varchar(200) default '';
    declare curr int default 1;

    cnt:loop
    
        if curr >= 10 then
            leave cnt;#结束循环
        end if;
            
        set curr = curr + 1;
        if (curr mod 2) then
            iterate cnt;
        else
            set msg = concat(msg,',',curr);    
        end if;            
        
    end loop cnt;
    
    select msg;
end;

iterate在不符合条件时直接进入下一次循环。

6.3 repeat循环

1）语法

[begin_label:] repeat
    statement_list
until condition
end repeat [begin_label]

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

2）存储过程使用

打印1到10：

create procedure proc_var12()
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 >= 10 #符合条件结束循环  
    end repeat cnt;
    
    select msg;
end;

需要注意的是，until后不能加逗号。

6.4 while循环

1）语法

[begin_label:] while condition do    
    statement_list
end while [begin_label]

2）存储过程使用

打印1到10：

create procedure proc_var13()
begin
    declare msg varchar(100) default '';
    declare curr int default 1;
    
    cnt:while curr<=10 do

        set msg = concat(msg,',',curr);
        set curr = curr + 1;

    end while;
    select msg;
end;

7.游标

7.1数据准备

创建表并添加数据

CREATE 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;
CREATE 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);

2）查询开发部的所有员工的基本信息，包含工资：

select emp.* from emp,dept
where emp.dept_no = dept.dept_no and dept.dept_name = '开发部';

若先使用存储过程给开发部所有员工加120块的工资，如何去做？

7.2游标语法

#声明游标
declare cursor_name for select_statement
#打开游标
open cursor_name 
#遍历取值
fetch cursor_name into var_name ...
#关闭游标，使用完后需关闭
close cursor_name

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

使用fetch关键字进行遍历，但当结果集遍历到最后一条时，它会报错，错误码是1329，错误状态是02000，那么就需要使用Handler（句柄）来判断异常进行处理。

需要注意的是，声明必须按照顺序，变量声明、游标声明、句柄声明的先后顺序不能错。

7.3使用游标

1）给开发部的员工涨工资的存储过程：

create procedure proc_var14(in dept_name varchar(200),in add_sal decimal)
begin
    declare user_id int;
    declare finished int default 0;
    
    declare cur_emp cursor for 
        select e.id  from emp e,dept d
        where e.dept_no = d.dept_no and d.dept_name = dept_name;
        
    #声明句柄,
    declare continue handler for 1329 set finished = 1;

    open cur_emp;
    
    emp_loop:loop
        fetch cur_emp into user_id;
        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;

声明句柄的注意事项：

（1）声明类型为continue时：当未出现1329时，执行后续语句，当出现1329时，就执行后面的set语句
（2）声明类型为exit时：直接退出存储过程，后续的语句不会执行

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

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

2）调用存储过程：

call proc_var14('开发部',120);

调用后查询，工资已涨。

码农公寓

1.引言

2.语法结构

2.1创建的语法结构

2.2删除的语法结构

2.3示例演示

3.变量及赋值

3.1局部变量

1）语法

2）使用set赋值

2）使用into赋值

3.2用户变量

1）语法

2）赋值

3.3会话变量

3.4全局变量

4.入参和出参

4.1语法

4.2入参

1）创建存储过程

2）调用存储过程

4.3出参

1）创建存储过程

2）调用存储过程

4.4入参和出参

1）创建存储过程

2）调用存储过程

5.判断（流程控制）

5.1 if判断

1）仅有if

2）if-else

3）if-elseif-else

5.2 case分支

1）case语法

6.循环（流程控制）

6.1继续、结束循环

1）leave结束循环

2）iterate继续循环

6.2 loop循环

1）语法

2）存储过程使用

6.3 repeat循环

1）语法

2）存储过程使用

6.4 while循环

1）语法

2）存储过程使用

7.游标

7.1数据准备

7.2游标语法

7.3使用游标

相关文章