MySQL的存储过程

存储过程：

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

简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法:存储过就是数据库SQL语言层面的代码封装与重用。

特性：

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

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

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

格式：

delimiter 自定义结束符号

create procedure  储存名  ([ in ,out ,inout ]  参数名  数据类形...)

begin 

sql语句

end自定义的结束符合

delimiter ;

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

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

1，变量定义

（1）局部变量

格式：

用户自定义，在begin/end块中有效

语法:声明变量  declare  var_name  type [default var_value];

举例: declare nickname varchar ( 32);

delimiter $$
create procedure proc02()
begin 
 declare var_name01 varchar(20) default 'aaa'; -- 声明，定义变量
 set var_name01='zhangsan'; -- 给变量赋值
 select var_name01; -- 输出变量的值
 end  $$ 
delimiter;

call proc02();

MySQL中还可以使用SELECT..IlNTO语句为变量赋值。其基本语法如下:

select col_name [ ...]  into var_name [ , ...]

from table_name wehre condition

其中:

col_name参数表示查询的字段名称;

var_name参数是变量的名称;

table_name参数指表的名称;

condition参数指查询条件

注意:当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列

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;

（2）用户变量

格式：

用户自定义，当前会话（连接）有效。类比java的成员变量

语法:

@var_name

不需要提前声明,使用即声明

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

select @var_name01;-- 也可以使用

（3）系统变量

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

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

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

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

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

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

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

1）全局变量

由系统提供，在整个数据库有效。

格式：

语法:

@@global.var_name

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

2）会话变量

由系统提供，当前会话（连接）有效

格式：

语法：

@@session.var_name

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

2，参数传递

（1）in

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

delimiter $$
create procedure proc06(in empno int)
begin 
 select * from emp where emp.empno=empno;
 end $$
delimiter;
call proc06(1001);

delimiter $$
create procedure proc07(in param_dname varchar(50),in param_sal decimal(7,2))
begin 
 select * from dept a,emp b where a.deptno=b.deptno and a.dname=param_dname  and b.sal>param_sal;
 end $$
delimiter;
call proc07('学工部',20000);

（2）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; 
-- 传入1001
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; 
-- 传入1001
call proc09(1001,@o_ename,@o_sal);

select @o_ename,@o_sal;

（3）inout

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

delimiter $$
create procedure proc10(inout num int)
begin 
 set num=num*10;
 end $$
delimiter; 

set @inout_num=2;
call proc10(@inout_num);
select @inout_num;


delimiter $$
create procedure proc11(inout ename varchar(50),inout sal int)
begin 
 select concat_ws('_',deptno,ename) into ename from emp where emp.ename=ename;
 set sal=sal*12;
 end $$
delimiter; 

set @ename='关羽';
set @sal=3000;

call proc11(@ename,@sal);

select @ename, @sal;

3，流程控制

（1）分支语句

1）if

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

语法：

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 proc12(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 proc12(78);

2）case

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

语法1：

case case_value

        when when_value then statement_list

        [when when_value then statement_list] ...

        [else statement_list]

end case

语法2：

case

        when search_condition then statement_list

        [when search_condition then statement_list] ...

        [else statement__list]

end case

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

call proc13(1);

（2）循环语句

循环是一段在程序中只出现一次,但可能会连续运行多次的代码

循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

分类：

while

repeat

loop

循环控制：

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

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

1）while

【标签: 】 while循环条件do

        循环体;

end while【标签】;

delimiter $$
create procedure proc16(in insertCount int)
begin 
declare i int default 1;
 co:while i<=insertCount do 
 insert into user(uid,username,password) values(i,concat('user-',i),'123456');
 set i=i+1;
 end while co;
 end $$
delimiter; --其中co为标签

-- while-leave
delimiter $$
create procedure proc17(in insertCount int)
begin 
declare i int default 1;
 co:while i<=insertCount do 
  insert into user(uid,username,password) values(i,concat('user-',i),'123456');
  if i=5 then 
   leave co;
   end if;
  set i=i+1;
 end while co;
end $$
delimiter; 

-- while iterate 同上

2）repeat

格式：

[标签: ]repeat

循环体;

until条件表达式

end repeat [标签];

delimiter $$
create procedure proc18(in insertCount int)
begin 
declare i int default 1;
 co:repeat 
 insert into user(uid,username,password) values(i,concat('user-',i),'123456');
 set i=i+1;
 until i>insertCount
 end repeat co;
 end $$
delimiter;

3）loop

[标签:]loop

循环体;

if 条件表达式 then

        leave[标签];

end if;

end loop;

delimiter $$
create procedure proc19(in insertCount int)
begin 
declare i int default 1;
 co:loop 
  insert into user(uid,username,password) values(i,concat('user-',i),'123456');
  set i=i+1;
	
  if i>insertCount then 
    leave co;
  end if;
	
  end loop co;
 end $$
delimiter;

4，游标

游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE.

格式：

声明游标：

declare cursor_name cursor for select_statement

打开游标：

open cursor_name

取值游标：

fetch cursor_name into var_name [, var_name] ...

关闭游标：

close cursor_name

-- 输入部门号，查询部门员工的编号，名字，薪资，查询结果进行添加游标
delimiter $$
create procedure proc20(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;
	-- 通过游标获取值
	co:loop 
	fetch my_cursor into var_empno,var_ename,var_sal;
	select var_empno,var_ename,var_sal;
	end loop co;
	-- 关闭游标
	close my_cursor;
end $$;
delimiter;

5，异常处理

句柄handler

格式：

DECLARE handler_action HANDLER

        FORcondition_value l, condition_value] ...

        statement

handler_action: {

        CONTINUE

      |  EXIT

      |  UNDO

}

condition_value: {

        mysql_error_code

        | condition_name

        | SQLWARNING

        | NOT FOUND

        | SQLEXCEPTION 

}

特别注意:

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错

continue:继续执行剩余代码
        exit:直接终止程序
        undo:不支持
        
        触发条件：
            条件码：
            1329
            条件名：
            SQLWARNING
            NOT FOUND 
            SQLEXCEPTION

-- 包含异常处理
-- 输入部门号，查询部门员工的编号，名字，薪资，查询结果进行添加游标
delimiter $$
create procedure proc20(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;
	-- 定义句柄：	定义异常的处理方式
		/*
		continue:继续执行剩余代码
		exit:直接终止程序
		undo:不支持
		
		触发条件：
			条件码：
			1329
			条件名：
			SQLWARNING
			NOT FOUND 
			SQLEXCEPTION
			
		*/
	declare	continue handler for 1329 flag=0;
	-- 打开游标
	open my_cursor;
	-- 通过游标获取值
	co:loop 
	fetch my_cursor into var_empno,var_ename,var_sal;
	-- 判断 1：执行 0：结束
	if flag=1 then
	select var_empno,var_ename,var_sal;
	 else 
	  leave co;
	 end if;
	end loop co;
	-- 关闭游标
	close my_cursor;
end $$;
delimiter;