《数据库技术及应用（MySQL）》课件 第7章 存储过程和触发器

MySQL数据库技术及应用第7章存储过程与触发器存储过程和触发器都是嵌入在MySQL一段程序。存储过程可以将经常使用的SQL语句封装起来，进行多次使用。游标可以实现数据检索。触发器是由插入、修改和删除命令来触发某个操作，当执行这些命令时，就会激发触发器执行相应的操作。事件是规定计划任务执行的时间和频率。前言学习要点了解存储过程概念掌握存储过程使用方法掌握游标的使用方法。了解触发器概念掌握触发器的使用方法了解事件概念熟悉事件的使用方法第7章存储过程和触发器学习内容7.1存储过程7.2游标7.3触发器7.4事件第7章存储过程和触发器存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能提升效率。第7章存储过程和触发器7.1存储过程7.1.1创建存储过程创建存储过程与创建函数相同，需要先临时修改语句结束符号，再使用CREATE语句创建，其基本语法格式如下。CREATEPROCEDUREsp_name([IN|OUT|INOUT)param_nameTYPE])[characteristic…]routinebody;语法说明：·sp_name：创建存储过程的名称。·IN|OUT|INOUTparam_nametype：IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；TYPE表示参数的类型，该类型可以是MySQL数据库中的任意类型。·routinebody：是SQL代码的内容，可以用BEQIN···END来表示SQL代码的开始和结束。第7章存储过程和触发器7.1存储过程7.1.1创建存储过程【例7-1】创建名称为proSumCno存储过程。统计course表中课程的门数。DELIMITER$$CREATEPROCEDUREproSumCno(OUTsumcINT)BEGINSELECTCOUNT(*)INTOsumcFROMcourse;END$$DELIMITER;第7章存储过程和触发器7.1存储过程7.1.2调用存储过程创建完存储过程后，需要调用存储过程才能实现其功能。SQL采用CALL语句用来调用一个已创建存储过程，基本语法格式如下。CALLsp_name([parameter[,…]]}语法说明：·sp_name：已创建存储过程的名称。·parameter[,…]：实数必须与该过程定义时形参相对应。当形参是IN时，则实参可以是变量或数据，当形参被指定是INOUT或OUT时，则实参必须是变量。第7章存储过程和触发器7.1存储过程7.1.2调用存储过程【例7-2】创建名称为procStu存储过程，根据学号，查询某个学生的信息。DELIMITER$$CREATEPROCEDUREprocStu(INidVARCHAR(20))BEGINSELECT*FROMstudentWHERESno=id;END$$DELIMITER;CALLprocStu(‘20231160');第7章存储过程和触发器7.1存储过程7.1.3查看存储过程1.查看存储过程的状态使用SHOWPROCEDURESTATUS语句查看存储过程的状态，语法格式如下。SHOWPROCEDURESTATUS[LIKE'pattern'];【例7-3】查看procStu存储过程的状态。SHOWPROCEDURESTATUSLIKE'pro%'\G;第7章存储过程和触发器7.1存储过程7.1.3查看存储过程2.查看存储过程的信息使用SHOWCREATEPROCEDURE查看存储过程的信息，语法格式如下。SHOWCREATEPROCEDUREsp_name;语法说明：·sp_name：存储过程名称。【例7-4】查看procStu存储过程信息。SHOWCREATEPROCEDUREprocStu\G;第7章存储过程和触发器7.1存储过程7.1.3查看存储过程3.通过表查看存储过程查询information_schema表可以查询相关存储过程的信息，语法格式如下。SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='sp_name’;语法说明：·ROUNTINE_NAME：ROUTINES表的字段名，该字段存储所属有存储过程的名称。【例7-5】通过表查看procStu存储过程信息。SELECT*FROMinformation_schema.routinesWHEREroutine_name='procStu'\G;第7章存储过程和触发器7.1存储过程7.1.4修改存储过程语法结构如下。ALTERPROCEDUREsp_name[characteristic…];语法说明如下：·characteristic：此项可选，与存储过程创建时的“characteristic”功能相同第7章存储过程和触发器7.1存储过程7.1.4修改存储过程【例7-6】修改存储过程procStu的定义，将读写权限改为MODIFIESSQLDATA，并执行权限由定义者修改为调用者。首先查看procStu修改前的信息。SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu’;修改存储过程procStu的定义。ALTERPROCEDUREprocStuMODIFIESSQLDATASQLSECURITYINVOKER;查看procStu修改后的信息。SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu';第7章存储过程和触发器7.1存储过程7.1.5删除存储过程如果对数据库中已创建的存储过程需要删除时，用户可以通过SQL中DROP语句删除存储过程，语法格式如下。DROPPROCEDURE[IFEXISTS]sp_name;【例7-7】删除存储过程procStu。DROPPROCEDUREIFEXISTSprocStu;第7章存储过程和触发器7.1存储过程学习内容7.1存储过程7.2游标7.3触发器7.4事件第7章存储过程和触发器在MySQL中，存储过程或函数中的查询有时会返回多条记录，而使用简单的SELECT语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标是一种对数据进行遍历的机制，当查询返回结果集后，通过游标来逐行访问结果集，并针对每一行执行相应的操作。第7章存储过程和触发器7.2游标7.2.1游标操作1.创建游标基本语法结构如下。DECLAREcursor_nameCURSORFORselectstatement;·cursor_name：创建游标的名称。·selectstatement：查询语句，返回个结果集，声明的光标基于该结果集进行操作。第7章存储过程和触发器7.2游标7.2.1游标操作1.创建游标【例7-8】定义游标cur_Zame。DECLAREcur_ZameCURSORFORSELECTZno,ZnameFROMspecialty;第7章存储过程和触发器7.2游标7.2.1游标操作2.打开游标游标定义后，在使用之前必须先打开游标，其基本语法格式如下。OPENcursor_name;【例7-9】打开游标cur_Zame。OPENcur_Zame;第7章存储过程和触发器7.2游标7.2.1游标操作3.取游标中的结果集在打开游标后，使用MySQL提供的FETCH语句检索SELECT结果集的记录。FETCH语句每执行一次，就获取一条记录，然后游标的内部指针就向前移动指向下一条记录，以确保每次得到的记录不同。获取结果的基本语法格式如下。FETCHcursor_nameINTOvar_name1[,var_name];【例7-10】使用游标cur_Zame，将查询取得的结果集分别存入标量id和name。FETCHcur_ZameINTOid,name;第7章存储过程和触发器7.2游标7.2.1游标操作4.关闭游标游标检索完记录后，应使用MySQL提供的语句关闭游标，释放游标占用的内存资源。其基本语法格式如下。CLOSEcursor_name;【例7-11】关闭游标cur_Zame。CLOSEcur_Zame;第7章存储过程和触发器7.2游标7.2.2游标使用【例7-12】在jxxx数据库中，定义proc_curc存储过程，使用游标curc将学分大于3的课程号和课程名存储。DELIMITER$$CREATEPROCEDUREproc_curc()BEGINDECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREcurcCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>=3;OPENcurc;FETCHcurcINTOid,NAME;SELECTid,NAME;CLOSEcurc;END$$DELIMITER;CALLproc_curc();第7章存储过程和触发器7.2游标7.2.2游标使用【例7-13】在jxxx数据库中，定义proc_while_cur存储过程，使用游标locur及循环语句，将课程表的课程号和课程名逐一显示。DELIMITER$$CREATEPROCEDUREproc_while_cur()BEGINDECLAREflagINTDEFAULTFALSE;DECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREloccurCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>2;DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=TRUE;OPENloccur;FETCHloccurINTOid,NAME;WHILE(NOTflag)DOSELECTid,NAME;FETCHloccurINTOid,NAME;ENDWHILE;CLOSEloccur;SELECTid,NAME;END$$DELIMITER;CALLproc_while_cur();第7章存储过程和触发器7.2游标学习内容7.1存储过程7.2游标7.3触发器7.4事件第7章存储过程和触发器触发器是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作(INSERT、DELETE、UPDATE)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。第7章存储过程和触发器7.3触发器7.3.1创建触发器本语法格式如下CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtb1_nameFOREACHROWtrigger_stmt;语法说明：·trigger_name：触发器的名称。·trigger_time：触发时间，可以指定为BEFORE或AFTER，BEFORE表示触发器的命令在操作数据之前执行，AFTER表示触发器的命令在操作数据之后执行。·trigger_event：触发事件，包括INSERT、UPDATE和DELET。·tb1_name：建立触发器的表名。·FOREACHROW：表示任何一条记录上的操作，只要满足条件，就会激活触发器程序体。·trigger_stmt：触发器程序体，可以是一条或多条语句，使用BEGIN…END将其封装。第7章存储过程和触发器7.3触发器7.3.1创建触发器【例7-14】创建触发器trig_in_cou，实现当向课程表Course添加一条记录时，用户变量c的值加1。SET@c=0;CREATETRIGGERtrig_in_couAFTERINSERTONcourseFOREACHROWSET@c=@c+1;INSERTINTOCourseVALUES('20110114','计算机组成',3,'计算机');SELECT@c;使用BEGIN…END将其封装。第7章存储过程和触发器7.3触发器7.3.1创建触发器【例7-15】创建触发器trig_delete_score，实现当向学生表student删除一条记录时，sc表中与该学生相关的记录也删除。CREATETRIGGERtrig_delete_sourceAFTERDELETEONstudentFOREACHROWDELETEFROMscWHERESno=OLD.Sno;SELECT*FROMscWHERESno=‘20231160';DELETEFROMstudentWHERESno=‘20231160';SELECT*FROMscWHERESno=‘20231160';第7章存储过程和触发器7.3触发器7.3.2查看触发器可以使用SHOWTRIGGERS语句查看数据库中存在的触发器的定义、状态和语法信息等。【例7-16】查看jxxx数据库中触发器。SHOWTRIGGERS\G;第7章存储过程和触发器7.3触发器7.3.2查看触发器删除触发器是指删除MySQL已经定义的触发器，其基本语法格式如下。DROPTRIGGER[schema_name][IFEXISTS]trigger_name;语法说明：·schema_name：可选项，表示数据库的名称。若省略，则默认为当前数据库。·trigger_name：触发器名称·IFEXISTS：可选项，若将要删除的触发器不存在，系统将相应的提示信息。【例7-17】删除trig_delete_source触发器。DROPTRIGGERIFEXISTStrig_delete_source;第7章存储过程和触发器7.3触发器学习内容7.1存储过程7.2游标7.3触发器7.4事件第7章存储过程和触发器事件是指在某个特定的时间或每隔一段时间根据计划自动完成指定的任务。MySQL的事件可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下是非常实用的。事件是由MySQL提供的事件调度器定时触发执行的，从这个角度上看也可以称作是“临时触发器”。但它与触发器又有所区别，触发器只针对某张表产生的DELETE、INSERT、UPDATE事件，执行特定语句。而事件调度器则是在某一段(间隔)时间执行有固定操作的任务。第7章存储过程和触发器7.4事件7.4.1开启事件调度器事件调度器一个特定的线程，事件是由它来执行和管理。在默认情况下事件调度器处于关闭状态，因此，创建事件必须先查看和设置事件调度器的状态。其基本语法格式如下。SHOWVARIABLESLIKE'event_scheduler';语法说明：·event_scheduler：全局变量，保存事件调度器的状态，有OFF和ON两个值。OFF表示事件调度器关闭；ON表示事件调度器打开。【例7-18】查看事件调度器状态，若为关闭，将其设置为打开。SHOWVARIABLESLIKE'event_scheduler';SETGLOBALevent_scheduler=ON;第7章存储过程和触发器7.4事件7.4.2创建事件当事件调度器处于打开时，就可以创建事件。其基本语法格式如下。CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE|DISABLEONSLAVE][COMMENT'comment']DOevent_body;第7章存储过程和触发器7.4事件7.4.2创建事件语法说明：·event_name：指定事件名称，它的最大长度为64个字符，不区分大小写。·ONSCHEDULE：指定任务执行的时间和时间间隔，有AT和EVERY两种形式进行选择，后面详细说明。·ONCOMPLETION[NOT]PRESERVE：可选项，定义事件是否循环执行，即是一次执行还是永久执行，默认NOTPRESERVE，为一次执行。·ENABLE|DISABLE：可选项，ENABLE表示该事件可用，DISABLE表示该事件不可用。·COMMENT：事件设置注释。·DO：指定事件启动时所要执行SQL语句，如果包含多条语句，使用BEGIN...END结构将其包含其中。第7章存储过程和触发器7.4事件7.4.2创建事件参数schedule的值为一个AT子句，用于指定事件在某个时刻发生。另一个EVERY子句，表示指定事件的重复发生。其语法格式如下。ATtimestamp[+INTERVALinterval]...|EVERYinterval[STARTStimestamp[+INTERVALinterval]...][ENDStimestamp[+INTERVALinterval]...]第7章存储过程和触发器7.4事件7.4.2创建事件【例7-19】创建名称为insert_course_event事件，实现从现在开始10分钟30秒后向Course表添加一条记录。CREATEEVENTinsert_course_eventONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL10MINUTE+INTERVAL30SECONDONCOMPLETIONPRESERVEDOINSERTINTOcourseVALUES('58130080','云计算技术',3);第7章存储过程和触发器7.4事件7.4.2创建事件【例7-20】创建名称为delete_sc_event的事件，用于每隔20秒钟向sc表中删除一条成绩小于或等于0的数据。CREATEEVENTdelete_sc_eventONSCHEDULEEVERY5SECONDONCOMPLETIONPRESERVEDODELETEFROMscWHEREGrade<=0;第7章存储过程和触发器7.4事件7.4.3查看事件事件