《MySQL存储过程幻灯片》由会员分享，可在线阅读，更多相关《MySQL存储过程幻灯片（29页珍藏版）》请在金锄头文库上搜索。
1、生物信息学数据库设计生物信息学数据库设计第六章第六章MySQL存储过程存储过程存储过程的概念q所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。q与自定义函数的区别：自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。q执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。q函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数不可用等等。而存储过程的限制相对就
2、比较少。q一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。q对于存储过程来说可以返回参数，而函数只能返回值或者表对象。q存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。存储过程的优点1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。2.当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据
3、库提供的事务处理结合一起使用。3.存储过程可以重复使用，可减少数据库开发人员的工作量。4.安全性高，可设定只有某此用户才具有对指定存储过程的使用权。基本的创建、删除语法qCREATEPROCEDUREsp_name(proc_parameter,.)characteristic.routine_bodyq默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。sp_name 存储过程的名字proc_parameter指定参数为IN，OUT，或INOUTcharacteristic 特征routine_body包
4、含合法的SQL过程语句。qin把数据从外部传递给存储过程qout从存储过程内部返回值给外部使用者qinout把数据传递给存储过程和将存储过程的返回值传递给外部使用者qDROPPROCEDURE|FUNCTIONIFEXISTSsp_name这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL5.1中，你必须有ALTERROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。IFEXISTS子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误showprocedurestatus;查看存储过程状态delimiter/DROPPROCEDUR
5、EIFEXISTSsp_test/CREATEPROCEDUREsp_test/*存储过程名存储过程名*/(INinparmsINT,OUToutparamsvarchar(32)/*输入参数输入参数*/BEGIN/*语句块头语句块头*/DECLAREvarCHAR(10);/*变量声明变量声明*/DECLAREnumint;IFinparms=1THEN/*IF条件开始条件开始*/SETvar=hello;/*赋值赋值*/ELSESETvar=world;ENDIF;/*IF结束结束*/INSERTINTOt1VALUES(var);/*SQL语句语句*/selectcount(*)from
6、t1intonum;SELECTnameFROMt1LIMITnum,1INTOoutparams;END/delimiter;callsp_test(1,out);Selectout;事先创建表事先创建表createtablet1(idintnotnullauto_increment,namevarchar(45),primarykeypk_id(id);存储过程的变量l声明变量：DECLAREvar_name,.typeDEFAULTvalue这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAUL
7、T子句，初始值为NULL。局部变量的作用范围在它被声明的BEGIN.END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。变量赋值q变量赋值，SET语句：SETvar_name=expr,var_name=expr.也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较操作符，如下所示：mysqlSETt1=0,t2=0,t3=0;mysqlSELECTt1:=0,t2:=0,t3:=0;对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过
8、值，则为NULL。q变量赋值，SELECT.INTO语句SELECTcol_name,.INTOvar_name,.table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。SELECTid,dataINTOx,yFROMtest.t1LIMIT1;BEGIN.END复合语句begin_label:BEGINstatement_listENDend_label存储子程序可以使用BEGIN.END复合语句来包含多个语句。statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。复合语句可以被标记。
9、除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的。使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为/）使得;可被用在子程序体中。流程控制lIF语句IFsearch_conditionTHENstatement_listELSEIFsearch_conditionTHENstatement_list.ELSEstatement_listENDIFIF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行
10、。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。q举例：DELIMITER/CREATEPROCEDUREp1(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;IFvariable1=0THENINSERTINTOtVALUES(17);ENDIF;IFparameter1=0THENUPDATEtSETs1=s1+1;ELSEUPDATEtSETs1=s1+2;ENDIF;END/DELIMITER;qCASE语句CASE
11、case_valueWHENwhen_valueTHENstatement_listWHENwhen_valueTHENstatement_list.ELSEstatement_listENDCASEOr:CASEWHENsearch_conditionTHENstatement_listWHENsearch_conditionTHENstatement_list.ELSEstatement_listENDCASEq存储程序的CASE语句实现一个复杂的条件构造。如果search_condition求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。q举例：CREA
12、TEPROCEDUREp2(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;CASEvariable1WHEN0THENINSERTINTOtVALUES(17);WHEN1THENINSERTINTOtVALUES(18);ELSEINSERTINTOtVALUES(19);ENDCASE;END/q循环语句WHILEENDWHILE举例：CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv=5THENLEAVEloop_label;ENDIF;ENDLOOP;
13、END/begin_label:LOOPstatement_listENDLOOPend_labelLOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE语句。q补充：迭代（ITERATE）语句CREATEPROCEDUREp7()BEGINDECLAREvINT;SETv=0;loop_label:LOOPIFv=3THENSETv=v+1;ITERATEloop_label;ENDIF;INSERTINTOtVALUES(v);SETv=v+1;IFv=5THENLEAVEloop_label;ENDIF;END
14、LOOP;END/q注释语法：mysql存储过程可使用两种风格的注释双模杠：-，该风格一般用于单行注释c风格：/*注释内容*/，一般用于多行注释q使用权限：CREATEROUTINE建立存储过程ALTERROUTINE编辑和删除存储过程EXECUTE执行存储过程条件和异常处理程序DECLAREhandler_typeHANDLERFORcondition_value,.sp_statementhandler_type:CONTINUE|EXITcondition_value:SQLSTATEVALUEsqlstate_value|condition_name|SQLWARNING|NOTFOU
15、ND|SQLEXCEPTION这个语句指定每个可以处理一个或多个条件的处理程序。如这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。果产生一个或多个条件，指定的语句被执行。对一个对一个CONTINUE处理程序，当前子程序的执行在执行处理处理程序，当前子程序的执行在执行处理程序语句之后继续。对于程序语句之后继续。对于EXIT处理程序，当前处理程序，当前BEGIN.END复合语句的执行被终止。复合语句的执行被终止。SQLWARNING是对所有以是对所有以01开头的开头的SQLSTATE代代码的速记。码的速记。NOTFOUND是对所有以是对所有以02开头的开
16、头的SQLSTATE代码代码的速记。的速记。SQLEXCEPTION是对所有没有被是对所有没有被SQLWARNING或或NOTFOUND捕获的捕获的SQLSTATE代码的速记。代码的速记。声明自定义条件：声明自定义条件：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:SQLSTATEVALUEsqlstate_value举例：CREATETABLEt2(s1int,primarykey(s1);delimiter/CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHAN
17、DLERFORSQLSTATE23000SETx2=1;SETx=1;INSERTINTOt2VALUES(1);SETx=2;INSERTINTOt2VALUES(1);SETx=3;END/delimiter;游标声明游标DECLAREcursor_nameCURSORFORselect_statement这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。打开游标OPENcursor_name这个语句打开先前声明的光标。游标FETCHFETCHcursor_nameINTOvar_name,var_name.这个语句用指定的打开光标读取下一行（如
18、果有下一行的话），并且前进光标指针。关闭游标CLOSECLOSEcursor_name这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。游标的特性：READONLY只读，只能取值而不能赋值；NOTSCROOLABLE不可回滚，只能顺序读取；ASENSITIVE敏感，不能在已经打开游标的表上执行update事务；举例：createtablestu_sumscore(namechar(16),sum_scoreint);delimiter/CREATEPROCEDUREsp_cur()BEGINDECLAREdoneINTDEFAULT0;DECLAREv_id
19、,v_nameCHAR(16);DECLAREv_subjectid,v_scoreINT;DECLAREcur1CURSORFORSELECTdistinctidFROMstu_grade;DECLARECONTINUEHANDLERFORSQLSTATE02000SETdone=1;OPENcur1;REPEATFETCHcur1INTOv_id;SELECTSUM(SCORE)INTOv_scorefromstu_gradewhereid=v_id;SELECTnameINTOv_namefromstud_infowhereid=v_id;IFNOTdoneTHENINSERTINTOstu_sumscoreVALUES(v_name,v_score);ENDIF;UNTILdoneENDREPEAT;CLOSEcur1;/*CLOSEcur2;*/END/delimiter;作业q利用存储过程，统计每门课程的总成绩，空值默认为0；q利用存储过程，统计每门课程上课人数，考试平均成绩。潜龙勿用潜龙勿用见龙在田，利见大人见龙在田，利见大人29
