第9章 数据库编程.ppt

《第9章 数据库编程.ppt》由会员分享，可在线阅读，更多相关《第9章 数据库编程.ppt（65页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第9 9章章 数据库编程数据库编程v9.1 9.1 存储过程存储过程v9.2 9.2 触发器触发器v9.3 9.3 游标游标9.1 9.1 存储过程存储过程v9.1.1 9.1.1 存储过程概念存储过程概念v9.1.2 9.1.2 创建和执行存储过程创建和执行存储过程v9.1.3 9.1.3 查看和维护存储过程查看和维护存储过程9.1.1 9.1.1 存储过程概念存储过程概念vSQLSQL语言是应用程序和数据库之间的主要编语言是应用程序和数据库之间的主要编程接口。程接口。v使用使用SQLSQL语言编写访问数据库的代码时，可语言编写访问数据库的代码时，可用两种方法存储和执行这些代码。用两种方法

2、存储和执行这些代码。在客户端存储代码，并创建向数据库服务器在客户端存储代码，并创建向数据库服务器发送的发送的SQLSQL命令命令将将SQLSQL语句存储在数据库服务器端，然后由应语句存储在数据库服务器端，然后由应用程序调用执行这些用程序调用执行这些SQLSQL语句。语句。v存储在数据库服务器端供客户端调用执行的存储在数据库服务器端供客户端调用执行的SQLSQL语句就是存储过程。语句就是存储过程。存储过程功能存储过程功能v接受输入参数并以输出参数的形式将多接受输入参数并以输出参数的形式将多个值返回给调用者。个值返回给调用者。v包含执行数据库操作的语句。包含执行数据库操作的语句。v将查询语句执行结

3、果返回到客户端内存将查询语句执行结果返回到客户端内存中。中。存储过程优点存储过程优点v允许模块化程序设计允许模块化程序设计只需创建一次并存储在数据库中，就可以在应只需创建一次并存储在数据库中，就可以在应用程序中反复调用该存储过程用程序中反复调用该存储过程v改善性能改善性能在创建存储过程时对代码进行分析和优化，并在创建存储过程时对代码进行分析和优化，并在第一次执行时进行语法检查和编译，将编译在第一次执行时进行语法检查和编译，将编译好的可执行代码存储在内存的一个专门缓冲区好的可执行代码存储在内存的一个专门缓冲区中，以后再执行此存储过程时，只需直接执行中，以后再执行此存储过程时，只需直接执行内存中的

4、可执行代码即可。内存中的可执行代码即可。存储过程优点（续）存储过程优点（续）v减少网络流量减少网络流量只需要一条执行存储过程的代码即可实现，只需要一条执行存储过程的代码即可实现，因此，不再需要在网络中传送大量的代码。因此，不再需要在网络中传送大量的代码。v可作为安全机制使用可作为安全机制使用对于即使没有直接执行存储过程中的语句权对于即使没有直接执行存储过程中的语句权限的用户，也可以授予他们执行该存储过程限的用户，也可以授予他们执行该存储过程的权限。的权限。9.1.2 9.1.2 创建和执行存储过程创建和执行存储过程创建存储过程创建存储过程CREATE PROCEDURE CREATE PROC

5、EDURE 存储过程名存储过程名 参数名参数名 数据类型数据类型 =defaultdefault OUTPUT OUTPUT ,n n AS AS SQL SQL语句语句 n n 执行存储过程执行存储过程 EXEC UTE EXEC UTE 存储过程名存储过程名 实参实参 ,OUTPUT,OUTPUT,n n 示例示例v例例1 1不带参数的存储过程。查询计算机系学生的不带参数的存储过程。查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。考试情况，列出学生的姓名、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade1CREATE PROCEDURE p_S

6、tudentGrade1ASAS SELECT Sname,Cname,Grade SELECT Sname,Cname,Grade FROM Student s INNER JOIN SC FROM Student s INNER JOIN SC ON s.Sno=SC.Sno INNER JOIN Course c ON s.Sno=SC.Sno INNER JOIN Course cON c.Cno=sc.CnoON c.Cno=sc.Cno WHERE Sdept=WHERE Sdept=计算机系计算机系 v执行此存储过程：执行此存储过程：EXEC p_StudentGrade1EXE

7、C p_StudentGrade1示例示例v例例2 2带输入参数的存储过程。查询某个指定系学带输入参数的存储过程。查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。名和考试成绩。CREATE PROCEDURE p_StudentGrade2CREATE PROCEDURE p_StudentGrade2 dept char(20)dept char(20)ASAS SELECT Sname,Sdept,Cname,Grade SELECT Sname,Sdept,Cname,Grade FROM Student s INNER

8、JOIN SC FROM Student s INNER JOIN SC ON s.Sno=SC.Sno INNER JOIN Course c ON s.Sno=SC.Sno INNER JOIN Course c ON c.Cno=SC.Cno ON c.Cno=SC.Cno WHERE Sdept=dept WHERE Sdept=deptv执行存储过程，查询信息管理系学生的修课情况执行存储过程，查询信息管理系学生的修课情况 EXEC p_StudentGrade2 EXEC p_StudentGrade2 信息管理系信息管理系 示例示例v例例3 3带多个输入参数并有默认值的存储过程：查

9、询带多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，课程的默认值为某个学生某门课程的考试成绩，课程的默认值为“VBVB”。CREATE PROCEDURE p_StudentGrade3 CREATE PROCEDURE p_StudentGrade3 sname char(10),cname char(20)=VB sname char(10),cname char(20)=VBAS AS SELECT Sname,Cname,Grade SELECT Sname,Cname,Grade FROM Student s INNER JOIN SC FROM Student s

10、 INNER JOIN SC ON s.Sno=SC.sno INNER JOIN Course c ON s.Sno=SC.sno INNER JOIN Course c ON c.Cno=SC.Cno ON c.Cno=SC.Cno WHERE sname=sname AND cname=cname WHERE sname=sname AND cname=cname参数传递方式参数传递方式v按参数位置传值按参数位置传值实参的排列顺序与创建存储过程时参数实参的排列顺序与创建存储过程时参数定义的顺序一致。定义的顺序一致。EXEC p_StudentGrade3 EXEC p_StudentGr

11、ade3 吴宾吴宾,高等数学高等数学 v按参数名传值按参数名传值在在EXECEXEC语句中，指明定义存储过程时指语句中，指明定义存储过程时指定的参数的名字以及参数的值。定的参数的名字以及参数的值。EXEC p_StudentGrade3 EXEC p_StudentGrade3 sname=sname=吴宾吴宾,cname=,cname=高等数学高等数学 示例示例v例例4 4带输出参数的存储过程。统计全体学带输出参数的存储过程。统计全体学生人数，并将统计结果用输出参数返回。生人数，并将统计结果用输出参数返回。CREATE PROCEDURE p_CountCREATE PROCEDURE p_

12、Count total int total int OUTPUTOUTPUTAs As SELECT total=COUNT(*)FROM StudentSELECT total=COUNT(*)FROM Student v执行此存储过程：执行此存储过程：DECLARE res intDECLARE res intEXEC p_Count res EXEC p_Count res OUTPUTOUTPUTPRINT resPRINT res示例示例v例例5 5带输入参数和输出参数的存储过程。带输入参数和输出参数的存储过程。统计指定课程（课程名）的平均成绩，并统计指定课程（课程名）的平均成绩，并

13、将统计结果用输出参数返回。将统计结果用输出参数返回。CREATE PROC p_AvgGradeCREATE PROC p_AvgGrade cn char(20),cn char(20),avg_grade int avg_grade int OUTPUTOUTPUTASAS SELECT avg_grade=AVG(Grade)FROM SC SELECT avg_grade=AVG(Grade)FROM SC JOIN Course C ON C.Cno=SC.Cno JOIN Course C ON C.Cno=SC.Cno WHERE Cname=cn WHERE Cname=cn执

14、行例执行例5 5存储过程示例存储过程示例v查询查询VBVB课程的平均成绩。课程的平均成绩。DECLARE Avg_Grade intDECLARE Avg_Grade intEXEC p_AvgGrade VB,Avg_Grade EXEC p_AvgGrade VB,Avg_Grade OUTPUTOUTPUTPRINT Avg_GradePRINT Avg_Grade示例示例v例例6 6统计指定系选修指定课程（课程名）的学生统计指定系选修指定课程（课程名）的学生人数和考试平均成绩，并用输出参数返回选课人数人数和考试平均成绩，并用输出参数返回选课人数和平均成绩。和平均成绩。CREATE PR

15、OC p_CountAvgCREATE PROC p_CountAvg dept varchar(20),cn varchar(20),dept varchar(20),cn varchar(20),cnt int cnt int OUTPUTOUTPUT,avg_grade int,avg_grade int OUTPUTOUTPUTASAS SELECT cnt=COUNT(*),SELECT cnt=COUNT(*),avg_grade=AVG(Grade)avg_grade=AVG(Grade)FROM SC JOIN Course C ON C.Cno=SC.Cno FROM SC

16、JOIN Course C ON C.Cno=SC.Cno JOIN Student S ON S.Sno=SC.Sno JOIN Student S ON S.Sno=SC.Sno WHERE Sdept=dept AND Cname=cn WHERE Sdept=dept AND Cname=cn执行例执行例5 5存储过程示例存储过程示例v查询计算机系修查询计算机系修“高等数学高等数学”的学生人数的学生人数和考试平均成绩。和考试平均成绩。DECLARE Count int,AvgGrade intDECLARE Count int,AvgGrade intEXEC p_CountAvg E

17、XEC p_CountAvg 计算机系计算机系,高等数学高等数学,Count Count OUTPUTOUTPUT,AvgGrade,AvgGrade OUTPUTOUTPUTSELECT Count AS SELECT Count AS 人数人数,AvgGrade AS AvgGrade AS 平均成绩平均成绩修改数据示例修改数据示例v例例7 7将指定课程（课程号）的学分增加将指定课程（课程号）的学分增加指定的分数。指定的分数。CREATE PROC p_UpdateCreditCREATE PROC p_UpdateCredit cno varchar(10),inc int cno va

18、rchar(10),inc intASAS UPDATE Course SET Credit=UPDATE Course SET Credit=Credit+inc Credit+inc WHERE Cno=cno WHERE Cno=cno删除数据示例删除数据示例v例例8 8删除指定课程（课程名）中考试成删除指定课程（课程名）中考试成绩不及格学生的此门课程的修课记录。绩不及格学生的此门课程的修课记录。CREATE PROC p_DeleteSCCREATE PROC p_DeleteSC cn varchar(20)cn varchar(20)ASAS DELETE FROM SC WHER

19、E Grade 60 DELETE FROM SC WHERE Grade=80 WHERE Sdept=dept AND Grade=803.3.删除存储过程删除存储过程v删除存储过程语法删除存储过程语法DROP PROC|PROCEDURE DROP PROC|PROCEDURE 存储过程名存储过程名 ,n n v例例1111删除删除p_StudentGrade1p_StudentGrade1存储过程。存储过程。DROP PROC p_StudentGrade1DROP PROC p_StudentGrade19.2 9.2 触发器触发器v9.2.1 9.2.1 创建触发器创建触发器v9.

20、2.2 9.2.2 后触发型触发器后触发型触发器v9.2.3 9.2.3 前触发型触发器前触发型触发器v9.2.4 9.2.4 查看和维护触发器查看和维护触发器触发器基本概念触发器基本概念v触发器是一段由对数据的更改操作引发触发器是一段由对数据的更改操作引发的自动执行的代码，这些更改操作包括：的自动执行的代码，这些更改操作包括：UPDATEUPDATE、INSERTINSERT和和DELETEDELETE。v触发器通常用于保证业务规则和数据完触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和业务规则，方法来实现复杂的

21、处理逻辑和业务规则，增强了数据完整性约束的功能。增强了数据完整性约束的功能。9.2.1 9.2.1 创建触发器创建触发器CREATE TRIGGER CREATE TRIGGER 触发器名触发器名ON ON 表名表名 FOR|AFTER|INSTEAD OF FOR|AFTER|INSTEAD OF INSERT,DELETE,INSERT,DELETE,UPDATE UPDATE AS AS SQL SQL 语句语句创建触发器注意事项创建触发器注意事项v在一个表上可以建立多个名称不同、类型在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由一个或多各异的触发器，每个触发器可由一个

22、或多个数据更改语句引发。个数据更改语句引发。v对于对于AFTERAFTER型的触发器，可以在同一种操作型的触发器，可以在同一种操作上建立多个触发器；上建立多个触发器；v对于对于INSTEAD OFINSTEAD OF型的触发器，在同一种操型的触发器，在同一种操作上只能建立一个触发器。作上只能建立一个触发器。v创建和更改数据库以及数据库对象的语句、创建和更改数据库以及数据库对象的语句、所有的所有的DROPDROP语句都不允许在触发器中使用。语句都不允许在触发器中使用。触发器中的两个临时工作表触发器中的两个临时工作表vINSERTEDINSERTED表和表和DELETEDDELETED表：结构同建

23、立触表：结构同建立触发器的表的结构相同，而且只能用在触发器的表的结构相同，而且只能用在触发器代码中。发器代码中。vINSERTEDINSERTED：保存了：保存了INSERTINSERT操作中新插入操作中新插入的数据和的数据和UPDATEUPDATE操作中更新后的数据；操作中更新后的数据；vDELETEDDELETED：保存了：保存了DELETEDELETE操作中删除的数操作中删除的数据和据和UPDATEUPDATE操作中更新前的数据。操作中更新前的数据。说明说明v如果在某个表上即定义了完整性约束，如果在某个表上即定义了完整性约束，又定义了触发器，则是先执行完整性约又定义了触发器，则是先执行完

24、整性约束检查，符合约束后才执行数据操作语束检查，符合约束后才执行数据操作语句，然后才能引发触发器执行。句，然后才能引发触发器执行。v因此完整性约束的检查总是先于触发器因此完整性约束的检查总是先于触发器的执行。的执行。9.2.2 9.2.2 后触发型触发器后触发型触发器v只有在引发触发器执行的语句中指定的操只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。成功完成后，才执行触发器。执行到引发触发器操作的语句执行该语句执行触发器示例示例v例例1 1查看后触发器对数据的影响。查看后触发器对数据的影响。CREATE T

25、ABLE t1(c1 int,c2 char(4)CREATE TABLE t1(c1 int,c2 char(4)设设t1t1表现包含如下表现包含如下2 2行数据：行数据：（1 1，a a）（2 2，bb）CREATE Trigger tri_AfterCREATE Trigger tri_After ON t1 AFTER INSERT ON t1 AFTER INSERTASAS SELECT*FROM t1 SELECT*FROM t1 SELECT*FROM INSERTED SELECT*FROM INSERTEDvINSERT INTO t1 VALUES(100,test)IN

26、SERT INTO t1 VALUES(100,test)(a)t1(a)t1表中数据表中数据(b)INSERTED(b)INSERTED表中数据表中数据示例示例v例例2 2限制列取值范围。限定限制列取值范围。限定CourseCourse表中表中SemesterSemester的取值范围为的取值范围为1 1 1010。CREATE Trigger tri_SemesterCREATE Trigger tri_Semester ON Course AFTER INSERT,UPDATE ON Course AFTER INSERT,UPDATEASAS IF EXISTS(SELECT*FROM

27、 INSERTED IF EXISTS(SELECT*FROM INSERTED -判断是否违反约束判断是否违反约束 WHERE Semester NOT BETWEEN 1 AND 10)WHERE Semester NOT BETWEEN 1 AND 10)ROLLBACK ROLLBACK -撤消操作撤消操作注意注意v触发器与引发触发器执行的操作共同构触发器与引发触发器执行的操作共同构成了一个事务，这个事务是系统隐含建成了一个事务，这个事务是系统隐含建立的。立的。v事务的开始是引发触发器执行的操作，事务的开始是引发触发器执行的操作，事务的结束是触发器的结束。事务的结束是触发器的结束。示例

28、示例v例例3 3实现业务规则。限制每个学期开设的课实现业务规则。限制每个学期开设的课程总数不能超过程总数不能超过1010门，如果超过了门，如果超过了1010门，则门，则给出提示信息：本学期课程太多。给出提示信息：本学期课程太多。CREATE Trigger tri_TotalCnoCREATE Trigger tri_TotalCno ON Course AFTER INSERT ON Course AFTER INSERTASAS IF(SELECT COUNT(*)FROM Course C IF(SELECT COUNT(*)FROM Course C JOIN INSERTED I O

29、N I.Semester=C.Semester)10 JOIN INSERTED I ON I.Semester=C.Semester)10 BEGIN BEGIN PRINT PRINT 本学期课程太多本学期课程太多!ROLLBACK ROLLBACK END END示例示例v例例4.4.实现业务规则。在实现业务规则。在SCSC表中，不能删除考试表中，不能删除考试成绩不及格学生的该门课程的考试记录。成绩不及格学生的该门课程的考试记录。CREATE Trigger tri_DeleteSCCREATE Trigger tri_DeleteSC ON SC AFTER DELETE ON SC

30、AFTER DELETEASAS IF EXISTS(SELECT*FROM DELETED WHERE Grade 60)IF EXISTS(SELECT*FROM DELETED WHERE Grade 60)BEGIN BEGIN PRINT PRINT 不能删除成绩不及格的考试记录不能删除成绩不及格的考试记录!ROLLBACK ROLLBACK END END示例示例v例例5.5.实现业务规则。在实现业务规则。在SCSC表中，不能将不及表中，不能将不及格的考试成绩改为及格。格的考试成绩改为及格。CREATE Trigger tri_UpdateGradeCREATE Trigger t

31、ri_UpdateGrade ON SC AFTER UPDATE ON SC AFTER UPDATEASAS IF EXISTS(SELECT*FROM INSERTED I IF EXISTS(SELECT*FROM INSERTED I JOIN DELETED D ON I.Sno=D.Sno AND I.Cno=D.Cno JOIN DELETED D ON I.Sno=D.Sno AND I.Cno=D.Cno WHERE D.Grade=60)WHERE D.Grade=60)BEGIN BEGIN PRINT PRINT 不能将不及格成绩改为及格不能将不及格成绩改为及格!RO

32、LLBACK ROLLBACK END END9.2.3 9.2.3 前触发型触发器前触发型触发器v在前触发器中，指定执行触发器而不是执在前触发器中，指定执行触发器而不是执行引发触发器执行的行引发触发器执行的SQLSQL语句，从而替代引语句，从而替代引发语句的操作。发语句的操作。v在一张表上，每个在一张表上，每个INSERTINSERT、UPDATEUPDATE或或DELETEDELETE操作最多只能定义一个操作最多只能定义一个INSTEAD OFINSTEAD OF触发器。触发器。执行到引发触发器操作的语句执行到引发触发器操作的语句执行触发器执行触发器示例示例v例例6 6查看前触发器对数据的

33、影响查看前触发器对数据的影响设设t1t1表现包含如下表现包含如下2 2行数据：行数据：（1 1，a a）（2 2，b b）CREATE Trigger tri_InsteadCREATE Trigger tri_Instead ON t1 INSTEAD OF INSERT ON t1 INSTEAD OF INSERTASAS SELECT*FROM t1 SELECT*FROM t1 SELECT*FROM INSERTED SELECT*FROM INSERTED(a)t1(a)t1表中数据表中数据(b)INSERTED(b)INSERTED表中数据表中数据示例示例例例7 7用前触发器实

34、现限定用前触发器实现限定CourseCourse表中表中SemesterSemester的取值范围为的取值范围为1 1 1010的约束。的约束。CREATE CREATE Trigger tri_Semester1Trigger tri_Semester1 ON Course INSTEAD OF INSERT,UPDATE ON Course INSTEAD OF INSERT,UPDATE AS AS IF NOT EXISTS(SELECT*FROM INSERTED IF NOT EXISTS(SELECT*FROM INSERTED -判断是否符合约束要求判断是否符合约束要求 WHE

35、RE Semester NOT BETWEEN 1 AND 10)WHERE Semester NOT BETWEEN 1 AND 10)INSERT INTO Course SELECT*FROM INSERTED INSERT INTO Course SELECT*FROM INSERTED -重做操作重做操作示例示例v例例8 8用前触发器实现限制每个学期开设的用前触发器实现限制每个学期开设的课程总数不能超过课程总数不能超过1010门的约束。门的约束。CREATE Trigger tri_TotalCno1CREATE Trigger tri_TotalCno1 ON Course INS

36、TEAD OF INSERT ON Course INSTEAD OF INSERTASAS IF(SELECT COUNT(*)FROM Course C IF(SELECT COUNT(*)FROM Course C JOIN INSERTED I JOIN INSERTED I ON I.Semester=C.Semester)10 ON I.Semester=C.Semester)10 INSERT INTO Course INSERT INTO Course SELECT*FROM INSERTED SELECT*FROM INSERTED示例示例v例例9 9用前触发器实现不能删除考

37、试成绩不及用前触发器实现不能删除考试成绩不及格学生的该门课程的考试记录约束。格学生的该门课程的考试记录约束。CREATE Trigger tri_DeleteSC1CREATE Trigger tri_DeleteSC1 ON SC INSTEAD OF DELETE ON SC INSTEAD OF DELETEASAS IF NOT EXISTS(SELECT*FROM DELETED IF NOT EXISTS(SELECT*FROM DELETED WHERE Grade 60)WHERE Grade=80 WHERE Cname=cname AND Grade=80示例示例3 3代码

38、（续）代码（续）OPEN C2OPEN C2 FETCH NEXT FROM C2 INTO sname,sex,dept,FETCH NEXT FROM C2 INTO sname,sex,dept,gradegrade WHILE FETCH_STATUS=0 WHILE FETCH_STATUS=0 BEGIN BEGIN PRINT sname+sex+dept+cast(grade as PRINT sname+sex+dept+cast(grade as char(4)char(4)FETCH NEXT FROM C2 INTO sname,sex,dept,FETCH NEXT FROM C2 INTO sname,sex,dept,gradegrade END END CLOSE C2 CLOSE C2 DEALLOCATE C2 DEALLOCATE C2 PRINT PRINT FETCH NEXT FROM C1 INTO cname FETCH NEXT FROM C1 INTO cnameENDENDCLOSE C1CLOSE C1DEALLOCATE C1DEALLOCATE C1