• 周六. 8月 20th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL存储过程

admin

11月 28, 2021

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

调用后查询,工资已涨。

就是这么简单,你学废了吗?感觉有用的话,给笔者点个赞吧 !

发表回复

您的电子邮箱地址不会被公开。