数据库原理及应用课件：第10章 存储过程和触发器

1、数据库基础与实践技术（SQL Server 2008）第十章第10章 后台数据库编程10.1 存储过程10.2 触发器2/8310.1 存储过程10.1.1 存储过程概念10.1.2 创建和执行存储过程10.1.3 从存储过程中返回数据10.1.4 查看和维护存储过程10.1.5 一些系统存储过程3/8310.1.1 存储过程概念存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。SQL语句的存储和执行分两种情况：在客户端存储代码，并创建向数据库服务器发送的SQL命令；在数据库服务器端存储代码，然后由应用程序调

2、用执行这些SQL语句。4/83存储过程功能接受输入参数并以输出参数的形式将多个值返回给调用者。包含执行数据库操作的语句。 将查询语句执行结果返回到客户端内存中。5/83存储过程分类存储过程类型 系统存储过程扩展存储过程用户自定义存储过程6/83系统存储过程存储以SP_为前缀,是由SQL Server2008自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。10.1.2 创建和执行存储过程1.创建存储过程CREATE PROCEDURE 存储

3、过程名 参数名 数据类型 = default OUTPUT , n AS SQL语句 n 2.执行存储过程EXEC UTE 存储过程名 实参 , OUTPUT , n 7/83【例1】不带参数的存储过程：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade1AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.Sno INNER JOIN Course cON c.Cno = sc.Cno WHERE Dept = 计算机系执行此存

4、储过程：EXEC p_StudentGrade18/8310.1.2 创建和执行存储过程【例2】带输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade2 dept char(20)AS SELECT Sname, Dept, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.Sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE Dept = dept执行存储过程，查询信息管理系学生的修课情况

5、 EXEC p_StudentGrade2 信息管理系9/8310.1.2 创建和执行存储过程【例3】带多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，课程的默认值为“java”。CREATE PROCEDURE p_StudentGrade3 sname char(10), cname char(20) = JavaAS SELECT Sname, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE sname = sn

6、ame AND cname = cname10/8310.1.2 创建和执行存储过程执行存储过程（1）按参数位置传值实参的排列顺序与创建存储过程时参数定义的顺序一致。EXEC p_StudentGrade3 吴宾, 高等数学EXEC p_StudentGrade3 吴宾11/8310.1.2 创建和执行存储过程执行存储过程（2）按参数名传值指明定义存储过程时指定的参数的名字以及参数的值。EXEC p_StudentGrade3 sname = 吴宾, cname = 高等数学12/8310.1.2 创建和执行存储过程【例4】带输出参数的存储过程：统计全体学生人数，并将统计结果用输出参数返回。C

7、REATE PROCEDURE p_Count total int OUTPUTAs SELECT total = COUNT(*) FROM Student 执行此存储过程：DECLARE res intEXEC p_Count res OUTPUTPRINT res13/8310.1.2 创建和执行存储过程【例5】带输入参数和输出参数的存储过程：统计指定课程（课程名）的平均成绩，并将统计结果用输出参数返回。CREATE PROC p_AvgGrade cn char(20),avg_grade int OUTPUTAS SELECT avg_grade = AVG(Grade) FROM

8、SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn14/8310.1.2 创建和执行存储过程查询Java课程的平均成绩。DECLARE Avg_Grade intEXEC p_AvgGrade java, Avg_Grade OUTPUTPRINT Avg_Grade15/8310.1.2 创建和执行存储过程【例6】将指定课程（课程号）的学分增加指定的分数。CREATE PROC p_UpdateCredit cno varchar(10), inc intAS UPDATE Course SET Credit = Credit + inc

9、WHERE Cno = cno16/8310.1.2 创建和执行存储过程【例7】删除指定课程（课程名）中考试成绩不及格学生的此门课程的修课记录。CREATE PROC p_DeleteSC cn varchar(20)AS DELETE FROM SC WHERE Grade = 8025/832.修改存储过程2. 删除存储过程删除存储过程语法DROP PROC | PROCEDURE 存储过程名 , n 【例11】删除p_StudentGrade1存储过程。 DROP PROC p_StudentGrade126/831.sp_columns作用：返回当前环境中指定表或视图的列信息。语法格式

10、：sp_columns table_name = object , table_owner = owner , table_qualifier = qualifier , column_name = column 10.1.5 一些系统存储过程27/83【例】查询Student表中包含的列信息。EXEC sp_columns table_name = Student28/8310.1.5 一些系统存储过程作用：返回当前环境中单个表的主键信息。语法格式：sp_pkeys table_name = name , table_owner = owner , table_qualifier = qua

11、lifier 2. sp_pkeys29/8310.1.5 一些系统存储过程【例2】查询SC表包含的主键。EXEC sp_pkeys table_name = SC30/8310.1.5 一些系统存储过程作用：返回当前环境的逻辑外键信息。3.sp_fkeys31/8310.1.5 一些系统存储过程【例3】查看引用Student表的外键表和外键列。EXEC sp_fkeys pktable_name = Student4. sp_tables作用：返回可在当前环境中查询的对象列表，也就是返回任何能够在 FROM 子句中出现的对象。32/8310.1.5 一些系统存储过程【例4】在MySimple

12、DB数据库中执行下述代码，查看Production架构中的可查询对象。 EXEC sp_tables table_name = %, table_owner = Production, table_qualifier = MySimpleDB33/8310.1.5 一些系统存储过程10.2 触发器10.2.1 创建触发器10.2.2 DML触发器示例10.2.3 DDL触发器示例10.2.4 查看和维护触发器34/8310.2.1 创建触发器1.触发器基本概念触发器是一类特殊的存储过程，被定义为在对特定表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行。35/8310.

13、2.1 创建触发器2.触发器特点1)与表紧密相连，可以看作表定义的一部分2)不能通过名称被直接调用，更不允许带参数。3）当用户对表中的数据进行编辑时，自动执行。4)可以用于SQL Server约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。 3.DML触发器DML触发器，也称为标准触发器，是定义在某个表上的，当数据库服务器中发生数据操纵语言（DML）事件，如INSERT、UPDATE、DELETE时所触发的操作。DML触发器用于在数据被修改时强制执行业务规则。10.2.1 创建触发器37/8310.2.1 创建触发器4.创建DML触发器语句CREATE TRIGGER 触发器名O

14、N 表名|视图名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL 语句38/83创建触发器注意事项对于AFTER型的触发器，可以在同一种操作上建立多个触发器；对于INSTEAD OF型的触发器，在同一种操作上只能建立一个触发器。CREATE、ALTER和DROP数据库以及数据库对象的语句都不允许在触发器中使用。如果在某个表上既定义了完整性约束，又定义了触发器，则是先执行完整性约束检查，符合约束后才执行数据操作语句，然后才能引发触发器执行。39/83触发器中的两个临时工作表INSERTED表和DELETED表：结构同建立触发器的表

15、的结构相同，而且只能用在触发器代码中。INSERTED：保存了INSERT操作中新插入的数据和UPDATE操作中更新后的数据；DELETED：保存了DELETE操作中删除的数据和UPDATE操作中更新前的数据。40/8310.2.2 DML触发器示例1.后触发型触发器只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。执行到引发触发器操作的语句执行该语句执行触发器41/83【例1】在学生表上创建一个insert触发器，当向其中插入数据时，检查是否有重名的学生存在，如果有，在新插入的学生姓名前加*号。CREATE TRIGGER tr_insert_

16、On_studentON student FOR insertASif(select count(*) from student s join inserted i on s.sname=i.sname)1)update student set sname=*+s.sname from student t join inserted i on t.sno=i.sno42/8310.2.2 DML触发器示例【例2】限定Course表中Semester的取值范围为1-10。CREATE Trigger tr_course_Semester ON Course AFTER INSERT, UPDAT

17、EAS IF EXISTS(SELECT * FROM INSERTED WHERE Semester NOT BETWEEN 1 AND 10) ROLLBACK - 撤消操作43/8310.2.2 DML触发器示例【例3】用触发器实现在student数据库中的student表中一次只能从该表中删除一条记录。Create TRIGGER tr_delete_on_studentON studentAFTER DELETEASDECLARE num intSELECT num=count(*) FROM DELETEDIF num=2 PRINT (不能删除两条以上记录) ROLLBACK T

18、RANSACTIONGO10.2.2 DML触发器示例【例4】实现业务规则：限制每个学期开设的课程总数不能超过10门，如果超过了10门，则给出提示信息：本学期课程太多。CREATE Trigger tr_course_TotalCno ON Course AFTER INSERTAS IF (SELECT COUNT(*) FROM Course C JOIN INSERTED I ON I.Semester = C.Semester ) 10 BEGIN PRINT 本学期课程太多! ROLLBACK END45/8310.2.2 DML触发器示例【例5】 实现业务规则。在SC表中，不能删除

19、考试成绩不及格学生的该门课程的考试记录。CREATE Trigger tr_Delete_on_SC ON SC AFTER DELETEAS IF EXISTS(SELECT * FROM DELETED WHERE Grade 60 ) BEGIN PRINT 不能删除成绩不及格的考试记录! ROLLBACK END46/8310.2.2 DML触发器示例2. 前触发型触发器在前触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。在一张表上，每个INSERT、UPDATE或DELETE操作最多只能定义一个INSTEAD OF触发器。执行到引发触发器操作的语

20、句执行触发器47/8310.2.2 DML触发器示例【例6】用前触发器实现限定Course表中Semester的取值范围为110的约束。 CREATE Trigger tr_course_Semester1 ON Course INSTEAD OF INSERT, UPDATE AS IF EXISTS(SELECT * FROM INSERTEDWHERE Semester BETWEEN 1 AND 10) -判断是否符合约束要求 INSERT INTO Course SELECT * FROM INSERTED -重做操作48/8310.2.2 DML触发器示例【例7】用前触发器实现限制

21、每个学期开设的课程总数不能超过10门的约束。CREATE Trigger tr_TotalCno_Course ON Course INSTEAD OF INSERTAS IF (SELECT COUNT(*) FROM Course C JOIN INSERTED I ON I.Semester = C.Semester ) =60 ) DELETE FROM SC FROM DELETED d JOIN SC ON sc.sno=d.sno and o=o 50/8310.2.2 DML触发器示例【练习】定义触发器，限制不能删除有人选的课程。Create trigger tr_delete

22、_courseOn course after deleteAs if exists(select * from deleted d join sc on o=o) begin print “不能删除有人选的课程” rollback end51/8310.2.2 DML触发器示例DDL触发器是在响应数据定义语言（DDL）语句时执行。这些DDL语句包括CREATE、ALTER、DROP、GRANT、DENY和REVOKE等。从DBMS视角看，DDL分为实例和数据库两种级别。实例级别的DDL触发器，其影响范围就是整个SQL Server服务器。数据库级别的DDL触发器，主要是对数据库级别安全对象的DDL动作进行监控。10.2.3 DDL触发器示例52/83CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER event_type | event_group ,.n AS sql_statement ; ,.n 创建DDL触发器语句53/83【例9】 服务器级的DDL触发器。本示例将防止删除数据库的操作。CREATE TRIGGER ddl_tr_