MySQL存储过程的概念与用法实例

概念

mysql5.0版本开始支持存储过程,存储过程就是一组sql语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于java语言中的方法,存储过就是数据库sql与层层面的代码封装与重用

特性

1.有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能

2.函数的普通特性:模块化,封装,代码复用

3.速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤

存储过程通常有以下优点

(1).存储过程增强了sql语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的sql语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的transaction-sql代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的transaction-sql语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的transaction-sql语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

格式

创建存储过程

-- 创建存储过程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_empnoend $$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.其他支付*/-- 格式1delimiter $$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循环

-- 需求:向表中添加指定条数据-- whiledelimiter $$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+leavedelimiter $$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存储过程内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！