MYSQL触发器和存储过程.pdf

MySQ工E.XXkdi管坐k处，请联系本人改正。本早主要内谷 4.1存储过程和函数米4.1.1创建与修改存储过程或函数4.1.2删除存储过程或函数米4.1.3查看存储过程或者函数米4.1.4使用变量米4.1.5定义条件和处理米4.1.6光标的使用米4.1.7流程控制语句4.2触发器*4.2.1创建触发器*4.2.2删除与查看触发器米4.2.3触发器实现CHECK、级联第1页 数据库技术与应用第四章MYSQL触发器与存储过程存储程序和函数是事先经过编译并存储在数据库中的一套SQL语句。创建后，客户端不需要再重新发布单独的语句，而是可以引用存储程序来替。存储程序可以提供改良后的性能,因为只有较少的信息需要在服务器和客户算之间传送，代价是 增加数据库服务器系统的负荷。存储程序也允许你在数据库服 务器上有函数库，从而提高了开发人员的开发效率。以下情况存储程序尤其有用：米当用不同语言编写多客户应用程序，或多客户应用程序在不同平台 上运行且需要执行相同的数据库操作之时。米安全极为重要之时。比如，银行对所有普通操作使用存储程序。程 序可以确保每一个操作都被妥善记入日志。应用程序和用户不可能 直接访问数据库表，但是仅可以执行指定的存储程序。数据库技术与应用第2页第四章MYSQL触发器与存储过程MySQL4J创逢写修改春储过程或函数：存储过程示例1 一返回单个数据mysql de limite r$mysql CREATE PROCEDURE qq_c ount(OUT num INT)-BEGIN-SELECT COUNT(*)INTO num FROM qq;-END;-$mysql de limite r;mysql CALL qq_c ount(a);mysql SELECT a;mysql select Pa；-1 row in set:存储过程示例2返回数据集第3页CREATE PROCEDURE ge tre c ordO BEGINSELECT*FROM qq;END;n;sql call getrecordO；i qqno!tid!email i：4567897971 !79798792!65789789731 !79798794!564987795i i!558774556S i+-4卜-+-+6 rows in set 数据库技术与应用第四章MYSQL触发器与存储过程MySQL4J创建写修改存储过程或函数:存储函数的创建示例mysql de limite r$mysql CREATE FUNCTION id_e mail(id int)RETURNS varc h ar(20)Reads SQL data-mysql mysql mysqlBEGINde c lare email varc h ar(20);SELECT email INTO email FROM qq wh ere tid=id;RETURN email;END;$de limite r;CALL id e mail(1);SELECT id_e mail(l);mysql select id_email；+-+i id_enail+-+I inysql select;*from qq；i qqno!tid!email i!4567897971!79798792!6578978973!zzhstudio(?!79798794：!564987795 i zzh(?163.con)i!558774556 S 55877455P+-+-+|说明：函数的创建需要指定返回值类型；同时应 当在定义体中指明返回的结果。调用函数不能使 涌CALL语句，函数可直接使用。1 row in set select*from qq_t；!tid!qqname!email23432435!declare mail varchar(25);set id=l;select tid,qqname,email into id,name,mail from qq_t where tid=id;select id,name,mail;end$DELIMITER;卜-+-+!1 天使 zzhstudio(? h-+-+-+;第15页数据薛技术国应用*第四章MYSQL触发器与存储过程MySQL定义条件和处理程序能够事先定义程序执行过程中有可能遇 到的问题，并采用一定的机制解决相关问题。*基本步骤*1 DECLARE条件的定义DECLARE condition_name CONDITION FOR condition_value c ondition_name：条件的名称 c ondition_value：可 以取sqlstate _value或mysql_e rror_c ode,都 表示MYSQL的错误代码。例如：下面插入重复主键值的错误代码为ERROR 1062(23000),则sqlstate _value=23000,mysql_error_c ode=1062n insert into qqCqqno,t id ualues,705646790f,1；ERROR 1062 23000）：Duplicate entry T for key PRIMARY第16页 数据座技术局应用 第四章MYSQL触发器与存储过程MySQLJA 4.1.5蔻义案件新处理2条件的处理DECLARE h andler_type HANDLER FOR c ondition_value,.sp_statement h andler_type:CONTINUE|EXIT|UNDO（未支持）c ondition_value:sqlstate_value|c ondition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_c odeSQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的 SQLSTATE代码的速记。第17页数据彝技术局应用第四章MYSQL触发器与存储过程MuSQL4.1.5定义条件知处理一不例mysql delimiter$mysql CREATE PROCEDURE qq_condition()-BEGIN-DECLARE CONTINUE HANDLER FOR SQLSTATE*23000*SET x2=1;-SET x=1;-INSERT INTO qq(qqno,tid)VALUES(687475394)；-SET x=2;-INSERT INTO qq(qqno,tid)VALUES(705646790,1-SET x=3;-END;-$iysql insert into qqCqqno,tid ualues,705646790J,1；:RROR 1062(23000)：Duplicate entry For key PRIMARYmysqlcall qq_condit ion；Query OK,0 rows affected,1 warning mysql select(?x,Px2；!(?x!0 x2!3：1!+-+-+1 row in set 将上面存储过程句柄改为exit。DECLARE EXIT HANDLER FOR SQLSTATE,23000*SET x2=1;mysql call qq_condit ion_exit ；Query OK.0 rows affected,1 warning nysql select Px,0 x2；!(?x!0 x2!+-+-+!2:1：+-+-+1 row in set 第18页数据彝技本局应用*第四章MYSQL触发器与存储过程V 文档也供_4.1.5 定岁 壬 ip:其他条件形式n/sql insert into qq ualuesC1 68747539J,1,7056467901,1；ERROR 1062 423000)：Duplicate entry 廿 for key PRIMARY捕 mysql_error_codeDECLARE CONTINUE HANDLER FOR 1062 SET x2=1;事先定义 conditi(m_iiameDECLARE DuplicateKey CONDITION FOR SQLSTATE 23000,；DECLARE CONTINUE HANDLER FOR DuplicateKey SET x2=1;捕获 SQLEXCEPTIONDECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x2=1;第19页数据彝技本局应用*第四章MYSQL触发器与存储过程4MySQL文档gg赞，口髭鬻学I请.1人改正。利用基于变量的select into语句，仅能处理一条记录的数 据。通过光标（或游标），能够对查询的结果集进行循环处理。:基本步骤米 L 声明光标：DECLARE c ursor_name CURSOR FOR selec t_stateme nt米2.打开光标：OPEN c ursor_name奈3使用光标：FETCH c ursor_name INTO var_name,var_name.奈4.关闭光标：CLOSE c ursor_name说明：1、光标必须在声明处理程序之前被声明，并且声明变量和条件之后。2、SELECT语句中不能有INTO子句。select*from qq；Empty set DECLARE d varchar(25);mysql select*from qq_t；-#-+DECLARE curl CURSOR FOR SELECT qqno9tid9qqu!qqno!tid!qqname!emailDECLARE exit HANDLER FOR NOT FOUND CLOS+-+!23432435!9898080!+1 天使 i 2!钟老师；zzh(?!OPEN curl;12 rows in set REPEATFETCH curl INTO a,b,c,d;mysql call qq_cursor_insert；Query OK,0 rows affected,1 warning insert into qq(qqno9tid9qqname9email)values(a,b,c,dmysql select*from qq；+-UNTIL 0 END REPEAT;qqnoi tid!qqname i email-CLOSE curl;!23432435!1!天使!zzhstudio(?!9898080：2!钟老师；!END12 rows in set 第21页数据彝技术与应用第四章MYSQL触发器与存储过程n 4.L 7;流程控制语句 IF语句CASE 语句:.LOOP语句,参看教材P316：LEAVE 语句:ITERATE 语句：REPEAT 语句：WHILE语句 delete)的特殊过程。利用触发器能够 有效地保证数据完整性，也便于执行一些自动的数据操作。4.2.1创建触发器 4.2.2触发器实现级联、CHECK 4.2.3删除与查看触发器第23页数据彝技术与应用第四章MYSQL触发器与存储过程4改正。MySQL:触发器创建的基本语法：CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事 件 ON表名FOR EACH ROW触发器语句*触发器只能建立在基本表上，不能建立在临时表或视图上。米触发事件包括：INSERT、UPDATE DELETE米FOR EACH ROW行级触发器，MYSQL目前还没有支持语句级的触发器。*一个表上不能有两个相同时间和事件的触发程序。米MYSQL中，触发器执行的顺序是BEFORE触发器、表操作（INSERT、：UPDATE 和 DELETE）AFTER触发器。第24页 数据座技术与应用*第四章MYSQL触发器与存储过程文鳌盖赞d口无璜之热不踩4人改正。4-2.1创建触发器使用OLD（旧值）和NEW（新值）关键字,能够访问受触发程序影响的行中 的字段值（OLD和NEW不区分大小写）。在INSERT触发程序中，仅能使用NEW.c ol_name,没有旧值。在DELETE触发程序中，仅能使用OLD.c ol_name,没有新值。在UPDATE触发程序中，可以使用OLD.c ol_name来引用更新前的旧值，也能使用NEW.c ol_name来引用更新后的行中的新值。OLD列是只读的，可以引用它，但不能更改它。NEW列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如 果具有UPDATE权限，可使用“SET NEW.c ol_name=value”更改它的 值。意味着可以使用触发程序来更改将要转入到新行中的值，或用于 更新行的值。第25页 数据座技术与应用*第四章MYSQL触发器与存储过程MySQL罩暖器频级联利用触发器实现学生与成绩表学生编号上的级联更新。create trigger stud_update beginafter inysql select*from stud；+-+-!snosname!ssex!sage!sdepdeclare newsnodeclare oldsnochar(10);char(10);+-+-+!2009010101!矍三!M!2009010102；季四!Mrows in set set newsno=new.sno;set oldsno=old.sno;if newsnoOoldsno thenupdate scsc set sno=newsno wl End if;nysql select*ror scsc；+sno1eno1grade20090101011 110011 17020090101021 110021 185i+b rows in set END;20;21!iysqlupdate stud set sno=2009010103 where sname=张二 luery OK,1 row affected：ows matched：1 Changed：1 Warnings：0；素!iysql select*f i*on scsc；sno1eno1 1grade20090101031 110011 17020090101021 110021 185*2 rows in set 第26页数据彝技术与应用第四章MYSQL触发器与存储过程My4 4.22爱器知殴联利用触发器实现学生与成绩表学生编号上的级联删除。create trigger stud_delete after delete on stud for each row begindeclare oldsno char(10);set oldsno=old.sno;delete from scsc where sno=oldsno;END;mysql select*fron scsc；i sno!eno!grade!2009010103!1001!70!2009010102!1002!85!2 rows in set mysql select*from stud；sno!sname ssex sage sdep!2009010102!李四：M!21!计料系；!2009010103!张三：M!20：计料素!2 rows in set mysql delete from stud where sno=,2009010103,；Query OK,1 row affected mysql select*from stud；!sno i snane i ssex!sage i sdep!+-+-+-+-+-+!2009010102!李四：M!21!计科系!U row in set Msql select*from scsc；sno eno!grade ii 2009010102!1002!85：U row in set 第27页 数据座技术与应用*第四章MYSQL触发器与存储过程MySQL4 2.2触爰器I颤Check功能利用触发器实现学生的年龄只能是1640岁，性别只能是M或F。create trigger student_check before insert on student for each rowbeginDECLARE ssex CHAR(5);declare sage smallint;set sage=new.sage;set ssex=new.ssex;mysql select*from student；+-1sno1snane-!ssex1sagef-i sdep!120090801011mark!m1 120i a1 120090801021 1nary F1 120compute i1 1+-20090801031 1John!m-1 117i Rath i-3 rows in set if(sage15 and sage insert into student ualuesL2009080104nikem,15,compute)；ERROR 1146：Table*st.xxxf doesnt existnysql insert into student ualues；ERROR 1146：Table*st.xxxf doesnt existinsert into xxxx values(1);end if;end;悦丽由于而后dill海库币演卖顼加66面功能同前R巨求甬声盲死及药茶百 I没有实现。所以通过触发器，在进行相关操作时导致违规而不能完成来实现。第28页 3薮jj需技太每位算3 第四章MYSQL触发器与存储过程4.2.3:查看系统中所有的触发器SHOW GRIGGERS;:查看 M YSQL 系统数据库 information_sc h e ma 中的 TRIGGERS 表selec t*from trigge rs wh ere trigger_name=6stud_update5 G第29页数据彝技术与应用*第四章MYSQL触发器与存储过程:删除语法DROP TRIGGER schema_name.trigger_name说明：从MySQL 5.0.10之前的MySQL版本升级到5010或更高版本时（包括所有的MySQL 5.1版本），必须在升级之前舍弃所有的触发程序，并 在随后重新创建它们，否则，在升级之后DROP TRIGGER不工作。第30页数据彝技术与应用第四章MYSQL触发器与存储过程MySQL本章内容小结:4.1存储过程和函数米4.1.1创建与修改存储过程或函数4.1.2删除存储过程或函数米4.1.3查看存储过程或者函数米4.1.4使用变量米4.1.5定义条件和处理米4.1.6光标的使用米4.1.7流程控制语句4.2触发器*4.2.1创建触发器米4.2.2触发器实现级联、CHECK耗4.2.3删除与查看触发器数据座技术国应用*第31页第四章MYSQL触发器与存储过程