MySQL存储过程的概念与用法实例(2)-木庄网络博客

当前第2页返回上一页

-- 流程控制语句:case
/*
支付方式:
	1.微信支付
	2.支付宝支付
	3.银行卡支付
	4.其他支付
*/
-- 格式1
delimiter $$
create procedure proc14_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 proc14_case(2);
call proc14_case(3);

-- 语法二
-- 输入学生的成绩,来判断成绩的级别
/*
score<60:不及格
score>=60 , score<80 :及格
score>=80 , score<90 :良好
score>=90 , score<=100 :优秀
score>100 :成就错误
*/
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(88);

控制流程-循环

概述

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

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

循环分类

while、repeat、loop

循环控制

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

iteater 类似于continue,继续,结束本次循环,继续下一次

while循环

-- 需求:向表中添加指定条数据
-- while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
	declare i int default 1;  -- 设置循环的增量
	label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');
	set i=i+1;
end while label;
end$$
delimiter ;
 
 
call proc16_while(10);

-- 需求:向表中添加指定条数据
-- while+leave
delimiter $$
create procedure proc16_while(in insertCount int)
begin
	declare i int default 1;  -- 设置循环的增量
	label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');
	if i=5 then leave label;   -- 如果i=5则跳出循环
	set i=i+1;
end while label;
end$$
delimiter ;
 
 
call proc16_while(10);

repeat循环

格式

-- 存储过程-循环控制-repeat
 
delimiter $$
create procedure proc18_repaet(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>inserCount  -- 这里不能写; 表示跳出循环
		end repeat label;
		select '循环结束';
end$$
delimiter ;
 
call proc18_repeat(100);

loop循环

-- 存储过程-循环控制-loop
 
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
	declare i 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 proc19_loop()

游标

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

-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_danme 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 my_cursor into var_empno,var_ename,var_sal;
-- 	select var_empno,var_ename,var_sal;  -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
	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('销售部');

异常处理-HANDLER句柄

MySQL存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现

格式

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

DECALARE handler_action 创建句柄

FOR 后面为条件值,为什么异常提供处理

statment 当没有数据发现这个异常发生时采取什么措施

handler_action :{

CONTINUE 继续执行

EXIT 退出

UNDO 取消(目前MySQL不支持)

}

condition_value :{

mysql_error_code 错误码

condition 条件名

SQLWARNING SQL警告

NOT FOUND 数据没有发现

SQLEXCEPTION SQL异常

}

/*用于测试句柄,改造上面游标代码会出现1329错误代码的情况*/
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_danme 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 my_cursor into var_empno,var_ename,var_sal;
-- 	select var_empno,var_ename,var_sal;  -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
	label:loop
		fetch my_cursor into var_empno,var_ename,var_sal;
		-- 判断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 proc19_cursor('销售部');