MySQL数据库管理与应用任务式教程（微课版）单元10 存储过程与触发器

单元10存储过程与触发器《MySQL数据库管理与应用任务式教程（慕课版）》学习内容任务1存储过程

2任务2触发器

存储过程是一种数据库对象，是存储在服务器上的一组预定义的SQL语句集合。使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用存储过程即可。存储过程可以用来完成转换数据、迁移数据、制作报表等数据库管理中的复杂操作，以减少数据库管理员的工作量。由于存储过程是在MySQL服务器中存储和执行的，因此可以避免重复编写相同的代码，减少客户端和服务器端的数据传输，执行速度快，提高系统性能。存储过程中通过流程控制语句可以完成较复杂的判断和运算，实现更强的功能。此外，存储过程也可以增强数据库使用的安全性和数据完整性。

存储过程具有与函数不同的特点，两者区别如下。（1）语法定义的关键字不同，存储过程使用PROCEDURE，而函数使用fUNCTION。（2）存储过程在创建时没有设置返回值，而函数定义时必须设置返回值。（3）存储过程没有返回值类型，且不能将结果直接赋给变量；而函数定义时要设置返回值类型，且在调用时必须将返回值赋给变量。（4）存储过程必须通过CALL调用，不能用SELECT语句调用；而函数可以直接使用SELECT语句调用。任务1

存储过程10.1.1创建存储过程1．创建存储过程创建存储过程与创建自定义函数大体相同，其语法格式如下。CREATEPROCEDURE存储过程名([[IN|OUT|INOUT]参数名数据类型])

存储过程体说明：（1）PROCEDURE：存储过程标识。（2）IN|OUT|INOUT：在为存储过程设置参数时，在参数名前可指定参数的来源及用途。IN为默认值，表示输入参数，即参数是在调用存储过程时传入存储过程里使用，传入的数据可以是直接数据，也可以是保存的数据变量；OUT表示输出参数，初始值为NULL，它的作用是将存储过程中的值保存到OUT指定的参数中，返回给调用者；INOUT表示输入输出参数，INOUT参数跟OUT类似，都可以从存储过程内部传值给调用者，不同的是调用者还可以通过INOUT参数传递值给存储过程。（3）过程体：存储过程的主体部分，包含在存储过程调用的时候必须执行的SQL语句。这个部分以关键字BEGIN开始，以关键字END结束。若存储过程体中只有一条SQL语句，则可以省略BEGIN和END关键字。任务1

存储过程【例题10.1】在cjgl数据库中创建一个名称为proc_rjxs的存储过程，其功能是显示学生xs表中软件技术专业的学生的信息。执行如下语句。USEcjgl;CREATEPROCEDUREproc_rjxs()

SELECT*FROMxsWHERE专业名='软件技术';任务1

存储过程【例题10.2】在cjgl数据库中创建一个名称为proc_xsxx的存储过程，其功能是根据给定的学号显示相应学生的信息。执行如下语句。DELIMITER//CREATEPROCEDUREproc_xsxx(xhCHAR(6))BEGIN

SELECT*FROMxsWHERE学号=xh;END//DELIMITER;【例题10.3】在cjgl数据库中创建一个带输入输出参数的存储过程proc_xmxh，其功能是根据给定的学生姓名返回相应学生的学号。执行如下语句。DELIMITER//CREATEPROCEDUREproc_xmxh(INxmCHAR(8),OUTxhCHAR(6))BEGINSELECT学号INTOxhFROMxsWHERE姓名=xm;END//DELIMITER;任务1

存储过程2．调用存储过程在MySQL中使用CALL语句来调用存储过程，其语法格式如下。CALL存储过程名([参数[...]]);【例题10.4】调用存储过程proc_rjxs，显示学生表xs中软件技术专业的学生的信息。调用存储过程p_xsxx，显示学号为001101的学生的信息。调用存储过程proc_xmxh，查询学生“王金华”的学号。分别执行如下语句。CALLproc_rjxs();CALLproc_xsxx('001101');CALLproc_xmxh('王金华',@xh);SELECT@xh;任务1

存储过程10.1.2管理存储过程1．查看存储过程（1）查看存储过程的定义。

在MySQL中可以通过SHOWCREATE语句查看存储过程的定义，其语法格式如下。

SHOWCREATEPROCEDURE存储过程名;

如SHOWCREATEPROCEDUREproc_rjxs;。（2）查看存储过程的状态信息。

在MySQL中可以通过SHOWSTATUS语句查看存储过程的状态，其语法格式如下。SHOWPROCEDURESTATUSLIKE存储过程名;如SHOWPROCEDURESTATUSLIKE'proc_xsxx';。任务1

存储过程2．修改存储过程的特征信息可以使用ALTERPROCEDURE语句修改存储过程的某些特征，其语法格式如下。ALTERPROCEDURE存储过程名[特征...]特征用于指定存储过程的特性，特征内容主要包括如下选项，其中特征信息的顺序可任意设置。（1）COMMENT'注释内容'：表示注释信息。（2）CONTAINSSQL：表示子程序包含SQL语句，但不包含读或写数据的语句。（3）NOSQL：表示子程序中不包含SQL语句。（4）SQLSECURITYDEFINER：表示只有定义者才有权执行存储过程。（5）SQLSECURITYINVOKER：表示调用者有权执行存储过程。（6）READSSQLDATA：表示子程序中包含读数据的语句。（7）MODIFIESSQLDATA：表示子程序中包含写数据的语句。任务1

存储过程注意：ALTERPROCEDURE语句不能用来修改存储过程体。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的名字创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的名字创建新的存储过程。【例题10.5】修改存储过程proc_rjxs的定义，将读写权限改为MODIFIESSQLDATA，并指明可调用者。代码如下。ALTERPROCEDUREproc_rjxsMODIFIESSQLDATASQLSECURITYINVOKER;任务1

存储过程3．删除存储过程在MySQL中，可使用DROPPROCEDURE语句来删除数据库中已经存在的存储过程，其语法格式如下。DROPPROCEDURE[IFEXISTS]存储过程名；【例题10.6】删除名为proc_rjxs的存储过程。代码如下。DROPPROCEDUREIFEXISTSproc_rjxs;10.1.3使用MySQLWorkbench图形化工具创建和管理存储过程1．创建存储过程【例10.7】在cjgl数据库中创建一个名称为proc_wlxs的存储过程，其功能是显示学生表xs中网络技术专业的学生的信息。打开MySQLWorkbench，在工具栏中单击“创建一个新存储过程”按钮，即可打开新建存储过程界面。任务1

存储过程在创建存储过程的界面中，设置存储过程的名称和定义，单击Apply按钮，可以预览当前操作的SQL语句。然后单击Apply按钮，在弹出的对话框中单击Finish按钮，即可完成存储过程proc_wlxs的创建。任务1

存储过程调用存储过程proc_wlxs，结果如图10-3所示。任务1

存储过程2．管理存储过程在SCHEMAS栏中，展开cjgl数据库中的StoredProcedures，在存储过程proc_rjxs上右击，通过弹出式菜单，可以实现创建存储过程、修改存储过程、删除存储过程等操作，如图10-4所示。任务1

存储过程图10-4管理存储过程的弹出式菜单10.2.1触发器概述

触发器是一种特殊类型的存储过程，与存储过程的区别在于存储过程通过调用实现，而触发器不能被显式地调用，只有在预先定义好的事件发生时，才会被激活从而被执行。触发器与数据表关联，在MySQL中，只有执行INSERT、UPDATE和DELETE操作时才能激活触发器，其他SQL语句则不会激活触发器。例如，向数据表中插入数据前强制检验或转换数据，或在触发器中的代码执行发生了错误后撤销已执行成功的操作等。

触发器主要用于加强业务规则和数据完整性。触发器可以实现比CHECK约束更复杂的检查和操作，以保护表中的数据。可以由触发器通过数据库中的相关表进行级联无痕更新操作，实现对数据的完全校验，在一定程度上保证了数据的完整性。一般在使用触发器前应优先考虑用约束，在必要时才使用触发器。

使用触发器的不足之处是如果需要变动的数据量较大，触发器的执行效率会非常低。使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是在涉及多个触发器的情况下，会增加维护成本。

根据触发事件，触发器分为3种：INSERT触发器、UPDATE触发器和DELETE触发器

根据触发器发生的时机，触发器可分为BEFORE触发器和AFTER触发器。任务2

触发器10.2.2创建触发器

在MySQL中，可使用CREATETRIGGER语句创建触发器，其语法格式如下。CREATETRIGGER触发器名<BEFORE|AFTER><INSERT|UPDATE|DELETE>ON表名FOREACHROW[FOLLOWS|PRECEDES]

触发器体说明：（1）BEFORE|AFTER：触发时机，表示触发器是在激活它的语句之前或之后触发BEFORE：在触发它的语句执行之前执行，一般用来验证新数据是否满足条件。AFTER：在触发它的语句执行之后再执行一些操作。（2）INSERT|UPDATE|DELETE：触发事件，表示激活触发器的操作类型。INSERT：将新行插入表时激活触发器。DELETE：从表中删除某一行数据时激活触发器。UPDATE：更改表中某一行数据时激活触发器。任务2

触发器（3）ON表FOREACHROW：用于指定触发器的操作对象。FOREACHROW：指行级触发，对于触发事件影响的每一行都要激活触发器的动作。（4）FOLLOWS|PRECEDES：触发顺序，表示指定同一个表中多个触发器的执行顺序，默认按创建顺序激活。FOLLOWS：表示新触发器在现有触发器之后激活。PRECEDES：表示新触发器在现有触发器之前激活。任务2

触发器当对学生表进行插入、更新或删除操作时，有时希望自动给出一些提示信息，或者自动做一些相应的处理，如在学生表中删除一条学生信息时，需要删除其成绩表上的对应记录，这时就可以使用触发器。【例题10.8】创建触发器xs_AFTER_INSERT，其功能是在向学生表中插入一条学生记录后，可显示“已向xs表中插入一条记录”的消息。实现代码如下，结果如图10-5所示。DELIMITER//CREATETRIGGERxs_AFTER_INSERTAFTERINSERTONxsFOREACHROWBEGINDECLAREmessagevarchar(40);SELECT'已向xs表中插入一条记录'INTO@message;END//DELIMITER;INSERTINTOxs(姓名,学号,专业名,性别,出生时间,总学分,备注)VALUES('刘国梁','001112','计算机应用','男','2000-1-300:0:0',46,NULL);SELECT@message;任务2

触发器触发器是因事件被触发而执行的。本例中，先向学生表xs中插入一条记录，触发器xs_AFTER_INSERT将被触发并激活执行，然后查看变量@message的值。MySQL提供了两个逻辑表NEW和OLD。NEW和OLD表的结构与触发器所在数据表的结构完全一致，当触发器执行完成之后，这两个表也会被自动删除。对于UPDATE事件，OLD表中存放的是更新前的记录，NEW表用来存放更新后的记录。对于INSERT事件，OLD表没有记录，NEW表中存放的是要插入的记录。对于DELETE事件，OLD表中存放的是被删除的记录，NEW表中没有记录。任务2

触发器【例题10.9】创建触发器xs_BEFORE_DELETE，其功能是当删除学生表xs中某个学生的记录时，成绩表中该学生的相关成绩信息也一并被删除。实现代码如下。DELIMITER//CREATETRIGGERxs_BEFORE_DELETEBEFOREDELETEONxsFOREACHROWBEGINDELETEFROMcjWHERE学号=old.学号;END//DELIMITER;先向成绩表cj中插入一条记录，执行如下语句。INSERTINTOcj(学号,课程号,成绩)VALUES('001112','206,86);然后，删除学生表中学号为“001112”的学生记录，执行如下语句。DELETEFROMxsWHERE学号='001112';此时，触发器xs_AFTER_INSERT将被触发并激活执行，成绩表中学号“001112”对应的所有成绩全部被删除，通过SELECT语句进行检查，如下所示。SELECT*FROMcj;任务2

触发器10.2.3管理触发器1．查看触发器查看触发器是指查看数据库中已经存在的触发器的定义、状态和语法信息等。（1）用SHOWTRIGGERS语句查看触发器信息。在MySQL中，可以用SHOWTRIGGERS语句来查看触发器的基本信息，其语法格式如下。SHOWTRIGGERS[FROM数据库名][LIKE区配模式|WHERE条件表达式];【例题10.10】查看当前数据库中创建的所有触发器的信息。执行如下语句。SHOWTRIGGERS;（2）在triggers表中查看触发器信息。在MySQL中，所有触发器的信息都存在information_schema数据库的triggers数据表中，可以使用SELECT语句直接从该表中查看指定触发器或所有触发器的信息，具体的语法格式如下。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='触发器名';任务2

触发器【例题10.11】查看触发器xs_BEFORE_DELETE的信息。执行如下语句。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='xs_BEFORE_DELETE'\G也可以查看所有触发器的信息。执行如下语句。SELECT*FROMinformation_schema.triggers\G2．修改触发器触发器的定义不可修改，如果要修改触发器，可以先删除原触发器，再以相同的名称创建新的触发器。任务2

触发器3．删除触发器可以使用DROPTRIGGER语句将触发器从数据库中删除，其语法格式如下。DROPTRIGGER[IFEXISTS]触发器名;执行DROPTRIGGER语句需要SUPER权限。【例题10.12】删除名为xs_BEFORE_DELETE的触发器。执行如下语句。DROPTRIGGERxs_BEFORE_DELETE;注意：在删除表时，表中的触发器也一并删除。任务2

触发器10.2.4使用MySQLWorkbench图形化工具创建和管理触发器

1．创建触发器打开MySQLWorkbench，在SCHEMAS栏中单击cjgl数据库，右击xs表，在弹出式菜单中选择修改表结构的菜单，打开修改表结构的窗口，如图10-6所示。单击下方的Triggers选项卡，打开图10-7所示的创建触发器窗口。任务2

触发器单击左侧BEFOREDELETE行后面的加号会生成触发器名xs_BEFORE