MySQL存储过程课件

生物信息学数据库设计第六章MySQL存储过程生物信息学数据库设计第六章MySQL存储过程存储过程的概念所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。

与自定义函数的区别：

自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。

存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。

存储过程的概念所谓的存储过程就是存储在数据库当中的可执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数不可用等等。而存储过程的限制相对就比较少。

执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

对于存储过程来说可以返回参数，而函数只能返回值或者表对象。存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。一般来说，存储过程实现的功能要复杂一存储过程的优点存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

存储过程可以重复使用，可减少数据库开发人员的工作量。

安全性高，可设定只有某此用户才具有对指定存储过程的使用权。存储过程的优点存储过程只在创造时进行编译，以后每次执行存储过基本的创建、删除语法CREATEPROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body

默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

sp_name存储过程的名字proc_parameter指定参数为IN，OUT，或INOUTcharacteristic特征routine_body

包含合法的SQL过程语句。基本的创建、删除语法CREATEPROCEDUREsp_in把数据从外部传递给存储过程out从存储过程内部返回值给外部使用者inout把数据传递给存储过程和将存储过程的返回值传递给外部使用者in把数据从外部传递给存储过程DROP{PROCEDURE|FUNCTION}[IFEXISTS]sp_name

这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL5.1中，你必须有ALTERROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。IFEXISTS子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误showprocedurestatus;查看存储过程状态MySQL存储过程课件delimiter//DROPPROCEDUREIFEXISTSsp_test//CREATEPROCEDUREsp_test

/*存储过程名*/(INinparmsINT,OUToutparamsvarchar(32))

/*输入参数*/BEGIN/*语句块头*/DECLAREvarCHAR(10);/*变量声明*/DECLAREnumint;IFinparms=1THEN/*IF条件开始*/SETvar='hello';/*赋值*/ELSESETvar='world';ENDIF;/*IF结束*/INSERTINTOt1VALUES(var);

/*SQL语句*/selectcount(*)fromt1intonum;SELECTnameFROMt1LIMITnum,1INTOoutparams;END//delimiter;

callsp_test(1,@out);Select@out;事先创建表createtablet1(idintnotnullauto_increment,namevarchar(45),primarykeypk_id(id));delimiter//存储过程的变量声明变量：DECLAREvar_name[,...]type[DEFAULTvalue]

这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN...END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。存储过程的变量声明变量：变量赋值变量赋值，SET语句：SETvar_name=expr[,var_name=expr]...也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较操作符，如下所示：

mysql>SET@t1=0,@t2=0,@t3=0;

mysql>SELECT@t1:=0,@t2:=0,@t3:=0;

对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。变量赋值变量赋值，SET语句：变量赋值，SELECT...INTO语句SELECTcol_name[,...]INTOvar_name[,...]table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。SELECTid,dataINTOx,yFROMtest.t1LIMIT1;变量赋值，SELECT...INTO语句BEGIN...END复合语句

[begin_label:]BEGIN[statement_list]END[end_label]

存储子程序可以使用BEGIN...END复合语句来包含多个语句。statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的。

使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为//）使得;可被用在子程序体中。BEGIN...END复合语句[begin_label:]流程控制IF语句IFsearch_conditionTHENstatement_list

[ELSEIFsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDIF

IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

流程控制IF语句举例：DELIMITER//CREATEPROCEDUREp1(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;IFvariable1=0THENINSERTINTOtVALUES(17);ENDIF;IFparameter1=0THENUPDATEtSETs1=s1+1;ELSEUPDATEtSETs1=s1+2;ENDIF;END//DELIMITER;举例：CASE语句CASEcase_value

WHENwhen_valueTHENstatement_list

[WHENwhen_valueTHENstatement_list]...

[ELSEstatement_list]ENDCASEOr:CASE

WHENsearch_conditionTHENstatement_list

[WHENsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDCASECASE语句存储程序的CASE语句实现一个复杂的条件构造。如果search_condition求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。举例：CREATEPROCEDUREp2(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;CASEvariable1WHEN0THENINSERTINTOtVALUES(17);WHEN1THENINSERTINTOtVALUES(18);ELSEINSERTINTOtVALUES(19);ENDCASE;END//存储程序的CASE语句实现一个复杂的条件构造。如果searc循环语句WHILE…ENDWHILE举例：

CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv<5DOINSERTINTOtVALUES(v);SETv=v+1;ENDWHILE;END//循环语句循环语句LOOP…ENDLOOP举例：CREATEPROCEDUREp5()BEGINDECLAREvINT;SETv=0;loop_label:LOOPINSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END//[begin_label:]LOOP

statement_listENDLOOP[end_label]LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE语句。循环语句补充：迭代（ITERATE）语句CREATEPROCEDUREp7()BEGINDECLAREvINT;SETv=0;loop_label:LOOPIFv=3THENSETv=v+1;ITERATEloop_label;ENDIF;INSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END//补充：迭代（ITERATE）语句注释语法：mysql存储过程可使用两种风格的注释双模杠：--，该风格一般用于单行注释c风格：/*注释内容*/，一般用于多行注释使用权限：

CREATEROUTINE建立存储过程ALTERROUTINE编辑和删除存储过程EXECUTE执行存储过程注释语法：条件和异常处理程序DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statement

handler_type:

CONTINUE

|EXIT

condition_value:

SQLSTATE[VALUE]sqlstate_value

|condition_name

|SQLWARNING

|NOTFOUND

|SQLEXCEPTION这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。条件和异常处理程序DECLAREhandler_typeSQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOTFOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE代码的速记。声明自定义条件：DECLAREcondition_nameCONDITIONFORcondition_valuecondition_value:SQLSTATE[VALUE]sqlstate_valueSQLWARNING是对所有以01开头的SQLSTATE代码举例：CREATETABLEt2(s1int,primarykey(s1));delimiter//CREATEPROCEDUREhandlerdemo()BEGINDECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;

SET@x=1;INSERTINTOt2VALUES(1);SET@x=2;INSERTINTOt2VALUES(1);SET@x=3;END//delimiter;举例：游标声明游标DECLAREcursor_nameCURSORFORselect_statement这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。打开游标OPENcursor_name这个语句打开先前声明的光标。游标FETCHFETCHcursor_nameINTOvar_name[,var_name]...这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

游标声明游标关闭游标CLOSECLOSEcursor_name这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。游标的特性：READONLY只读，只能取值而不能赋值；NOTSCROOLABLE不可回滚，只能顺序读取；ASENSITIVE敏感，不能在已经打开游标的表上执行update事务；关闭游标CLOSE举例：createtablestu_sumscore(namechar(16),sum_scoreint);delimiter//CREATEPROCEDUREsp_cur()BEGINDECLAREdoneINTDEFAULT0;DECLAREv_id,v_nameCHAR(16);DECLAREv_subjectid,v_scoreINT;DECLAREcur1CURSORFORSELECTdistinctidFROMstu_grade;DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=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;举例：作业利用存储过程，统计每门课程的总成绩，空值默认为0；利用存储过程，统计每门课程上课人数，考试平均成绩。作业利用存储过程，统计每门课程的总成绩，空值默认为0；潜龙勿用

见龙在田，利见大人

29潜龙勿用

见龙在田，利见大人29生物信息学数据库设计第六章MySQL存储过程生物信息学数据库设计第六章MySQL存储过程存储过程的概念所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。

与自定义函数的区别：

自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。

存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。

存储过程的概念所谓的存储过程就是存储在数据库当中的可执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数不可用等等。而存储过程的限制相对就比较少。

执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

对于存储过程来说可以返回参数，而函数只能返回值或者表对象。存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。一般来说，存储过程实现的功能要复杂一存储过程的优点存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

存储过程可以重复使用，可减少数据库开发人员的工作量。

安全性高，可设定只有某此用户才具有对指定存储过程的使用权。存储过程的优点存储过程只在创造时进行编译，以后每次执行存储过基本的创建、删除语法CREATEPROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body

默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

sp_name存储过程的名字proc_parameter指定参数为IN，OUT，或INOUTcharacteristic特征routine_body

包含合法的SQL过程语句。基本的创建、删除语法CREATEPROCEDUREsp_in把数据从外部传递给存储过程out从存储过程内部返回值给外部使用者inout把数据传递给存储过程和将存储过程的返回值传递给外部使用者in把数据从外部传递给存储过程DROP{PROCEDURE|FUNCTION}[IFEXISTS]sp_name

这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL5.1中，你必须有ALTERROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。IFEXISTS子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误showprocedurestatus;查看存储过程状态MySQL存储过程课件delimiter//DROPPROCEDUREIFEXISTSsp_test//CREATEPROCEDUREsp_test

/*存储过程名*/(INinparmsINT,OUToutparamsvarchar(32))

/*输入参数*/BEGIN/*语句块头*/DECLAREvarCHAR(10);/*变量声明*/DECLAREnumint;IFinparms=1THEN/*IF条件开始*/SETvar='hello';/*赋值*/ELSESETvar='world';ENDIF;/*IF结束*/INSERTINTOt1VALUES(var);

/*SQL语句*/selectcount(*)fromt1intonum;SELECTnameFROMt1LIMITnum,1INTOoutparams;END//delimiter;

callsp_test(1,@out);Select@out;事先创建表createtablet1(idintnotnullauto_increment,namevarchar(45),primarykeypk_id(id));delimiter//存储过程的变量声明变量：DECLAREvar_name[,...]type[DEFAULTvalue]

这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN...END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。存储过程的变量声明变量：变量赋值变量赋值，SET语句：SETvar_name=expr[,var_name=expr]...也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较操作符，如下所示：

mysql>SET@t1=0,@t2=0,@t3=0;

mysql>SELECT@t1:=0,@t2:=0,@t3:=0;

对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。变量赋值变量赋值，SET语句：变量赋值，SELECT...INTO语句SELECTcol_name[,...]INTOvar_name[,...]table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。SELECTid,dataINTOx,yFROMtest.t1LIMIT1;变量赋值，SELECT...INTO语句BEGIN...END复合语句

[begin_label:]BEGIN[statement_list]END[end_label]

存储子程序可以使用BEGIN...END复合语句来包含多个语句。statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的。

使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为//）使得;可被用在子程序体中。BEGIN...END复合语句[begin_label:]流程控制IF语句IFsearch_conditionTHENstatement_list

[ELSEIFsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDIF

IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

流程控制IF语句举例：DELIMITER//CREATEPROCEDUREp1(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;IFvariable1=0THENINSERTINTOtVALUES(17);ENDIF;IFparameter1=0THENUPDATEtSETs1=s1+1;ELSEUPDATEtSETs1=s1+2;ENDIF;END//DELIMITER;举例：CASE语句CASEcase_value

WHENwhen_valueTHENstatement_list

[WHENwhen_valueTHENstatement_list]...

[ELSEstatement_list]ENDCASEOr:CASE

WHENsearch_conditionTHENstatement_list

[WHENsearch_conditionTHENstatement_list]...

[ELSEstatement_list]ENDCASECASE语句存储程序的CASE语句实现一个复杂的条件构造。如果search_condition求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。举例：CREATEPROCEDUREp2(INparameter1INT)BEGINDECLAREvariable1INT;SETvariable1=parameter1+1;CASEvariable1WHEN0THENINSERTINTOtVALUES(17);WHEN1THENINSERTINTOtVALUES(18);ELSEINSERTINTOtVALUES(19);ENDCASE;END//存储程序的CASE语句实现一个复杂的条件构造。如果searc循环语句WHILE…ENDWHILE举例：

CREATEPROCEDUREp4()BEGINDECLAREvINT;SETv=0;WHILEv<5DOINSERTINTOtVALUES(v);SETv=v+1;ENDWHILE;END//循环语句循环语句LOOP…ENDLOOP举例：CREATEPROCEDUREp5()BEGINDECLAREvINT;SETv=0;loop_label:LOOPINSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END//[begin_label:]LOOP

statement_listENDLOOP[end_label]LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE语句。循环语句补充：迭代（ITERATE）语句CREATEPROCEDUREp7()BEGINDECLAREvINT;SETv=0;loop_label:LOOPIFv=3THENSETv=v+1;ITERATEloop_label;ENDIF;INSERTINTOtVALUES(v);SETv=v+1;IFv>=5THENLEAVEloop_label;ENDIF;ENDLOOP;END//补充：迭代（ITERATE）语句注释语法：mysql存储过程可使用两种风格的注释双模杠：--，该风格一般用于单行注释c风格：/*注释内容*/，一般用于多行注释使用权限：

CREATEROUTINE建立存储过程ALTERROUTINE编辑和删除存储过程EXECUTE执行存储过程注释语法：条件和异常处理程序DECLAREhandler_typeHANDLERFORcondition_value[,...]sp_statement

handler_type:

CONTINUE

|EXIT

condition_value:

SQLSTATE[VALUE]sqlstate_value

|condition_name

|SQLWARNING

|NOTFOUND

|SQLEXCEPTION这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。条件和异常处理程序DECLAREhandler_typeSQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOTFOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE代码的速记。声明自定义条件：DECLAREcondition_nameCO