1 / 70

第 7 章 SQL Server 存储过程和触发器

第 7 章 SQL Server 存储过程和触发器. 任课教师：郭黎明. 7.2 存储过程的使用. [ 应用实例 1] 统计每一个学生的平均成绩 , 要求显示学生姓名和平均成绩。 分析 : 关联查询和分组查询 使用 T-SQL 语句实现：. SELECT sname,avg(grade) FROM sc,student WHERE sc.sno=student.sno GROUP BY sname. 7.1 存储过程的使用. [ 应用实例 1] 统计学生所选课程所得的总学分 , 要求显示系科名 , 学号 , 姓名和总学分。 使用存储过程实现：.

adrian-hahn
Download Presentation

第 7 章 SQL Server 存储过程和触发器

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第7章 SQL Server存储过程和触发器 任课教师：郭黎明

  2. 7.2 存储过程的使用 • [应用实例1] 统计每一个学生的平均成绩,要求显示学生姓名和平均成绩。 • 分析:关联查询和分组查询 • 使用T-SQL语句实现： SELECT sname,avg(grade) FROM sc,student WHERE sc.sno=student.sno GROUP BY sname

  3. 7.1 存储过程的使用 • [应用实例1]统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。 • 使用存储过程实现： CREATE PROCEDURE Avg_of_Grade AS SELECT sname,avg(grade) FROM sc,student WHERE sc.sno=student.sno GROUP BY sname 在查询分析器中运行过程： EXEC Avg_of_Grade

  4. 7.1 存储过程概述 • 存储过程的概念 • 存储过程是SQL Server服务器上一组预先定义并编译好的Transact-SQL语句，它可以接受参数，返回状态值和参数值。 • 存储过程应用 • 涉及服务器处理多，而与用户较少的程序 • 存储过程的分类 • 系统存储过程：sp开头 SP_database • 本地存储过程 • 临时存储过程 • 远程存储过程 • 扩展存储过程

  5. 7.1 存储过程概述 • 存储过程的优点 • 模块化的程序设计 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用 • 快速执行－当存储过程被编译并存储在高速缓冲区中时，它能以相当高的效率执行。 • 减少网络通信量－时常会有长达数百行的S Q L语句。如果能把那些功能封装进存储过程，就可以通过向SQL Server发送一行代码起到成百行的语句的作用，从而节省大量的网络带宽 • 安全性－存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对象的许可。

  6. 7.2 存储过程的使用 • 存储过程创建 • 存储过程的执行 • 存储过程修改 • 存储过程删除

  7. 7.2 存储过程的使用 • 存储过程创建 • 存储过程的创建方法 • ①利用SQL Server 企业管理器创建存储过程。 • ②使用Transact-SQL语句中的CREATE。 • ③使用创建存储过程向导创建存储过程。 • 存储过程的组成 • ①所有的输入参数以及传给调用者的输出参数。 • ②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 • ③返回给调用者的状态值，以指明调用是成功还是失败。

  8. 7.2 存储过程的使用 • 存储过程创建 • 使用Transact-SQL语句中的CREATE。 CREATE PROC[EDURE] procedure_name[;number] [{@parameter data_type} [VARYING][=default][OUTPUT]][,...n] WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS sql_statement [ ...n ]

  9. 7.2 存储过程的使用 • 存储过程创建 • procedure_name：用于指定要创建的存储过程的名称。 • number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 • @parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 • data_type：用于指定参数的数据类型。 • VARYING：用于指定作为输出OUTPUT参数支持的结果集。 • Default：用于指定参数的默认值。 • OUTPUT：表明该参数是一个返回参数。

  10. 7.2 存储过程的使用 • 存储过程创建 【实例1－－无参数的存储过程】：统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。 分析:关联查询和分组查询 CREATE PROCEDURE Total_of_Credit AS SELECT dname, student.sno,sname, sum(ccredit) FROM student,sc,course,dept WHERE student.sno=sc.sno AND course.cno=sc.cno and dept.dno=student.dno GROUP BY dname,student.sno,student.sname Total_of_Credit EXEC Total_of_Credit

  11. 7.2 存储过程的使用 • 存储过程创建 【实例2----带输入参数的存储过程】 】建立一存储过程，用户输入学生姓名，学号，如果student表中存在此项，则删除后再插入，若不存在此学生，则直接插入该记录。 • 说明：exists(select_statement)函数是指select语句是否能返回行，如果能返回，则为真，否则为假。 • 分析:显然学号和姓名应该作为存储过程的传入参数

  12. 7.2 存储过程的使用 • 【Ins_Del_S】代码 CREATE PROCEDURE Ins_Del_S @sno AS CHAR(6), @sname AS CHAR(10) AS IF EXISTS(SELECT * FROM student WHERE sno=@sno ) BEGIN DELETE student WHERE sno=@sno INSERT INTO student(sno,sname) VALUES( @sno ,@sname) END ELSE INSERT INTO student(sno,sname) VALUES( @sno ,@sname) GO exec ins_del_s '1244','df'

  13. 7.2 存储过程的使用 • 存储过程创建 【实例3－－带输出参数的存储过程】：根据用户键入的学生姓名，查询学生的年龄。 CREATE PROCEDURE Getage @name CHAR(8)=NULL, @age INT OUTPUT AS IF @name=NULL BEGIN PRINT '查找哪个同学的年龄' RETURN END SELECT @age=YEAR(Getdate())-YEAR(sbirthday) FROM student WHERE sname=@name RETURN DECLARE @age INT EXEC Getage '李一名',@age OUTPUT SELECT @age

  14. 7.2 存储过程的使用 • 存储过程创建 【实例4－－通过Return参数返回状态】：创建一个存储过程,检查某学生的选课的门数 RETURN语句返回的是整形值 CREATE PROCEDURE SelCount @sno CHAR(8)=NULL AS DECLARE @count integer SELECT @count=count(*) FROM sc WHERE sno=@sno RETURN @count declare @count integer execute @count=selcount '95001' print @count

  15. 7.2 存储过程的使用 • 存储过程的执行 • 直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下： [[EXEC[UTE]]{ [@return_status=]{procedure_name[;number]|@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n] [ WITH RECOMPILE ]。

  16. 7.2 存储过程的使用 • 存储过程的修改 • 使用Transact-SQL语句中的ALTER。 ALTER PROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT]][,...n] WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS sql_statement [ ...n ]

  17. 7.2 存储过程的使用 • 存储过程的删除 • 使用Transact-SQL语句中的DROP。 DROP PROC[EDURE] <procedure_name>

  18. 7.2 存储过程的使用 • 存储过程的错误处理 • 可以使用@@ERROR捕捉错误代码, @@ERROR在执行每一个T-SQL语句之后都会得到一个值,对于成功的执行, @@ERROR的值为0,如果出现错误@@ERROR的值大于0 CREATE PROCEDURE INSERT_STUDENT @sno char(5),@sname char(10),@ssex char(2) as insert into student(sno,sname,ssex) values(@sno,@sname,@ssex) if @@error=0 print '执行成功' else print '执行失败' print @@error

  19. 练习 • 综合练习 • 1.根据用户键入的学生学号和课程号，查询学生的成绩。 CREATE PROC Getgrade @sno VARCHAR(10), @cno VARCHAR(10) AS SELECT student.sno,sname,cno,grade FROM sc WHERE sno=@sno and cno=@cno

  20. 练习 • 二、存储过程综合练习 • 2. 建立一存储过程，使用户输入系别，查询该系学生选择各课程的情况。（模糊查询）列出系别、课程号以及对应的人数，并按系别排序。【Sel_Course】 • 3. 建立一存储过程，使用户输入课程名时，列出该课程名、对应的课程号、选择该课程的学生总人数、平均分【Sel_Stu】

  21. 参考答案 二、存储过程综合练习 【2】 CREATE PROCEDURE Sel_Course @dno AS CHAR(2) AS SELECT dept.dno,dname,sc.cno,count(*) FROM sc,dept,student WHERE sc.sno=student.sno AND student.dno=dept.dno AND dept.dno=@dno GROUP BY dept.dno,dname,sc.cno GO

  22. 参考答案 二、存储过程综合练习 【3】 CREATE PROCEDURE Sel_Stu @cname AS CHAR(20) AS SELECT sc.cno,cname,count(*),avg(grade) FROM sc,course WHERE sc.cno=course.cno AND cname=@cname GROUP BY sc.cno,cname GO

  23. 7.3 触发器 • 7.3.1触发器概述 • 问题的引入 • 触发器的概念 • 触发器的原理 • 触发器的分类 • 7.3.2触发器的创建,删除 • 应用示例

  24. 7.3.1 触发器概述 • 问题的引入 • 在[学生管理数据库]中,某学生的[选课门数]是一个经常访问的属性,因此在student表中增加一个属性sselnum (smallint）． • 在实际应用 • 实例展示 4 1 删除一个记录(“95003”,”002”,56 95001 006 90 增加一个记录

  25. 7.3.1 触发器概述 • 问题的引入 • 考虑该问题涉及两个表之间的关系，可否用参照完整性来实现？ • 可否用嵌套的SQL语句来实现? • update student set sselnum=(select count(*) from sc where sc.sno=student.sno) • 存在的问题 • 问题的解决：使用触发器trigger • 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性

  26. 7.3.1 触发器概述 • 触发器的概念 • 触发器是一种特殊类型的存储过程。在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。 • 触发器触发原因对表进行插入、更新或删除操作触发事件。 • 触发结果：另一段程序的执行 • 思考 ：在【问题的引入】中，？是触发原因 • ？触发结果 • 触发器主要是通过事件触发而执行的，而存储过程是通过调用存储过程名称而执行的。 INSERT INTO sc VALUES(‘ 95001’, ‘ 006 ’, 90) EXEC Getgrade '00001','02' • 触发器可以用于SQL Server 约束、默认值和规则的完整性检查。

  27. 7.3.1 触发器概述 • 触发器的原理 • 在对表进行【INSERT】【DELETE】【UPDATE】操作时，SQL Server自动执行触发器定义的一段程序 • 触发器用到的专用临时表：Inserted和Deleted • 特点(内存，时间，结构，只读） • 存储在内存中 • 触发事件执行以后，逻辑表即刻被删除 • 结构同触发事件所操作的表一致 • 只读 操作 Inserted表 Deleted表 Insert 存放插入的记录 Delete 存放删除的记录 Update 存放要更新的新行 存放更新前的行

  28. 7.3.1 触发器概述 • 触发器的原理 • Inserted表和Deleted表的理解： （1）如果基于SC表的UPDATE操作定义了触发器 （2）再执行语句： UPDATE SC SET grade=70 WHERE sno=‘95001’ AND cno=‘001’ 在UPDATE语句执行过程中 Deleted表的内容： Inserted表的内容： SC表

  29. 7.3.1 触发器概述 • 触发器的分类 • AFTER类型触发器: • 只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发 • 只能在表上定义。 • INSTEAD OF 触发器: • 并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。 • 既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器.

  30. 7.3.2 触发器的使用 • 触发器的创建 • 触发器创建的方法 • 用EM创建 • 用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框 • 用SQL创建

  31. 7.3.2 触发器的使用 • 触发器的创建 • 创建触发器的语法 CREATE TRIGGER trigger_name ON{table|view} {{ { FOR | AFTER | INSTEAD OF } { [DELETE][,][ INSERT ] [ , ] [ UPDATE ] }AS ... 触发事件所操作的表或视图 触发事件 被触发的程序段

  32. 7.3.2 触发器的使用 • 触发器的创建 【实例1】创建一个Insert触发器，功能是当在sc上插入一行数据时，自动使得student表对应学号上的sselnum字段的值加1 【分析】Insert操作的表是sc,而要求系统自动更新的表是student, 因此关键是获得sc表上新插入记录的学号,然后在student表上修改该学号的sselnum的值 【思考】（1）如何获得sc表上新插入记录的学号? （2）触发器定义在？一个表上 SELECT sno FROM Inserted

  33. 7.3.2 触发器的使用 • 触发器的创建 【实例1】创建一个Insert触发器，功能是当在sc上插入一行数据时，自动使得student表对应学号上的sselnum字段的值加1 CREATE TRIGGER sc_insert ON sc FOR INSERT AS UPDATE student SET sselnum=sselnum+1 FROM student WHERE sno IN (SELECT sno FROM inserted) 局限性：只能对单行的处理，可能存在一条语句影响多条记录的情况 注意：Sselnum默认初值设置为0，否则NULL值不能参加算术运算。

  34. 7.3.2 触发器的使用 • 触发器的创建 • 【实例1的使用】创建了触发器sc_insert以后 • 【测试】在sc表中插入新的记录(“95001”,”006”,90), INSERT INTO sc VALUES (‘95001’,’006’,90) 检测student表中”95001”的sselnum是否自动增加1 • 【解析】－－【Insert】触发器的执行过程 • 首先执行insert语句，如果语句正确，插入记录 • 执行被触发的程序段：update语句 • 注意：触发器触发的语句和触发它的语句是同一事务来执行的 • 自己上机检测

  35. 7.3.2 触发器的使用 • 触发器的创建 【实例2】创建一个Insert触发器，功能是当在sc上插入数据时，自动更新student表对应学号上的sselnum字段，应考虑成批修改数据的情况。 【举例】合并sc1表的数据到sc表中(两个表的结构相同) INSERT INTO sc SELECT * FROM sc1 【

  36. 7.3.2 触发器的使用 【实例2】创建一个Insert触发器，功能是当在sc上插入数据时，自动更新student表对应学号上的sselnum字段，应考虑成批修改数据的情况。 【分析】分成两种情况 (1)单行记录插入,同[实例1] (2)多行记录插入,从inserted表中可以获得新插入记录的所有的sno,以及在每个sno上插入的记录的个数，以此更新Student表中的记录 (3)如何区别单行记录操作和多行记录操作？

  37. 7.3.2 触发器的使用 • 触发器的创建 【实例2】创建一个Insert触发器，功能是当在sc上插入数据时，自动更新student表对应学号上的sselnum字段，应考虑成批修改数据的情况。 CREATE TRIGGER sc_insert ON sc FOR INSERT AS IF @@ROWCOUNT=1 BEGIN UPDATE student SET sselnum=sselnum+1 FROM student WHERE student.snoIN(SELECT sno FROM inserted) END ELSE BEGIN UPDATE student SET sselnum=sselnum+ (SELECT COUNT(cno) FROM inserted WHERE student.sno=inserted.sno) WHERE student.sno IN (SELECT sno FROM inserted) END

  38. 7.3.2 触发器的使用 • 触发器的创建 • 【实例2的使用】创建了触发器sc_insert以后 • 【测试】执行INSERT INTO sc SELECT * FROM sc1 检测STUDENT表中Sselnum字段的值 • 【问题】－－检测正确? • 【扩展思考】 如果在SC表的Insert操作上定义两个不同的触发器，系统怎样处理？

  39. 7.3.2 触发器的使用 • 触发器的创建 【实例3】建立一触发器，当删除student表中某学生记录时，同时删除sc表中所有该学生的选课记录。 CREATE TRIGGER Del_s_sc ON student] FOR DELETE AS DELETE sc WHERE sno IN (SELECT sno FROM deleted) 该问题还可以用其他方法解决。

  40. 7.3.2 触发器的使用 • 触发器的创建 【实例4】创建一个Update触发器，功能是当在sc上修改一行数据时，显示修改的记录的修改前的值和修改后的值 CREATE TRIGGER sc_update ON sc FOR UPDATE AS SELECT *,’修改前’ FROM deleted SELECT *,’修改后’ FROM inserted

  41. 7.3.2 触发器的使用 • 触发器的创建 在有些更新中，更新的内容并不是整个记录，而仅仅是一列或几列，这时就要用到用于检查列改变的更新型触发器。  [ { IF UPDATE ( column )            [ { AND | OR } UPDATE ( column ) ]

  42. 7.3.2 触发器的使用 • 触发器的创建 【实例5】使用IF UPDATE（）保留字，实现当修改Student表中的sno属性时，sc表中相应的sno也要修改 CREATE TRIGGER s_update ON student FOR UPDATE AS IF @@rowcount>1 PRINT '不允许同时修改多行记录的学号（SNO)' ELSE IF UPDATE(sno) UPDATE sc set sno=(select sno from inserted) WHERE sno=(select sno from deleted)

  43. 7.3.2 触发器的使用 • 触发器的创建（INSTEAD OF) • INSTEAD OF 触发器的主要优点是使不可被修改的视图能够支持修改。其中典型的例子是分割视图（partitioned view）。分割视图通常是一个来自多个表的结果集 • 【举例】全校学生人数多 将学生表按系科分成若干表， 为了教务处统一管理数据，合并表到分割视图 例如 ：将student分成三个表Student01,Student02,Student03, 表示01系科，02系科，03系科的学生,创建分割视图student_view统一管理数据 create view student_view as select * from student01 union all select * from student02 union all select * from student03 存在问题：不能更新

  44. 7.3.2 触发器的使用 【实例5】如何使用INSTEAD OF 触发器来支持对分割视图所引用的基本表的修改。 【分析】触发器创建在 视图上 触发事件是Insert 可以通过临时表Inserted获得插入的记录的”系科”和其他属性 在该”系科”对应表的上插入inserted中的记录.

  45. 7.3.2 触发器的使用 【实例5】如何使用INSTEAD OF 触发器来支持对分割视图所引用的基本表的修改。 CREATE TRIGGER studentviewtrg ON student_view INSTEAD OF insert AS BEGIN DEClARE @deptno CHAR(3) SELECT @deptno=dno FROM inserted IF @deptno='01' BEGIN INSERT INTO student01 SELECT * FROM inserted END IF @deptno='02' BEGIN INSERT INTO student02 SELECT * FROM inserted END IF @deptno='03' BEGIN INSERT INTO student03 SELECT * FROM inserted END END 思考：在视图上的insert语句是否执行

  46. 7.3.2 触发器的使用 【实例5】应用 执行INSERT INTO student_View(sno，dno) VALUES(“95010”,”01”) 视图本身不能更新，但是数据发生了改变，为什么？

  47. 7.3.2 触发器的使用 • 触发器的修改 • 修改触发器的语法 ALTER TRIGGER trigger_name ON{table|view} [WITHENCRYPTION] {{ { FOR | AFTER | INSTEAD OF } { [DELETE][,][ INSERT ] [ , ] [ UPDATE ] }[WITHAPPEND][NOTFORREPLICATION]AS

  48. 7.3.2 触发器的使用 • 触发器的删除 • 删除触发器的语法 DROP TRIGGER trigger_name

  49. 练习 基础练习题 1、触发器分为_____、_____两种。 2、激活触发器的操作有_____、____ 、____。 3、触发器定义在SC表上，触发事件是UPDATE，则在SC表S上执行UPDATE语句，会产生逻辑表_____、____ 。

  50. 练习 • 二、触发器综合练习 • 1. 建立一触发器，当向SC表插入记录时，若对应的课程号的选课人数小于等于60人，则可插入，否则发出出错信息“该课程选课人数已满！请另选其他课程。” sc_ins_60 • 2. 建立一触发器，当修改某学生的系别时或插入记录时，判断该系学生人数是否小于等于100，如果是，则允许修改或插入，否则发出出错信息“该系人数已满!!” Ins_Up_100

More Related