第11章 视图、存储过程和触发器的建立和使用

1、数据库系统原理数据库系统原理与应用技术与应用技术主讲：陈漫红主讲：陈漫红 北京联合大学师范学院北京联合大学师范学院 电气信息系电气信息系第第11章视图、存储过程和触发器的建章视图、存储过程和触发器的建立和使用立和使用 n11.1视图视图 n11.2 存储过程存储过程 n11.3 触发器触发器 n11.4 本章小结本章小结11.1 视图11.1.1 视图概念和作用和定义11.1.2 通过视图进行的查询、插入、修改、删除数据 11.1.3 视图的管理11.1.1 视图概念n视图可以被看成是虚拟表。n视图中的数据不物理地存储在数据库内。nSELECT 语句的结果集构成了视图的内容。基本表基本表1基本

2、表基本表2视图视图视图的作用n简化数据查询语句n使用户能从多角度看到同一数据n提高了数据的安全性n提供了一定程度的逻辑独立性11.1.1 视图的创建视图的创建创建视图时应该注意：（1）只能在当前数据库中创建视图，在视图中最多只能引用1024列，视图中记录的数目限制只由其基表中的记录数决定。（2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。（3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。（4）不能在视图上创建索引，不能在规则、默认、触发器的定义中引用视图。（5）当通过视图查询数据时，SQL Server要检查以确保

3、语句中涉及的所有数据库对象存在，每个数据库对象在语句的上下文中有效，而且数据修改语句不能违反数据完整性规则。（6）视图的名称必须遵循标识符的规则，且对每个用户必须是惟一的。利用利用SQL Server管理平台创建视图管理平台创建视图（1）在SQL Server管理平台中，展开指定的服务器，打开要创建视图的数据库文件夹，右击该数据库图标，从弹出的快捷菜单中依次选择“新建（New）视图”选项 。利用利用SQL Server管理平台创建视图管理平台创建视图选中“视图”节点单击右键，在弹出的快捷菜单中依次选择“新建视图” 。利用利用SQL Server管理平台创建视图管理平台创建视图（2）选择好创建视

4、图所需的表、视图、函数后，如选择student,sc,course表，通过单击字段左边的复选框选择需要的字段。 利用利用SQL Server管理平台创建视图管理平台创建视图n单击工具栏中的“保存”按钮，或者单击鼠标右键，从快捷菜单中选择保存选项保存视图，出现选择名称对话框，输入视图名，即可完成视图的创建。 .使用使用Transact-SQL语句创建视图语句创建视图n语法： CREATE VIEW （视图列名表）AS 查询语句 WITH CHECK OPTION 定义单源表视图n建立信息系学生的视图。CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage

5、FROM Student WHERE Sdept = 信息系 定义多源表视图n建立信息系选修了c01号课程的学生的视图。CREATE VIEW V_IS_S1(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 在已有视图上定义新视图n建立信息系选修了课程且成绩在90分以上的学生的视图。CREATE VIEW V_IS_S2ASSELECT V_IS_S1.Sno, V_IS_S1.

6、Sname, sc.Grade FROM V_IS_S1 join sc on V_IS_S1.sno=sc.sno WHERE sc.Grade = 90 定义带表达式的视图n定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2010-Sage FROM Student 含分组统计信息的视图n定义一个存放每个学生的学号及平均成绩的视图。CREATE VIEW S_G(Sno, AverageGrade)AS SELECT Sno, AVG(Grade) FROM SCGROUP BY Sno 对

7、视图进行加密对视图进行加密n创建一个 名为“v综合信息”的视图，用于查询学生的学号、姓名、专业名、课程名、成绩等信息。并对视图的定义进行加密。CREATE VIEW V综合信息WITH ENCRYPTION ASSELECT student.sno,sname,sdept,cname,grade FROM student, sc , course WHERE student.sno = sc.sno and sc. cno= o使用视图模板创建视图使用视图模板创建视图 （1）在）在SQL Server管理平台中，选择管理平台中，选择“视图视图”菜单中的菜单中的“模板资源管理器模板资源管理器 ”

8、选项，如图选项，如图11-7所示。所示。使用视图模板创建视图使用视图模板创建视图（2）在出现的“模板资源管理器”选项中选择“视图”中的“创建视图”选项 。（3）按照模板提示输入视图名称，select语句后，执行此语句，即可创建视图。 11.1.2 通过视图进行的查询、插入、通过视图进行的查询、插入、修改、删除数据修改、删除数据 1.查询视图数据利用select语句或SQL Server管理平台可以查看视图的输出数据。在SQL Server管理平台中，右击某个视图的名称，从弹出的快捷菜单中选择“打开视图”选项，就会显示该视图的输出数据。还可以用SELECT查询命令查看视图数据。 11.1.2 通

9、过视图进行的查询、插入、通过视图进行的查询、插入、修改、删除数据修改、删除数据2.插入视图数据 可使用INSERT插入语句向视图中插入数据。例如向信息系的视图插入数据。INSERT INTO IS_ student VALUES（090051,李力,22,信息系）再执行：SELECT * FROM IS_ student 例如例如:首先创建一个包含限制条件的视图首先创建一个包含限制条件的视图v_student2，限制条，限制条件为年龄件为年龄20，然后插入了一条不满足限制条件的记录，再，然后插入了一条不满足限制条件的记录，再用用SELECT语句检索视图和表。程序清单如下：语句检索视图和表。程序

10、清单如下：CREATE VIEW v_student2ASSELECT * FROM studentWHERE sage20GOINSERT INTO v_ student2VALUES（090052,王则, 女，10 ,信息系）GOSELECT * FROM student GOSELECT * FROM v_ student 2GO11.1.2 通过视图进行的查询、插入、通过视图进行的查询、插入、修改、删除数据修改、删除数据3.使用视图更新和删除数据 n使用视图更新记录，要注意的是，更新的只是数据库中的基表。n使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可

11、。 【例如】创建了一个基于表student的视图v_student2，然后通过该视图修改表student中的记录。 程序清单如下：UPDATE v_ student2SET name=张然WHERE name=张三SELECT * FROM student GOSELECT * FROM v_ student2GO【例11-7】利用视图v_ student2删除表student中姓名为“张三”的记录。 程序清单如下：DELETE FROM v_ student2WHERE name=张然 SELECT * FROM student GOSELECT * FROM v_ student2GO我们

12、看到视图v_ student2和表student的张然的记录都做了删除。11.1.2 通过视图进行的查询、插入、通过视图进行的查询、插入、修改、删除数据修改、删除数据n使用视图修改数据时，需要注意：（1）不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。（2）不能修改那些通过计算得到的字段。（3）如果在创建视图时指定了WITH CHECK OPTION选项，那么使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。（4）执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。（5）如果视图引用多个

13、表时，无法用DELETE命令删除数据，若使用UPDATE命令则应与INSERT操作一样，被更新的列必须属于同一个表。 11.1.3 视图的管理视图的管理1.重命名视图（1）在SQL Server管理平台中，选择要修改名称的视图，并右击该视图，从弹出的快捷菜单中选择“重命名”选项。或者在视图上再次单击，也可以修改视图的名称。接着该视图的名称变成可输入状态，可以直接输入新的视图名称。（2）使用系统存储过程sp_rename来修改视图的名称： sp_rename old_name,new_name11.1.3 视图的管理视图的管理（3）查看视图信息 n查看视图信息可以使用系统存储过程sp_help来

14、显示视图特征n使用sp_helptext来显示视图在系统表中的定义n使用sp_depends来显示该视图所依赖的对象。 11.1.3 视图的管理视图的管理（4）修改视图的结构 n在SQL Server管理平台中，右击要修改的视图，从弹出的快捷菜单中选择“修改”选项，出现视图修改对话框。n使用ALTER VIEW语句修改视图，但首先必须拥有使用视图的权限，语法形式如下： ALTER VIEW view_name （column,.n） WITH ENCRYPTION AS select_statement WITH CHECK OPTION 11.2 存储过程存储过程 n11.2.1 存储过程的

15、类型 n11.2.2 用户存储过程的创建与执行 n11.2.3 用户存储过程的查看、修改和删除 11.2 存储过程存储过程n存储过程是 SQL 语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。n它使得对数据库的管理和操作更加容易、效率更高。 11.2 存储过程存储过程n使用存储过程有如下优点：允许模块化程序设计 改善性能 减少网络流量 提供了安全机制 简化管理和操作 11.2.1 存储过程的类型存储过程的类型共分为五类：系统存储过程、用户定义的存储过程、扩展存储过程、临时存储过程和远程存储过程。系统存储过程：在安装SQL Server

16、2005时，系统创建了许多系统存储过程，这些系统存储过程存储在master和msdb数据库中。以“sp_”为前缀。 用户定义的存储过程：是由用户为完成某一特定功能而编写的存储过程，该存储过程存储在当前的数据库中。 11.2.1 存储过程的类型存储过程的类型扩展存储过程：是对动态链接库（DLL）函数的调用，在SQL Server 2005环境外执行，一般以“xp_”为前缀。临时存储过程：以“#”和“#”为前缀的过程，“#” 表示全局临时存储过程，它们存储在tempdb数据库中。远程存储过程：是在远程服务器的数据库中创建和存储的过程。 11.2.2 用户存储过程的创建与执行 n使用使用SQL Se

17、rver管理平台创建存储过程管理平台创建存储过程 （1）选中某个SQL Server服务器中的数据库，这里选中school数据库,展开可编程性节点。右键单击数据库下的“存储过程”选项，弹出快捷菜单。 （2）在快捷菜单中选择“新建存储过程”命令如图所示。 11.2.2 用户存储过程的创建与执行 11.2.2 用户存储过程的创建与执行（3）在“创建存储过程模板”的文本框中输入存储过程名称和程序语句。（4）单击检查语法按钮，执行语法正确性检验。（5）单击执行按钮，则在对象资源管理器窗口，可以看到所创建的存储过程p_cj1。11.2.2 用户存储过程的创建与执行n使用向导创建存储过程使用向导创建存储过

18、程 （1）选择“视图”菜单中的“模板资源管理器”菜单项，在系统屏幕的右侧会弹出“模板资源管理器”窗口。（2）选中“存储过程”选项中的“新建存储过程” 11.2.2 用户存储过程的创建与执行（3）出现创建存储过程的模板。（4）按照模板提示输入存储过程名称，以及存储过程要执行的语句后，点击执行命令按钮，即可创建该存储过程。 11.2.2 用户存储过程的创建与执行n使用使用Transact-SQL语句创建和执行存储过程语句创建和执行存储过程 （1）创建存储过程的语句格式：CREATE PROCEDURE 过程名 （， ） AS /*描述存储过程的操作*/11.2.2 用户存储过程的创建与执行（2）存

19、储过程的执行 在数据库应用程序中通过嵌入式SQL语句调用，不同的开发工具提供的调用形式不一样； 通过DBMS（或第三方软件）提供的数据库管理工具调用，调用的语法格式取决于具体的管理工具。 11.2.2 用户存储过程的创建与执行n执行存储过程的SQL语句的语法格式为： EXEC UTE 存储过程名 实参 , OUTPUT , n n输入参数的传递方式有两种：（1）按位置传递：直接给出参数的值，实参与形参一一对应（2）通过参数名传递：使用“参数名=参数值“的形式，参数可以任意顺序给出。n注：SQL Server 2005采用EXECUTE 语句，可缩写为EXEC。创建不带参数的存储过程n查询计算机

20、系学生的考试成绩，列出学生的姓名、课程名和成绩。CREATE PROCEDURE student_grade1AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = 计算机系 创建带输入参数的存储过程n查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。CREATE PROCEDURE student_grade2 dept char(20)ASSELECT Sname, Sdept, Cnam

21、e, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = dept 创建带多个输入参数的存储过程n查询某个学生某门课程的考试成绩，列出学生的姓名、课程名和成绩。CREATE PROCEDURE student_grade3 student_name char(10), course_name char(20) AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno INN

22、ER JOIN course c ON o = o WHERE sname = student_name AND cname = course_name 参数的传递方式n按参数位置传递值 EXEC student_grade3 刘晨, VB n按参数名传递值 EXEC Student_grade3 student_name = 刘晨, course_name=VB 创建带多个输入参数并有默认值的存储过程n查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为“数据库基础”。CREATE PROCEDURE student_grade4 student_name char(10), cou

23、rse_name char(20) = 数据库基础AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE sname = student_name AND cname = course_name调用参数有默认值的存储过程EXEC student_grade4 吴宾n等价于执行：EXEC student_grade4 吴宾, 数据库基础创建带有多个输入参数并均指定默认值的存储过程n查询指定系、指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值为

24、“计算机系”，性别的默认值为“男生”， 年龄的默认值为20。CREATE PROC P_Student dept char(20) = 计算机系, sex char(2) = 男, age int = 20AS SELECT * FROM Student WHERE Sdept = dept AND Ssex = sex AND Sage = age执行存储过程n执行1：不提供任何参数值。EXEC P_Studentn执行2：提供全部参数值。EXEC P_Student 信息系, 女, 19n执行3：只提供第二个参数的值。EXEC P_Student sex = 女n执行4：只提供第一个和第三

25、个参数的值。EXEC P_Student sex=女 , age = 19创建带有输出参数的存储过程n计算两个数的和。CREATE PROCEDURE sum1var1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 n执行此存储过程：Declare res intExecute sum1 5,7,res outputPrint res 创建带输入参数和一个输出参数的存储过程n统计指定课程的平均成绩，并将统计的结果用输出参数返回。CREATE PROCEDURE AvgGrade cn char(20), avg_grade in

26、t outputAS SELECT avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn 创建带输入参数和多个输出参数的存储过程n统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。CREATE PROCEDURE Avg_Count cn char(20), avg_grade int output, total int outputASSELECT avg_grade = AVG(Grade), total = COUNT(*)FROM SC JOIN Course C ON

27、 C.Cno = SC.CnoWHERE Cname = cn 创建删除数据的存储过程n删除考试成绩不及格学生的修课记录。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade 60创建修改数据的存储过程n将指定课程的学分增加2分。CREATE PROCEDURE p_UpdateCredit cn varchar(20)AS UPDATE course SET credit=credit+2 WHERE cname = cn11.2.3 用户存储过程的查看、修改和删除 1.查看用户存储过程（1）使用SQL Server管理平台查看用户创

28、建的存储过程。展开指定的服务器和数据库，选择并依次展开“程序存储过程”，然后右击要查看的存储过程名称。 11.2.3 用户存储过程的查看、修改和删除（2）使用系统存储过程来查看用户存储过程 。n用于显示存储过程的参数及其数据类型，其语法为： sp_help objname= name n用于显示存储过程的源代码，其语法为： sp_helptext objname= namen用于显示和存储过程相关的数据库对象，其语法为： sp_depends objname=objectn用于返回当前数据库中的存储过程列表，其语法为： sp_stored_proceduressp_name=name ,sp_

29、owner=owner ,sp_qualifier = qualifier 11.2.3 用户存储过程的查看、修改和删除2.修改用户存储过程ALTER PROCEDURE 存储过程名 形参 数据类型 =默认值 output ，n AS SQL语句11.2.3 用户存储过程的查看、修改和删除3.存储过程的删除 DROP PROCEDURE 数据库名. 11.3 触发器n11.3.1 创建和使用DML触发器 n11.3.2 创建和使用DDL触发器 n11.3.3 触发器的管理 n11.3.4 触发器实例 11.3 触发器n触发器是一种特殊的存储过程，它在执行操作事件时自动触发执行。n触发器与存储过

30、程的区别：（1）触发器是自动执行的，而存储过程需要显示调用才能执行。（2）触发器是建立在表或视图之上的，而存储过程是建立在数据库之上的。删除表就删除了该表之上的所有触发器，与该表有关的存储过程仍然存在，只有删除数据库才能删除该数据库上的所有存储过程。11.3 触发器nSQL Server2005 包括两大类触发器 :（1）DML 触发器在数据库中发生数据操作语言 (DML) 事件时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。 （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语

31、言 (DDL) 事件时将调用这些触发器。 11.3.1 创建和使用DML触发器 nDML 触发器的主要优点如下：（1）DML 触发器可通过数据库中的相关表实现级联更改。（2）DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。与 CHECK 约束不同，DML 触发器可以引用其他表中的列。（3）DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 11.3.1 创建和使用DML触发器当创建一个DML触发器时必须指定如下选项：名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据

32、修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句可激活同一个触发器；执行触发操作的编程语句。 11.3.1 创建和使用DML触发器nDML 触发器所使用的 deleted 和 inserted 逻辑表 :Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本

33、。 11.3.1 创建和使用DML触发器nDML 触发器的类型：insert触发器：在表或视图执行插入记录操作时触发。update触发器：在表或视图执行修改记录操作时触发。delete触发器：在表或视图执行删除记录操作时触发。DML 触发器的创建 （1）使用）使用SQL Server管理平台创建触发器管理平台创建触发器 在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建编辑窗口。用户可参照模板在其中输入创建触发器的T-SQL语句。最后，单击“执行”按钮，即可成功

34、创建触发器。 DML 触发器的创建（2）使用）使用CREATE TRIGGER命令创建命令创建DML触发器触发器 CREATE TRIGGER schema_name.触发器名 ON 表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS SQL 语句 .n DML 触发器的创建例如：对school数据库的sc表建立触发器。说明inserted和deleted表的作用。CREATE TRIGGER tr1ON scFOR INSERT, UPD

35、ATE, DELETE AS PRINT inserted表：Select * from insertedPRINT deleted表：Select * from deletedGo当执行插入操作：insert into sc values(0912103,c03,78,必修) DML 触发器的创建n例如：在school数据库的student表上创建一个名为tr_delete_stu的触发器，当要删除指定学号的行时，激发该触发器，撤消删除操作，并给出提示信息“不能删除student表中的信息!”。 程序清单如下：CREATE TRIGGER tr_delete_stu ON student A

36、FTER DELETEAS ROLLBACK TRANSACTION PRINT 不能删除student表中的信息!GO当执行：DELETE student WHERE sno= 0912101 时，系统将提示 “不能删除student表中的信息!” 的信息。DML 触发器的创建例如：在student表上创建一个触发器。当更新了某位学生的学号信息时，就激活触发器级联更新sc表中相关成绩记录中的学号信息，并使用print语句返回一个提示信息。解决的方法有：创建外键约束(不允许修改，或者允许级联更新)，用触发器实现自动级联修改。 DML 触发器的创建CREATE TRIGGER tr_update

37、_stu1 ON STUDENT AFTER UPDATEASDECLARE 原学号 char(6), 新学号 char(6)SELECT 原学号=deleted.sno, 新学号= inserted.snoFROM DELETED, INSERTED WHERE deleted.sname = inserted.snamePRINT 准备级联更新sc表中的学号信息.UPDATE sc SET sno=新学号 WHERE sno=原学号 PRINT 已经级联更新了sc表中原学号为+ 原学号 +的成绩信息。n建立了该触发器后当执行：UPDATE student SET sno=0912111 w

38、here sno=0912101操作后，会看到 sc表中学号为0912101的记录也都改为091211111.3.2 创建和使用DDL触发器 nDDL触发器一般用于以下目的：防止对数据库结构进行某些更改。希望数据库中发生某种情况以响应数据库结构中的更改。要记录数据库结构中的更改或事件。11.3.2 创建和使用DDL触发器1.创建创建DDL触发器触发器 CREATE TRIGGER 触发器名称ON ALL SERVER|DATABASE FOR|AFTER event_type|event_group,nAS sql_statement11.3.2 创建和使用DDL触发器2.DDL触发器的应用触

39、发器的应用n在响应当前数据库或服务器中处理的T-SQL事件时，可以激发DDL触发器。例如：在SCHOOL数据库中创建一个DDL触发器safe，用来防止该数据库中的任一表被修改或删除。11.3.2 创建和使用DDL触发器程序清单如下：USE SCHOOLGOCREATE TRIGGER safeON DATABASEAFTER DROP_TABLE,ALTER_TABLEASBEGIN RAISERROR(不能修改表结构,16,2) ROLLBACKENDGOn当执行以下程序：USE SCHOOLGOALTER TABLE student ADD nation char(10)GO11.3.3触

40、发器的管理 1.使用使用SQL Server管理平台修改、禁用和删除触发管理平台修改、禁用和删除触发器器 （1）启动SQL Server Management Studio,在“对象资源管理器”中依次展开数据库表节点触发器（2）击鼠标右键出现快捷菜单。在图中选择【修改】可以完成触发器的修改操作。 （3）在图中选择【删除】可以删除指定的触发器。 （4）在“禁用触发器”对话框中，单击【关闭】按钮，即可禁用选定的触发器，被禁用的触发器的图标将变成灰色。 2. 使用系统存储过程来查看用户创建使用系统存储过程来查看用户创建的触发器的触发器 （1）sp_help。用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。其语法格式为：sp_help 触发器名（2）sp_helptext。用于显示触发器的正文信息，其语法为： sp_helptext 触发器名（3）sp_depends。用于显示和触发器相关的数据库对象，其语法为 sp_depends 触发器名3.利用利用T-SQL语句修改和删除触发器语句修改和删除触发器 （1）触发器的修改 ALTER TRIGGER 触发器名称On 表名FOR | AFTER|INSERT OFINSERT,DELETE,UPDATEAS