MySQL的存储过程实例解析

2024-07-24 12:32:51

字体：大中小

来源：转载

供稿：网友

　　今天小编给大家分享一下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块中有效

　　MySQL的存储过程实例分析

　　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的存储过程实例分析”这篇文章的所有内容，感谢各位的阅读！

（编辑：武林网）

上一篇：在mysql中有没有嵌套查询语句

下一篇：mysql的log文件要怎么看