王昂2019.02 第六章 MySQL过程式数据库对象 主讲内容：存储过程、存储函数、触发器、事件 《MySQL数据库应用》/ 第2页 教学内容
1.存储过程
2.存储函数
3.触发器
4.事件 《MySQL数据库应用》/ 第3页 6.1存储过程 存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程名称和参数来调用它。
存储过程的优点：
（1）在存储过程内部可以编写各种功能代码，完成复杂的判断和复杂的运算，增强灵活性。

（2）存储过程被创建后，可以在程序中被多次调用，可以随时修改，不影响应用程序和源代码。

（3）存储过程在服务器端运行，执行速度快。

（4）增加安全性。
通过设置存储过程权限，可以避免非授权用户对数据的访问。
《MySQL数据库应用》/ 第4页 6.1.1创建存储过程
1、创建存储过程命令 语法格式： CREATEPROCEDURE存储过程名([过程参数...])[特征...]存储过程体 说明：–过程参数格式为：[IN|OUT|INOUT]参数名参数类型–系统默认在当前数据库中创建。
需要在特定数据库中创建存储过程时，则要 在名称前面加上数据库的名称，格式为：数据库名.存储过程名。
–当存储过程有多个参数时，中间用逗号隔开。
《MySQL数据库应用》/ 第5页 6.1.2存储过程体 案例：createprocedurexx_student(inxhchar
(6))createprocedurexscj.xx_student(inxhchar
(6))createproceduresex_xs(inxhchar
(6),outshuchuchar(20)) 《MySQL数据库应用》/ 第6页 6.1.1创建存储过程
（1）存储过程特征的格式 LanguageSQL|[not]deterministic|{containsSQL|NOSQL|reads|SQLsecurity{definer|invoker}|ment'string' SQLdata|modifies SQLdata} 说明：
（1）LANGUAGESQL：表明编写这个存储过程的语言为SQL语言。

（2）DETERMINISTIC：–DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果；–NOTDETERMINISTIC：表示会产生不确定的结果；–默认为NOTDETERMINISTIC。
《MySQL数据库应用》/ 第7页 6.1.1创建存储过程
（3）CONTAINSSQL：表示存储过程不包含读或写数据的语句。
–NOSQL：表示存储过程不包含SQL语句；–READSSQLDATA：表示存储过程包含读数据的语句，但不包含写数据的语句；–MODIFIESSQLDATA：表示存储过程包含写数据的语句；–如果这些特征没有明确给定，默认的是CONTAINSSQL。

（4）SQLSECURITY：用来指明谁有权来执行。
–DEFINER：使用创建该存储过程的用户的权限来执行；–INVOKER：使用调用者的权限来执行；–默认值是DEFINER。

（5）COMMENT'string'：注释信息，对存储过程的描述。
《MySQL数据库应用》/ 第8页 6.1.1创建存储过程
（2）存储过程体 存储过程体包含了在过程调用的时候必须执行的语句。
delimiter结束符号begin 过程体end结束符号delimiter; 例如：delimiter$$begin 过程体end$$delimiter; 《MySQL数据库应用》/ 第9页 6.1.1创建存储过程 【例6.1】用存储过程实现删除一个特定学生的信息。
createproceduredelete_student(inxhchar
(6))begin deletefromxswhere学号=xh;end;代码的执行过程： delimiter$$createproceduredelete_student(inxhchar
(6))begin deletefromxswhere学号=xh;end$$delimiter; 《MySQL数据库应用》/ 第10页 6.1.1创建存储过程 案例：创建一个存储过程，实现根据学号查看学生信息的功能。
delimiter$$createprocedurexx_student(inxhchar
(6))begin select*fromxswhere学号=xh;end$$ delimiter; 《MySQL数据库应用》/ 第11页 6.1.1创建存储过程 案例：创建一个带out参数的存储过程，实现查看学生姓名的功能。
delimiter$$createprocedurexm_student(outxmchar
(8))begin select姓名fromxs;end$$ delimiter; 《MySQL数据库应用》/ 第12页 6.1.1创建存储过程 案例：创建一个不带参数的存储过程，实现查看xs表信息的功能。
delimiter$$createprocedurexs_student(）begin select*fromxs;end$$ delimiter; 《MySQL数据库应用》/ 第13页 6.1.2存储过程体 存储过程体中可以使用所有的SQL语句，也可以使用过程式语句，例如：变量、控制流语句等。

1．变量MySQL存储过程中的参数主要有局部参数和全局参数，又称为局部变量和全局变量，局部变量只在定义局部变量的begin……end范围内有效，全局变量在整个存储范围内均有效。

（1）局部变量局部变量以关键字DECLARE声明。
语法格式： DECLARE变量名...类型[默认值] 《MySQL数据库应用》/ 第14页 6.1.2存储过程体 案例：声明一个整型变量和两个字符变量。
BEGINdeclarenumint
(4);declarestr1,str2varchar
(6);……END 说明： ‒局部变量只能在BEGIN…END语句块中声明，而且必须在存储过程的开头；‒声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块 中不可以使用它。
‒也可以在声明变量的同时指定默认值，否则默认为null。
例如：declareidintdefault10; 《MySQL数据库应用》/ 第15页 6.1.2存储过程体
（2）全局变量 MySQL中的全局变量不必声明即可使用，全局变量在整个存储过程中有效，全局变量名以字符“@”作为起始字符。
《MySQL数据库应用》/ 第16页 6.1.2存储过程体
2．为变量赋值要给局部变量赋值可以使用SET语句，或者使用SELECT…INTO语句。

（1）使用SET语句赋值语法格式：SET变量名=expr[,变量名=expr]... 例如，在存储过程中给变量赋值。
setnum=1；setstr1='hello',str2=‘word’;set@name='王林';用select直接赋值，分配符必须为“：=”，全局变量名以@开头。
例如：select@t2:=(@t2:=2)+5ast2; 《MySQL数据库应用》/ 第17页 6.1.2存储过程体
（2）使用SELECT...INTO语句赋值 使用这个SELECT…INTO语句可以把选定的列值直接存储到变量中。
这种方式的赋值只有单一的行可以被取回。
语法格式：SELECT列名[,...]INTO变量名[,...]table_expr 其中：‒table_expr是SELECT语句中的FROM子句及后面的部分。
《MySQL数据库应用》/ 第18页 6.1.2存储过程体 【例6.2】在存储过程体中，将xs表中的学号为“081101”的学生姓名和专业名的值分别赋给变量name和project。
语句如下： declarenamechar
(8);declareprojectchar(10); select姓名,专业名intoname,projectfromxs;where学号='081101'; 《MySQL数据库应用》/ 第19页 6.1.2存储过程体
3．流程控制语句 在执M行yS顺QL序中：，常见的过程式SQL语句可以用在一个存储过程体中。
例如：首先判断条件1是否为真，如果条件1为真，执行语句1； IF语句、CASE语句、WHILE语句、REPEA如T语果句条、件L1O为OP假语，句则。
执行判断条件2；
（1）IF语如句果条件2为真，则执行语句2；如果条件2为假，则执行语句
3。
If语句包含多个条件判断，根据判断结果为true或false执行相应的 语句，当条件为true时就执行sql语句。
语法格式： IF条件1THEN语句1[ELSEIF条件2THEN语句2]...[ELSE语句3]ENDIF 《MySQL数据库应用》/ 第20页 6.1.2存储过程体 【例6.3】创建xscj数据库的存储过程，判断两个输入的参数哪一个更大。
delimiter$$createpar (ink1integer,ink2integer,outk3char
(6))begin ifk1>k2thensetk3='大于'; elseifk1=k2thensetk3='等于'; elsesetk3='小于'; endif;End$$Delimiter;par(2,
3,@k3); 《MySQL数据库应用》/ 第21页 6.1.2存储过程体 案例：创建一个存储过程，输入学生的学号，如果学生的性别为男，输 出“你是一位男生！”，如果学生的性别为女，输出“你是一位女生！” 语句如下： delimiter$$ createproceduresex_xs(inxhchar
(6),outshuchuchar(20)) begin declarethe_sexinteger; select性别intothe_sexfromxswhere学号=xh; ifthe_sex=1thensetshuchu='你是一位男生！'; elseifthe_sex=0thensetshuchu=‘你是一位女生！'; endif; end$$ delimiter; callsex_xs(‘081102’,@shuchu); 《MySQL数据库应用》/ 第22页 6.1.2存储过程体
（2）CASE语句 语法格式：CASE条件表达式 WHEN值1THEN语句1[WHEN值2THEN语句2]...[ELSE语句3]ENDCASE或者：CASEWHEN条件1THEN语句1[WHEN条件2THEN语句2]...[ELSE语句3]ENDCASE 《MySQL数据库应用》/ 第23页 6.1.2存储过程体 【例6.4】创建一个存储过程，针对参数的不同，返回不同的结果。
方法一：delimiter$$createprocedureresult (instrvarchar
(4),outbegincasestr when'm'thensetsex='男';when'f'thensetsex='女';elsesetsex='无';endcase;end$$delimiter; sexvarchar
(4)) 《MySQL数据库应用》/ 第24页 6.1.2存储过程体 【例6.4】创建一个存储过程，针对参数的不同，返回不同的结果。
方法二： delimiter$$createprocedureresult (instrvarchar
(4),outsexvarchar
(4))begincase whenstr='m'thensetsex='男';whenstr='f'thensetsex='女';elsesetsex='无';endcase;end$$delimiter; 《MySQL数据库应用》/ 第25页 6.1.2存储过程体 【例6.4】创建一个存储过程，针对参数的不同，返回不同的结果。
方法三： delimiter$$createprocedureresult (instrvarchar
(4),outsexvarchar
(4))begin ifstr='m'thensetsex='男';elseifstr='f'thensetsex='女';elsesetsex='无';endif;end$$delimiter; 《MySQL数据库应用》/ 第26页 6.1.2存储过程体
（3）循环语句在存储过程中可以定义0个、1个或多个循环语句。
MySQL支持3条循环语句：WHILE、REPEAT和LOOP语句。
①WHILE语句while语句是带条件判断的循环。
首先判断条件是否为真，为真则执行对应的语句，然后再次进行判 断，为真则继续循环，不为真则结束循环。
语法格式为： WHILE条件DO语句 ENDWHILE 《MySQL数据库应用》/ 第27页 6.1.2存储过程体 【例6.5】创建一个带WHILE循环的存储过程。
delimiter$$createproceduremydowhile()begin declarev1intdefault5;whilev1>0do setv1=v1-1;endwhile;end$$delimiter; 《MySQL数据库应用》/ 第28页 6.1.2存储过程体 ②REPEAT语句REPEAT语句也是带条件判断的循环。
但是与while不同，REPEAT语句首先执行指定的语句，然后判断条件 是否为真，若不为真则继续循环；直到为真则停止循环。
语法格式如下： REPEAT语句 UNTIL条件ENDREPEAT 《MySQL数据库应用》/ 第29页 6.1.2存储过程体 【例6.5】创建一个带WHILE循环的存储过程。
delimiter$$createproceduremydowhile()begindeclarev1intdefault5;repeat v1=v1-1;untilv1<1;endrepeat;end$$delimiter; 《MySQL数据库应用》/ 第30页 6.1.2存储过程体 ③LOOP语句LOOP循环语句是用来重复执行某些语句，实现一个简单的循环构造， 但并不进行条件判断。
语法格式如下：[Begin_label]:LOOP 语句ENDLOOP[end_label] 说明：LOOP内的语句一直重复执行至循环被退出，退出时通常伴随着一个LEAVE子句,结构：LEAVElabelLEAVE语句中的label是语句中标注的名字；LEAVE语句经常和BEGIN...END或循环一起使用。
《MySQL数据库应用》/ 第31页 6.1.2存储过程体 【例6.6】创建一个带LOOP语句的存储过程。
delimiter$$createproceduremydoloop()begin set@a=10;label:loop set@a=@a-1;if@a<0thenleaveendif;endlooplabel;end$$delimiter; label; 《MySQL数据库应用》/ 第32页 6.1.2存储过程体 案例：使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程。
delimiter$$createproceduremydoloop()begin declareidintdefault0;label:loop setid=id+1;ifid>=10thenleaveendif;endlooplabel;end$$delimiter; label; 《MySQL数据库应用》/ 第33页 6.1.2存储过程体 另外，循环语句中还有一个iterate语句，意为“再次循环”,iterate语句将执行顺序转到语句段开头处。
它只可以出现在LOOP、REPEAT和WHILE语句内。
语法格式为：ITERATElabel 《MySQL数据库应用》/ 第34页 6.1.2存储过程体 案例：iterate语句示例。
delimiter$$createproceduremydoloop1()begin declarep1intdefault0;label:loop setp1=p1+1;ifp1<10theniteratelabel;elseifp1>20thenleavelabel;endif;endlooplabel;end$$delimiter; 《MySQL数据库应用》/ 第35页 6.1.2存储过程体
4．处理程序和条件 在存储过程中处理SQL语句可能导致一条错误消息。
例如，向一个表中插入新的行而主键值已经存在，这条INSERT语句会导致一个出错消息，并且MySQL立即停止对存储过程的处理。
为了防止MySQL在一条错误消息产生时就停止处理，需要使用到DECLAREHANDLER语句。
DECLAREHANDLER语句为错误代码声明了一个所谓的处理程序，它指明：对一条SQL语句的处理如果导致一条错误消息，将会发生什么。
declarehandler语法格式为： DECLARE处理程序的类型HANDLERFORcondition_value[,...]存储过程语句 《MySQL数据库应用》/ 第36页 6.1.2存储过程体
（1）处理程序的类型CONTINUE：MySQL不中断存储过程的处理。
EXIT：当前BEGIN...END复合语句的执行被终止。
UNDO：处理程序类型语句暂时还不被支持。
（2）condition_value表示错误类型 condition_value=：SQLSTATE[VALUE]sqlstate_value |condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code 《MySQL数据库应用》/ 第37页 6.1.2存储过程体
（3）存储过程语句存储过程语句是处理程序激活时将要执行的动作。
【例6.7】创建一个存储过程，向xs表插入一行数据（'081101','王 民','计算机',
1,'1994-02-10',50,NULL,NULL），已知学号 081101在XS表中已存在。
如果出现错误，程序继续进行。
delimiter$$ createproceduremy_insert() begin declarecontinuehandlerforsqlstate'23000'set@x2=1; set@x=2; insertintoxsvalues('081101','王民','计算机',
1,'1994-02-10',50, null,null); set@x=3; end$$ delimiter; callmy_insert(); select@x; 《MySQL数据库应用》/ 第38页 6.1.4存储过程的调用、删除和修改
1．存储过程的调用存储过程创建完后，可以在程序、触发器或者其他存储过程中被调用。
语法格式： CALL存储过程名([参数...]) 【例6.10】创建存储过程，实现查询xs表中学生人数的功能，该存储过程不带参数。
createproceduredo_query()selectcount(*)fromxsorderby学号; calldo_query(); 《MySQL数据库应用》/ 第39页 6.1.4存储过程的调用、删除和修改 【例6.11】创建xscj数据库的存储过程，判断两个输入的参数哪一个更大。
调用该存储过程。
delimiter$$createprocedurepar (ink1integer,ink2integer,outk3char
(6))begin ifk1>k2thensetk3='大于'; elseifk1=k2thensetk3='等于'; elsesetk3='小于'; endif;end$$delimiter;par(3,
6,@k);select@k; 《MySQL数据库应用》/ 第40页 6.1.4存储过程的调用、删除和修改 【例6.12】创建一个存储过程，有两个输入参数：xh和kcm，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。
delimiter$$createprocedurexscj.do_update(inxhchar
(6),inkcmchar(16))begin declarekchchar
(3);declarexftinyint;declarecjtinyint;select课程号,学分intokch,xffromkcwhere课程名=kcm;select成绩intocjfromxs_kcwhere学号=xhand课程号=kch;ifcj<60then updatexs_kcset学分=0where学号=xhand课程号=kch;else updatexs_kcset学分=xfwhere学号=xhand课程号=kch;endif;end$$delimiter;insertintoxs_kcvalues('081101','208',50,null);calldo_update('081101','数据结构');select*fromxs_kcwhere学号='081101'and课程号='208'; 《MySQL数据库应用》/ 第41页 6.1.4存储过程的调用、删除和修改 【例6.13】创建一个存储过程do_insert1，作用是向xs表中插入一行数据。
再创建另外一个存储过程do_insert2，在其中调用第一个存储过程，并根据条件处理该行数据。
创建第一个存储过程：createproceduredo_insert1()insertintoxsvalues('091101','陶伟','软件工程',
1,'199403-05',50,null,null); 《MySQL数据库应用》/ 第42页 6.1.4存储过程的调用、删除和修改 创建第二个存储过程：delimiter$$createproceduredo_insert2(inxbit
(1),outstrbegin calldo_insert1();ifx=0then updatexsset姓名='刘英',性别=0wheresetstr='修改成功';elseifx=1thendeletefromxswhere学号='091101';setstr='删除成功';endif;end$$delimiter;calldo_insert2(
1,@str);select@str;calldo_insert2(
0,@str);select@str; char
(8))学号='091101'; 《MySQL数据库应用》/ 第43页 6.1.4存储过程的调用、删除和修改
2．存储过程的删除存储过程创建后需要删除时使用DROPPROCEDURE语句。
在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。
语法格式为： DROPPROCEDURE[IFEXISTS]存储过程名 例如，删除存储过程dowhile。
命令如下：dropprocedureifexistsdowhile; 《MySQL数据库应用》/ 第44页 6.1.4存储过程的调用、删除和修改
3．存储过程的修改使用ALTERPROCEDURE语句可以修改存储过程的某些特征。
语法格式： ALTERPROCEDURE存储过程名[特征...] 特征=：{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string' 《MySQL数据库应用》/ 第45页 6.1.4存储过程的调用、删除和修改 案例：修改存储过程countproc的定义，将读写权限改为MODIFIESSQLDATA，并指明调用者可以执行。
alterprocedurecountprocmodifiessqldatasqlsecurityinvoker; 《MySQL数据库应用》/ 第46页 6.1.4存储过程的调用、删除和修改 【例6.14】使用先删除后修改的方法修改存储过程。
delimiter$$dropprocedureifexistsdo_query; createproceduredo_query()begin select*fromxs;end$$delimiter; calldo_query(); 《MySQL数据库应用》/ 第47页 练习
1、创建一个不带参数的存储过程count_xs，用于统计xs表中男生的总人数；
2、创建一个存储过程，有两个输入参数：xh和kch，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。
《MySQL数据库应用》/ 第48页 练习
1、创建一个不带参数的存储过程count_xs，用于统计xs表中男生的总人数； delimiter$$createprocedurecount_xs()begin selectcount(*)fromxswhere性别=1;end$$delimiter; callcount_xs();selectcount(*)fromxswhere性别=1; 《MySQL数据库应用》/ 第49页 练习
2、创建一个存储过程，有两个输入参数：xh和kch，要 求当某学生某门课程的成绩小于60分时将其学分修改为零， 大于等于60分时将学分修改为此课程的学分。
delimiter
$$createproceduremy_update(inxhchar
(6),inkchchar
(3))begin declarexftinyint;declarecjtinyint;select学分intoxffromkcwhere课程号=kch;select成绩intocjfromxs_kcwhere学号=xhand课程号=kch;ifcj<60then updatexs_kcset学分=0where学号=xhand课程号=kch;else updatexs_kcset学分=xfwhere学号=xhand课程号=kch;endif;end$$delimiter; insertintoxs_kcvalues('081101',’208’,80,null);callmy_update('081101',‘208’);select*fromxs_kcwhere学号='081101'and课程号=‘208'; 《MySQL数据库应用》/ 第50页