数据库基础知识---------------------------（2）

MYSQL的存储过程

就是数据库 SQL 语言层面的代码封装与重用

语法格式

delimiter 自定义结束符号

create procedure 存储名({in,out,inout} 参数名,数据类型...)

begin

sql 语句

end 自定义结束符

delimiter;

变量定义

局部变量用户自定义仅在begin / end 块中有效

当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列或单行多列
delimiter $$
create procedure proc03()
begindeclare my_ename varchar(20) [default 'Peter'] ;--   定义局部变量set @var_name = 'ZS';--  用户变量（会话变量）不用提前声明 使用即声明select ename into my_ename from emp where empno=1001;--  用select into 为变量赋值select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();
select @var_name;  --  可以看到结果
系统变量
会话变量
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份来作为会话变量。
由系统提供，当前会话（连接）有效
语法：
@@session.var_name
全局变量
由系统提供，在整个数据库有效。
语法：
@@global.var_name

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

存储过程传参

in：表示传入的参数，可以是数值或者变量。

即使传入变量也不会更改变量的值，可以内部更改，仅仅作用在函数范围内

out：表示从存储过程内部传值给调用者
# 案例：
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc04(in in_empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
beginselect ename,sal into out_ename,out_sal from emp where empno = in_empno;
end $$delimiter ;call proc04(1001, @o_dname,@o_sal);
select @o_dname,@o_sal;
inout：表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值
案例：
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
beginselect  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 proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;
in 输入参数，参数要传到存储过程的过程里面去，
在存储过程中修改该参数的值不能被返回

out 输出参数:该值可在存储过程内部被改变，并向外输出

inout 输入输出参数，既能输入一个值又能传出来一个值

流程控制

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 proc12_if(in in_ename varchar(50))
begindeclare 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 < 30000then set result = '转正薪资';else set result = '元老薪资';end if;select result;
end$$
delimiter ;
call proc12_if('庞统');

case 判断

语法格式一：
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case

delimiter $$
create procedure proc14_case(in pay_type int)
begincase pay_typewhen  1   then select '微信支付' ;when  2 then select '支付宝支付' ;when  3 then select '银行卡支付';else select '其他方式支付';end case ;
end $$
delimiter ;call proc14_case(2);

语法格式二：
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case

 delimiter  $$
create procedure proc_15_case(in score int)
begincasewhen 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 proc_15_case(88);

while 循环

[label:]while 循环条件 do
循环体;
end while[ label];

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begindeclare i int default 1;label:while i<=insertcount doinsert 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 proc16_while2(10);-- ---------------------------------------------------------------------------------------- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begindeclare i int default 1;label:while i<=insertcount doset 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 proc16_while3(10);

leave 类似于 break，跳出，结束当前所在的循环
iterate类似于 continue，继续，结束本次循环，继续下一次

repeat循环

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

truncate table user;
delimiter $$
create procedure proc18_repeat(in insertCount int)
begindeclare i int default 1;label:repeatinsert into user(uid, username, password) values(i,concat('user-',i),'123456');set i = i + 1;until  i  > insertCountend repeat label;select '循环结束';
end $$
delimiter ;call proc18_repeat(100);

loop循环

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

 truncate table user;
delimiter $$
create procedure proc19_loop(in insertCount int) 
begindeclare i int default 1;label:loopinsert 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 proc19_loop(10);

游标

用来存储查询结果集的数据类型，在存储过程中可以使用光标对结果集进行循环的处理

-- 声明语法
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_cursor(in in_dname varchar(50))
begin-- 定义局部变量declare var_empno varchar(50);declare var_ename varchar(50);declare var_sal  decimal(7,2);-- 声明游标declare my_cursor cursor forselect empno , ename, sal from  dept a ,emp bwhere a.deptno = b.deptno and a.dname = in_dname;-- 打开游标open my_cursor;-- 通过游标获取每一行数据label:loopfetch my_cursor into var_empno, var_ename, var_sal;select var_empno, var_ename, var_sal;end loop label;-- 关闭游标close my_cursor;
end-- 调用存储过程call proc20_cursor('销售部');

异常处理--handler句柄

格式：
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement

handler_action: {
CONTINUE
| EXIT
| UNDO
}

condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION

drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc20_cursor(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 forselect empno,ename,salfrom dept a, emp bwhere a.deptno = b.deptno and a.dname = in_dname;-- 定义句柄，当数据未发现时将标记位设置为0declare continue handler for NOT FOUND set flag = 0;   -- 打开游标open my_cursor;-- 通过游标获取值label:loopfetch my_cursor into var_empno, var_ename,var_sal;-- 判断标志位if flag = 1 thenselect var_empno, var_ename,var_sal;elseleave label;end if;end loop label;-- 关闭游标close my_cursor;
end $$;delimiter ;
call proc21_cursor_handler('销售部');

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