共计 6954 个字符，预计需要花费 18 分钟才能阅读完成。
今天丸趣 TV 小编给大家分享一下 MySQL 的存储过程实例分析的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
MySQL5.0 版本开始支持存储过程, 存储过程就是一组 SQL 语句集, 功能强大, 可以实现一些比较复杂的逻辑功能, 类似于 JAVA 语言中的方法, 存储过就是数据库 SQL 与层层面的代码封装与重用
1. 有输入输出参数, 可以声明变量, 有 if/else/case/while 等控制语句, 通过编写存储过程, 可以实现复杂的逻辑功能
2. 函数的普通特性: 模块化, 封装, 代码复用
3. 速度快, 只有首次执行需要经过编译和优化步骤, 后续被调用可以直接执行, 省去以上步骤
格式创建存储过程
-- 创建存储过程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01;定义变量局部变量
用户自定义, 在 begin/end 块中有效
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default aaa -- 声明 / 定义变量
set var_naem01 = zhangsan -- 给变量赋值
select var_name01; -- 输出变量的值
end $$
dilimiter ;
call proc02();用户变量
用户自定义, 当前会话 (连接) 有效. 类比 java 的成员变量
delimiter $$
create procedure proc04()
begin
set @var_name01 = beijing
select @var_name01;
end $$
dilimiter ;
call proc04();
select @var_name01; -- 外部也是可以使用用户变量系统变量
1. 系统变量有分为全局变量与会话变量
2. 全局变量在 MYSQL 启动的时候由服务器自动将他的初始化为默认值, 这些默认值可以通过更改 my.ini 这个文件来更改
3. 会话变量在每次建立一个新的连接的时候, 由 MYSQL 来初始化,MYSQL 会将当前所有全局变量的值复制一份, 来作为会话变量
4. 也就是说, 如果在建立会话以后, 没有手动更改过会话变量与全局变量的值, 那所有这些变量的值都是一样的
5. 全局变量与会话变量的区别在于, 对全局变量的修改会影响整个服务器, 但是对会话变量的修改, 只会影响到当前的会话(也就是当前的数据库连接)
6. 有些系统变量的值是可以利用语句来动态进行更改的, 但是有些系统变量的值缺是只读的, 对于那些可以更改的系统变量, 我们可以利用 set 语句进行更改
系统变量 - 全局变量
由系统系统, 在整个数据库中有效
存储过程存储过程传参 -in
in 表示传入的参数, 可以传入数值或者变量, 即使传入变量, 并不会改变变量的值, 可以内部更改, 仅仅作用在函数范围内
delimiter $$
create procedure proc06(in param_empno int)
begin
select*from emp where empno = param_empno
end $$
delimiter ;
call proc06(1001);多个参数传参
存储过程传参 -out
out 表示从存储过程内部传值给调用者
存储过程传参 -inout
inout 表示从外部传入的参数经过修改后可以返回的变量, 既可以使用传入变量的值也可以修改变量的值(即使函数执行完)
流程控制流程控制 - 判断
IF 语句包含多个条件判断, 根据结果为 TURE 和 FALSE 执行语句, 与编程语言中的 if、else if、else 语法类似, 其语法格式如下
-- 存储过程 -if
-- 案例 1
-- 输入学生的成绩, 来判断成绩的级别
score 60: 不及格
score =60 , score 80 : 及格
score =80 , score 90 : 良好
score =90 , score =100 : 优秀
score 100 : 成就错误
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 ;
set @score=55;
call proc_12_if(@score)
-- 输入员工的名字, 判断工资的情况
sal 10000 : 适用薪资
sal =10000 and sal 20000 : 转正薪资
sal =20000 : 元老薪资
delimiter $$
create procedure proc_13_if(in in_ename varchar)
begin
declare var_sal decimal(7,2); -- 定义局部变量,7 个长度,2 个小数
declare result varchar(20);
select sal into var_sal from emp where ename=in_eanme; -- 将值赋给局部变量
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(刘备流程控制语句 -case
CASE 是另一个条件判断语句, 类似于编程语言中的 switch 语法
语法一: 当 case 后面的语句与 when 后面的语句相等时, 则会执行 then 后面的语句, 如果均不匹配则执行 else
语法二: 直接写 case 当 when 后面的条件成立则执行 then 后面的语句, 如果都不成立, 则执行 else
-- 流程控制语句: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(销售部
以上就是“MySQL 的存储过程实例分析”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。
