MYSQL的存储过程

MYSQL的存储过程

概述

什么是存储过程?mysql5.0版本开始支持存储过程;简单的说,存储过程就是一组sql语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于java语言中的方法;存储过程就是数据库sql语言层面的代码封装与重用有哪些特征?有输入输出参数,可以声明变量,有if/else,cash,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;	函数的普遍特性:模块化,封装,代码重用	速度快,只有首次执行需要经过编译和优化步骤,后续掉用可以直接执行,省去以上步骤.

入门案例

格式:	delimiter 自定义结束符号	create procedure 存储名([in,out,inout] 参数名 数据类型.....)begin	sql语句end 自定义的结束符号delimitereg:	delimiter $$	create procedure proce() begin 	select empno,ename from emp;	 end $$	 delimiter;调用存储过程:call proce();

变量定义

1:局部变量

用户自定义,在begin/end块中生效语法: 声明变量 declare var_name type [default var_value];eg:	declare nickName varchar(32);small demo:		delimiter $$	create procedure pro()	begin	declare var_name varchar(20) default 'hh'; -- 定义/声明变量	set var_name='ene'; -- 给变量赋值	select var_name; -- 输出变量的值	end $$	delimiter;另一种方法为变量赋值:select col_name [...] into var_name[...] from table_name where ...col_name 表示查询的字段名称var_name 表示变量的名称table_name 表示表的名称注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列.small demo:		delimiter $$	create procedure pro()	begin	declare var_name varchar(20) ; -- 定义/声明变量	select ename into var_name from emp where empno=1;  -- 给变量赋值	select var_name; -- 输出变量的值	end $$	delimiter;

2:用户变量

用户自定义,当前会话(连接)有效,类似java的成员变量语法:@var_name 不需要提前声明,使用即声明eg:	delimiter $$	create procedure proce() begin 	set @var_name='1';		select  @var_name;	end $$	delimiter; 		call proce(); -- 输出 1select  @var_name; -- 输出 1

3:系统变量

①系统变量又分为全局变量和会话变量;②全局变量在mysql启动的时候由服务器自动将他们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改③会话变量在每次建立一个新的连接的时候,由mysql来初始化,mysql会将当前所有全局变量的值复制一份,来做为会话变量④也就是说如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的.⑤全局变量与会话变量的区别在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)⑥有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统变量的值却是只读的,对于哪些可以更改的系统变量,我们可以利用set语句进行更改全局变量	-- 查看全局变量	show global variables;	-- 查看某个全局变量	select @@global.名字;	-- 修改全局变量的值	set global 变量名 = 值;	set @@global.名字 = 值;会话变量	-- 查看会话变量	show session variables;	-- 查看某会话变量	select @@session.变量名;	-- 修改会话变量的值	set session 变量名 = 值;	set @@session.变量名 = 值;

储存过程-参数传递

存储过程传参-in

in 表示传入的参数,可以传入数值或者变量,即使传入变量,并不会更改变量的值,可以内部更改,仅仅作用在函数范围内.eg:	-- 封装有参数的存储过程,传入员工编号,查找员工信息	delimiter $$	create procedure dcc_param01(in param_empno varchar(20))	begin		select * from emp where empno=param_empno;	end $$	delimiter;	call dcc_param01('1001')；

存储过程传参-out

out 表示从存储过程内部传值给调用者-- 封装有参数的存储过程,传入员工编号,返回员工的名字eg:	delimiter $$	begin	create procedure pro(in param_empno varchar(20),out out_name varchar(20),out out_sal decimal(7,2)) 	select epname,sal into out_name,out_sal from emp where  empno = param_empno; 	end $$ 	delimiter ;	call pro('1001',@name,@sal);	select @name,@sal;

存储过程传参-inout

inout 表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入变量的值也可以修改变量的值(即使函数执行完)-- 传入员工名,拼接部门号,传入薪资,求出年薪	delimiter $$	create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)	begin		select concat(deptno,"-",inout_ename) into inout_ename from emp where ename = inout_ename;		set inout_sal = inout_sal * 12;	end $$	delimiter;	set @inout_ename = '关羽';	set @inout_sal = 3000;	call proc10(@inout_name,@inout_sal); -- inout 不可以直接传入实参	select @inout_name;	select @inout_sal;

存储过程-流程控制

存储过程-流程控制-分支语句if

eg:delimiter $$create procedure pro(int score int)begin	if score < 60then		select '不及格';	elseif score >= 60 and score < 80then		select '及格'; 	elseif score >= 80 and score < 90	 then		select '良好'; 	elseif score >= 90 and score <= 100	 then		select '优秀'; 	else select '成绩错误';	end if;end $$ delimiter;

存储过程-流程控制-分支语句case

eg:delimiter $$create procedure pro(in pay_type int)begin	case pay_type 			when 1 then select '微信支付';		when 1 then select '支付宝支付';		when 1 then select '银行卡支付';			else select '其他支付方式';	end case;end $$delimiter $$;

存储过程-流程控制-循环语句-while循环

循环控制:①leave类似于break; ②iterate类似于continue

格式:	[标签:]while 循环条件 do循环体;	end while [标签];eg: 需求->向表中添加指定条数的数据	delimiter $$	create procedure pro(in cout int)begindeclare i int default 1;	while i <= cout doinsert into user(uid,uname),values(i,concat('user-',i));		set i = i + 1;	end while ;	end &&delimiter;

存储过程-流程控制-循环语句-repeat循环

格式:	[标签:]repeat	循环体;	until 条件表达式	end repeat [标签];

存储过程-流程控制-循环语句-loop循环

格式:	[标签:] loop	循环体;	if 条件表达式 then		leave [标签];	end if;	end loop;

存储过程-游标cursor的使用

游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环处理,光标的使用包括光标的声明,OPEN,Fetch,close格式:	-- 声明语法	declare cursor_name cursor for select_statement;	-- 打开语法	open cursor_name;	-- 取值语法	fetch cursor_name into var_name1,var_name2....;	-- 关闭语法	close cursor_name;eg: 输入一个部门名,查询该部门员工的编号,名字,薪资,将查询的结果集添加游标delimiter $$create procedure pro(in in_dname varchar(30))begin	-- 定义局部变量	declare var_empno int;	declare var_ename varchar(50);	declare sal decimal(7,2);	-- 声明游标	declare my_cursor cursor forselect empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;	-- 打开游标	open my_cursor;	-- 通过游标获取值	while 1=1 do	fetch my_cursor into var_empno,var_ename,sal;	select var_empno,var_ename,sal;	end while;	-- 关闭游标	close my_cursor;end $$ delimiter;

存储过程-异常处理-句柄handler

DECLARE handler_action HANDLER	FOR condition_value [,condition_value]...statement 解释:hendler_action:{CONTINUE|EXIT|UNDO}condition_value:{mysql_error_code|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION}

mysql的存储函数

create function func_name([param_name] type[,..])retus typebegin..end ;eg:delimiter $$create function fun() retus intbegindeclare cnt int defalut 0;select count(*) into cnt from emp;retu cnt;end $$delimiter ;-- 调用存储函数select fun();