MySQL存储程序技术

MySQL存储程序技术张宝军

2010-01-05--什么是存储过程？--为什么要使用存储过程？--MySQL存储过程的创立--MySQL存储过程的调用--MySQL存储过程的查询--MySQL存储过程的修改--MySQL存储过程的删除--MySQL存储过程的控制语句--MySQL游标的使用--MySQL存储程序之ErrorHandling--MySQL存储函数--MySQL触发器目录什么是存储过程?Q:什么是存储过程?A:数据库中一组为完成特定功能SQL代码程序段

常用的SQL语句在执行的时候需要先编译后执行，而存储过程〔StoredProcedure〕经编译后存储在数据库中，用户通过指定存储过程名字并给定参数〔假设带参数〕来调用执行它。一个存储过程是一个可编程的函数，它在数据库中创立并保存。它可以有SQL语句和一些特殊的控制结构组成。当需要在不同的应用程序或平台执行相同的函数或封装特定功能时，存储过程非常有用，且它允许控制数据的访问方式。MySQL5.0以后才支持存储过程为什么要使用存储过程?存储过程通常有以下优点：

增强了SQL语言的功能和灵活性且平安性高。当对数据库进行复杂操作时，可将此操作用存储过程封装起来，它可以用流控制语言编写，有很强的灵活性，可以完成复杂的运算。可以重复使用，减少工作量。它被创立后，可以在程序中被屡次调用，而不必重新编写SQL语句，而且可以随时修改，对应用程序源代码毫无影响。能实现较快的执行速度。存储过程只在创立时进行编译，以后每次执行都不需要重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高执行速度。能够减少网络流量。针对同一数据库对象的操作，如果这一操作所涉及的SQL被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少了网络流量并降低了网络负载。格式：

MySQL存储过程创立的格式：CREATEPROCEDURE过程名([过程参数[,…]])[特性]。MySQL存储过程的创立DELIMITER关键字申明当前段分隔符存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数c，类型是int型，如果有多个参数用“，”分割开过程体的开始和结束使用BEGIN和END进行标识参数：

MySQL存储过程的参数用在存储过程定义，共有三种参数类型IN，OUT，INOUT，形式如下:

MySQL存储过程的创立CREATEPROCEDURE([[IN|OUT|INOUT]参数名数据类型…])IN输入参数：这是缺省的模式，它说明参数可以被传入存储过程内部，但是任何对于该参数的修改都不会被返回给调用它的程序OUT输出参数：这个模式意味着存储过程可以对参数赋值，并且这个被修改的值会被返回给它的调用程序INOUT输入输出参数：这个模式意味着存储过程既可以读取传入的参数，并且任何对于该参数的修改对于它的调用程序而言是可见的IN参数例子：

MySQL存储过程的创立OUT参数例子：

MySQL存储过程的创立INOUT参数例子：

MySQL存储过程的创立变量：

MySQL存储过程的创立I.变量定义DECLAREvariable_name[,variable_name…]datatype[DEFAULTvalue];其中，datatype为MySQL的数据类型，如：int,float,date,varchar(length)例如：DECLAREl_intintunsignedDEFAULT10000;DECLAREl_numbericnumber(8,2)DEFAULT4.55;DECLAREl_datedateDEFAULT‘2011-01-0517:45:45’;DECLAREl_varcharvarchar(255)DEFAULT‘Helloworld!’;II.变量赋值SET变量名=表达式[,variable_name=expression…]

或SELECT表达式INTO变量名[FROMtable_nameWHERE…]

或SELECT变量名:=表达式[,variable_name=expression…][FROMtable_nameWHERE…]III.用户变量

基于会话实现的，可以暂存值，并传递给同一连接的下一条SQL使用的变量，当客户端连接退出时，变量会被释放注意： -用户变量前只有一个@，2个@是用于存取系统变量，如@@version-滥用用户变量会导致程序难以理解和管理注释：

MySQL存储过程的创立MySQL存储过程可使用两种风格的注释#号：#该风格一般用于单行注释，直到本行结束双模杠：-- 该风格一般用于单行注释C风格：/*…*/该风格一般用于多行注释调用：

CALLsp_name([parameter[,…]])MySQL存储过程的调用CALL语句调用一个先前用CREATEPROCEDURE创立的存储过程CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回值。它也“返回”受影响的行数，客户端程序可以在SQL级别通过调用ROW＿COUNT()函数获得个数。查询：

SELECTnameFROMcWHEREdb=‘数据库名’MySQL存储过程的查询SELECTroutine_nameFROMinformation_schema.routinesWHEREroutine_schema=‘数据库名’SHOWprocedurestatusWHEREdb=‘数据库名’SHOWCREATEPROCEDURE数据库名.存储过程名SHOW支持LIKE%修改：

ALTERPROCEDUREsp_name[characteristic…]MySQL存储过程的修改Characteristic: {CONTANSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT‘string’这个语句可以被用来改变一个存储过程的特征，不能修改正程代码。在MySQL5.1中，你必须用ALTERROUTINE权限才可用此子程序。这个权限被自动授予子程序的创立者。在ALTERPROCEDURE语句中，可以指定超过一个的改变。删除：

DROPPROCEDURE[IFEXISTS]sp_nameMySQL存储过程的删除这个语句被用来移除一个存储过程，即从数据库效劳器中移除一个指定的子程序。在MySQL5.1中，你必须有ALTERROUTINE权限才可用此程序。这个权限被自动授予程序的创立者。IFEXISTS子句是一个MySQL扩展，如果存储过程不存在，它防止发生错误，产生一个可以用SHOWWARNINGS查看的警告。(1)变量作用域：

存储过程内部的变量在其作用域范围内享有更高的优先权，当执行到END时，内部变量消失，此时已经在作用域外，变量不再可见，但可以通过OUT参数或将其值指派给会话变量来保存其值。MySQL存储过程的控制语句(2)条件语句：

I.IF-THEN-ELSE语句:MySQL存储过程的控制语句(2)条件语句：

II.CASE语句MySQL存储过程的控制语句(3)循环语句：

I.WHILE…ENDWHILEMySQL存储过程的控制语句(3)循环语句：

II.REPEAT…UNTIL…ENDREPEAT

它在执行后检查结果，而WHILE那么是执行前进行检查。MySQL存储过程的控制语句(3)循环语句：

III.LOOP…ENDLOOP

LOOP循环不需要初始条件，和WHILE相似，同时和REPEAT一样不需要结束条件，LEAVE语句的意义是离开循环。MySQL存储过程的控制语句(3)循环语句：

IV.LABLES标号

标号可以用在BEGIN,REPEAT,WHILE或LOOP语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令到达复合语句的最后一步。通过和LEAVE一起用。MySQL存储过程的控制语句(4)ITERATE迭代：

I.ITERATE

通过引用复合语句的标号来从新开始复合语句。MySQL存储过程的控制语句游标：

MySQL游标的使用I.定义游标DECLARE游标名CURSORFORSELECT字段1,字段2…FROMtable_name;例如：DECLAREcurCURSORFORSELECTmgcidFROMmg_group_ctg;II.使用游标OPEN游标名

FETCH数据

FETCHcursorinto变量1,变量2…;III.关闭游标CLOSE游标名;游标举例：

MySQL游标的使用CREATEPROCEDUREcurdemo()BEGINDECLAREdoneINTDEFAULT0;DECLAREaCHAR(16);DECLAREb,cINT;DECLAREcur1CURSORFORSELECTid,dataFROMtest.t1;DECLAREcur2CURSORFORSELECTiFROMtest.t2;DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;

OPENcur1;OPENcur2;

REPEATFETCHcur1INTOa,b;FETCHcur2INTOc;IFNOTdoneTHENIFb<cTHENINSERTINTOtest.t3VALUES(a,b);ELSEINSERTINTOtest.t3VALUES(a,c);ENDIF;ENDIF;UNTILdoneENDREPEAT;

CLOSEcur1;CLOSEcur2;ENDHanlers：

DECLARE{CONTINUE|EXIT}HANDLERFOR

{SQLSTATEsqlstate_code|MySQLerrorcode|condition_name}

handler_actions

MySQL存储程序之ErrorHandlingHandlers类型：1.EXIT:发生错误时退出当前代码块〔可能是子代码块或者main代码块〕2.CONTINUE:发送错误时继续执行后续代码Handlers条件:1.MySQLerrorcode，如10622.ANSI标准SQLSTATEcode，如230003.命名条件，如NOTFOUND优先级：MySQLErrorcode>SQLSTATEcode>命名条件使用SQLSTATE还是MySQLErrorCode？1.SALSTATE是标准，貌似会更portable，但是实际上MySQL、DB2、Oracle等等的存储程序语法大相径庭，所以portable的优势不存在2.MySQLerrorcode与SQLSTATE并不是一一对应的，比方很多MySQLerrorcode都映射到同一SQLSTATEcode(HY000)常见的MySQLerrorcode和SQLSTATEcode：

MySQL存储程序之ErrorHandling存储函数：

CREATEFUNCTIONsp_name([func_parameter[,...]])

RETURNStype

存储储函数和存储过程很想像，它们都是包含一个或多个SQL语句的被命名程序单元。和存储过程不同的地方有以下几点：MySQL存储函数函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。IN是缺省的函数必须返回一个值，它的类型被定义于函数的头部函数能被SQL语句所调用函数可能不返回任何结果集可以在其他存储程序中〔过程，函数和触发器〕中使用存储函数查看和删除：

MySQL存储函数查看： SHOWFUNCTIONstatusWHEREdb=‘数据库名’ SHOWCREATEFUNCTION数据库名.存储过程名 SHOW支持LIKE%删除: DROPFUNCTION数据库名.存储函数名存储函数实例：MySQL存储函数函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。IN是缺省的函数必须返回一个值，它的类型被定义于函数的头部函数能被SQL语句所