今天小编给大家分享一下MySQL的存储过程实例分析的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。
MySQL5.0版本开始支持存储过程,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法,存储过就是数据库SQL与层层面的代码封装与重用
1.有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
2.函数的普通特性:模块化,封装,代码复用
3.速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤
格式
创建存储过程
--创建存储过程 delimiter$$ createprocedureproc01() begin selectempno,enamefromemp; end$$ delimiter; --调用存储过程 callproc01;
定义变量
局部变量
用户自定义,在begin/end块中有效
delimiter$$ createprocedureproc02() begin declarevar_name01varchar(20)default'aaa';--声明/定义变量 setvar_naem01='zhangsan';--给变量赋值 selectvar_name01;--输出变量的值 end$$ dilimiter; callproc02();
用户变量
用户自定义,当前会话(连接)有效.类比java的成员变量
delimiter$$ createprocedureproc04() begin set@var_name01='beijing'; select@var_name01; end$$ dilimiter; callproc04(); select@var_name01;--外部也是可以使用用户变量
系统变量
1.系统变量有分为全局变量与会话变量
2.全局变量在MYSQL启动的时候由服务器自动将他的初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改
3.会话变量在每次建立一个新的连接的时候,由MYSQL来初始化,MYSQL会将当前所有全局变量的值复制一份,来作为会话变量
4.也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的
5.全局变量与会话变量的区别在于,对全局变量的修改会影响整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)
6.有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统变量的值缺是只读的,对于那些可以更改的系统变量,我们可以利用set语句进行更改
系统变量-全局变量
由系统系统,在整个数据库中有效
存储过程
存储过程传参-in
in表示传入的参数,可以传入数值或者变量,即使传入变量,并不会改变变量的值,可以内部更改,仅仅作用在函数范围内
delimiter$$ createprocedureproc06(inparam_empnoint) begin select*fromempwhereempno=param_empno end$$ delimiter; callproc06(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$$ createprocedureproc_12_if(inscoreint) begin ifscore<60thenselect'不及格'; elseifscore>=60andscore<80thenselect'及格'; elseifscore>=80andscore<90thenselect'良好'; elseifscore>=90andscore<=100thenselect'优秀'; elseselect'成绩错误' endif; end$$ delimiter; set@score=55; callproc_12_if(@score)
--输入员工的名字,判断工资的情况 /* sal<10000:适用薪资 sal>=10000andsal<20000:转正薪资 sal>=20000:元老薪资 */ delimiter$$ createprocedureproc_13_if(inin_enamevarchar) begin declarevar_saldecimal(7,2);--定义局部变量,7个长度,2个小数 declareresultvarchar(20); selectsalintovar_salfromempwhereename=in_eanme;--将值赋给局部变量 ifvar_sal<10000thensetresult='适用薪资'; elseifvar_sal<20000thensetresult='转正薪资'; elsesetresult='元老薪资'; endif; selectresult; end$$ delimiter; --调用 callproc_13_if('刘备');
流程控制语句-case
CASE是另一个条件判断语句,类似于编程语言中的switch语法
语法一:当case后面的语句与when后面的语句相等时,则会执行then后面的语句,如果均不匹配则执行else
语法二:直接写case 当when后面的条件成立则执行then后面的语句,如果都不成立,则执行else
--流程控制语句:case /* 支付方式: 1.微信支付 2.支付宝支付 3.银行卡支付 4.其他支付 */ --格式1 delimiter$$ createprocedureproc14_case(inpay_typeint) begin casepay_type when1thenselect'微信支付'; when2thenselect'支付宝支付'; when3thenselect'银行卡支付'; elseselect'其他方式支付'; endcase; end$$ delimiter; callproc14_case(2); callproc14_case(3);
--语法二 --输入学生的成绩,来判断成绩的级别 /* score<60:不及格 score>=60,score<80:及格 score>=80,score<90:良好 score>=90,score<=100:优秀 score>100:成就错误 */ delimiter$$ createprocedureproc_15_case(inscoreint) begin case whenscore<60thenselect'不及格'; whenscore>=60andscore<80thenselect'及格'; whenscore>=80andscore<90thenselect'良好'; whenscore>=90andscore<=100thenselect'优秀'; elseselect'成绩错误' endcase; end$$ delimiter; callproc_15_case(88);
控制流程-循环
概述
循环是一段在程序中只出现一次,但可能会连续运行多次的代码
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
循环分类
while、repeat、loop
循环控制
leave 类似于break,跳出,结束当前所在的循环
iteater 类似于continue,继续,结束本次循环,继续下一次
while循环
--需求:向表中添加指定条数据 --while delimiter$$ createprocedureproc16_while(ininsertCountint) begin declareiintdefault1;--设置循环的增量 label:whilei<=insertCountdoinsertintouser(id,username,password)values(i,concat('user-',i),'123456'); seti=i+1; endwhilelabel; end$$ delimiter; callproc16_while(10);
--需求:向表中添加指定条数据 --while+leave delimiter$$ createprocedureproc16_while(ininsertCountint) begin declareiintdefault1;--设置循环的增量 label:whilei<=insertCountdoinsertintouser(id,username,password)values(i,concat('user-',i),'123456'); ifi=5thenleavelabel;--如果i=5则跳出循环 seti=i+1; endwhilelabel; end$$ delimiter; callproc16_while(10);
repeat循环
格式
--存储过程-循环控制-repeat delimiter$$ createprocedureproc18_repaet(ininsertCountint) begin declareiintdefault1; label:repeat insertintouser(uid,username,password)values(i,concat('user-',i),'123456'); seti=i+1; untili>inserCount--这里不能写;表示跳出循环 endrepeatlabel; select'循环结束'; end$$ delimiter; callproc18_repeat(100);
loop循环
--存储过程-循环控制-loop delimiter$$ createprocedureproc19_loop(ininsertCountint) begin declareidefault1; label:loopinsertintouser(uid,username,password)values(i,concat('user-')i,'123456'); seti=i+1; ifi>insertCountthenleavelabel; endif; endlooplabel; end$$ delimiter; callproc19_loop()
游标
游标(cursor)是庸才存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理,光标的使用包括光标的声明、OPEN、FETCH、CLOSE
--操作游标(cursor) --声明游标 --打开游标 --通过游标获取值 --关闭游标 --需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标 delimiter$$ createprocedureproc19_cursor(inin_danmevarchar(50)) begin --定义局部变量 declarevar_empnoint; declarevar_enamevarchar(50); declarevar_saldecimal(7,2); --声明游标 declaremy_cursorcursorforselectempno,ename,salfromdepta,empbwherea.deptno=b.deptnoanda.dname=in_dname; --打开游标 openmy_cursor; --通过游标获取值 -- fetchmy_cursorintovar_empno,var_ename,var_sal; -- selectvar_empno,var_ename,var_sal;--注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环 label:loop fetchmy_cursorintovar_empno,var_ename,var_sal; selectvar_empno,var_ename,var_sal; endlooplabel; --关闭游标 closemy_cursor; end$$ delimiter; callproc19_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$$ createprocedureproc21_cursor_handler(inin_danmevarchar(50)) begin --定义局部变量 declarevar_empnoint; declarevar_enamevarchar(50); declarevar_saldecimal(7,2); --定义标记值 declareflagintdefault1; --声明游标 declaremy_cursorcursorforselectempno,ename,salfromdepta,empbwherea.deptno=b.deptnoanda.dname=in_dname; --定义句柄:定义异常的处理方式 /* 1:异常处理完之后,程序应该如何执行 continue:继续执行剩余代码 exit:直接终止程序 undo:不支持 2:触发条件 条件码:1329..... 条件名: SQLWARNING NOTFOUND SQLEXCEPTION 3:异常触发之后执行什么代码 设置flag的值->0 */ declarecontinuehandlerfor1329setflag=0; --打开游标 openmy_cursor; --通过游标获取值 -- fetchmy_cursorintovar_empno,var_ename,var_sal; -- selectvar_empno,var_ename,var_sal;--注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环 label:loop fetchmy_cursorintovar_empno,var_ename,var_sal; --判断flag的值如果为1则执行,否则不执行 ifflag=1then selectvar_empno,var_ename,var_sal; elseleavelabel; endif; endlooplabel; --关闭游标 closemy_cursor; end$$ delimiter; callproc19_cursor('销售部');
以上就是“MySQL的存储过程实例分析”这篇文章的所有内容,感谢各位的阅读!相信大家阅读完这篇文章都有很大的收获,小编每天都会为大家更新不同的知识,如果还想学习更多的知识,请关注恒创行业资讯频道。