数据库系统概论13数据库完整性(2)

授课主题第13讲计划学时2第五章数据库完整性(2)教学目的和要求1、理解触发器的应用2、掌握存储过程的定义、创建和调用教学重点和难点存储过程的创建和调用教学内容1、修改和删除触发器2、使用触发器的考虑3、存储过程的概念4、常用的系统存储过程5、创建存储过程6、调用存储过程教学过程见课件实例1：为STUDENT表创建触发器S_delete，当一次删除的记录大于一行时，取消删除操作。CREATEtriggers_deleteonstudentfordeleteasdeclare@aintselect@a=@@rowcountif(@a1)beginrollbacktransactionraiserror('当前删除的记录数为%d,一次值允许删除一行记录！',16,1,@a)end格式：RAISERROR(msg_str,severity,state,argument[,...n]])功能：返回用户定义的错误信息并设系统标志。用户定义的与该消息关联的严重级别。可以指定0到18之间的严重级别。表示错误的状态。1至127之间的值。用于代替msg_str消息中的定义的变量的参数用户定义的特定信息，最长255个字符。方法二：createtriggerdf1onwork_tablefordeleteasdeclare@znochar(3),@znamechar(10)select@zno=职工号,@zname=职工姓名fromdeletedbegininsertintofree_table(职工号,姓名)values(@zno,@zname)end思考：在work_table表中删除一个职工，就要触发free_table表中增加一条记录。命令如何写？方法一：createtriggerdfonwork_tablefordeleteasbegininsertintofree_table(职工号,姓名)(select职工号,姓名fromdeleted)end6.删除和修改触发器删除触发表时，触发器被随之删除。也可以用DROP语句删除定义的触发器。语句格式：DROPTRIGGER[OWNER.]触发器名[，[OWNER.]触发器名]可以使用ALTERTRIGGER语句修改触发器定义。修改触发器与定义一个触发器类似。7.使用触发器的考虑•对于每个数据操作语句，无论它影响一行还是多行，对同一个触发器都只能激活一次。•触发器最大的用途是维护数据完整性，而不是返回结果。•触发器在操作发生之后执行，约束在操作发生之前起作用。如果在触发器表上有约束，那么这些约束在触发器执行之前进行检查。如果触发器操作与约束有冲突，该触发器不执行。•只是在必要的时候使用触发器。如果使用约束，规则，默认就可以实现预定的数据完整性约束时，应优先考虑使用这3种措施。下表说明它们的性能。数据完整性方法功能开支操作之前或之后约束默认和规则触发器中低高低高中之前之前之后实例2：为STUDENT表创建触发器S_insert，当向STUDENT表中插入数据时，要求学号必须以“b”开头，且系名必须在dept表中存在，否则取消插入操作。CREATEtriggers_insertonstudentforinsertasdeclare@snochar(4),@sdeptchar(20)select@sno=学号,@sdept=系别frominsertedif(left(@sno,1)!='b')beginrollbacktransactionendif(@sdeptnotin(select系名fromdept))beginrollbacktransactionendRAISERROR('输入的学号：%s不是以b开头，请确认后重新录入！',16,1,@Sno)RAISERROR('输入的系名：%s在dept表中不存在，请确认后重新录入！',16,1,@Sdept)学号以”08”或者”09”开头?(LEFT(Sno,2)='08'orLEFT(Sno,2)='09')not补充：5.6存储过程1、存储过程的概念2、常用的系统存储过程3、创建存储过程4、调用存储过程1、存储过程的概念•存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。•存储过程用来执行管理任务或应用复杂的业务关系。•存储过程可以带参数，也可以返回结果。存储过程类似C语言中的函数intsum(inta,intb){ints;s=a+b;returns;}（1）存储过程的优点：①存储过程只在创造时进行编译（编译好的存储过程存储在过程高速缓存中），以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。②当对数据库进行复杂操作时(如对多个表进行Update，Insert，Query，Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。③存储过程可以重复使用，可减少数据库开发人员的工作量。④安全性高，可设定只有某此用户才具有对指定存储过程的使用权。（2）存储过程的分类：系统提供的存储过程：系统过程主要存储在master数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLserver提供支持。用户自定义的存储过程：由用户创建并能完成某一特定功能(如查询用户所需数据信息)的存储过程。（3）常见的系统存储过程：系统存储过程说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境下的所有存储过程sp_password添加或修改登录账户的密码例：usedb_studentgoexecutesp_databases--列出当前系统中的数据库execsp_columnsstudent--返回student表中列的信息execsp_helpstudent--返回student表的信息execsp_helpconstraintstudent--查看student表中的约束调用存储过程2、创建用户自定义存储过程建立存储过程的语句如下：CREATEPROCEDURE〈过程名〉（参数表）[with{recompile|encyption|recompile,encyption}]ASSQL语句说明：recompile所创建的存储过程不在高速缓冲区中保存，每次执行前需要重新编译。Encyption对存储在系统syscomments表中的存储过程定义文本进行加密，避免他人查看或修改。例：创建一个不带参数的存储过程，查询本次考试的平均分，若平均成绩大于85分，打印优秀，小于85分打印较差。并显示未通过考试的学生名单。createprocedureproc_studentasdeclare@avgfloat(4)select@avg=avg(成绩)fromscprint'本次考试的平均成绩为：'+convert(varchar(5),@avg)if(@avg=85)print'本次考试成绩：优秀'elseprint'本次考试成绩：较差'print'………………………………'print'参加本次考试未通过的学生名单'selectstudent.学号,姓名,课程代号,成绩fromstudent,scwherestudent.学号=sc.学号and成绩603、执行存储过程格式：EXECUTE过程名[参数]例：EXECproc_student存储过程的参数分为两种:输入参数：用于向存储过程传入值。输出参数：用于在调用存储过程后返回结果。c=sum(5,8)intsum(inta,intb){ints;s=a+b;returns;}①带输入参数的存储过程例：显示出未通过考试的学生名单。由于每次考试的难易程度不同，每次考试的及格线也随时变化（不再是60分），这导致考试的评判成绩也随时发生变化。分析：在存储过程中添加一个输入参数@jigexiancreateprocedureproc_student2(@jigexianint)asprint'本次考试的及格线为：'+convert(varchar(5),@jigexian)print'参加本次考试未通过的学生名单'selectstudent.学号,姓名,课程代号,成绩fromstudent,scwherestudent.学号=sc.学号and成绩@jigexian调用：execproc_student255或者execproc_student2@jigexian=55②带输出参数的存储过程如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（output）参数了。例：修改上例题，返回未通过考试的学生名单及人数。createprocedureproc_student3(@jigexianint,@notpassintoutput)Asprint'参加本次考试未通过的学生名单'selectstudent.学号,姓名,课程代号,成绩fromstudent,scwherestudent.学号=sc.学号and成绩@jigexianprint'参加本次考试未通过的学生人数'select@notpass=count(学号)fromscwhere成绩@jigexianGO调用：declare@sumintexecuteproc_student355,@sumoutputSelect@sum调用时必须带output关键字，返回结果将存放在变量@sum中。if@sum=3print‘未通过人数'+convert(varchar(5),@sum)+'人，超过60%，及格线还应往下调'elseprint‘未通过人数'+convert(varchar(5),@sum)+'人，及格线适中'4、更改和删除存储过程•更改存储过程：ALTERPROCEDURESQLServer中更改存储过程，就是用新定义的存储过程替换原来的定义。语句格式：ALTERPROC[EDURE]存储过程名{同定义}•删除存储过程：DROPPROCEDURE从当前数据库删除用户定义的存储过程。语句格式：DROPPROC[EDURE]存储过程名本章小结1、掌握三种完整性实现的机制，包括完整性约束定义机制，完整性检测机制和违背完整性约束条件时RDBMS应采取的动作。2、学会通过默认对象和规则对象来保证数据库的完整性。3、触发器的使用。重点掌握insert,update,delete这三种触发器，以及使用这三种触发器时存在的两张临时表:inserted和deleted。4、学会使用用户自定义的存储过程，特别是带输入和输出参数的存储过程的定义和调用。1、在数据库系统中，通常用三级模式来描述数据库，其中_____是用户与数据库的接口，是应用程序可见到的数据描述，______是对数据整体的_______的描述，而_______描述了数据的_________。A、外模式B、概念模式C、内模式D、逻辑结构E、层次结构F、物理结构ABDCF2、有关系R(A,B,C)主码=A，S(D,A)主码=D，外码=A，参照于R的属性A。关系R和S的元组如图所示。指出关系S中违反关系完整性规则的元组是________。A、(1,2)B、(2,null)C、(3,3)D、(4,1)ABC122133DA12342NULL31RSC3、为某百货公司设计一个E-R模型。百货公司管辖若干连锁商店，每家商店经营若干商品，每家商店有若干职工，但每个职工只能服务于一家商店。实体类型“商店”的属性有：店号、店名、店址、店经理实体类型“商