MySQL存储过程实例详解.docx
《MySQL存储过程实例详解.docx》由会员分享,可在线阅读,更多相关《MySQL存储过程实例详解.docx(6页珍藏版)》请在冰豆网上搜索。
MySQL存储过程实例详解
MySQL存储过程使用实例详解
本文介绍关于在MySQL存储过程游标使用实例,包括简单游标使用与游标循环跳出等方法
例1、一个简单存储过程游标实例
复制代码代码如下:
DELIMITER$$
DROPPROCEDUREIFEXISTSgetUserInfo$$
CREATEPROCEDUREgetUserInfo(indate_daydatetime)
--
--实例
--存储过程名为:
getUserInfo
--参数为:
date_day日期格式:
2008-03-08
--
BEGIN
declare_userNamevarchar(12);--用户名
declare_chineseint;--语文
declare_mathint; --数学
declaredoneint;
--定义游标
DECLARErs_cursorCURSORFORSELECTusername,chinese,mathfromuserInfowheredatediff(createDate,date_day)=0;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
--获取昨天的日期
ifdate_dayisnullthen
setdate_day=date_add(now(),interval-1day);
endif;
openrs_cursor;
cursor_loop:
loop
FETCHrs_cursorinto_userName,_chinese,_math;--取数据
ifdone=1then
leavecursor_loop;
endif;
--更新表
updateinfoSumsettotal=_chinese+_mathwhereUserName=_userName;
endloopcursor_loop;
closers_cursor;
END$$
DELIMITER;
例2、存储过程游标循环跳出现
在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.
1.REPEAT
复制代码代码如下:
REPEAT
Statements;
UNTILexpression
ENDREPEAT
demo
DECLAREnumINT;
DECLAREmy_string VARCHAR(255);
REPEAT
SET my_string=CONCAT(my_string,num,',');
SET num=num+1;
UNTILnum<5
ENDREPEAT;
2.WHILE
复制代码代码如下:
WHILEexpressionDO
Statements;
ENDWHILE
demo
DECLAREnumINT;
DECLAREmy_string VARCHAR(255);
SETnum=1;
SETstr='';
WHILEnum 10DO
SET my_string=CONCAT(my_string,num,',');
SET num=num+1;
ENDWHILE;
3.LOOP(这里面有非常重要的ITERATE,LEAVE)
代码如下复制代码
DECLAREnum INT;
DECLAREstr VARCHAR(255);
SETnum=1;
SETmy_string='';
loop_label:
LOOP
IF num<10THEN
LEAVE loop_label;
ENDIF;
SET num=num+1;
IF(nummod3)THEN
ITERATE loop_label;
ELSE
SET my_string=CONCAT(my_string,num,',');
ENDIF;
ENDLOOP;
PS:
可以这样理解ITERATE就是我们程序中常用的contiune,而ITERATE就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.
例3,mysql存储过程中使用多游标
先创建一张表,插入一些测试数据:
复制代码代码如下:
DROPTABLEIFEXISTSnetingcn_proc_test;
CREATETABLE`netingcn_proc_test`(
`id`INTEGER(11)NOTNULLAUTO_INCREMENT,
`name`VARCHAR(20),
`password`VARCHAR(20),
PRIMARYKEY(`id`)
)ENGINE=InnoDB;
insertintonetingcn_proc_test(name,password)values
('procedure1','pass1'),
('procedure2','pass2'),
('procedure3','pass3'),
('procedure4','pass4');下面就是一个简单存储过程的例子:
dropprocedureIFEXISTStest_proc;
delimiter//
createproceduretest_proc()
begin
--声明一个标志done,用来判断游标是否遍历完成
DECLAREdoneINTDEFAULT0;
--声明一个变量,用来存放从游标中提取的数据
--特别注意这里的名字不能与由游标中使用的列明相同,否则得到的数据都是NULL
DECLAREtnamevarchar(50)DEFAULTNULL;
DECLAREtpassvarchar(50)DEFAULTNULL;
--声明游标对应的SQL语句
DECLAREcurCURSORFOR
selectname,passwordfromnetingcn_proc_test;
--在游标循环到最后会将done设置为1
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
--执行查询
opencur;
--遍历游标每一行
REPEAT
--把一行的信息存放在对应的变量中
FETCHcurINTOtname,tpass;
ifnotdonethen
--这里就可以使用tname,tpass对应的信息了
selecttname,tpass;
endif;
UNTILdoneENDREPEAT;
CLOSEcur;
end
//
delimiter;
--执行存储过程
calltest_proc();
需要注意的是变量的声明、游标的声明和HANDLER声明的顺序不能搞错,必须是先声明变量,再申明游标,最后声明HANDLER。
上述存储过程的例子中只使用了一个游标,那么如果要使用两个或者更多游标怎么办,其实很简单,可以这么说,一个怎么用两个就是怎么用的。
例子如下:
复制代码代码如下:
dropprocedureIFEXISTStest_proc_1;
delimiter//
createproceduretest_proc_1()
begin
DECLAREdoneINTDEFAULT0;
DECLAREtidint(11)DEFAULT0;
DECLAREtnamevarchar(50)DEFAULTNULL;
DECLAREtpassvarchar(50)DEFAULTNULL;
DECLAREcur_1CURSORFOR
selectname,passwordfromnetingcn_proc_test;
DECLAREcur_2CURSORFOR
selectid,namefromnetingcn_proc_test;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
opencur_1;
REPEAT
FETCHcur_1INTOtname,tpass;
ifnotdonethen
selecttname,tpass;
endif;
UNTILdoneENDREPEAT;
CLOSEcur_1;
--注意这里,一定要重置done的值为0
setdone=0;
opencur_2;
REPEAT
FETCHcur_2INTOtid,tname;
ifnotdonethen
selecttid,tname;
endif;
UNTILdoneENDREPEAT;
CLOSEcur_2;
end
//
delimiter;
calltest_proc_1();
上述代码和第一个例子中基本一样,就是多了一个游标声明和遍历游标。
这里需要注意的是,在遍历第二个游标前使用了setdone=0,因为当第一个游标遍历玩后其值被handler设置为1了,如果不用set把它设置为0,那么第二个游标就不会遍历了。
当然好习惯是在每个打开游标的操作前都用该语句,确保游标能真正遍历。
当然还可以使用begin语句块嵌套的方式来处理多个游标,例如:
复制代码代码如下:
dropprocedureIFEXISTStest_proc_2;
delimiter//
createproceduretest_proc_2()
begin
DECLAREdoneINTDEFAULT0;
DECLAREtnamevarchar(50)DEFAULTNULL;
DECLAREtpassvarchar(50)DEFAULTNULL;
DECLAREcur_1CURSORFOR
selectname,passwordfromnetingcn_proc_test;
DECLAREcur_2CURSORFOR
selectid,namefromnetingcn_proc_test;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
opencur_1;
REPEAT
FETCHcur_1INTOtname,tpass;
ifnotdonethen
selecttname,tpass;
endif;
UNTILdoneENDREPEAT;
CLOSEcur_1;
begin
DECLAREdoneINTDEFAULT0;
DECLAREtidint(11)DEFAULT0;
DECLAREtnamevarchar(50)DEFAULTNULL;
DECLAREcur_2CURSORFOR
selectid,namefromnetingcn_proc_test;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
opencur_2;
REPEAT
FETCHcur_2INTOtid,tname;
ifnotdonethen
selecttid,tname;
endif;
UNTILdoneENDREPEAT;
CLOSEcur_2;
end;
end
//
delimiter;
calltest_proc_2();