MySQL教程（第4版） 课件 第7章 过程式数据库对象

第7章

过程式数据库对象——存储过程存储过程使用存储过程的优点如下。（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码即可，提高了系统性能。（3）确保数据库安全。使用存储过程可以完成所有数据库操作，并可以通过编程方式控制上述操作对数据库信息进行访问的权限。01创建存储过程创建存储过程存储过程主体存储过程调用存储过程显示存储过程的修改删除存储过程创建存储过程1.创建存储过程创建存储过程语句如下。CREATEPROCEDURE存储过程名([参数,...])[特征...] [存储过程主体]存储过程名：默认在当前数据库中创建存储过程。需要在特定数据库中创建存储过程时，则在名称前面加上数据库的名称，格式为“数据库名.存储过程名”。参数：参数名后为参数的类型，当有多个参数时中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持3种类型的参数：[IN|OUT|INOUT]参数名typeIN、OUT和INOUT分别是输入参数、输出参数和输入/输出参数关键字。特征：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'注释'创建存储过程2.存储过程主体存储过程主体包含了在过程调用的时候必须执行的若干个语句，这个部分总是以BEGIN开始，以END结束。当然，当存储过程主体中只有一个SQL语句时可以省略BEGIN-END标志。在MySQL5.7中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程主体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，服务器处理程序时遇到第一个分号就会认为程序结束，这肯定是不行的。所以使用DELIMITER命令将MySQL语句的结束标志临时修改为其他符号，例如：DELIMITER$$说明：这里$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”、两个“￥”等。当使用DELIMITER命令时，应该避免使用反斜杠（“\”）字符，因为它是MySQL的转义字符。但Navicat中也可以不进行设置。可以用下列语句检验一下：DELIMITER//USExscj//SELECT姓名FROMxsWHERE学号='221101'//DELIMITER;创建存储过程【例】创建存储过程，删除一个指定学生记录。USExscj;DELIMITER$$CREATEPROCEDUREdelete_xs(INxhchar(6))BEGIN DELETEFROMxs1WHERE学号=xh; SELECT*FROMxs;END$$DELIMITER;创建存储过程3.存储过程调用存储过程创建完后，可以在命令窗口、触发器或者其他存储过程中调用。CALL存储过程名([参数,...])说明：（1）如果要调用非当前数据库的存储过程，则需要在存储过程名前面加上该数据库的名称。（2）这条语句中的参数个数必须总是等于定义该存储过程的参数个数。例如：CALLdelete_xs('221101');创建存储过程4.存储过程显示（1）查看数据库存储过程的具体信息使用语句：SHOWCREATEPROCEDURE存储过程名例如：USExscj;SHOWCREATEPROCEDUREdelete_student;显示结果如图。（2）查看所有存储过程的状态，包括系统存储过程和用户定义的存储过程：SHOWPROCEDURESTATUS创建存储过程5.存储过程的修改ALTERPROCEDURE存储过程名[特征...]其中，特征：{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'注释内容'6.删除存储过程DROPPROCEDURE[IFEXISTS]存储过程名例如，删除存储过程delete_xs：USExscj;DROPPROCEDUREdelete_xs;02存储过程体局部变量分支语句循环语句存储过程体1．局部变量（1）声明局部变量要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。DECLARE变量名[,...]数据类型[DEFAULT值]说明：DEFAULT子句给变量指定一个默认值，如果不指定则默认为NULL。例如，声明一个整型变量和两个字符变量：DECLAREnumint(4);DECLAREstr1,str2varchar(6);局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，而其他语句块中不可以使用。（2）使用SET语句给变量赋值要给局部变量赋值可以使用SET语句，它也是SQL本身的一部分。SET变量名=表达式,...例如，在存储过程中给局部变量赋值：SETnum=1,str1='HELLO';存储过程体（3）SELECT...INTO语句给变量赋值使用SELECT…INTO语句可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。SELECT列名,...INTO变量名,...FROM表名,...【例】通过变量显示xs表中指定学号学生姓名和专业名的值（分别赋给变量）。DELIMITER$$CREATEPROCEDURExscj.xs_vdisp (INxhchar(6))BEGINDECLAREname,projectvarchar(10);SELECT姓名,专业INTOname,project FROMxs WHERE学号=xh;SELECTname,project;END$$DELIMITER;

CALLxscj.xs_vdisp('221101’);显示结果如图。存储过程体2.分支语句（1）IF语句IF语句可根据不同的条件执行不同的操作。IF条件1THEN语句序列1[ELSEIF条件2THEN语句序列2]...[ELSE语句序列0]ENDIF存储过程体【例】创建存储过程，比较第1个参数和第2个参数整数大小，显示比较结果。DELIMITER$$CREATEPROCEDUREComp (INk1integer,INk2integer,OUTk3char(6))BEGIN IFk1>k2THEN SETk3='大于'; ELSEIFk1=k2THEN SETk3='等于'; ELSE SETk3='小于'; ENDIF;END$$DELIMITER;

CALLComp(12,46,@k3);SELECT@k3;显示结果如图。存储过程体（2）CASE语句前面在介绍SELECT选择列时涉及的CASE输出项变换不是语句，这里介绍CASE语句在存储过程中的用法，与之前略有不同。CASE表达式 WHEN值1 THEN语句序列1 [WHEN值2THEN语句序列2] ... [ELSE语句序列0]ENDCASE或者CASE WHEN条件1THEN语句序列1 [WHEN条件2THEN语句序列2] ... [ELSE语句序列0]ENDCASE存储过程体【例】创建一个存储过程，把百分制变成等级。DELIMITER$$CREATEPROCEDURExscj.cjGrade (INcjtinyint(1),OUTgradevarchar(4))BEGINCASE WHENcj>=90THENSETgrade='优秀'; WHENcj>=80ANDcj<90

THENSETgrade='良好'; WHENcj>=70ANDcj<80

THENSETgrade='中等'; WHENcj>=60ANDcj<70

THENSETgrade='及格'; ELSESETgrade='不及格'; SELECTgrade;ENDCASE;END$$DELIMITER;

CALLxscj.cjGrade(85,@gcj);SELECT@gcj; #显示“良好”存储过程体3.循环语句MySQL支持3种循环语句：WHILE、REPEAT和LOOP语句。循环语句可以嵌套。（1）先判断条件再执行语句序列：WHILE语句WHILE条件DO

语句序列ENDWHILE存储过程体【例】采用WHILE计算1+2+3+…+n。USExscj;DROPPROCEDUREIFEXISTSproc_nsum;CREATEPROCEDUREproc_nsum()BEGIN DECLAREn,sintDEFAULT0;

WHILEn<=@nDO SETs=s+n; SETn=n+1;

ENDWHILE; SELECT'1+2+3+…+',@n,'=',s;END;

SET@n=50;CALLproc_nsum();其中，n是局部变量，@n是用户定义变量。运行结果如图。存储过程体（2）先执行语句序列再判断条件：REPEAT语句REPEAT

语句序列UNTIL条件ENDREPEAT【例】采用REPEAT语句计算1+2+3+…+n。将本例的BEGIN…END替换为：BEGIN DECLAREn,sintDEFAULT0;

REPEAT SETn=n+1; SETs=s+n;

UNTILn>=@nENDREPEAT; SELECT'1+2+3+…+',@n,'=',s;END;存储过程体（3）通过语句体控制循环结束：LOOP和LEAVE标签或者ITERATE语句[标签:]LOOP

语句序列 LEAVE标签|ITERATE语句 ...ENDLOOP[标签]说明：语句序列是需要重复执行的语句。执行到“LEAVE标签”语句终止循环，跳转到ENDLOOP[标签]语句的下一个语句。存储过程体【例】采用LOOP语句计算1+2+3+…+n。将本例的BEGIN…END替换为：BEGIN DECLAREn,sintDEFAULT0; SETn=0;

mylabel:LOOP SETs=s+n; IFn>=@nTHEN

LEAVEmylabel; ENDIF; SETn=n+1;

ENDLOOPmylabel; SELECT'1+2+3+…+',@n,'=',s;END;03错误自动处理条件名称定义条件处理定义错误自动处理每一个错误都有一个唯一错误代码和一个SQLSTATE代码，例如，SQLSTATE23000属于如下的错误代码：Error1022,"Can'twrite;duplicatekeyintable"Error1048,"Columncannotbenull"Error1052,"Columnisambiguous"Error1062,"Duplicateentryforkey"MySQL手册中“错误消息和代码”列出了所有可能的错误消息及它们各自的代码。1．条件名称定义DECLARE条件名称CONDITIONFOR条件值其中：条件值：ERROR=MySQL错误代码|SQLSTATE[VALUE]SQL状态值条件名称定义不是必须的。错误自动处理2．条件处理定义DECLARE处理动作HANDLERFOR条件值,...处理程序处理动作：CONTINUE|EXIT|UNDOCONTINUE不中断程序的处理；EXIT对当前BEGIN...END复合语句的执行被终止；UNDO撤销之前的操作，但目前MySQL暂不支持撤销操作。条件值：错误代码|SQLSTATE[VALUE]sql状态码|条件名称|SQLWARNING|NOTFOUND|SQLEXCEPTION处理程序可以根据不同错误定义不同的处理程序，或者采用统一的程序，甚至一个语句。错误自动处理【例】数据库中不存在的表错误处理程序测试。（1）在MySQL命令行窗口操作，系统会显示出错信息，如图。错误自动处理（2）在Navicat创建存储过程执行查询操作，不加控制时显示出错信息。USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DESCxstest;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';调用存储过程后，系统显示错误信息如图。错误自动处理（3）若在存储过程中控制该错误，仅用一个语句对其进行处理。USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFOR1146 SET@ERR=1; DESCxstest;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后';调用存储过程后，系统显示如图。错误自动处理（4）若在存储过程中错误控制需要使用多个语句进行处理如下：USExscj;DROPPROCEDUREIFEXISTSproc_test;CREATEPROCEDUREPROC_test()BEGIN DECLARECONTINUEHANDLERFORSQLSTATE'42S02' BEGIN SET@ERR=1; SELECT'文件不存在！'AS出错信息; #（a） END; SET@ERR=2; DESCxstest; SELECT@ERRAS'DESC后'; #（b） SET@ERR=3;END;

SET@ERR=0;CALLproc_test();SELECT@ERRAS'CALL后'; #（c）调用存储过程后，系统显示如图。

错误自动处理（5）修改第（4）步代码中的语句，将CONTINUE改成EXIT：DECLAREEXITHANDLERFORSQLSTATE'42S02'运行结果变成如图。第7章

过程式数据库对象——存储函数存储函数1.创建存储函数CREATEFUNCTION存储函数名([参数,...]) RETURNS数据类型 [特征...]

存储函数的主体说明：存储函数的主体包含下列语句：RETURN表达式;其中，表达式的值与定义的RETURNS后的数据类型相同。2.存储函数修改ALTERFUNCTION存储函数名[特征...]3.存储函数删除DROPFUNCTION[IFEXISTS]存储函数名存储函数【例】创建一个存储函数，把百分制变成等级。USExscj;DROPFUNCTIONIFEXISTSxscj.cjGradeFun;DELIMITER$$CREATEFUNCTIONxscj.cjGradeFun(cjtinyint(1))RETURNSchar(3)BEGIN DECLAREgradechar(3); CASE WHENcj>=90THENSETgrade='优秀'; WHENcj>=80ANDcj<90THENSETgrade='良好'; WHENcj>=70ANDcj<80THENSETgrade='中等'; WHENcj>=60ANDcj<70THENSETgrade='及格'; ELSESETgrade='不及格'; ENDCASE; RETURNgrade;END$$DELIMITER;

SET@cj=85;SELECT@cj,xscj.cjGradeFun(@cj);执行结果如图。第7章

过程式数据库对象——游

标游

标1.声明游标DECLARE游标名CURSORFORSELECT语句说明：（1）游标名使用与表名同样的规则。（2）SELECT语句返回的是一行或多行数据。（2）在存储过程中定义多个游标，但是一个块中的每一个游标都必须有唯一的名字。（4）SELECT语句不能有INTO子句。例如：DECLARExs_cur1CURSOR FOR SELECT学号,姓名,性别,出生日期,总学分 FROMxscj WHERE专业='计算机';游

标2.打开游标声明游标后，想要使用游标从中提取数据，就必须先打开游标。OPEN游标名在程序中，一个游标可以打开多次，由于其他的用户或程序可能已经更新了表，所以每次打开结果可能不同。3.读取数据游标打开后，就可以从中读取数据。FETCH游标名INTO变量名,...说明：FETCH...INTO语句与SELECT...INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。4.关闭游标游标使用完以后要及时关闭。关闭游标使用CLOSE语句。CLOSE游标名例如：CLOSExs_cur2游

标【例】创建一个包含游标的存储过程，统计xs表中指定班级人数和获得最大学号。USExscj;DELIMITER$$DROPPROCEDUREIFEXISTSclass;CREATEPROCEDUREclass(INbjchar(4),OUTnumberinteger,OUTxhmaxchar(6))BEGIN DECLARExhchar(6); DECLAREfoundbitDEFAULTTRUE; DECLAREnumber_xsCURSORFOR SELECT学号FROMxsWHERELEFT(学号,4)=bjORDERBY学号; DECLARECONTINUEHANDLERFORNOTFOUND SETfound=false; SETnumber=0; OPENnumber_xs; FETCHnumber_xsINTOxh; WHILEfoundDO SETnumber=number+1;SETxhmax=xh; FETCHnumber_xsINTOxh; ENDWHILE; CLOSEnumber_xs;END$$DELIMITER;游

标调用此存储过程并查看结果：SET@bj='2211';CALLxscj.class('2211',@num,@xh);SELECT@bj班级,@num人数,@xh最大学号;执行结果如图。第7章

过程式数据库对象——触

发

器触

发

器1.创建触发器CREATETRIGGER触发器名触发时刻触发事件ON表名FOREACHROW触发器动作说明：触发器名：触发器在当前数据库中必须具有唯一的名称。触发时刻：有AFTER和BEFORE两个选项。触发事件：指明激活触发程序的语句类型。表名：只有在该表上发生触发事件才会激活触发器。FOREACHROW：这个声明用来指定对于受触发事件影响的每一行，都要激活触发器的动作。触发器动作：包含触发器激活时将要执行的语句。触

发

器2.查看当前数据库中触发器SHOWTRIGGERS3.删除触发器DROPTRIGGERIFEXISTS触发器名【例】创建触发器，当向cj表插入一行数据时，根据成绩对xs表的总学分进行修改。如果成绩≥60分，则总学分加上该课程的学分，否则总学分不变。（1）创建触发器USExscj;DROPTRIGGERIFEXISTScj_insert;DELIMITER$$CREATETRIGGERcj_insertAFTERINSERT ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT学分INTOxfFROMkcWHERE课程号=NEW.课程号; IFnew.成绩>=60THEN UPDATExsSET总学分=总学分+xfWHERE学号=NEW.学号; ENDIF;END$$DELIMITER;触

发

器（2）验证触发器功能USExscj;INSERTINTOxs VALUES('221199','王大庆','计算机',1,'2004-08-14',16,NULL,NULL);SELECT学号,姓名,总学分FROMxsWHERE学号='221199'; #（a）INSERTINTOcj(学号,课程号,成绩)VALUES('221199','206',60);SELECT学号,姓名,总学分FROMxsWHERE学号='221199'; #（b）执行结果如图。

触

发

器【例】创建一个触发器，当修改cj表中数据时，根据修改前后成绩是否大于等于60，调整xs表总学分。（1）创建一个触发器USExscj;DROPTRIGGERIFEXISTScj_update;DELIMITER$$CREATETRIGGERcj_updateBEFOREUPDATE ONcjFOREACHROWBEGIN DECLARExfint(1); SELECT学分INTOxfFROMkcWHERE课程号=NEW.课程号; IFOLD.成绩>=60ANDNEW.成绩<60THEN UPDATExsSET总学分=总学分-xfWHERE学号=OLD.学号; ENDIF; IFOLD.成绩<60ANDNEW.成绩>=60THEN UPDATExsSET总学分=总学分+xfWHERE学号=OLD.学号; ENDIF; END$$DELIMITER;触

发

器（2）验证触发器功能USExscj;UPDATEcjSET成绩=50WHERE学号='221199';SELECT学号,姓名,总学分FROMxsWHERE学号='221199';执行结果如图。触

发

器【例】创建一个触发器，当删除表xs中某个学生的信息时，同时将cj表中与该学生有关的记录全部删除。（1）创建触发器USExscj;DROPTRIGGERIFEXISTSxs_delete;DELIMITER$$CREATETRIGGERxs_deleteAFTERDELETE ONxsFOREACHROWBEGIN DELETEFROMcjWHERE学号=OLD.学号;END$$DELIMITER;（2）验证一下触发器的功能：删除xs表学号为'221199'学生记录。SELECT*FROMcjWHERE学号='221199'; #（a）DELETEFROMxsWHERE学号='221199';SELECT*FROMcjWHERE学号='221199'; #（b）执行结果如图。

第7章

过程式数据库对象——事

件01创

建

事

件创建事件创建事件的语句如下。CREATEEVENT[IFNOTEXISTS]事件名 ONSCHEDULE时间描述 [ONCOMPLETION[NOT]PRESERVE] [ENABLE|DISABLE|DISABLEONSLAVE] [COMMENT'注释内容'] DOSQL语句;其中，时间描述：AT时间点[+INTERVAL时间间隔]|EVERY时间间隔[STARTS时间点[+INTERVAL时间间隔]][ENDS时间点[+INTERVAL时间间隔]]时间间隔：数值{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}创建事件MySQL事件调度器负责调用事件。要创建事件，必须打开调度器。可以使用系统变量EVENT_SCHEDULER来打开调度器，TRUE为打开，FALSE为关闭：SETGLOBALEVENT_SCHEDULER=TRUE;【例】2022年11月11日0时0分所有商品单价下调10%。（1）创建test数据库商品表sp：USEtest;CREATETABLEsp( id int(2)NOTNULL, price float(6.2));INSERTINTOsp VALUES(1,20.50),(2,68.00);SELECT*FROMsp;显示结果如图。创建事件（2）创建事件USEtest;DROPEVENTIFEXISTSchangePrice;CREATEEVENTchangePrice ONSCHEDULEAT'2022-11-11' DO #在商品数据库中进行调整价格的操作 updatetest.spSETsp.price=ROUND(0.9*sp.price);SETGLOBALEVENT_SCHEDULER=TRUE;（3）测试事件Windows10及以上版本具有联网同步系统时间的功能，如果临时断开网络修改系统日期和时间，事件就不会启动。所以为了测试事件功能，可以临时修改事件启动时间点。例如，修改为ATnow()+INTERVAL10SECOND，重新创建事件，在10秒钟后运行下列语句测试：USEtest;SELECT*FROMsp;显示结果如图。创建事件【例】从2022年开始，每年9月30日将已经毕业的学生表和成绩表记录转移到历史数据库中，至2030年结束。（1）创建与学生（xs）表和成绩（cj）表相同结构的历史表xs_ls和cj_ls。USExscj;CREATETABLExs_lsLIKExs;CREATETABLEcj_lsLIKEcj;（2）准备学生（xs）表和成绩（cj）表拷贝，将进校最早的学生（学号'20xxxx'）根据当前系统时间，修改为毕业班学生学号，这样既可以测试事件，又不破坏原表数据记录。USExscj;CREATETABLExs_copyAS(SELECT*FROMxs);CREATETABLEcj_copyAS(SELECT*FROMcj);UPDATExs_copySET学号=CONCAT(@byyear,SUBSTR(学号,3,4))WHERELEFT(学号,2)='20';UPDATEcj_copySET学号=CONCAT(@byyear,SUBSTR(学号,3,4))WHERELEFT(学号,2)='20';创建事件（3）创建事件调用的存储过程，实现事件功能。USExscj;DELIMITER$$DROPPROCEDUREIFEXISTSxscj.xscj_lsp;CREATE