mysql存储过程(实例详解)-IT技术网站

mysql存储过程

SQL句子需要先编译然后履行，而存储进程（StoredProcedure）是一组为了完结特定功用的SQL句子集，经编译后存储在数据库中，用户经过指定存储进程的姓名并给定参数（假如该存储进程带有参数）来调用履行它。
存储进程是可编程的函数，在数据库中创立并保存，能够由SQL句子和控制结构组成。当想要在不同的应用程序或平台上履行相同的函数，或者封装特定功用时，存储进程对错常有用的。数据库中的存储进程能够看做是对编程中面向对象办法的模拟，它允许控制数据的拜访方式。
存储进程的优点：
(1).增强SQL言语的功用和灵活性：存储进程能够用控制句子编写，有很强的灵活性，能够完结复杂的判断和较复杂的运算。
(2).标准组件式编程：存储进程被创立后，能够在程序中被屡次调用，而不必重新编写该存储进程的SQL句子。而且数据库专业人员能够随时对存储进程进行修正，对应用程序源代码毫无影响。
(3).较快的履行速度：假如某一操作包含很多的Transaction-SQL代码或分别被屡次履行，那么存储进程要比批处理的履行速度快很多。由于存储进程是预编译的。在初次运行一个存储进程时查询，优化器对其进行剖析优化，而且给出最终被存储在体系表中的履行计划。而批处理的Transaction-SQL句子在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).削减网络流量：针对同一个数据库对象的操作（如查询、修正），假如这一操作所涉及的Transaction-SQL句子被组织进存储进程，那么当在客户计算机上调用该存储进程时，网络中传送的仅仅该调用句子，从而大大削减网络流量并降低了网络负载。
(5).作为一种安全机制来充分利用：经过对履行某一存储进程的权限进行限制，能够实现对相应的数据的拜访权限的限制，避免了非授权用户对数据的拜访，保证了数据的安全。
MySQL的存储进程
存储进程是数据库的一个重要的功用，MySQL5.0曾经并不支撑存储进程，这使得MySQL在应用上大打折扣。好在MySQL5.0开端支撑存储进程，这样即能够大大进步数据库的处理速度，同时也能够进步数据库编程的灵活性。
MySQL存储进程的创立
语法
CREATEPROCEDURE进程名([[IN|OUT|INOUT]参数名数据类型[,[IN|OUT|INOUT]参数名数据类型…]])[特性…]进程体
DELIMITER//
CREATEPROCEDUREmyproc(OUTsint)
BEGIN
SELECTCOUNT(*)INTOsFROMstudents;
END
//
DELIMITER;
分隔符
MySQL默许以”;”为分隔符，假如没有声明分割符，则编译器会把存储进程当成SQL句子进行处理，因此编译进程会报错，所以要事先用“DELIMITER//”声明当前段分隔符，让编译器把两个”//”之间的内容作为存储进程的代码，不会履行这些代码；“DELIMITER;”的意为把分隔符还原。
参数
存储进程根据需要或许会有输入、输出、输入输出参数，假如有多个参数用”,”分割开。MySQL存储进程的参数用在存储进程的界说，共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储进程时指定，在存储进程中修正该参数的值不能被回来，为默许值
OUT:该值可在存储进程内部被改动，并可回来
INOUT:调用时指定，而且可被改动和回来
进程体
进程体的开端与完毕使用BEGIN与END进行标识。
IN参数例子
DELIMITER//
CREATEPROCEDUREin_param(INp_inint)
BEGIN
SELECTp_in;
SETp_in=2;
SELECTp_in;
END;
//
DELIMITER;
#调用
SET@p_in=1;
CALLin_param(@p_in);
SELECT@p_in;
履行成果：
image
image
image
以上能够看出，p_in虽然在存储进程中被修正，但并不影响@p_id的值
OUT参数例子
#存储进程OUT参数
DELIMITER//
CREATEPROCEDUREout_param(OUTp_outint)
BEGIN
SELECTp_out;
SETp_out=2;
SELECTp_out;
END;
//
DELIMITER;
#调用
SET@p_out=1;
CALLout_param(@p_out);
SELECT@p_out;
履行成果：
image
image
image
INOUT参数例子
#存储进程INOUT参数
DELIMITER//
CREATEPROCEDUREinout_param(INOUTp_inoutint)
BEGIN
SELECTp_inout;
SETp_inout=2;
SELECTp_inout;
END;
//
DELIMITER;
#调用
SET@p_inout=1;
CALLinout_param(@p_inout);
SELECT@p_inout;
履行成果：
image
image
image
变量
语法：DECLARE变量名1[,变量名2…]数据类型[默许值];
数据类型为MySQL的数据类型：

mysql存储过程实例详解

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存储过程游标循环跳出现
在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.
1.REPEAT
REPEAT
Statements;
UNTILexpression
ENDREPEAT
demo
DECLAREnumINT;
DECLAREmy_stringVARCHAR(255);
REPEAT
SETmy_string=CONCAT(my_string,num,’,’);
SETnum=num+1;
UNTILnum<5
ENDREPEAT;
2.WHILE
WHILEexpressionDO
Statements;
ENDWHILE
demo
DECLAREnumINT;
DECLAREmy_stringVARCHAR(255);
SETnum=1;
SETstr=”;
WHILEnum<span>10DO
SETmy_string=CONCAT(my_string,num,’,’);
SETnum=num+1;
ENDWHILE;
3.LOOP(这里面有非常重要的ITERATE,LEAVE)
代码如下复制代码
DECLAREnumINT;
DECLAREstrVARCHAR(255);
SETnum=1;
SETmy_string=”;
loop_label:LOOP
IFnum<10THEN
LEAVEloop_label;
ENDIF;
SETnum=num+1;
IF(nummod3)THEN
ITERATEloop_label;
ELSE
SETmy_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();