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

1、授课主题授课主题 第第13讲讲 计划学计划学 时时 2 第五章第五章 数据库完整性数据库完整性(2) 教学目的教学目的 和要求和要求 1、理解触发器的应用、理解触发器的应用 2、掌握存储过程的定义、创建和调用、掌握存储过程的定义、创建和调用 教学重点教学重点 和难点和难点 存储过程的创建和调用存储过程的创建和调用 教学内容教学内容 1、修改和删除触发器、修改和删除触发器 2、使用触发器的考虑、使用触发器的考虑 3、存储过程的概念、存储过程的概念 4、常用的系统存储过程、常用的系统存储过程 5、创建存储过程、创建存储过程 6、调用存储过程、调用存储过程 教学过程教学过程见课件见课件 实例1 ：为

2、STUDENT表创建触发器S_delete，当一次删除的记录大 于一行时，取消删除操作。 CREATE trigger s_delete on student for delete as declare a int select a=rowcount if(a1) begin rollback transaction raiserror(当前删除的记录数为%d,一次值允许删除一行记录！ ,16,1,a) end 格式： RAISERROR (msg_str ,severity ,state ,argument ,.n ) 功能：返回用户定义的错误信息并设系统标志。 用户定义的与该消息关 联的严

3、重级别。可以指 定 0 到 18 之间的严重 级别。 表示错误的状态。1至 127之间的值。 用于代替 msg_str 消息中的定义的变 量的参数 用户定义的特 定信息，最长 255个字符。 方法二： create trigger df1 on work_table for delete as declare zno char(3),zname char(10) select zno=职工号,zname=职工姓名 from deleted begin insert into free_table (职工号,姓名) values(zno,zname) end 思考：在work_table表中删除

4、一个职工，就要触发free_table 表中增加一条记录。命令如何写？ 方法一：create trigger df on work_table for delete as begin insert into free_table(职工号,姓名) (select 职工号,姓名 from deleted ) end 6. 6. 删除和修改触发器删除和修改触发器 删除触发表时，触发器被随之删除。也可以用DROP 语句删除定义的触发器。 语句格式： DROP TRIGGER OWNER. 触发器名 ，OWNER. 触发器名 可以使用ALTER TRIGGER语句修改触发器定义。修改 触发器与定义一个触

5、发器类似。 7. 7. 使用触发器的考虑使用触发器的考虑 对于每个数据操作语句，无论它影响一行还是多行，对同 一个触发器都只能激活一次。 触发器最大的用途是维护数据完整性，而不是返回结果。 触发器在操作发生之后执行，约束在操作发生之前起作用。 如果在触发器表上有约束，那么这些约束在触发器执行之前 进行检查。如果触发器操作与约束有冲突，该触发器不执行。 只是在必要的时候使用触发器。如果使用约束，规则， 默认就可以实现预定的数据完整性约束时，应优先考虑 使用这3种措施。下表说明它们的性能。 数据完整性方法功能开支 操作之前或之后 约束 默认和规则 触发器 中 低 高 低 高 中 之前 之前 之后

6、实例2：为STUDENT表创建触发器S_insert，当向STUDENT表 中插入数据时，要求学号必须以 “b”开头，且系名必须在dept 表中存在，否则取消插入操作。 CREATE trigger s_insert on student for insert as declare sno char(4),sdept char(20) select sno=学号,sdept=系别 from inserted if(left(sno,1)!=b) begin rollback transaction end if(sdept not in(select 系名 from dept) begin r

7、ollback transaction end RAISERROR(输入的学号：%s不是以b开头，请确认后重新录入！,16,1, Sno) RAISERROR(输入的系名：%s在dept表中不存在，请确认后重新录入！,16,1, Sdept) 学号以”08”或者”09”开头? (LEFT(Sno,2)=08 or LEFT(Sno,2)=09 )not 补充：补充：5.6 5.6 存储过程存储过程 1 1、存储过程的概念、存储过程的概念 2 2、常用的系统存储过程、常用的系统存储过程 3 3、创建存储过程、创建存储过程 4 4、调用存储过程、调用存储过程 1 1、存储过程的概念、存储过程的概念

8、 存储过程是一组为了完成特定功能的SQL语句集，经编译 后存储在数据库中。用户通过指定存储过程的名字并给出 参数（如果该存储过程带有参数）来执行它。 存储过程用来执行管理任务或应用复杂的业务关系。 存储过程可以带参数，也可以返回结果。 存储过程类似C 语言中的函数 int sum(int a,int b) int s; s=a+b; return s; （1）存储过程的优点：）存储过程的优点： 存储过程只在创造时进行编译（编译好的存储过程存储在过程 高速缓存中），以后每次执行存储过程都不需再重新编译，而一 般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据 库执行速度。 当对数据库进

9、行复杂操作时(如对多个表进行Update，Insert， Query，Delete时），可将此复杂操作用存储过程封装起来与数据 库提供的事务处理结合一起使用。 存储过程可以重复使用，可减少数据库开发人员的工作量。 安全性高，可设定只有某此用户才具有对指定存储过程的使用 权。 （2）存储过程的分类：）存储过程的分类： 系统提供的存储过程系统提供的存储过程：系统过程主要存储在master数据库中 ，并以sp_为前缀，并且系统存储过程主要是从系统表中获 取信息，从而为系统管理员管理SQL server提供支持。 用户自定义的存储过程用户自定义的存储过程：由用户创建并能完成某一特定功 能(如查询用户所

10、需数据信息)的存储过程。 （3）常见的系统存储过程：）常见的系统存储过程： 系统存储过程说明 sp_databases列出服务器上的所有数据库 sp_helpdb报告有关指定或所有数据库的信息 sp_renamedb更改数据库的名称 sp_tables返回当前环境下可查询的对象列表 sp_columns返回某个表列的信息 sp_help查看某个表的所有信息 sp_helpconstraint查看某个表的约束 sp_helpindex查看某个表的索引 sp_stored_procedures列出当前环境下的所有存储过程 sp_password添加或修改登录账户的密码 例： use db_stud

11、ent go execute sp_databases -列出当前系统中的数据库 exec sp_columns student -返回student表中列的信息 exec sp_help student -返回student表的信息 exec sp_helpconstraint student -查看student表中的约束 调用存储 过程 2 2、创建、创建用户自定义用户自定义存储过程存储过程 建立存储过程的语句如下：建立存储过程的语句如下： CREATE PROCEDURE过程名（参数表） withrecompile|encyption|recompile,encyption AS 说明

12、： recompile所创建的存储过程不在高速缓冲区中保存 ，每次执行前需要重新编译。 Encyption对存储在系统syscomments表中的存储过程 定义文本进行加密，避免他人查看或修改。 例：创建一个不带参数的存储过程，查询本次考试的平均分， 若平均成绩大于85分，打印优秀，小于85分打印较差。并显示 未通过考试的学生名单。 create procedure proc_student as declare avg float(4) select avg=avg(成绩) from sc print 本次考试的平均成绩为：+convert(varchar(5),avg) if(avg=85

13、) print 本次考试成绩：优秀 else print 本次考试成绩：较差 print print 参加本次考试未通过的学生名单 select student.学号,姓名,课程代号,成绩 from student,sc where student.学号=sc.学号 and 成绩60 3 3、执行存储过程、执行存储过程 格式：EXECUTE 过程名参数 例：EXEC proc_student 存储过程的参数分为两种: 输入参数：用于向存储过程传入值。 输出参数：用于在调用存储过程后返回结果。 c=sum(5,8) int sum(int a,int b) int s; s=a+b; retur

14、n s; 带输入参数的存储过程带输入参数的存储过程 例：显示出未通过考试的学生名单。由于每次考试的难易程度 不同，每次考试的及格线也随时变化（不再是60分），这导致 考试的评判成绩也随时发生变化。 分析：在存储过程中添加一个输入参数jigexian create procedure proc_student2(jigexian int) as print 本次考试的及格线为：+convert(varchar(5),jigexian) print 参加本次考试未通过的学生名单 select student.学号,姓名,课程代号,成绩 from student,sc where student.学

15、 号=sc.学号 and 成绩jigexian 调用：exec proc_student2 55 或者 exec proc_student2 jigexian=55 带输出参数的存储过程带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，这时就 需要使用输出（output）参数了。 例：修改上例题，返回未通过考试的学生名单及人数。 create procedure proc_student3(jigexian int,notpass int output) As print 参加本次考试未通过的学生名单 select student.学号,姓名,课程代号,成绩 from stude

16、nt,sc where student.学 号=sc.学号 and 成绩jigexian print 参加本次考试未通过的学生人数 select notpass=count(学号) from sc where 成绩=3 print 未通过人数+convert(varchar(5),sum)+人，超过60%，及格线还应往下调 else print 未通过人数+convert(varchar(5),sum)+人，及格线适中 4 4、更改和删除存储过程、更改和删除存储过程 更改存储过程：ALTER PROCEDURE SQL Server中更改存储过程，就是用新定义的存储过 程替换原来的定义。语句格

17、式： ALTER PROCEDURE 同定义 删除存储过程：DROP PROCEDURE 从当前数据库删除用户定义的存储过程。语句格式： DROP PROCEDURE 本章小结 1、掌握三种完整性实现的机制，包括完整性约束定义机制， 完整性检测机制和违背完整性约束条件时RDBMS应采取的 动作。 2、学会通过默认对象和规则对象来保证数据库的完整性。 3、触发器的使用。重点掌握insert,update,delete这三种触发 器，以及使用这三种触发器时存在的两张临时表:inserted和 deleted。 4、学会使用用户自定义的存储过程，特别是带输入和输出参 数的存储过程的定义和调用。 1、

18、在数据库系统中，通常用三级模式来描述数据库，其中_是用户与数据库的 接口，是应用程序可见到的数据描述，_是对数据整体的_的描述，而 _描述了数据的_。 A、外模式 B、概念模式 C、内模式 D、逻辑结构 E、层次结构 F、物理结构 A B D C F 2、有关系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) A B C 1 2 2 1 3 3 DA 1 2 3 4 2 NULL 3 1 R S C 3、为某百货公司设计一个E-R模型。百货公司管辖若干连锁商店，每家商店经营若 干商品，每家商店有若干职工，但每个职工只能服务于一家商店。 实体类型“商店”的属性有：店号、店名、店址、店经理 实体类型“商品”的属性有：商品号、品名、单价、产地 实体类型“职工”的属性有：工号、姓名、性别、工资 在联系中反映出职工参加某商店工作的开始时间、商店销售商品的月销售量。 试画出反映商店、商品、职工实体类型及其联接类型的E-R图、并将其转换为关系 模式集。 商店职工 商品 隶 属 经 营 店 名 店 址 店经 理 店 号 月