第7章SQL SERVER存储过程和触发器.ppt

1、第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统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。 使用存储过程实现：,CREATE PROCEDURE Avg_of_Grade AS SELECT sname,avg(grade

2、) FROM sc,student WHERE sc.sno=student.sno GROUP BY sname,在查询分析器中运行过程： EXEC Avg_of_Grade,7.1 存储过程概述,存储过程的概念 存储过程是SQL Server服务器上一组预先定义并编译好的Transact-SQL语句，它可以接受参数，返回状态值和参数值。 存储过程应用 涉及服务器处理多，而与用户较少的程序 存储过程的分类 系统存储过程：sp开头 SP_database 本地存储过程 临时存储过程 远程存储过程 扩展存储过程,7.1 存储过程概述,存储过程的优点 模块化的程序设计 创建一个存储过程存放在数据库

3、中后，就可以被其他程序反复使用 快速执行当存储过程被编译并存储在高速缓冲区中时，它能以相当高的效率执行。 减少网络通信量时常会有长达数百行的S Q L语句。如果能把那些功能封装进存储过程，就可以通过向SQL Server发送一行代码起到成百行的语句的作用，从而节省大量的网络带宽 安全性存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对象的许可。,7.2 存储过程的使用,存储过程创建 存储过程的执行 存储过程修改 存储过程删除,7.2 存储过程的使用,存储过程创建 存储过程的创建方法 利用SQL Serv

4、er 企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE。 使用创建存储过程向导创建存储过程。 存储过程的组成 所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,7.2 存储过程的使用,存储过程创建 使用Transact-SQL语句中的CREATE。,CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RE

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

6、 OUTPUT：表明该参数是一个返回参数。,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 o=o and dept.dno=student.dno GROUP BY dname,student.sno,stu

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

8、), 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,7.2 存储过程的使用,存储过程创建 【实例3带输出参数的存储过程】：根据用户键入的学生姓名，查询学生的年龄。,

9、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,7.2 存储过程的使用,存储过程创建 【实例4通过Return参数返回状态】：创建一个存储过程,检查某学生的选课的门数

10、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,7.2 存储过程的使用,存储过程的执行 直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：,EXECUTE return_status=procedure_name;numbe

11、r|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE 。,7.2 存储过程的使用,存储过程的修改 使用Transact-SQL语句中的ALTER。,ALTER PROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement .n ,7.2 存储过程的使用,

12、存储过程的删除 使用Transact-SQL语句中的DROP。,DROP PROCEDURE ,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 prin

13、t 执行成功 else print 执行失败 print error,练习,综合练习 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,练习,二、存储过程综合练习 2. 建立一存储过程，使用户输入系别，查询该系学生选择各课程的情况。（模糊查询）列出系别、课程号以及对应的人数，并按系别排序。【Sel_Course】 3. 建立一存储过程，使用户

14、输入课程名时，列出该课程名、对应的课程号、选择该课程的学生总人数、平均分【Sel_Stu】,参考答案,二、存储过程综合练习 【2】 CREATE PROCEDURE Sel_Course dno AS CHAR(2) AS SELECT dept.dno,dname,o,count(*) FROM sc,dept,student WHERE sc.sno=student.sno AND student.dno=dept.dno AND dept.dno=dno GROUP BY dept.dno,dname,o GO,参考答案,二、存储过程综合练习 【3】 CREATE PROCEDURE S

15、el_Stu cname AS CHAR(20) AS SELECT o,cname,count(*),avg(grade) FROM sc,course WHERE o=o AND cname=cname GROUP BY o,cname GO,7.3 触发器,7.3.1触发器概述 问题的引入 触发器的概念 触发器的原理 触发器的分类 7.3.2触发器的创建,删除 应用示例,7.3.1 触发器概述,问题的引入 在学生管理数据库中,某学生的选课门数是一个经常访问的属性,因此在student表中增加一个属性sselnum (smallint） 在实际应用 实例展示,增加一个记录,删除一个记录(“

16、95003”,”002”,56,95001 006 90,4,1,7.3.1 触发器概述,问题的引入 考虑该问题涉及两个表之间的关系，可否用参照完整性来实现？ 可否用嵌套的SQL语句来实现? update student set sselnum=(select count(*) from sc where sc.sno=student.sno) 存在的问题 问题的解决：使用触发器trigger 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性,7.3.1 触发器概述,触发器的概念 触发器是一种特殊类型的存储过程。在SQL Server里面也就是对某一个表的一

17、定的操作，触发某种条件，从而执行的一段程序。 触发器触发原因对表进行插入、更新或删除操作触发事件。 触发结果：另一段程序的执行 思考 ：在【问题的引入】中，？是触发原因 ？触发结果 触发器主要是通过事件触发而执行的，而存储过程是通过调用存储过程名称而执行的。 INSERT INTO sc VALUES( 95001, 006 , 90) EXEC Getgrade 00001,02 触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。,7.3.1 触发器概述,触发器的原理 在对表进行【INSERT】【DELETE】【UPDATE】操作时，SQL Server自动执行触发器定义

18、的一段程序 触发器用到的专用临时表：Inserted和Deleted 特点(内存，时间，结构，只读） 存储在内存中 触发事件执行以后，逻辑表即刻被删除 结构同触发事件所操作的表一致 只读,操作,Inserted表,Deleted表,Insert,存放插入的记录,Delete,存放删除的记录,Update,存放要更新的新行,存放更新前的行,7.3.1 触发器概述,触发器的原理 Inserted表和Deleted表的理解： （1）如果基于SC表的UPDATE操作定义了触发器 （2）再执行语句： UPDATE SC SET grade=70 WHERE sno=95001 AND cno=001 在

19、UPDATE语句执行过程中 Deleted表的内容： Inserted表的内容：,SC表,7.3.1 触发器概述,触发器的分类 AFTER类型触发器: 只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发 只能在表上定义。 INSTEAD OF 触发器: 并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。 既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器.,7.3.2 触发器的使用,触发器的创建 触发器创建的方法 用EM创建 用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下

20、的管理触发器选项，则会出现触发器属性对话框 用SQL创建,7.3.2 触发器的使用,触发器的创建 创建触发器的语法,CREATE TRIGGER trigger_name ONtable|view FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE AS .,触发事件所操作的表或视图,触发事件,被触发的程序段,7.3.2 触发器的使用,触发器的创建 【实例1】创建一个Insert触发器，功能是当在sc上插入一行数据时，自动使得student表对应学号上的sselnum字段的值加1 【分析】Insert操作的表是sc,而要求系统自动更新的表是stud

21、ent, 因此关键是获得sc表上新插入记录的学号,然后在student表上修改该学号的sselnum的值 【思考】（1）如何获得sc表上新插入记录的学号? （2）触发器定义在？一个表上,SELECT sno FROM Inserted,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

22、WHERE sno IN (SELECT sno FROM inserted),注意：Sselnum默认初值设置为0，否则NULL值不能参加算术运算。,局限性：只能对单行的处理，可能存在一条语句影响多条记录的情况,7.3.2 触发器的使用,触发器的创建 【实例1的使用】创建了触发器sc_insert以后 【测试】在sc表中插入新的记录(“95001”,”006”,90), INSERT INTO sc VALUES (95001,006,90) 检测student表中”95001”的sselnum是否自动增加1 【解析】【Insert】触发器的执行过程 首先执行insert语句，如果语句正确，

23、插入记录 执行被触发的程序段：update语句 注意：触发器触发的语句和触发它的语句是同一事务来执行的 自己上机检测,7.3.2 触发器的使用,触发器的创建 【实例2】创建一个Insert触发器，功能是当在sc上插入数据时，自动更新student表对应学号上的sselnum字段，应考虑成批修改数据的情况。 【举例】合并sc1表的数据到sc表中(两个表的结构相同) INSERT INTO sc SELECT * FROM sc1 【,7.3.2 触发器的使用,【实例2】创建一个Insert触发器，功能是当在sc上插入数据时，自动更新student表对应学号上的sselnum字段，应考虑成批修改数

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

25、t SET sselnum=sselnum+1 FROM student WHERE student.sno IN (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,7.3.2 触发器的使用,触发器的创建 【实例2的使用】创建了触发器sc_insert以后 【

26、测试】执行INSERT INTO sc SELECT * FROM sc1 检测STUDENT表中Sselnum字段的值 【问题】检测正确? 【扩展思考】 如果在SC表的Insert操作上定义两个不同的触发器，系统怎样处理？,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),该问题还可以用其他方法解决。,7.3

27、.2 触发器的使用,触发器的创建 【实例4】创建一个Update触发器，功能是当在sc上修改一行数据时，显示修改的记录的修改前的值和修改后的值,CREATE TRIGGER sc_update ON sc FOR UPDATE AS SELECT *,修改前 FROM deleted SELECT *,修改后 FROM inserted,7.3.2 触发器的使用,触发器的创建 在有些更新中，更新的内容并不是整个记录，而仅仅是一列或几列，这时就要用到用于检查列改变的更新型触发器。, IF UPDATE ( column ) AND | OR UPDATE ( column ) ,7.3.2 触发

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

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

30、t02 union all select * from student03,存在问题：不能更新,7.3.2 触发器的使用,【分析】触发器创建在 视图上 触发事件是Insert 可以通过临时表Inserted获得插入的记录的”系科”和其他属性 在该”系科”对应表的上插入inserted中的记录.,【实例5】如何使用INSTEAD OF 触发器来支持对分割视图所引用的基本表的修改。,7.3.2 触发器的使用,CREATE TRIGGER studentviewtrg ON student_view INSTEAD OF insert AS BEGIN DEClARE deptno CHAR(3)

31、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,【实例5】如何使用INSTEAD OF 触发器来支持对分割视图所引用的基本表的修改。,思考：在视图上的insert语句是

32、否执行,7.3.2 触发器的使用,执行INSERT INTO student_View(sno，dno) VALUES(“95010”,”01”) 视图本身不能更新，但是数据发生了改变，为什么？,【实例5】应用,7.3.2 触发器的使用,触发器的修改 修改触发器的语法,ALTER TRIGGER trigger_name ONtable|view WITHENCRYPTION FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE WITHAPPENDNOTFORREPLICATIONAS,7.3.2 触发器的使用,触发器的删除 删除触发器的语法,DR

33、OP TRIGGER trigger_name,练习,基础练习题 1、触发器分为_、_两种。 2、激活触发器的操作有_、_ 、_。 3、触发器定义在SC表上，触发事件是UPDATE，则在SC表S上执行UPDATE语句，会产生逻辑表_、_ 。,练习,二、触发器综合练习 1. 建立一触发器，当向SC表插入记录时，若对应的课程号的选课人数小于等于60人，则可插入，否则发出出错信息“该课程选课人数已满！请另选其他课程。” sc_ins_60 2. 建立一触发器，当修改某学生的系别时或插入记录时，判断该系学生人数是否小于等于100，如果是，则允许修改或插入，否则发出出错信息“该系人数已满!” Ins_U

34、p_100,练习,二、触发器综合练习 3. 建立一触发器，当删除student表中某学生记录时，同时删除sc表中所有该学生的选课记录。 Del_s_sc,参考答案,三、触发器综合练习 【1】 CREATE TRIGGER sc_ins_60 ON dbo.sc FOR INSERT AS IF (SELECT COUNT(*) FROM sc,inserted WHERE o=o )=5 BEGIN PRINT 该课程选课人数已满！请另选其他课程 ROLLBACK END,参考答案,三、触发器综合练习 【2】 CREATE TRIGGER Ins_Up_100 ON dbo.student F

35、OR INSERT, UPDATE AS IF (SELECT COUNT(*) FROM student, inserted WHERE student.dno= inserted.dno)=5 BEGIN PRINT 系人数已满，不能插入或修改！ ROLLBACK END,参考答案,三、触发器综合练习 【3】 CREATE TRIGGER Del_s_sc ON dbo.student FOR DELETE AS DELETE sc WHERE sno IN (SELECT sno FROM deleted) 注意 ：要设置student的关系中，设置,7.4 游标,问题的引入 关系数据库

36、管理系统实质是面向集合的，除非使用where 子句来限制只有一条记录被选中 借助于游标来进行面向单条记录的数据处理 游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作 它还提供对基于游标位置而对表中数据进行删除或更新的能力 游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来,7.4 游标,7.4.1游标的基础 游标的概念:游标提供一种机制,使得应用程序每次处理结果集中的一行或一部分 游标的功能 定位在结果集的特定行 从结果集当前位置检索一行或多行 修改结果集当前位置的行 在存储过程或触发器中使用,7.4 游标,

37、7.4.1游标的基础 游标的种类 （1） Transact_SQL 游标 Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上。 （2） API 游标 API 游标支持在OLE DB， ODBC 以及DB_library 中使用游标函数，主要用在服务器上。 （3） 客户游标 客户游标主要是当在客户机上缓存结果集时才使用。,7.4 游标,7.4.2游标的使用 每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序1.DECLARE 游标2.OPEN 游标3.从一个

38、游标中FETCH 信息4.CLOSE 游标,7.4 游标,7.4.2游标的使用 每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序1.DECLARE 游标2.OPEN 游标3.从一个游标中FETCH 信息4.CLOSE 游标和Deallocate游标,7.4 游标,7.4.2游标的使用 声明游标 通常我们使用DECLARE 来声明一个游标声明 游标名字 数据来源（表和列） 选取条件 属性（仅读或可修改） 其语法格式如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY |

39、 UPDATE OF column_name ,.n,7.4 游标,7.4.2游标的使用 示例1定义一个查询student表的标准游标,declare cur_st cursor for select * from student,For readonly-只读游标的定义,For Update-更新游标的定义,7.4 游标,7.4.2游标的使用 打开游标 打开游标游标在声明以后，如果要从游标中读取数据必须打开游标。 OPEN GLOBAL cursor_name | cursor_variable_name 各参数说明如下： GLOBAL 定义游标为一全局游标。 cursor_name 为声明

40、的游标名字。 cursor_variable_name 为游标变量。 注意：在游标被成功打开之后，CURSOR_ROWS 全局变量将用来记录游标内数据行数。,7.4 游标,7.4.2游标的使用 读游标 当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。,7.4 游标,7.4.2游标的使用 关闭游标 使用CLOSE 命令关闭游标 在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁。 CLOSE GLOBAL cursor_name | cursor_variable_name 释放游标 因此常使用DEALLOCATE 命令。通过该命令可以删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。其语法规则为： DEALLOCATE GLOBAL cursor_name | cursor_variable_name,7.4 游标,7.4.2游标的使用 示例2使用游标读取student表中的数据,decla