数据基础及应用- 3

第2章MySQL数据库系统基础第4章MySQL数据表第3章MySQL数据库操作第5章使用SQL语句查询数据第1章数据库系统概述第6章视图和索引的使用第8章数据库的安全性管理第7章存储过程和触发器第9章数据库的备份与恢复目录目标导航重点和难点1．存储过程概述及分类；2．创建与维护存储过程的语法格式；3．变量、常量、运算符和表达式的使用；4．游标的使用；5．流程控制语句的使用。6.触发器概述及分类;7.创建与管理触发器语法格式。学习目标1.理解存储过程和存储函数;2.掌握创建存储过程和存储函数的语法格式;3.掌握变量、常量、运算符和表达式的使用;4.掌握游标的使用;5.掌握流程控制语句的使用;6.理解触发器的基本概念;7.掌握创建与管理触发器的语法格式。技能目标1.具备使用SQL语句创建存储过程和存储函数的能力;2.具备使用SQL语句维护存储过程和存储函数的能力;3.具备使用SQL语句创建和管理触发器的能力;7.1.1存储过程与存储函数概述1.存储过程存储过程是在数据库中定义一些完成特定功能的SQL语句集合，经编译后存储在数据库中。存储过程可包含变量、流程控制语句及各种SQL语句。它们可以包含输入参数、输出参数，可以返回单个或者多个结果。一个存储过程就是一个可编程的函数，它在数据库中创建并保存。当希望在不同的应用程序或平台上执行相同的函数或者封装特定功能时，存储过程是非常有用的。第7章存储过程和触发器7.1存储过程7.1.1存储过程与存储函数概述2.存储过程的优点存储过程增强了SQL语句的功能和灵活性。存储过程被创建后，可以在程序中被多次调用，而不必重新编写。存储过程是预编译的，在首次运行存储过程时进行编译和优化，以后每次执行存储过程都不需再重新编译。而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。存储过程可以减少网络流量。存储过程可被作为一种安全机制充分利用。第7章存储过程和触发器7.1存储过程7.1.1存储过程与存储函数概述3.存储函数第7章存储过程和触发器7.1存储过程（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数（2）不能用CALL语句来调用存储函数（3）存储函数必须包含一条RETURN语句，而存储过程中不能包含7.1.2变量、常量、运算符和表达式1.常量常量是指在程序运行过程中值不变的量。常量分为字符串常量、数值常量、十六进制常量、日期时间常量等，如表7-1所示。第7章存储过程和触发器7.1存储过程类型说明举例整型常量没有小数点60、25、-365浮点型常量定点和浮点两种表达形式15.63、-200.25、123E-3、-12E5字符串常量存在于单引号或双引号中的字符序列'学生’、’student’日期时间常量用单引号引起来'2020-3-25'、'May122008'、布尔常量两个值：TRUE和FALSE10、10110十六进制常量使用前缀0x后跟十六进制字符表示0xF12、0x1A2、0x5677.1.2变量、常量、运算符和表达式2.变量变量指在程序运行过程中值可以发生变化的量。常用于保存程序运行过程中的计算结果或输入/输出结果。变量有名字及其数据类型两个属性。变量的数据类型确定了该变量存放值的格式及允许的运算。MySQL中变量分为用户变量和系统变量。（1）用户变量用户自定义的变量既用户变量。用户变量在使用前必须先定义和初始化，没有初始化的变量的值为NULL。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。定义变量的语句为：SET@变量名1=expression1,@变量名2=expression2expression1、expression2为给变量附的值，可以是常量、变量或表达式。例如：SET@user='张三',@password=123456;SET@password=@password+1;SELECT@password;第7章存储过程和触发器7.1存储过程7.1.2变量、常量、运算符和表达式2.变量（2）系统变量MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL数据库服务器启动时就被引入并初始化为默认值，用户可以直接使用。例如：获得现在使用的MySQL版本，语句为：SELECT@@version;在MySQL中,部分系统变量的值是不可以改变的,例如VERSION和CURRENT_DATE。而另一部分系统变量可通过SET语句动态修改,例如SQL_WARNINGS。第7章存储过程和触发器7.1存储过程7.1.2变量、常量、运算符和表达式3.运算符MySQL提供了如下几类运算符：算术运算符、比较运算符、逻辑运算符、位运算符。（1）算术运算符算术运算符有：+（加）、-（减）、*（乘）、/（除）和%（取余）5个，参与运算的数据是数值类型数据，其运算结果也是数值类型数据。另外，加（+）和减（-）运算符也可用于对日期型数据进行运算，还可进行值性字符数据与数值类型数据进行运算。（2）比较运算符常用的比较运算符有：>(大于)、>=(大于等于)、=(等于)、<>(不等于)、<(小于)、<=(小于等于)、!=(不等于)。比较运算符用于测试两个相同类型表达式的顺序、大小、相同与否。比较运算符可以用于所有的表达式，即用于数值大小的比较、字符串在字典排列顺序的前后的比较、日期数据前后的比较。比较运算结果有三种值：正确(TRUE)、错误(FALSE)、未知(UNKNOWN)。第7章存储过程和触发器7.1存储过程7.1.2变量、常量、运算符和表达式3.运算符（3）逻辑运算符逻辑运算符用于对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。逻辑表达式用于IF语句和WHILE语句的条件、WHERE子句和HAVING子句的条件，见表7-2所示。（4）位运算符位运算符包括&(位与)、|(位或)、^(位异或)、～（位取反）、>>（位右移）、<<（位左移）。位运算符在两个表达式之间执行位操作，这两个表达式的结果可以是整数或整数兼容的数据类型。第7章存储过程和触发器7.1存储过程运算符含义AND如果两个逻辑表达式都是为TRUE,则运算结果是TRUEOR如果两个逻辑表达式中的一个为TRUE,则运算结果是TRUENOT

对任何其他布尔运算符的值取反

XOR如果包含的值或表达式一个为真，另一个为假，结果为真，否则为假7.1.2变量、常量、运算符和表达式3.运算符（5）运算符优先级当一个复杂的表达式有多个运算符时，运算符优先性决定执行运算的先后次序。执行的顺序可能严重地影响所得到的最终值。相关运算符的运算优先级如表7-3所示。4.表达式表达式可以是常量、函数、列名、变量、运算符、子查询等的组合。表达式通常可以得到一个值。与常量和变量一样,表达式的值也具有某种数据类型。第7章存储过程和触发器7.1存储过程优先级运算符1~(位非)、+（正）、-（负）

2*(乘)、/(除)、%(取模)

3+(加)、-(减)4=,

>、<、>=、<=、<>、!=、!>、!<(比较运算符)

5^(位异或)、|(位或)

6NOT

7AND

8ALL、ANY、BETWEEN、IN、LIKE、OR、SOME

9=（赋值）7.1.3游标一条SELECT语句返回的是多行数据，如果要一条一条处理数据，就必须引入游标。游标允许应用程序对查询语句返回的结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作。MySQL支持简单的游标。游标一定要在存储过程或函数中使用，不能单独在查询中使用。使用一个游标需要用到4条特殊语句：DECLARECURSOR（声明游标）、OPEN（打开游标）、FETCH（读取游标）和CLOSE（关闭游标）。如果使用DECLARECURSOR语句声明了一个游标，这样就把它连接到一个由SELECT语句返回的结果集中。使用OPENCURSOR语句打开这个游标，接着可以用FETCHCURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中。游标相当于一个指针，它指向当前的一行数据，使用FETCHCURSOR语句可以把游标移动到下一行。当处理完所有的行时，使用CLOSECURSOR语句关闭游标。第7章存储过程和触发器7.1存储过程7.1.3游标使用游标的操作步骤以及语法结构如下：第7章存储过程和触发器7.1存储过程（1）声明游标:DECLARE游标名CURSORFORSELECT语句;（2）打开游标:OPEN游标名;（3）读取数据:

FETCH游标名INTO变量名…;（4）关闭游标:

CLOSE游标名;7.1.4流程控制语言1.IF语句IF语句的语法格式如下：IF条件THEN语句[ELSEIF条件THEN语句]…[ELSE语句]ENDIF小提示：当条件为真时，就执行相应THEN后面的语句，语句可以是一个也可以是多个。第7章存储过程和触发器7.1存储过程7.1.4流程控制语言1.IF语句例1利用IF…THEN…ELSE语句判断是否进行分班上课,分班条件是人数大于30人。SET@record=3''0;SET@string=;IF@record>30THENSET@string='进行分班上课';ELSESET@string='不需要分班上课';ENDIF;第7章存储过程和触发器7.1存储过程7.1.4流程控制语言2.CASE语句根据测试/条件表达式的值不同,返回多个可能结果之一。CASE具有两种格式:简单CASE语句、搜索CASE语句。(1)简单CASE语句简单CASE语句就是将某个表达式的值与一组简单表达式的值进行比较以确定结果。语法格式如下:CASEcase_valueWHENwhen_valueTHENresult_value[…n][ELSEelse_result_value]ENDCASE第7章存储过程和触发器7.1存储过程7.1.4流程控制语言2.CASE语句说明:①when_value的值与case_value的值进行比较。②③reselseu_ltrvalusult_ev:当alucea:sva所lu有esew_hvenal_vueae比wh的_le为比TRU较均E不时返回为TR的U表达式E时返。回的值。④执行过程:首先计算case_value的值,然后计算第一个WHEN后的when_value的值,将两个值进行比较,如果相等,则返回第一个result_value的值;如果不相等,则继续与第二个果都不W为HENTRU后E,的且h定en了_valueELSE进子行句比,较则返。如回ls所e_scasult_e_vvalaulue;ewhe未指valuELSeE的计算子句,结则返回NULL。第7章存储过程和触发器7.1存储过程7.1.4流程控制语言2.CASE语句例2使用简单CASE语句针对不同的成绩,返回不同的成绩等级。SET@分数=88;CASEFLOOR(@分数/10)WHEN10THENSET@成绩级别=''优秀'';WHEN9THENSET@成绩级别='优秀';WHEN8THENSET@成绩级别='良好';WHEN7THENSET@成绩级别='中等';WHEN6THENSET@成绩级'别=及'格;ELSESET@成绩级别=不及格;ENDCASE;SELECT@成绩级别AS成绩级别;第7章存储过程和触发器7.1存储过程7.1.4流程控制语言第7章存储过程和触发器7.1存储过程2.CASE语句根据测试/条件表达式的值的不同，返回多个可能结果表达式之一。CASE具有两种格式：简单CASE语句、搜索式CASE语句。（1）简单CASE语句简单CASE语句就是将某个表达式的值与一组简单表达式的值进行比较以确定结果。语法格式如下：CASEcase_valueWHENwhen_valueTHENresult_value[...n][ELSEelse_result_value]ENDCASE说明：①when_value的值与case_value的值进行比较。②result_value：当case_value=when_value比较的结果为TRUE时返回的表达式。③else_result_value：当case_value=when_value比较的结果都不为TRUE时返回的值。④执行过程：首先计算input_value的值，然后计算第一个WHEN后的input_value的值，并与when_value的值进行比较，如果相等就返回第一个result_value的值，如果不相等继续和第二个WHEN后的input_value的值进行比较，如果input_value=when_value的计算结果都不为TRUE的情况下,如果指定了ELSE子句则返回else_result_value,如果没有指定ELSE子句则返回NULL。7.1.4流程控制语言2.CASE语句例3使用搜索式CASE语句针对不同的成绩,返回不同的成绩等级。SET@分数=88;CASEWHEN@分数>=90and@分数<=100THENSET@成绩级别'='优'秀';WHEN@分数>=80and@分数<90THENSET@成绩级别='良好';WHEN@分数>=70and@分数<80THENSET@成绩级别='中等';WHEN@分数>=60and@'分数<'70THENSET@成绩级别=及格;ELSESET@成绩级别=不及格;ENDCASE;SELECT@成绩级别AS成绩级别;第7章存储过程和触发器7.1存储过程7.1.4流程控制语言3.WHILE循环语句WHILE语句的作用是为重复执行某一语句或语句块设置条件。只要指定的条件为真,就重复执行语句,语法格式如下:WHILE条件DO语句ENDWHILE例4使用WHILE循环计算1+2+3+…+100的和。SET@i=1,@sum=0;WHILE@i<=100DOSET@sum=@sum+@i,@i=@i+1;ENDWHILE;SELECT@sum;第7章存储过程和触发器7.1存储过程7.1.4流程控制语言4.REPEAT语句REPEAT语句的语法格式如下:REPEAT语句UNTIL条件ENDREPEATREPEAT语句首先执行指定的语句,然后判断条件是否为真,为真则停止循环,不为真则继续循环.例5使用REPEAT语句计算1+2+3+…+100的和。SET@i=1,@sum=0;REPEATSET@sum=@sum+@i,@i=@i+1;UNTIL@i>100ENDREPEAT;SELECT@sum;第7章存储过程和触发器7.1存储过程7.1.4流程控制语言5.LOOP语句LOOP语句的语法格式如下:[begin_label:]LOOP语句ENDLOOP[end_label]第7章存储过程和触发器7.1存储过程例6使用LOOP语句计算1+2+3+…+100的和。SET@i=1,@sum=0;label1:LOOPSET@sum=@sum+@i,@i=@i+1;IF@i>100THENLEAVElabel1;ENDIF;ENDLOOPlabel1;Select@sum;7.1.4流程控制语言6.处理程序和条件在存储过程中执行SQL语句可能导致错误消息。例如,向一个表中插入新行时主键已经存在,这条INSERT语句会产生错误消息,并且MySQL会立即停止对该存储过程的处理。每个错误消息都有一个唯一代码和一个SQLSTATE代码。MySQL官方手册的“错误消息和代码”章节列出了所有错误消息及其对应的代码。为了防止MySQL在产生错误消息时立即停止处理,可以使用DECLAREHANDLER语句。DECLAREHANDLER语句为错误代码声明一个所谓的处理程序,指定SQL语句的执行导致错误消息时应如何处理。语法格式如下:DECLARE处理程序类型HANDLERFORcondition_value[,…]存储过程语句第7章存储过程和触发器7.1存储过程7.1.4流程控制语言6.处理程序和条件参数说明:(1)处理程序的类型:CONTINUE表示不中断存储过程的处理;EXIT表示当前语句的执行被终止。(2)condition_value格式如下:SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_codesqlstate_value给出了SQLSTATE的代码表示;condition_name是处理条件的名称;SQLWARNING是对所有以01开头的SQLSTATE代码的速记;NOTFOUND是对所有以02开头的SQLSTATE代码的速记;SQLEXCEPTION是对所有未被SQLWARNING或NOTFOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的错误消息都定义处理程序时,可以使用以上三种速记形式中的一种进行处理。mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值,MySQL错误代码也支持,表示形式为ERROR='string'第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程1.创建存储过程创建存储过程的语法结构如下：CREATEPROCEDURE存储过程名([参数...])[特征...]存储过程体参数说明：（1）存储过程名：用户自定义的存储过程的名称，默认在当前数据库中创建。如果要在特定的数据库中创建，需要在存储过程名前面加上数据库的名称，格式为：数据库名.存储过程名。第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程1.创建存储过程（2）参数：可选项，可以有0个或者多个参数，没有参数时后面的“()”不能省略，每个参数的格式为：[IN][OUT][INOUT]参数名数据类型IN是输入参数，OUT是输出参数，INOUT既可以充当输入参数，也可以充当输出参数，默认参数为IN类型；参数名：是参数的名称，参数的名字不要等于列的名字，如果等于，虽然不会报错，但是存储过程中SQL语句会将参数名看作列名，从而导致不可预知的后果；类型表示参数的类型。当有多个参数时，各个参数之间用逗号分隔。（3）特性：可选项，特性参数的基本语法格式如下：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程1.创建存储过程①LANGUAGESQL：说明存储过程体部分是由SQL语句组成的。②DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果，设置为NOTDETERMINISTIC则表示会产生不确定的结果。默认为：NOTDETERMINISTIC。③CONTAINSSQL：表示存储过程不包含读写数据的语句。④NOSQL：表示存储过程不包含SQL语句。⑤READSSQLDATA：表示存储过程包含读数据的语句。⑥MODIFIESSQLDATA：表示存储过程包含写数据的语句。⑦SQLSECURITY：指明谁有权限执行存储过程，DEFINER表示只有定义者才能执行，INVOKER表示拥有权限的调用者可以执行，默认情况下的值为DEFINER。⑧COMMENT'string'：对存储过程的描述，string为描述内容。第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程1.创建存储过程（4）存储过程体：是调用存储过程时要执行的语句。可以使用所有的SQL语句类型，包括所有的DLL、DCL和DML语句，也允许使用过程式语句，包括变量、流程控制语句等。多条语句要使用BEGIN语句开头，以END语句结束。每个SQL语句都是以分号为结尾的。小提示：存储过程中可以将SQL语句编译保存在数据库中，使用的时候直接调用，这大大提高了执行效率，同时降低了网络数据传输量，但是存储过程也不可以大量的使用。在使用时要注意以下几点：（1）各版本的数据库在存储过程中语法有可能不一样，不利于数据库的移植。（2）版本不好控制，不能进行多人协同开发，调试不方便。（3）不能把核心业务或经常发生变化的功能放在存储过程中。第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程2.执行存储过程存储过程创建完成后,可以在程序、触发器或存储过程中被执行,执行时必须使用CALL语句。其语法格式如下:CALL存储过程名([参数1,…])其中参数列表为执行该存储过程使用的参数,其个数必须与定义存储过程时的参数个数相同。例7创建并执行无参数的存储过程p_s1,查询学生的学号、姓名、电话号码和家庭住址(需设置别名)。REATEPROCEDUREp_s1()BEGINSELECTsno'学号',sname'姓名',sphone'电话号码',saddress'家庭住址'FROMstudent;END执行存储过程p_s1:CALLp_s1;第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例8创建并执行无参数的存储过程p_s2,查询“计算机16-1”班级的学生姓名、课程号和成绩。CREATEPROCEDUREp_s2()BEGINSELECTsname,cno,gradeFROMstudent,sc,classWHEREstudent.sno=sc.snoANDstudent.classno=class.classnoANDclassname='计算机16-1;END执行存储过程p_s2:CALLp_s2()第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例9创建并执行存储过程p_s3,实现功能:若存在学号为“2016010101”的学生记录,则显示该学生及其成绩表信息;若不存在此学生,则显示“没有这个学生!”。CREATEPROCEDUREp_s3()BEGINSET@i=(SELECTCOUNT(*)FROMstudentWHEREsno='2016010101');IF@i<>0THENELSESELECTSELECT**FROMstudFROMscWeHtREWHERsnos'n2o00'20160101011'0;101';SELECT'没有这个学生!';ENDIF;END执行存储过程p_s3:CALLp_s3()第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例10创建并执行带参数的存储过程p_s4,查询指定学生姓名的学生学号、姓名、电话号码和家庭住址。CREATEPROCEDUREp_s4(INs_nameCHAR(8))BEGINSELECTsno,sname,sphone,saddressFROMstudentENDWHEREsname=s_name;执行存储过程p_s4,查询“田园”同学的基本信息,语句如下:CALLp_s4('田园');第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例11创建并执行存储过程p_s5,根据学生学号输出该学生的总成绩。CREATEPROCEDUREp_s5(INs_noCHAR(10),OUTsINT)BEGINSELECTSUM(grade)INTOsFROMscENDWHEREsno=s_no;执行存储过程p_s5,查询学号为“2016010101”的学生的总成绩,语句如下:CALLp_s5('2016010101',@s);SELECT@s总成绩;第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例12创建并执行存储过程p_s6,根据课程号,输出该课程的最高成绩和最低成绩。CREATEPROCEDUREp_s6(INc_noCHAR(10),OUTmaxgradeINT,OUTmingradeINT)BEGINSELECTMAX(grade),MIN(grade)INTOmaxgrade,mingradeFROMscENDWHEREcno=c_no;执行存储过程p_s6,查询课程号为“001”的学生总成绩,语句如下:CALLp_s6('001',@maxgrade,@mingrade);SELECT@maxgrade最高成绩,@mingrade最低成绩;第7章存储过程和触发器7.1存储过程7.1.5创建和执行存储过程例13创建存储过程p_s7,根据课程号输出学生的成绩信息。CREATEPROCEDUREp_s7(INOUTc_noCHAR(10))BEGINSETSELEn',002cno',;gradeFROMscENDWHEREcno=c_no;执行存储过程p_s7,查询课程号为“001”的学生的成绩信息,语句如下:setCALL@p_co7'0c01no');;第7章存储过程和触发器7.1存储过程7.1.6创建和执行存储函数(1)创建存储函数使用CREATEFUNCTION语句创建存储函数,其语法格式如下:CREATEFUNCTION存储函数名([参数…])RETURNSTYPE[特征…]存储函数体参数说明:①存储函数的定义格式和存储过程相差不大。②存储函数不能拥有与存储过程相同的名字。存储函数的参数只有名称和类型,不能指定IN、OUT、INOUT。③RETURNSTYPE声明函数返回值的数据类型。④存储函数体:所有在存储过程中使用的SQL语句在存储函数中也适用,包含流程控制语句、游标等,但是存储函数体中必须包含RETURNvalue语句,value为存储函数的返回值。第7章存储过程和触发器7.1存储过程7.1.6创建和执行存储函数(2)执行存储函数执行存储函数的方法和使用系统的内置函数一样,使用SELECT语句就可以查看函数的返回值。语法格式如下:SELECT*FROM存储函数名([参数])例14创建并执行存储函数f_s1,返回学生表中的总人数。CREATEFUNCTIONf_s1()RETURNSINTEGERBEGINRETURN(SELECTCOUNT(*)FROMstudent);END执行存储函数f_s1,查询学生表中的总人数,执行语句如下:SELECTf_s1()总人数;第7章存储过程和触发器7.1存储过程7.1.6创建和执行存储函数例15创建并执行存储函数f_s2,根据给定的学生学号返回学生的姓名。CREATEFUNCTIONf_s2(s_noCHAR(10))RETURNSCHAR(8)BEGINRETURN(SELECTsnameFROMstudentWHEREsno=s_no);END执行存储函数f_s2,查询学号为2016010201的学生的姓名,执行语句如下:SET@sno='2016010201';SELECTf_s2(@sno)姓名;第7章存储过程和触发器7.1存储过程7.1.7管理存储过程和存储函数1.查看存储过程和存储函数的状态使用SHOWSTATUS语句可以查看存储过程和存储函数的状态,其基本语法格式如下:SHOWPROCEDURE|FUNCTIONSTATUS[LIKE'字符串']参数说明:(1)SHOWPROCEDURE表示查看存储过程;SHOWFUNCTION表示查看存储函数。(2)LIKE'字符串'为可选项,表示匹配存储过程或存储函数的名称。若没有指定,则查看所有存储过程或存储函数的信息。例16查看学生信息管理数据库的存储过程的状态。SHOWPROCEDURESTATUS例17查看学生信息管理数据库的存储函数的状态。SHOWFUNCTIONSTATUS第7章存储过程和触发器7.1存储过程7.1.7管理存储过程和存储函数2.查看存储过程和存储函数的定义MySQL还可以使用SHOWCREATE语句查看存储过程和存储函数的定义,其基本语法格式如下:SHOWCREATEPROCEDURE|FUNCTION

存储过程名或存储函数名例18查看学生信息管理数据库的存储过程p_s1的定义。SHOWCREATEPROCEDUREp_s1例19查看学生信息管理数据库的存储函数f_s1的定义。SHOWCREATEFUNCTIONf_s1第7章存储过程和触发器7.1存储过程7.1.7管理存储过程和存储函数3.从information_schema.Routines表中查看存储过程和存储函数的信息在MySQL中,存储过程和存储函数的信息存储在information_schema数据库下的Routines表中,可以通过查询该表的记录查询存储过程和存储函数的信息。语法格式如下:SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='存储过程名或存储函数名'例20从information_schema.Routines表中查看学生信息管理数据库的存储过SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='p_s1'例21从information_schema.Routines表中查看学生信息管理数据库的存储函数f_s1的信息SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='f_s1'第7章存储过程和触发器7.1存储过程7.1.7管理存储过程和存储函数4.修改存储过程和存储函数在MySQL中,存储过程和存储函数的信息存储在information_schema数据库下的Routines表中,可以通过查询该表的记录查询存储过程和存储函数的信息。语法格式如下:SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='存储过程名或存储函数名'例20从information_schema.Routines表中查看学生信息管理数据库的存储过SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='p_s1'例21从information_schema.Routines表中查看学生信息管理数据库的存储函数f_s1的信息SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='f_s1'第7章存储过程和触发器7.1存储过程7.1.7管理存储过程和存储函数4.修改存储过程和存储函数例22使用ALTERPROCEDURE将存储过程p_s1的特性修改为只包含读数据的语句。ALTERPROCEDUREp_s1READSSQLDATA例23使用ALTERFUNCTION将存储函数f_s1的特性修改为只包含读数据的语句。ALTERFUNCTIONf_s1READSSQLDATA5.删除存储过程和存储函数删除存储过程和存储函数可以使用DROP语句,语法格式如下:DROPPROCEDURE|FUNCTION[IFEXISTS][数据库名.]存储过程名或存储函数名IFEXISTS子句是MySQL的扩展,当存储函数或存储过程不存在时,可以避免发生错误。例24删除存储过程p_s1。DROPPROCEDUREIFEXISTSp_s1例25删除存储函数f_s1。DROPFUNCTIONIFEXISTSf_s1第7章存储过程和触发器7.1存储过程7.2.1触发器概述1.触发器的定义与功能触发器(TRIGGER)是一种与表事件相关的特殊存储过程,其执行不是由程序调用,也不是手动启动,而是由数据库事件触发。触发器常用于加强数据的完整性约束和业务规则。触发器可以查询其他表,并可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。触发器与存储过程的唯一区别是触发器不能通过CALL语句调用,而是在用户执行Transact-SQL语句时自动触发执行。触发器由特定事件触发,这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时,会自动激活触发器执行相应的操作。7.2触发器7.2.1触发器概述2.触发器的优点(1)触发器能够自动执行,在对表的数据进行任何修改(例如,手动输入或应用程序采集操作)之后立即激活。(2)触发器可以通过数据库的相关表实现级联更改,相比在前台代码中直接实现更为合理。(3)触发器可以强制限制,这些限制比使用CHECK约束定义更为灵活。与CHECK约束不同的是,触发器可以引用其他表中的列。7.2触发器7.2.1触发器概述2.触发器的优点(1)触发器不能调用将数据返回客户端的存储过程,也不能使用CALL语句执行动态SQL(不允许存储过程通过参数将数据返回触发器)。(2)触

发

器

不

能

使

用

以

显

示

或

隐

式

方

式

开

始

或

结

束

事

务

的

语

句,如STARTTRANSACTION、COMMIT或ROLLBACK。(3)MySQL触发器基于行级操作,处理大数据集时可能效率较低。(4)触发器不能保证操作的原子性。当一个更新触发器在更新数据表后,触发对另一个表的更新,若第二个表更新失败,系统并不会回滚第一个表的更新操作。7.2触发器7.2.2触发器的分类1.DML触发器当数据库表中的数据发生变化,包括执行INSERT、UPDATE、DELETE任意操作时,如果对该表编写了相应的DML触发器,则该触发器会自动执行。DML触发器的主要作用在于强制执行业务规则,以及扩展SQL约束、默认值等。约束仅能限制同一表中的数据,而触发器则可执行任意SQL命令。2.DDL触发器DDL触发器是较新的一类触发器,主要用于审核与规范对数据库中表、触发器、视图等结构对象的操作,例如修改表,修改列,新增表,新增列等。它在数据库结构发生变化时执行,常用于记录数据库的修改过程,以及限制程序员对数据库的修改,例如禁止删除某些指定表。7.2触发器7.2.2触发器的分类3.登录触发器登录触发器是为响应LOGIN事件而执行的存储过程。登录触发器会在登录身份验证阶段完成之后且用户会话实际建立之前被激发。因此,触发器内部产生的所有消息(例如错误消息或来自PRINT语句的输出)通常会传送到MySQL错误日志。若身份验证失败,登录触发器将不会被激发。7.2触发器7.2.3触发器的嵌套触发器可以包含影响另一个表的UPDATE、INSERT或DELETE语句,形成触发器的嵌套,即在一个触发器执行过程中,又引发了另一个触发器的执行。使用嵌套触发器可以更好地管理和维护数据库。MySQL允许的触发器嵌套层数最多为32层

。层数限制是为了防止无限递归和资源消耗大

。在实际应用中,应该尽量减少触发器的嵌套层数,以保证数据库的性能和稳定性

。设计触发器时,应尽量避免在一个触发器中执行可能导致其他触发器被触发的操作

。可以通过将复杂逻辑拆分为多个触发器,或使用存储过程来实现相关业务

。7.2触发器7.2.4创建触发器创建触发器需使用CREATETRIGGER语句,基本语法格式如下:CREATETRIGGERTrigger_nameTrigger_timeTrigger_eventONtbl_nameFOREACHROWBEGINTrigger_stmtEND7.2触发器7.2.4创建触发器参数说明:①Trigger_name:触发器的名称,需符合标识符命名规则;②Trigger_time:触发器的执行时机(AFTER或BEFORE)。BEFORE表示在SQL执行之前先执行触发器;AFTER则相反;③Trigger_event:触发器的触发事件(常见的有三种:INSERT、UPDATE、DELETE);④tbl_name:建立触发器的表名;⑤FOREACHROW:表示任何一条记录上的操作满足触发事件时都会触发该触发器;⑥Trigger_stmt:触发器执行语句。7.2触发器CREATETRIGGERTrigger_nameTrigger_timeTrigger_eventONtbl_nameFOREACHROWBEGINTrigger_stmtEND7.2.4创建触发器例26

创建一个触发器ins_cou,当新增课程信息后,给出提示信息“课程信息已成功插入!”。

创建触发器ins_cou语句:CREATETRIGGERins_couAFTERINSERTONcourseFOREACHROWBEGINSET@str='课程信息已成功插入!';END执行激活触发器ins_cou的语句:INSERTINTOcourseVALUES('009','工程测量',2,'03');查看变量,编写语句:SELECT@str;7.2触发器7.2.4创建触发器例26

创建一个触发器ins_cou,当新增课程信息后,给出提示信息“课程信息已成功插入!”。7.2触发器创建触发器并执行激活语句如图7-20所示,课程记录成功插入course表中,如图7-21所示。图7-21课程记录成功插入course表中图7-20创建触发器并执行激活语句7.2.4创建触发器例27

创建一个触发器ins_stu,当新增学生信息后,自动添加该学生的“001”号课程的选课信息。创建触发器ins_stu语句:CREATETRIGGERins_stuAFTERINSERTONstudentFOREACHROWBEGININSERTINTOscVALUES(NEW.sno,'001',NULL);END执行激活触发器ins_stu的语句:INSERTINTOstudent(sno,sname,ssex)VALUES('2016010103','赵宏林','男');7.2触发器7.2.4创建触发器例27

创建一个触发器ins_stu,当新增学生信息后,自动添加该学生的“001”号课程的选课信息。执行结果:student表中插入学生记录,如图7-22所示。同时该学生的“001”课程的选课信息成功插入sc表中,如图7-23所示。7.2触发器图7-22student表中插入学生记录图7-23sc表中插入选课记录7.2.4创建触发器例28创建一个触发器ins_stu2,当新增学生信息后,自动添加该学生的“计算机基础”课程的选课信息。创建触发器ins_stu2语句:CREATETRIGGERins_stu2AFTERINSERTONstudentFOREACHROWBEGINDECLAREcidCHAR(10);SETcid=(SELECTcnoFROMcourseWHEREcname='计算机基础');INSERTINTOscVALUES(NEW.sno,cid,NULL);END执行激活触发器ins_stu2的语句:INSERTINTOstudentVALUES('2016010203','张宏利','男','1994-3-15','辽宁沈阳市','116400',,'20160102');7.2触发器7.2.4创建触发器例28创建一个触发器ins_stu2,当新增学生信息后,自动添加该学生的“计算机基础”课程的选课信息。执行结果:student表中插入学号为'2016010203'的学生记录,同时该学生的“计算机基础”课程的选课信息成功插入sc表中,如图7-24所示。7.2触发器图7-24激活触发器ins_stu2后,sc表中记录7.2.4创建触发器例29创建一个触发器upd_stu,当修改学生学号后,自动修改学生选课表sc中的学号,以保持学生学号的一致。创建触发器upd_stu语句:CREATETRIGGERupd_stuAFTERUPDATEONstudentFOREACHROWBEGINUPDATEscSETsno=NEW.snoWHEREsno=OLD.sno;END执行激活触发器upd_stu的语句:UPDATEstudentSETsno='2016010105'WHEREsno='2016010102';7.2触发器7.2.4创建触发器例29创建一个触发器upd_stu,当修改学生学号后,自动修改学生选课表sc中的学号,以保持学生学号的一致。7.2触发器图7-25student表中学生学号的修改执行结果,student表中学生学号的修改,如图7-25所示。同时该学生的学号在sc表中同步修改,如图7-26所示。图7-26sc表中学生学号同步修改7.2.4创建触发器例30

创建一个触发器del_stu,当删除学生信息后,自动删除选课表sc中的该学生选课信息。创建触发器del_stu语句:CREATETRIGGERdel_stuAFTERDELETEONstudentFOREACHROWBEGINDELETEFROMscWHEREsno=OLD.sno;END执行激活触发器del_stu的语句:DELETEFROMstudentWHEREsno='2016010103';7.2触发器7.2.4创建触发器例30

创建一个触发器del_stu,当删除学生信息后,自动删除选课表sc中的该学生选课信息。创建触发器del_stu语句:7.2触发器执行结果,student表中学生信息被删除,如图7-27所示。同时该学生的选课信息在sc表中同步删除,如图7-28所示。图7-27删除学生信息图7-28删除学生选课信息7.2.4创建触发器例31创建触发器score_sc,在sc表上执行INSERT操作时,验证成绩是否有效,若成绩非法,则中断INSERT操作并抛出相应错误。创建触发器score_sc语句:CREATETRIGGERscore_scBEFOREINSERTONscFOREACHROWBEGINDECLAREmessageVARCHAR(30);IFnew.grade<0ORNEW.grade>100THENSETmessage='成绩必须在0-100!';SIGNALSQLSTATE'HY000'SETMESSAGE_TEXT=message;ENDIF;END7.2触发器7.2.4创建触发器例31创建触发器score_sc,在sc表上执行INSERT操作时,验证成绩是否有效,若成绩非法,则中断INSERT操作并抛出相应错误。执行激活触发器score_sc的语句:(1)INSERTINTOscVALUES('2016020102','003',-2);7.2触发器因成绩非法,不在0-100,该条记录未插入sc表中,抛出异常,给出提示“成绩必须在0-100!”,如图7-29所示。图7-29成绩非法,未成功插入记录7.2.4创建触发器例31创建触发器score_sc,在sc表上执行INSERT操作时,验证成绩是否有效,若成绩非法,则中断INSERT操作并抛出相应错误。执行激活触发器score_sc的语句:(2)INSERTINTOscVALUES('2016020102','001',85);7.2触发器执行后,该条记录被成功插入sc表中,如图7-30所示。图7-30记录插入成功7.2.5管理触发器1.查看触发器查看触发器是指查看数据库中已经存在的触发器的定义、状态和语法信息等。可以通过命令来查看已经创建的触发器。在MySQL中,通过SHOWTRIGGERS查看触发器的基本语法格式如下:SHOWTRIGGERSFROM[database_name];其中database_name表示要查看的数据库名称。7.2触发器7.2.5管理触发器例32查看学生信息管理数据库gradem中的触发器。输入查看触发器的语句:SHOWTRIGGERSFROMgradem;执行结果如图7-31所示。7.2触发器图7-31查看数据库gradem中的触发器7.2.5管理触发器例33使用NavicatforMySQL平台查看student表中的触发器。启动NavicatforMySQL平台,展开数据库“gradem”,展开表。在student表上单击右键,在快捷菜单中选择“设计表”命令,在student的设计界面,切换到“触发器”选项卡,可以看到student表上创建的所有触发器,如图7-32所示。7.2触发器图7-32查看student表中的触发器7.2.5管理触发器2.删除触发器使用DROPTRIGGER语句删除触发器,基本语法格式如下:DROPTRIGGER[database].[trigger_name];其中database.trigger_name表示要删除指定数据库的触发器名称。7.2触发器7.2.5管理触发器2.删除触发器例34使用NavicatforMySQL平台删除表course中的ins_cou触发器。启动NavicatforMySQL平台,展开数据库“gradem”,在表course上单击右键,在快捷菜单中选择“设计表”命令,在course的设计界面,切换到“触发器”选项卡。在触发器ins_cou上单击右键,选择“删除触发器”命令,即可完成触发器的删除,如图7-33所示。7.2触发器图7-33图形界面删除触发器7.2.5管理触发器2.删除触发器例35

使用DROPTRIGGER语句删除学生信息管理数据库中的ins_cou触发器。输入删除触发器的语句:DROPTRIGGERins_cou;执行结果如图7-34所示。7.2触发器图7-34使用语句删除触发器7.2.6事件1.事件的概念自MySQL5.1.0开

始,新

增

了

一

个

非

常

有

特

色

的

功

能——事

件

调

度

器(EventScheduler),可以用作定时执行某些特定任务(如删除记录、对数据进行汇总等),来取代原先只能由操作系统的计划任务来执行的工作。值得一提的是,MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用是非常适合的。7.2触发器7.2.6事件2.创建事件在MySQL中创建事件的基本语法格式如下:CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE|DISABLEONSLAVE][COMMENT'comment']DOevent_body;7.2触发器7.2.6事件2.创建事件参数说明:event_name:用于指定事件名称,event_name的最大长度为64个字符,如果未指定event_name,则默认为当前的MySQL用户名(不区分大小写)。ONSCHEDULEschedule:用于定义执行的时间和时间间隔,schedule表示触发点。如果为ATNOW()表示创建后立即启动事件,如果为ATCURRENT_TIMESTAMP+INTERVALnumberSECOND表示在当前时间后间隔number秒后启动事件。ONCOMPLETION[NOT]PRESERVE:用于定义事件是否循环执行,即是一次执行还是永久执行,默认为一次执行,