存储过程就是有业务逻辑和流程的集合(为以后的使用而保存的一条或多条MySQL语句的集合), 可以在存储过程中创建表,更新数据, 删除等等。
为什么要使用存储过程
1、通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。
2、由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
3、简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
存储过程的创建
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]])
[特性 ...]
过程体
create procedureporcedureName ()
begin
select name from user;
end;
DELIMITER //
create procedureporcedureName (OUT s int)
begin
select count(*) into s from students;
end
//
DELIMITER ;
分隔符
MySQL默认以";"为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER //”声明当前段分隔符,让编译器把两个"//"之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER;”的意为把分隔符还原。
参数
存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
IN参数:
仅需要将数据传入存储过程,并不需要返回计算后的该值。
OUT参数:
不接受外部传入的数据,仅返回计算之后的值。
INOUT参数:
需要数据传入存储过程经过调用计算后,再传出返回值。
过程体
过程体的开始与结束使用BEGIN与END进行标识。
DROP procedure IFEXISTS 2paramtest;
DELIMITER $$
USE zyttest$$
CREATEDEFINER=`root`@`localhost` PROCEDURE `2paramtest`(in paramin int)
BEGIN
select paramin;
set paramin = 12;
select paramin;
END$$
DELIMITER ;
变量
存储过程变量
语法:DECLARE变量名1[,变量名2...] 数据类型 [默认值];
特征:不能在参数名称前加“@”,主要用在存储过程中,在调用存储过程时,以DECLARE声明的变量都会被初始化为 NULL
例如:declaretemp1 int;
Set temp1 = 10;
会话变量
语法:SET变量名 = 变量值 [,变量名= 变量值 ...]
特征:会话变量 , 则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量
例如:declaretemp1 = 2;
变量一般以@开头
例如:
SELECT 'Hello World' into @x;
SELECT @x;
SET @y='Goodbye Cruel World';
SELECT @y;
SET @z=1+2+3;
SELECT @z;
查询存储过程
SELECT name FROMmysql.proc WHERE db='数据库名';
SELECT routine_nameFROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUSWHERE db='数据库名';
#查看存储过程详细信息
SHOW CREATE PROCEDURE数据库.存储过程名;
存储过程的修改
ALTER PROCEDURE sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NOSQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY {DEFINER | INVOKER }
| COMMENT 'string'
sp_name参数表示存储过程或函数的名称;
characteristic参数指定存储函数的特性。
CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;
NO SQL表示子程序中不包含SQL语句;
READS SQL DATA表示子程序中包含读数据的语句;
MODIFIES SQL DATA表示子程序中包含写数据的语句。
SQL SECURITY {DEFINER | INVOKER }指明谁有权限来执行,DEFINER表示只有定义者自己才能够执行;INVOKER表示调用者可以执行。
COMMENT 'string'是注释信息。
例如:
#将读写权限改为MODIFIES SQL DATA,并指明调用者可以执行。
ALTER PROCEDURE num_from_employee
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
#将读写权限改为READS SQL DATA,并加上注释信息'FIND NAME'。
ALTER PROCEDURE name_from_employee
READS SQL DATA
COMMENT 'FIND NAME' ;
存储过程的删除
DROP PROCEDURE [过程1[,过程2…]]
删除一个或多个存储过程。
存储过程的控制语句
1.变量作用域
内部变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,不再可见了,在存储
过程外再也找不到这个内部变量,但是可以通过out参数或者将其值指派给会话变量来保存其值
例如:
delimiter$$
DROPprocedure IF EXISTS testparam $$
createprocedure testparam()
begin
declare var1 varchar(67);
set var1 = "outer";
begin
declare var1 varchar(67);
set var1 = "inner";
select var1;
end;
#select var1;
end$$
delimiter$$
条件语句
IF-THEN-ELSE语句
delimiter $$
drop procedure ifexists testcontrol $$
create proceduretestcontrol()
begin
declare temp int;
set temp = -1;
if temp>0 then
select temp;
else
select 5;
end if;
end $$
delimiter $$
CASE-WHEN-THEN-ELSE语句
delimiter$$
dropprocedure if exists testcase $$
createprocedure testcase()
begin
declare temp int;
set temp = 1;
case temp
when 0 then
select 0;
when 1 then
select 1;
when 2 then
select 2;
end case;
end$$
delimiter$$
循环语句
WHILE-DO…END-WHILE
delimiter$$
dropprocedure if exists testwhile $$
createprocedure testwhile()
begin
declare temp int;
set temp = 3;
while(temp>0) do
select "true";
set temp = temp - 1;
end while;
end$$
delimiter$$
REPEAT...END REPEAT
delimiter $$
drop procedure if exists testrepeat$$
create procedure testrepeat()
begin
declaretemp int;
set temp = 5;
repeat
settemp = temp + 1;
select temp;
untiltemp > 7
end repeat;
end $$
delimiter $$
游标,应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作
提供对基于游标位置而对表中数据进行删除或更新的能力
#游标 对同一个结果集进行不同的操作
delimiter $$
drop procedure if exists procname ;
create procedure procname()
BEGIN
#创建一个初始变量用来存放游标读取出来的值
declare temp varchar(20) default '' ;
#创建一个初始变量用来存放游标读取出来的值的集合
declare endtemp varchar(200) default '' ;
#创建一个初始变量用来做一个flag
declare val int default 1 ;
#创建一个游标
declare cur cursor
#给personinfo.grade结果集添加游标
for select grade from personinfo ;
#在 FETCH 语句中引用的游标位置处于结果表最后一行之后设置val的值为0
declare continue handler for sqlstate '02000' set val = 0 ;
#开启游标
open cur ;
#将游标中的内容取出存储在temp中
fetch cur into temp ;
while val != 0 do
if endtemp = '' then
set endtemp = temp ;
else
set endtemp = concat(endtemp,',',temp);
end if ;
fetch cur into temp ;
end while ;
select endtemp ;
#关闭游标
close cur ;
END $$
delimiter $$
call procname();