SQL句子需要先编译然后履行,而存储进程(StoredProcedure)是一组为了完结特定功用的SQL句子集,经编译后存储在数据库中,用户经过指定存储进程的姓名并给定参数(假如该存储进程带有参数)来调用履行它。
存储进程是可编程的函数,在数据库中创立并保存,能够由SQL句子和控制结构组成。当想要在不同的应用程序或平台上履行相同的函数,或者封装特定功用时,存储进程对错常有用的。数据库中的存储进程能够看做是对编程中面向对象办法的模拟,它允许控制数据的拜访方式。
存储进程的优点:
(1).增强SQL言语的功用和灵活性:存储进程能够用控制句子编写,有很强的灵活性,能够完结复杂的判断和较复杂的运算。
(2).标准组件式编程:存储进程被创立后,能够在程序中被屡次调用,而不必重新编写该存储进程的SQL句子。而且数据库专业人员能够随时对存储进程进行修正,对应用程序源代码毫无影响。
(3).较快的履行速度:假如某一操作包含很多的Transaction-SQL代码或分别被屡次履行,那么存储进程要比批处理的履行速度快很多。由于存储进程是预编译的。在初次运行一个存储进程时查询,优化器对其进行剖析优化,而且给出最终被存储在体系表中的履行计划。而批处理的Transaction-SQL句子在每次运行时都要进行编译和优化,速度相对要慢一些。
(4).削减网络流量:针对同一个数据库对象的操作(如查询、修正),假如这一操作所涉及的Transaction-SQL句子被组织进存储进程,那么当在客户计算机上调用该存储进程时,网络中传送的仅仅该调用句子,从而大大削减网络流量并降低了网络负载。
(5).作为一种安全机制来充分利用:经过对履行某一存储进程的权限进行限制,能够实现对相应的数据的拜访权限的限制,避免了非授权用户对数据的拜访,保证了数据的安全。

mysql

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:该值可在存储进程内部被改动,并可回来
mysql1

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();

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注