第9章数据库编程1

1、数据库原理与应用（第2版）人民邮电出版社第第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命令命令, ,如在如在C#C#，JavaJava等编程语言中嵌入等编程语言中嵌入SQLSQL语句语句 将将SQLSQL语句存储在数据库语句存储在数据库服务器端服务器端（具体数据库的一（具体数据库的一个对象），然后由应用程序调用执行这些个对象），然后由应用程序调用执行这些SQLSQL语句。语句。v存储在数据库存储在数据库服务器端服务器端供客户端调用执行的供客户端调用执行的SQLSQL语句就是存储过程。语句就是存储过程。存储过程功能存储过

3、程功能v接受输入参数并以输出参数的形式将多接受输入参数并以输出参数的形式将多个值返回给调用者。个值返回给调用者。v包含执行数据库操作的语句。包含执行数据库操作的语句。 v将查询语句执行结果返回到客户端内存将查询语句执行结果返回到客户端内存中。中。存储过程优点存储过程优点v允许模块化程序设计允许模块化程序设计 只需创建一次并存储在数据库中，就可以在应只需创建一次并存储在数据库中，就可以在应用程序中反复调用该存储过程用程序中反复调用该存储过程v改善性能改善性能 在创建存储过程时对代码进行分析和优化，并在创建存储过程时对代码进行分析和优化，并在第一次执行时进行语法检查和编译，将编译在第一次执行时进行

4、语法检查和编译，将编译好的可执行代码存储在内存的一个专门缓冲区好的可执行代码存储在内存的一个专门缓冲区中，以后再执行此存储过程时，只需直接执行中，以后再执行此存储过程时，只需直接执行内存中的可执行代码即可。内存中的可执行代码即可。存储过程优点（续）存储过程优点（续）v减少网络流量减少网络流量 只需要一条执行存储过程的代码即可实现，只需要一条执行存储过程的代码即可实现，因此，不再需要在网络中传送大量的代码。因此，不再需要在网络中传送大量的代码。v可作为安全机制使用可作为安全机制使用 对于即使没有直接执行存储过程中的语句权对于即使没有直接执行存储过程中的语句权限的用户，也可以授予他们执行该存储过程

5、限的用户，也可以授予他们执行该存储过程的权限。的权限。9.1.2 9.1.2 创建和执行存储过程创建和执行存储过程创建存储过程创建存储过程CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名 参数名参数名 数据类型数据类型 =defaultdefaultOUTPUT OUTPUT , , n n AS AS SQL SQL语句语句 n n 执行存储过程执行存储过程 EXEC UTE EXEC UTE 存储过程名存储过程名 实参实参 , OUTPUT , n , OUTPUT , n 不带参数的存储过程不带参数的存储过程v例例1 1查询计算机系学生的考试情况，

6、列出学生的查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。姓名、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade1CREATE PROCEDURE p_StudentGrade1ASAS 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 Cour

7、se cON c.Cno = sc.CnoON c.Cno = sc.Cno WHERE Sdept = WHERE Sdept = 计算机系计算机系 v执行此存储过程：执行此存储过程：EXEC p_StudentGrade1EXEC p_StudentGrade1带输入参数的存储过程带输入参数的存储过程v例例2 2查询某个指定系学生的考试情况，列出学生查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程的姓名、所在系、课程名和考试成绩。名和考试成绩。CREATE PROCEDURE p_StudentGrade2CREATE PROCEDURE p_StudentGrade2 dept

8、 char(20)dept char(20)ASAS SELECT Sname, Sdept, Cname, Grade SELECT Sname, Sdept, 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 c ON c.Cno = SC.Cno ON c.Cno = SC.Cno WHERE Sdept = WHERE Sdept = deptdep

9、tv执行存储过程，查询信息管理系学生的修课情况执行存储过程，查询信息管理系学生的修课情况 EXEC p_StudentGrade2 EXEC p_StudentGrade2 信息管理系信息管理系 带多个输入参数并有默认值的存储过程带多个输入参数并有默认值的存储过程v例例3 3查询某个学生某门课程的考试成绩，课程的默查询某个学生某门课程的考试成绩，课程的默认值为认值为“VBVB”。CREATE PROCEDURE p_StudentGrade3 CREATE PROCEDURE p_StudentGrade3 sname char(10), sname char(10), cname char(

10、20) = VBcname char(20) = VBAS AS 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 c ON c.Cno = SC.Cno ON c.Cno = SC.Cno WHERE sname = WHERE sname = sname AND cname

11、 = sname AND cname = cnamecname参数传递方式参数传递方式v按参数位置传值按参数位置传值 实参的排列顺序与创建存储过程时参数实参的排列顺序与创建存储过程时参数定义的顺序一致。定义的顺序一致。EXEC p_StudentGrade3 EXEC p_StudentGrade3 吴宾吴宾, , 高等数学高等数学 v按参数名传值按参数名传值 在在EXECEXEC语句中，指明定义存储过程时指语句中，指明定义存储过程时指定的参数的名字以及参数的值。定的参数的名字以及参数的值。EXEC p_StudentGrade3 EXEC p_StudentGrade3 sname = sn

12、ame = 吴宾吴宾, , cname = cname = 高等数学高等数学 存储过程还可以实现增删改存储过程还可以实现增删改v例例7 7将指定课程（课程号）的学分增加将指定课程（课程号）的学分增加指定的分数。指定的分数。CREATE PROC p_UpdateCreditCREATE PROC p_UpdateCredit cno varchar(10), cno varchar(10), inc intinc intASAS UPDATE Course SET Credit = UPDATE Course SET Credit = Credit + Credit + incinc WHER

13、E Cno = WHERE Cno = cnocno带输出参数的存储过程带输出参数的存储过程v例例4 4统计全体学生人数，并将统计结果用统计全体学生人数，并将统计结果用输出参数返回。输出参数返回。CREATE PROCEDURE p_CountCREATE PROCEDURE p_Count total int total int OUTPUTOUTPUTAs As SELECT SELECT total = COUNT(total = COUNT(* *) FROM Student) FROM Student v执行此存储过程：执行此存储过程：DECLARE DECLARE res intr

14、es intEXEC p_Count EXEC p_Count res res OUTPUTOUTPUTPRINT PRINT resres删除数据示例删除数据示例v例例8 8删除指定课程（课程名）中考试成删除指定课程（课程名）中考试成绩不及格学生的此门课程的修课记录。绩不及格学生的此门课程的修课记录。CREATE PROC p_DeleteSCCREATE PROC p_DeleteSC cn varchar(20)cn varchar(20)ASAS DELETE FROM SC WHERE Grade 60 DELETE FROM SC WHERE Grade = 80dept AND

15、Grade = 803.3.删除存储过程删除存储过程DROPDROP PROC | PROCEDURE PROC | PROCEDURE 存储过程名存储过程名 , n , n 例例1111删除删除p_StudentGrade1p_StudentGrade1存储过程。存储过程。 DROP PROC p_StudentGrade1DROP PROC p_StudentGrade1触发器基本概念触发器基本概念v触发器是一段由对数据的更改操作引发触发器是一段由对数据的更改操作引发的自动执行的代码，这些更改操作包括的自动执行的代码，这些更改操作包括：UPDATEUPDATE、INSERTINSERT和和

16、DELETEDELETE。v触发器通常用于保证业务规则和数据完触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和业务规则方法来实现复杂的处理逻辑和业务规则，增强了数据完整性约束的功能。，增强了数据完整性约束的功能。用于限制列的取值范围用于限制列的取值范围v例：限定例：限定CourseCourse表中表中SemesterSemester的取值范围的取值范围为为1 1 1010。CREATE Trigger tri_SemesterCREATE Trigger tri_Semester ON Course ON Cour

17、se AFTER INSERT, UPDATEAFTER INSERT, UPDATEASAS IF EXISTSIF EXISTS(SELECT (SELECT * * FROM INSERTED FROM INSERTED - - 判断是否违反约束判断是否违反约束 WHERE Semester NOT BETWEEN 1 AND 10) WHERE Semester NOT BETWEEN 1 AND 10) ROLLBACKROLLBACK - - 撤消操作撤消操作9.2.1 9.2.1 创建触发器创建触发器CREATE TRIGGER CREATE TRIGGER 触发器名触发器名ON

18、 ON 表名表名 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT , DELETE , INSERT , DELETE , UPDATE UPDATE AS AS SQL SQL 语句语句触发器中的两个临时工作表触发器中的两个临时工作表vINSERTEDINSERTED：保存了：保存了INSERTINSERT操作中新插入操作中新插入的数据和的数据和UPDATEUPDATE操作中操作中更新后更新后的数据；的数据；vDELETEDDELETED：保存了：保存了DELETEDELETE操作中删除的数操作中删除的数据和据和UPDATEU

19、PDATE操作中操作中更新前更新前的数据。的数据。vINSERTEDINSERTED表和表和DELETEDDELETED表的结构同建立触表的结构同建立触发器的表的结构相同，且只能用在触发发器的表的结构相同，且只能用在触发器代码中。器代码中。9.2.2 9.2.2 后触发型触发器后触发型触发器v只有在引发触发器执行的语句中指定的操只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。成功完成后，才执行触发器。执行到引发触发器操作的语句执行到引发触发器操作的语句执行该语句执行该语句执行触发器执行触发器查看后触发器对数据

20、的影响查看后触发器对数据的影响v例例1 1设有设有t1t1表表 CREATE TABLE t1 (c1 int, c2 char(4) CREATE TABLE t1 (c1 int, c2 char(4)现现t1t1表包含如下表包含如下2 2行数据：行数据：（1 1，aa） （2 2，bb）创建如下触发器：创建如下触发器：CREATE Trigger tri_AfterCREATE Trigger tri_After ON t1 AFTER INSERT ON t1 AFTER INSERTASAS SELECT SELECT * * FROM t1 FROM t1 SELECT SELEC

21、T * * FROM INSERTED FROM INSERTED执行执行INSERT INTO t1 VALUES(100,test)INSERT INTO t1 VALUES(100,test)(a)t1(a)t1表中数据表中数据(b)INSERTED(b)INSERTED表中数据表中数据实现业务规则实现业务规则v例例4. 4. 在在SCSC表中，规定不能删除考试成绩不及格表中，规定不能删除考试成绩不及格学生的该门课程的考试记录。学生的该门课程的考试记录。CREATE Trigger tri_DeleteSCCREATE Trigger tri_DeleteSC ON SC AFTER D

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

23、teGradeCREATE Trigger tri_UpdateGrade ON SC AFTER UPDATE ON SC AFTER UPDATEASAS IF EXISTS(SELECT IF EXISTS(SELECT * * FROM INSERTED I 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

24、PRINT 不能将不及格成绩改为及格不能将不及格成绩改为及格! ROLLBACK ROLLBACK END END9.2.3 9.2.3 前触发型触发器前触发型触发器v在前触发器中，指定执行触发器而不是执在前触发器中，指定执行触发器而不是执行引发触发器执行的行引发触发器执行的SQLSQL语句，从而替代引语句，从而替代引发语句的操作。发语句的操作。v在一张表上，每个在一张表上，每个INSERTINSERT、UPDATEUPDATE或或DELETEDELETE操作最多只能定义一个操作最多只能定义一个INSTEAD OFINSTEAD OF触发器。触发器。执行到引发触发器操作的语句执行到引发触发器操

25、作的语句执行触发器执行触发器前触发器对数据的影响前触发器对数据的影响v例例6 6设设t1t1表包含如下表包含如下2 2行数据：行数据： （1 1，aa）（2 2，bb）创建触发器：创建触发器：CREATE Trigger tri_InsteadCREATE Trigger tri_Instead ON t1 INSTEAD OF INSERT ON t1 INSTEAD OF INSERTASAS SELECT SELECT * * FROM t1 FROM t1 SELECT SELECT * * FROM INSERTED FROM INSERTED(a)t1(a)t1表中数据表中数据(b

26、)INSERTED(b)INSERTED表中数据表中数据执执行行INSERT INTO t1 VALUES(100,test)9.2.4 9.2.4 查看和维护触发器查看和维护触发器v1 1查看已定义的触发器查看已定义的触发器v2 2修改触发器修改触发器v3 3删除触发器删除触发器1 1查看已定义的触发器查看已定义的触发器v在在SSMSSSMS工具的工具的“对象资源管理中对象资源管理中”，展，展开要查看触发器的数据库（假设这里展开要查看触发器的数据库（假设这里展开的是开的是StudentsStudents），），v然后展开数据库下的然后展开数据库下的“表表”节点，展开节点，展开某个定义了触发器

27、的表，某个定义了触发器的表，v最后再展开表下的最后再展开表下的“触发器触发器”节点，即节点，即可看到在该表上定义的全部触发器。可看到在该表上定义的全部触发器。2 2修改触发器修改触发器v修改触发器代码的语句为：修改触发器代码的语句为： ALTER TRIGGERALTER TRIGGERv其语法格式与定义触发器的其语法格式与定义触发器的CRETAE CRETAE TRIGGERTRIGGER语句一样，只是将语句一样，只是将CREATECREATE改为了改为了ALTERALTER。3 3删除触发器删除触发器v删除触发器语句的语法格式为：删除触发器语句的语法格式为： DROP TRIGGER DR

28、OP TRIGGER 触发器名触发器名 , n , n v例例1010删除删除tri_DeleteSCtri_DeleteSC触发器。触发器。DROP TRIGGER tri_DeleteSCDROP TRIGGER tri_DeleteSC9.3 9.3 游标游标v9.3.1 9.3.1 游标概念游标概念v9.3.2 9.3.2 使用游标使用游标v9.3.3 9.3.3 游标示例游标示例9.3.1 9.3.1 游标概念游标概念v游标（游标（cursorcursor）包括如下两部分内容：）包括如下两部分内容： 游标结果集：由游标结果集：由SELECTSELECT语句返回的查询结果。语句返回的查

29、询结果。 游标当前行指针：指向结果集中某一行的指针游标当前行指针：指向结果集中某一行的指针游标当前游标当前行指针行指针游标结游标结果集果集游标示例游标示例定义查询姓定义查询姓“王王”的学生姓名和所在系的游标，并输出结果。的学生姓名和所在系的游标，并输出结果。DECLARE sn CHAR(10), dept VARCHAR(20) - 声明变量声明变量DECLARE Sname_cursor CURSOR FOR - 声明游标声明游标SELECT Sname, Sdept FROM StudentWHERE Sname LIKE 王王%OPEN Sname_cursor - 打开游标打开游标F

30、ETCH NEXT FROM Sname_cursor INTO sn, dept - 取第一行数取第一行数- 通过检查通过检查FETCH_STATUS的值判断是否还有可提取的数据的值判断是否还有可提取的数据WHILE FETCH_STATUS = 0BEGIN PRINT sn + dept FETCH NEXT FROM Sname_cursor INTO sn, deptENDCLOSE Sname_cursor DEALLOCATE Sname_cursor9.3.2 9.3.2 游标的使用游标的使用是是否否声声明游明游标标打打开开游游标标提取提取数数据据处处理完成？理完成？关闭关闭游

31、游标标释释放游放游标标1.1.声明游标声明游标DECLAREDECLARE cursor_name cursor_name CURSOR CURSOR FORWARD_ONLY | SCROLL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | STATIC | KEYSET | DYNAMIC | FAST_FORWARD FAST_FORWARD FOR select_statement FOR select_statement FOR UPDATE OF column_name ,.n FOR UPDATE OF column_name

32、,.n 声明游标参数说明声明游标参数说明vFORWARD_ONLYFORWARD_ONLY：指定游标只能从第一行滚动到最：指定游标只能从第一行滚动到最后一行。后一行。vFAST_FORWARDFAST_FORWARD：只向前的游标。：只向前的游标。vSTATICSTATIC：静态游标。进行提取操作时返回的数据：静态游标。进行提取操作时返回的数据不反映游标打开后用户对表所做的修改。不反映游标打开后用户对表所做的修改。vDYNAMICDYNAMIC：动态游标。该类游标反映在结果集中做：动态游标。该类游标反映在结果集中做的所有更改。的所有更改。vKEYSETKEYSET：键集游标。指定当游标打开时，

33、游标中：键集游标。指定当游标打开时，游标中行的成员和顺序已经固定。行的成员和顺序已经固定。vUPDATE OF column_name UPDATE OF column_name ，.n.n：定义游标：定义游标内可更新的列。内可更新的列。2.2.打开游标打开游标OPEN cursor_nameOPEN cursor_name v只能打开已声明但还没有打开的游标。只能打开已声明但还没有打开的游标。3 3提取数据提取数据FETCHFETCH NEXT | PRIOR | FIRST | LAST NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | ABSOLUTE

34、 n | RELATIVE n | RELATIVE n FROM FROM cursor_name INTO cursor_name INTO variable_name variable_name ,.n ,.n 提取数据参数说明提取数据参数说明vNEXTNEXT：默认选项。返回紧跟在当前行之后的数据：默认选项。返回紧跟在当前行之后的数据行，且当前行递增为结果行。行，且当前行递增为结果行。vPRIORPRIOR：返回紧临当前行前面的数据行，且当前：返回紧临当前行前面的数据行，且当前行递减为结果行。行递减为结果行。vFIRSTFIRST：返回游标中的第一行并将其作为当前行：返回游标中的第一行并将其作为当前行vLASTLAST：返回游标中的最后一行并将其作为当前行：返回游标中的最后一行并将其作为当前行vINTO INTO variable_name variable_name ，.n.n：将提取的列数据：将提取的列数据存放到局部变量中。存放到局部变量中。vABSOLUTE nABSOLUTE n：如果：如果n n为正数，返回从游标开始的第为正数，返回从游标开始的第n n行；反之返回从游标最后一行开始之前的第行；反之返回从游标最后一行开始之前的第 n n 行行FETCH_STATUSFETCH_STATUSv可以使用可以使用FETCH_STATUS