MySQL的存储过程

一：介绍

（1）定义

MySQL 5.0 版本开始支持存储过程；

简单来说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

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

（2）特性

有输入输出参数，可以声明变量，有if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

函数的普遍特性：模块化，封装，代码复用；

速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

二：格式

delimiter $$
create procedure proc01()
begin
   select empno,ename from emp;
end $$
delimiter ;

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

三：变量定义

（1） 局部变量

use mydb1;
delimiter $$
create procedure proc02()
begin
   declare var_name01 varchar(20) default'aaa';-- 声明/定义变量(默认值)
   set var_name01 = 'zhangsan';-- 给变量赋值
   select var_name01;-- 输出变量值
end $$ 
  -- select var_name01;在此处无法使用，局部变量只在局部（begin和end之内）可以使用
delimiter ;

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

（2）局部变量其他赋值方式 

delimiter $$
create procedure proc03()
begin
     declare my_ename varchar(20) ;
	 set ename into my_ename from emp where empno = 1001;
	 select my_ename;
end $$ 

delimiter ;

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

（3）用户变量

delimiter $$
create procedure proc04()
begin
	 set @var_nam01 = 'beijing';
	 select @var_nam01;
end $$ 

delimiter ;

call proc04(); 

select @var_nam01;-- 在外部也可以使用

(4)系统变量

<1>介绍

系统变量又分为全局变量与会话变量；

全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改；

会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份，来做为会话变量；

也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的；

全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）；

有些系统变量的值是可以利用语句来动态进行修改的，但是有些系统的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改

<2>全局变量

<3>会话变量

四：参数传递

(1)in

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

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int)
begin
	 select * from emp where emp.empno = empno;
end $$ 

delimiter ;

call proc06(1001); 
call proc06(1002); 


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

delimiter ;

call proc07('学工部',20000); 
call proc07('销售部',10000); 

(2)out

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

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

delimiter ;

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


-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(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 proc09(1001,@o_ename,@o_sal); 
select @o_ename;
select @o_sal;

(3)inout

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

-- 传入一个数字，传出这个数字的10倍
delimiter $$
create procedure proc10(inout num int)
begin 
  set num = num*10;
end $$
delimiter ;

set @inout_num = 3;
call proc10(@inout_num);

select @inout_num;


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

delimiter ;

set @inout_ename = '关羽';
set @inout_sal = 3000;

call proc11(@inout_ename,@inout_sal);

select @inout_ename;
select @inout_sal

五：流程控制

（1）if判断

if语句包含多个条件判断，根据结果为true,false执行语句，与编程语言中的if,else,else if 语法类似，其语法格式如下

-- 案例一：判断成绩级别
delimiter $$
create procedure proc_12_if(in score int)
begin
     if score<60 then select '不及格';
	 elseif score>=60 and score <80 then select '及格';
	 elseif score>=80 and score <90 then select '良好';
	 elseif score>=90 and score <=100 then select '优秀';
	 else select '成绩错误';
	 end if;
end $$
delimiter ;

call proc_12_if(64);


-- 案例二：输入员工名字，判断工资情况
/*   sal<10000:试用薪资
	 sal>=10000 and sal<20000:转正薪资
	 sal>=20000:元老薪资
*/
delimiter $$
create procedure proc_13_if(in ename varchar(20))
begin
    declare var_sal decimal(7,2);
	declare result varchar(20);
    select sal into var_sal from emp where ename = in_ename;
	
	if var_sal < 10000 then set result = '试用薪资';
    elseif var_sal < 20000 then set result = '转正薪资';
    else set result = '元老薪资';
	end if;
select result;
end $$
delimiter ;

call proc_13_if('关羽');

(2)case

case是另一个条件判断的语句，类似于编程语言中的switch语法

-- 格式一
-- 支付方式判断
/*      1 微信支付
        2 支付宝支付
		3 银行卡支付
		4 其他方式支付
*/
use mydb1;
delimiter $$
create procedure proc_14_case(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 proc_14_case(2);


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

call proc_15_case(64);

（3）循环

<1>while

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

-- 需求：向表中添加指定条数的数据


--      存储过程-循环-while
delimiter $$
create procedure proc_16_while(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');
	   set i = i+1;
   end while label;
end $$
delimiter ;

call proc_16_while(10);


--      存储过程-循环-while+leave
truncate table user;
-- 清空表
delimiter $$
create procedure proc_17_while_leave(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 proc_17_while_leave(10);


--      存储过程-循环-while+iterate
truncate table user;
-- 清空表
delimiter $$
create procedure proc_17_while_iterate(in insertcount int)
begin 
   declare i int default 0;
	  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;
   select '循环结束';
end $$
delimiter ;

call proc_17_while_iterate(10);

<2>repeat

--      存储过程-循环-repeat
truncate table user;
delimiter $$
create procedure proc_18_repeat(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 proc_18_repeat(10);

<3>loop

--      存储过程-循环-loop
truncate table user;

delimiter $$
create procedure proc_19_loop(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>insertcount 
           then
            leave label;
		 end if;

	end loop label;
end $$
delimiter ;

call proc_19_loop(10);

(4)游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果进行循环的处理。光标的使用包括光标的声明,open,fetch,close。

-- 需求：输入一个部门名，查询该部门员工的编号，名字，薪资，将查询结果输出
delimiter $$
create procedure proc19_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
	declare var_empno int;
	declare var_ename varchar(50);
	declare var_sal decimal(7,2);
	
	-- 声明游标
	declare my_cursor cursor for
	  select empno,ename,sal
		from dept a,emp b
		  where a.deptno = b.deptno and a.dname = in_dname;
		
  -- 打开游标
	open my_cursor;
	
	-- 通过游标获取值(fetch一次，显示出一个值)
	label:loop
	  fetch my_cursor into var_empno,var_ename,var_sal;
	  select var_empno,var_ename,var_sal;
	end loop label;
	
	-- 关闭游标
	close my_cursor;
end $$
delimiter ;

call proc19_cursor();

(5)异常处理

-- 需求：输入一个部门名，查询该部门员工的编号，名字，薪资，将查询结果输出
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin
  -- 定义局部变量
	declare var_empno int;
	declare var_ename varchar(50);
	declare var_sal decimal(7,2);
	
	-- 定义标记值
	declare flag int default 1;
	
	-- 声明游标
	declare my_cursor cursor for
	  select empno,ename,sal
		from dept a,emp b
		  where a.deptno = b.deptno and a.dname = in_dname;
		
	-- 定义句柄：定义异常的处理方式
	/*
	     1：异常处理完之后程序该怎么执行
		     continue：继续执行剩余代码
				 exit：直接终止程序
				 undo：不支持
				 
		 2：触发条件
		     条件码：1329
			 条件名：sqlwarning
				    not found
				    sqlexception
								 
		 3：异常触发后执行什么代码
		     设置flag的值------>0 
	*/
	
	declare continue handler for 1329 set flag = 0;
	
    -- 打开游标
	open my_cursor;
	
	-- 通过游标获取值(fetch一次，显示出一个值)
	label:loop
	    fetch my_cursor into var_empno,var_ename,var_sal;
		-- 判断flag，如果flag的值为1，则执行，否则不执行
		if flag = 1 then 
	       select var_empno,var_ename,var_sal;
	    else
		   leave label;
	    end if;
	end loop label;
	
	-- 关闭游标
	close my_cursor;
end $$
delimiter ;

call proc21_cursor_handler('销售部');

六：MySQL的存储函数

-- 允许创建函数权限信任
set global log_bin_trust_function_creators = TRUE;
-- 创建存储函数--没有参数
drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin
     -- 定义局部变量
	 declare cnt int default 0;
	 select count(*) into cnt from emp;
	 return cnt;
end $$
delimiter ;

-- 调用存储函数
select myfunc1_emp();


-- 创建存储函数--有输入参数
-- 需求：传入一个员工编号，返回员工的名字
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int)returns varchar(50)
begin 
        declare out_ename varchar(50);
		select ename into out_ename from emp where empno = in_empno;
		return out_ename;
end $$
delimiter ;

-- 调用存储函数
select myfunc2_emp(1008);