MySQL高阶(六)——存储过程

6.MySQL高阶——存储过程

存储过程

存储过程就是数据库SQL语言层面的代码封装与重用。

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类型...)
begin
  sql语句...
end 自定义的结束符合
delimiter ;

数据准备

-- 1：创建数据库 
create database my_procedure; 
use my_procegure;

-- 2：在该数据库下导入sql脚本:procedure_data.sql

-- 3：创建存储过程
delimiter $$
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;

-- 调用存储过程
call proc01(); 

变量定义

局部变量

声明变量 declare var_name var_type [default var_value]; 

实例

### 局部变量  ################
delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default 'aaa';  -- 定义局部变量
    set var_name01 = 'zhangsan';-- 修改变量的值
    select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();

##或者使用select...into语句为变量赋值
### 局部变量  ################
delimiter $$
create procedure proc03()
begin
  declare my_ename varchar(20) ;
  select ename into my_ename from emp where empno=1001;
  select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();

会话变量-用户变量

@var_name 不需要提前声明，使用即声明

实例：

########### 会话变量 #################
delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter ;
call proc04() ;
-- 调用存储过程
select @var_name01;

全局变量

@@global.var_name

实例：

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000;

会话变量-系统变量

@@session.var_name

实例：

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

存出过程传参

存储过程传参-in

in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

实例：

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
        select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');


-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param02(in dname varchar(50),in sal decimal(7,2))
begin
        select * from dept a join emp b on a.deptno= b.deptno and b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param02('学工部',20000);

存储过程传参-out

out 表示从存储过程内部传值给调用者

实例：

-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure dec_param03(in in_empno int ,out out_ename varchar(50) )
begin
  select ename into out_ename from emp where empno = in_empno;
end $$
delimiter ;

call dec_param03(1001, @o_ename);
select @o_ename;

-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure dec_param04(in in_empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
  select ename,sal into out_ename,out_sal from emp where empno = in_empno;
end $$
delimiter ;

call dec_param04(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

存储过程传参-inout

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

实例：

-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc05(inout inout_ename varchar(50),inout inout_sal int)
begin
  select  concat(deptno,'_',inout_ename) into inout_ename from emp where ename = inout_ename;
  set inout_sal = inout_sal * 12;
end $$
delimiter ;

set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc05(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

流程控制—判断

if判断语句

-- 语法
if search_condition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2] ...
    [else statement_list_n]
end if

实例：

-- 输入学生的成绩，来判断成绩的级别
delimiter  $$
create procedure proc_06(in score int)
begin
    if score < 60  then select '不及格';
    elseif  score < 80  then select '及格' ;
    elseif  score < 90  then  select '良好';
    elseif  score <= 100 then select '优秀';
    else select '成绩错误';
    end if;
end $$
delimiter  ;

call proc_06(89)

加强

-- 输入员工的名字，判断工资的情况。
delimiter  $$
create procedure proc07(in in_ename varchar(50))
begin
    declare result varchar(20);
    declare var_sal decimal(7,2);
        select sal into  var_sal from emp where ename = in_ename;
    if var_sal < 10000
        then set result = '试用薪资';
    elseif var_sal < 30000
        then set result = '转正薪资';
    else
        set result = '元老薪资';
    end if;
    select result;
end $$
delimiter ;
call proc07('庞统');

case判断

-- 语法一：
case case_value
    when when_value then statement_list
    [when when_value then statement_list] ...
    [else statement_list]
end case
-- 语法二：
case
    when search_condition then statement_list
    [when search_condition then statement_list] ...
    [else statement_list]
end case

实例：

-- 语法一
delimiter $$
create procedure proc07(in pay_type int)
begin
  case pay_type
        when  1 then select '微信支付' ;
        when  2 then select '支付宝支付' ;
        when  3 then select '银行卡支付';
       else select '其他方式支付';
   end case ;
end $$
delimiter ;

call proc07(2);
call proc07(4);

-- 语法二
delimiter  $$
create procedure proc08(in score int)
begin
  case
    when score < 60  then  select '不及格';
    when score < 80  then   select '及格' ;
    when score < 90  then select '良好';
    when score <= 100 then  select '优秀';
    else select '成绩错误';
  end case;
end $$
delimiter  ;

call proc08(88);

流程控制—循环

循环控制

（1）leave类似于break，跳出，结束当前所在的循环

（2）iterate类似于continue，继续，结束本次循环，继续下一次

while循环

【标签:】while 循环条件 do
    循环体;
end while【 标签】;

准备数据：

############ 循环条件 ###################
-- 创建测试表
create table user (
    uid int primary key,
    username varchar ( 50 ),
    password varchar ( 50 )
);

实例1：while

-- -------存储过程-while
delimiter $$
create procedure proc09(in insertcount int)
begin
    declare i int default 1;
    while i<=insertcount do
       insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
       set i=i+1;
    end while;
end $$
delimiter ;

call proc09(10);

实例2：while + leave

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc10(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;

call proc10(10);

实例3：while + iterate

-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc11(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        set i=i+1;
        if i=5 then iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
    end while label;
end $$
delimiter ;

call proc11(10);

repeat循环

[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];

实例：

-- -------存储过程-循环控制-repeat
truncate table user;
delimiter $$
create procedure proc12(in insertCount int)
begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         until  i  > insertCount
     end repeat label;
     select '循环结束';
end $$
delimiter ;

call proc12(100);  ##循环到100结束

loop循环

[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

实例：

-- -------存储过程-循环控制-loop
truncate table user;
delimiter $$
create procedure proc13(in insertCount int)
begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5
          then
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$
delimiter ;

call proc13(10);

说明：loop相当于死循环，需要结合判断条件来结束进程。

-- -------存储过程-循环控制-loop
truncate table user;
delimiter  $$
create procedure proc13(in insertCount int)
begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5
          then
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$
delimiter ;

call proc13(10);
``

说明：loop相当于死循环，需要结合判断条件来结束进程。