第6章 MySQL过程式数据库对象

第6章MySQL过程式数据库对象——存储过程MySQL教程（第2版）存储过程存储过程的优点如下。存储过程在服务器端运行，执行速度快。存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。（1）（2）（3）确保数据库安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库信息的访问。01创建存储过程1.存储过程参数2.存储过程特征3.存储过程体创建存储过程创建存储过程命令如下，其语法格式为：CREATEPROCEDURE存储过程名([参数...]) [特征...]存储过程体1．存储过程参数参数格式为：[IN|OUT|INOUT]参数名参数类型说明：系统默认在当前数据库中创建。需要在特定数据库中创建存储过程时，则要在名称前面加上数据库的名称，格式为：

数据库名.存储过程名当存储过程有多个参数的时候中间用逗号隔开。MySQL存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是IN、OUT和INOUT。存储过程可以有0个、1个或多个参数。存储过程即使不加参数，名称后面的括号也是不可省略的。参数的名字不要采用列的名字，否则虽然不会返回出错消息，但是存储过程中的SQL语句会将参数名看作列名，从而引发不可预知的结果。创建存储过程2．存储过程特征特征格式为：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'说明：LANGUAGESQL：表明编写这个存储过程的语言为SQL语言。DETERMINISTIC：设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果，设置为NOTDETERMINISTIC则表示会产生不确定的结果。CONTAINSSQL：表示存储过程不包含读或写数据的语句。NOSQL：表示存储过程不包含SQL语句。READSSQLDATA：表示存储过程包含读数据的语句，但不包含写数据的语句。MODIFIESSQLDATA：表示存储过程包含写数据的语句。SQLSECURITY：可以用来指定存储过程是使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。COMMENT'string'：对存储过程的描述（就是备注），string为描述内容。创建存储过程3．存储过程体在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程以使用“DELIMITER结束符号”命令将MySQL语句的结束标志修改为其他符号。【例6.1】用存储过程实现删除一个特定学生的信息。DELIMITER$$CREATEPROCEDUREdelete_student(INxhchar(6))BEGIN DELETEFROMxsWHERE学号=xh;END$$DELIMITER;02存储过程体编程1.变量2.流程控制语句3.处理程序和条件存储过程体编程1．变量（1）局部变量。在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句，在声明局部变量的同时也可以为其赋一个初始值，如果不指定则默认值为NULL。语法格式如下：DECLARE变量名...类型[默认值]例如，声明一个整型变量和两个字符变量。DECLAREnumint(4);DECLAREstr1,str2varchar(6);（2）使用SET语句赋值变量。要给局部变量赋值可以使用SET语句，SET语句是SQL本身的一部分。语法格式如下：SET变量名=expr[,变量名=expr]...例如，在存储过程中给局部变量赋值。SETnum=1,str1='hello';存储过程体编程（3）SELECT...INTO语句赋值变量。使用SELECT…INTO语句可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。语法格式如下：SELECT列名[,...]INTO变量名[,...]table_expr其中，table_expr代表SELECT语句中的FROM子句及后面的部分。【例6.2】在存储过程体中，将xs表中的学号为“221101”学生的姓名和专业名的值分别赋给变量name和project。语句如下：SELECT姓名,专业名INTOname,project FROMxs; WHERE学号='221101';存储过程体编程2．流程控制语句（1）IF…THEN…ELSE语句。IF-THEN-ELSE语句可根据不同的条件执行不同的操作。语法格式如下：IF条件THEN语句[ELSEIF条件THEN语句]...[ELSE语句]ENDIF【例6.3】创建xscj数据库的存储过程，判断两个输入参数哪一个更大。DELIMITER$$CREATEPROCEDUREpar (INk1integer,INk2integer,OUTk3char(6))BEGIN IFk1>k2THEN SETk3='大于’; ELSEIFk1=k2THEN SETk3='等于’; ELSE SETk3='小于’; ENDIF; END$$DELIMITER;存储过程体编程（2）CASE语句。这里介绍CASE语句在存储过程中的用法，与之前略有不同。语法格式如下：CASEexprWHEN值1THEN语句[WHEN值2THEN语句]...[ELSE语句]ENDCASE或者：CASEWHEN条件1THEN语句[WHEN条件2THEN语句]...[ELSE语句]ENDCASE第一种格式中expr是要被判断的表达式，接下来是一系列的WHEN-THEN块，每一块的值i指定要与expr的值比较，如果为真，就执行相应SQL语句。第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，条件指定了一个比较表达式，表达式为真时执行THEN后面的语句。存储过程体编程【例6.4】创建一个存储过程，针对参数的不同，返回不同的结果。DELIMITER$$CREATEPROCEDURExscj.result (INstrvarchar(4),OUTsexvarchar(4))BEGIN CASEstr WHEN'm'THENSETsex='男’; WHEN'f'THENSETsex='女’; ELSESETsex='无’; ENDCASE;END$$DELIMITER;用第二种格式的CASE语句创建以上存储过程，程序片段如下：CASE WHENstr='m'THENSETsex='男’; WHENstr='f'THENSETsex='女’; ELSESETsex='无';ENDCASE;存储过程体编程（3）循环语句。在存储过程中可以定义0条、1条或多条循环语句。MySQL支持3种循环语句：WHILE语句、REPEAT语句和LOOP语句。①WHILE语句的语法格式如下：[label:]WHILE条件DO

语句ENDWHILE[label]【例6.5】创建一个带while循环的存储过程。DELIMITER$$CREATEPROCEDUREmydowhile()BEGIN DECLAREv1intDEFAULT5; WHILEv1>0DO SETv1=v1-1; ENDWHILE;END$$DELIMITER;存储过程体编程②REPEAT语句的语法格式如下：[label:]REPEAT

语句 UNTIL条件ENDREPEAT[label]用REPEAT语句创建一个如前例的存储过程，程序片段如下：REPEAT v1=v1-1; UNTILv1<1;ENDREPEAT;存储过程体编程③LOOP语句的语法格式如下：[label:] LOOP

语句ENDLOOP[label]LEAVE语句经常和BEGIN...END或循环一起使用，结构如下：LEAVElabellabel是语句中标注的名字，这个名字是自定义的。【例6.6】创建一个带LOOP语句的存储过程。DELIMITER$$CREATEPROCEDUREmydoloop()BEGIN SET@a=10; label:LOOP SET@a=@a-1; IF@a<0THEN LEAVElabel; ENDIF; ENDLOOPlabel;END$$DELIMITER;存储过程体编程调用此存储过程来查看最后结果。调用该存储过程使用如下命令：CALLmydoloop();接着，查看用户变量的值：select@a;执行结果如图。存储过程体编程3．处理程序和条件在存储过程中处理SQL语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条INSERT语句会导致一条错误消息，并且MySQL会立即停止对存储过程的处理。每一条错误消息都有一个唯一代码和一个SQLSTATE代码。例如：Error1022,"Can'twrite;duplicatekeyintable"Error1048,"Columncannotbenull"Error1052,"Columnisambiguous"Error1062,"Duplicateentryforkey"MySQL官方手册的“错误消息和代码”一章中列出了所有的错误消息及它们各自的代码。DECLAREHANDLER语句的语法格式如下：DECLARE处理程序的类型HANDLERFORcondition_value[,...]存储过程语句存储过程体编程（1）处理程序的类型。处理程序的类型主要有3种：CONTINUE、EXIT和UNDO。对于CONTINUE处理程序，MySQL不中断存储过程的处理。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句暂时还未被支持。（2）condition_value。condition_value格式如下：

SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_codesqlstate_value给出SQLSTATE的代码表示。condition_name是处理条件的名称。存储过程体编程（3）存储过程语句。存储过程语句是处理程序激活时将要执行的动作。【例6.7】创建一个存储过程，向xs表插入一行数据（'221101','王民','计算机',1,'2004-02-10',15,NULL,NULL），已知学号221101在xs表中已存在。如果出现错误，程序继续进行。USExscj;DELIMITER$$CREATEPROCEDUREmy_insert()BEGIN DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1; SET@x=2; INSERTINTOxsVALUES('221101','王民','计算机',1,'2004-02-10',15,NULL,NULL); SET@x=3;END$$DELIMITER;调用存储过程查看结果的语法格式为：CALLmy_insert();SELECT@x;执行结果如图。存储过程体编程为了提高可读性，可以使用DECLARECONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字，其语法格式如下：DECLAREcondition_nameCONDITIONFORcondition_value其中，condition_name表示处理条件的名称，condition_value表示要定义别名的SQLSTATE代码或出错代码，作用同DECLAREHANDLER的。【例6.8】修改例6.7中的存储过程，将SQLSTATE'23000'定义成NON_UNIQUE，并在处理程序中使用这个名称，程序片段为：BEGIN DECLARENON_UNIQUECONDITIONFORSQLSTATE'23000’; DECLARECONTINUEHANDLERFORNON_UNIQUESET@x2=1; SET@x=2; INSERTINTOxsVALUES('221101','王民','计算机',1,'2004-02-10',15,NULL,NULL); SET@x=3;END;03游标及其应用游标及其应用（1）声明游标。声明游标的语法格式如下：下面的定义符合一个游标声明：DECLARExs_cur1CURSORFOR SELECT学号,姓名,性别,出生日期,总学分 FROMxs WHERE专业名='计算机';（2）打开游标。声明游标后，要使用游标并从中提取数据，就必须先打开游标。使用OPEN语句打开游标，其格式如下：OPEN游标名

在程序中，一个游标可以打开多次，由于其他的用户或程序可能在期间已经更新了表，所以每次打开结果可能不同。DECLARE游标名CURSORFORselect语句游标及其应用（3）读取数据。游标打开后，就可以使用FETCH…INTO语句从中读取数据，其语法格式如下：FETCH游标名INTO变量名...（4）关闭游标。游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：CLOSE游标名语句参数的含义与OPEN语句中相同。例如：CLOSExs_cur2将关闭游标xs_cur2。游标及其应用【例6.9】创建一个存储过程，计算xs表中行的数目。DELIMITER$$CREATEPROCEDUREcompute(OUTnumberinteger)BEGIN DECLARExhchar(6); DECLAREFOUNDbooleanDEFAULTtrue; DECLAREnumber_xsCURSORFOR SELECT学号FROMxs; DECLARECONTINUEHANDLERFORNOTFOUND SETfound=false; SETnumber=0; OPENnumber_xs; FETCHnumber_xsINTOxh; WHILEfoundDO SETnumber=number+1; FETCHnumber_xsINTOxh; ENDWHILE; CLOSEnumber_xs;END$$DELIMITER;调用此存储过程并查看结果：CALLcompute(@num);SELECT@num;执行结果如图。04存储过程的调用、删除和修改1.调用存储过程2.删除存储过程3.修改存储过程存储过程的调用、删除和修改1．调用存储过程CALL语句的语法格式如下：CALL存储过程名([参数...])【例6.10】创建存储过程，实现查询xs表中学生人数的功能，该存储过程不带参数。USExscj;CREATEPROCEDUREdo_query() SELECTcount(*)FROMxsORDERBY学号;调用该存储过程：CALLdo_query();执行结果如图。存储过程的调用、删除和修改【例6.11】创建xscj数据库的存储过程，判断两个输入参数哪一个更大，并调用该存储过程。（1）创建存储过程DELIMITER$$CREATEPROCEDUREpar(INk1integer,INk2integer,OUTk3char(6))BEGIN IFk1>k2THEN SETk3='大于’; ELSEIFk1=k2THEN SETk3='等于’; ELSE SETk3='小于’; ENDIF;END$$DELIMITER;（2）调用存储过程CALLcompar(3,6,@k);SELECT@k;执行结果如图。存储过程的调用、删除和修改【例6.12】创建一个存储过程，有两个输入参数，即xh和kcm，要求当某学生某门课程的成绩小于60分时将其学分修改为0，大于等于60分时将其学分修改为此课程的学分。DELIMITER$$CREATEPROCEDURExscj.do_update(INxhchar(6),INkcmchar(16))BEGIN DECLAREkchchar(3); DECLARExftinyint; DECLAREcjtinyint; SELECT课程号,学分INTOkch,xfFROMkcWHERE课程名=kcm; SELECT成绩INTOcjFROMcjWHERE学号=xhAND课程号=kch; IFcj<60THEN UPDATEcjSET学分=0WHERE学号=xhAND课程号=kch; ELSE UPDATEcjSET学分=xfWHERE学号=xhAND课程号=kch; ENDIF;END$$DELIMITER;存储过程的调用、删除和修改接下来向cj表中输入一行数据：INSERTINTOcjvalues('221101','208',50,10);然后，再调用存储过程并查询调用结果：CALLdo_update('221101','数据结构');SELECT*FROMcjWHERE学号='221101'AND课程号='208';执行结果如图。存储过程的调用、删除和修改【例6.13】创建一个存储过程do_insert1，作用是向xs表中插入一行数据。再创建另外一个存储过程do_insert2，在其中调用第一个存储过程，并根据条件处理该行数据。创建第一个存储过程：CREATEPROCEDURExscj.do_insert1() INSERTINTOxsVALUES('191101','陶伟','软件工程',1,'2004-03-05',15,NULL,NULL);创建第二个存储过程：DELIMITER$$CREATEPROCEDURExscj.do_insert2(INxbit(1),OUTstrchar(8))BEGIN CALLdo_insert1(); IFx=0THEN UPDATExsSET姓名='刘英',性别=0WHERE学号='191101’; SETstr='修改成功’; ELSEIFx=1THEN DELETEFROMxsWHERE学号='191101’; SETstr='删除成功’; ENDIF;END$$DELIMITER;存储过程的调用、删除和修改接下来调用存储过程do_insert2来查看结果：CALLdo_insert2(1,@str);SELECT@str;执行结果如图。CALLdo_insert2(0,@str);SELECT@str;执行结果如图。存储过程的调用、删除和修改2．删除存储过程创建存储过程后需要将其删除时使用DROPPROCEDURE语句。在此之前，必须确认该存储过程与其他存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行，其语法格式如下：DROPPROCEDURE[IFEXISTS]存储过程名例如，删除存储过程dowhile，命令如下：DROPPROCEDUREIFEXISTSdowhile;存储过程的调用、删除和修改3．修改存储过程用户使用ALTERPROCEDURE语句可以修改存储过程的某些特征，其语法格式如下：ALTERPROCEDURE存储过程名[特征...]特征格式为：{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'【例6.14】使用先删除后再定义的方法修改存储过程。DELIMITER$$DROPPROCEDUREIFEXISTSdo_query;CREATEPROCEDUREdo_query()BEGIN SELECT*FROMxs;END$$DELIMITER;完成后可调用存储过程查询结果：CALLdo_query();第6章MySQL过程式数据库对象——存储函数01创建存储函数创建存储函数语法格式如下：CREATEFUNCTION存储过程名([参数...])RETURNStype[特征...]存储函数体【例6.15】创建一个存储函数，返回xs表中学生的人数。DELIMITER$$CREATEFUNCTIONnum_of_xs()RETURNSintegerBEGIN RETURN(SELECTcount(*)FROMxs);END$$DELIMITER;创建存储函数【例6.16】创建一个存储函数，返回某个学生的姓名。DELIMITER$$CREATEFUNCTIONname_of_stu(xhchar(6))RETURNSchar(8)BEGIN RETURN(SELECT姓名FROMxsWHERE学号=xh);END$$DELIMITER;【例6.17】创建一个存储函数来删除cj表中存在但xs表中不存在的学号。DELIMITER$$CREATEFUNCTIONdelete_stu(xhchar(6)) RETURNSbooleanBEGIN DECLAREstuchar(6); SELECT姓名INTOstuFROMxsWHERE学号=xh; IFstuISNULLTHEN DELETEFROMcjWHERE学号=xh; RETURNtrue; ELSE RETURNfalse; ENDIF;END$$DELIMITER;02存储函数的调用、删除和修改1.调用存储函数2.删除存储函数存储函数的调用、删除和修改1．调用存储函数存储函数创建完后，就如同系统提供的内置函数（如VERSION()函数），所以调用存储函数的方法也与其差不多，都是使用SELECT关键字。SELECT关键字的语法格式如下：SELECT存储函数名([参数[,...]])例如，无参数调用存储函数。命令如下：SELECTnum_of_xs();执行结果如图。例如，有参数调用存储函数。命令如下：SELECTname_of_stu('221106');执行结果如图。存储函数的调用、删除和修改【例6.18】创建一个存储函数，通过调用存储函数name_of_stu()获得指定学号学生的姓名，判断姓名是不是“王林”，是，则返回王林的出生日期，不是，则返回“false”。DELIMITER$$CREATEFUNCTIONis_stu(xhchar(6)) RETURNSchar(10)BEGIN DECLAREnamechar(8); SELECTname_of_stu(xh)INTOname; IFname='王林'THEN RETURN(SELECT出生日期FROMxsWHERE学号=xh); ELSE RETURN'false’; ENDIF;END$$DELIMITER;接着调用存储函数is_stu查看结果：SELECTis_stu('221102');执行结果如图。SELECTis_stu('221101');执行结果如图。存储函数的调用、删除和修改2．删除存储函数删除存储函数与删除存储过程的方法基本一样，都使用DROPFUNCTION语句，其语法格式如下：DROPFUNCTION[IFEXISTS]存储过程名例如，删除存储函数num_of_xs。DROPFUNCTIONIFEXISTSnum_of_xs;完成后读者可使用SHOWFUNCTIONSTATUS进行查看，确定已经没有这个函数了。使用ALTERFUNCTION语句可以修改存储函数的特征，其语法格式如下：ALTERFUNCTION存储过程名[特征...]第6章MySQL过程式数据库对象——触

发

器触

发

器1．创建触发器CREATETRIGGER的语法格式如下：CREATETRIGGER触发器名、触发时刻、触发事件 ON表名FOREACHROW触发器动作说明：触发器名称在当前数据库中必须唯一。触发时刻，有两个选项：AFTER和BEFORE，以表示触发器是在激活它的语句之前或之后触发。触发事件：指明了激活触发程序的语句的类型。表名：表示在该表上发生触发事件才会激活触发器。FOREACHROW：这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作。触发器动作：包含触发器激活时将要执行的语句。要查看数据库中有哪些触发器，需要使用SHOWTRIGGERS命令。触发器不能返回任何结果到客户端，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句。触

发

器【例6.19】创建一个表table1，其中只有一列a。在表中创建一个触发器，每次插入操作时，将用户变量str的值设为“triggerisworking”。CREATETABLEtable1(ainteger);CREATETRIGGERtable1_insertAFTERINSERT ONtable1FOREACHROW SET@str='triggerisworking';向table1中插入一行数据：INSERTINTOtable1VALUES(10);查看str的值：SELECT@str;执行结果如图。触

发

器（1）触发器中关联表中的列在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标识，那会使系统混淆。因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。【例6.20】创建一个触发器，当删除xs表中某个学生的信息时，同时将cj表中与该学生有关的信息全部删除。DELIMITER$$CREATETRIGGERxs_deleteAFTERDELETE ONxsFOREACHROWBEGIN DELETEFROMcjWHERE学号=OLD.学号;END$$DELIMITER;现在验证一下触发器的功能：DELETEFROMxsWHERE学号='221101';使用SELECT语句查看cj表中的情况：SELECT*FROMcj;这时可以发现，学号为221101的学生在cj表中的所有信息已经被删除了。触

发

器【例6.21】创建一个触发器，当修改cj表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分。DELIMITER$$CREATETRIGGERcj_updateBEFOREUPDATE ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT学分INTOxfFROMkcWHERE课程号=NEW.课程号; IFNEW.成绩<60THEN SETNEW.学分=0; ELSE SETNEW.学分=xf; ENDIF;END$$DELIMITER;触

发

器【例6.22】创建触发器，实现当向cj表中插入一行数据时，根据成绩对xs表中的总学分进行修改。如果成绩≥60，总学分加上该课程的学分，否则总学分不变。DELIMITER$$CREATETRIGGERcj_zxfAFTERINSERT ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT学分INTOxfFROMkcWHERE课程号=NEW.课程号; IFNEW.成绩>=60THEN UPDATExsSET总学分=总学分+xfWHERE学号=NEW.学号; ENDIF;END$$DELIMITER;触

发

器（2）在触发器中调用存储过程在触发器中还可以调用存储过程。【例6.23】假设xscj数据库中有一个与xs表结构完全一样的表student，创建一个触发器，在向xs表中添加数据的时候，调用存储过程，将student表中的数据与xs表同步。首先，定义存储过程：DELIMITER$$CREATEPROCEDUREchanges()BEGIN REPLACEINTOstudentSELECT*FROMxs;END$$DELIMITER;接着创建触发器：CREATETRIGGERstudent_changeAFTERINSERT ONxsFOREACHROW CALLchanges();触

发

器验证：INSERTINTOxs VALUES('191102','王大庆','计算机',1,'2004-08-14',48,NULL,NULL);SELECT*FROMstudent;执行结果如图。触

发

器2．删除触发器和其他数据库对象一样，使用DROP语句即可将触发器从数据库中删除，其语法格式如下：DROPTRIGGER[schema_name.]trigger_name例如，删除触发器xs_delete。USExscjDROPTRIGGERxs_delete;第6章MySQL过程式数据库对象——事件事件事件和触发器相似，都是在某些事情发生的时候启动。由于它们彼此相似，所以事件也称作临时性触发器（temporaltrigger）。事件的主要作用如下：（1）关闭账户；（2）打开或关闭数据库指示器；（3）使数据库中的数据在某个间隔后刷新；（4）执行对进入数据的复杂的检查工作。01创建事件创建事件语法格式如下：CREATEEVENT事件名 ONSCHEDULESCHEDULE [ONCOMPLETION[NOT]PRESERVE] [ENABLE|DISABLE|DISABLEONSLAVE] [COMMENT'COMMENT’] DOsql语句;SCHEDULE格式如下：