《高级数据库》课程教学课件（讲稿）Chapter 6 存储过程

Chapter6存储过程6.1存储过程概念SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户或应用程序通过指定存储过程的名字并给出参数来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。6.1.1存储过程优点■存储过程允许标准组件式编程存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。■存储过程能够实现较快的执行速度如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。■存储过程能够保证数据的安全存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问保证数据的安全。■自动完成需要预先执行的任务存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。6.1.2存储过程分类SQLServer中存储过程可以分为以下两类■系统存储过程系统存储过程是由SQLServer提供的存储过程，可以作为命令执行。系统过程以"sp_"开头，在Master数据库中创建并保存在该数据库中，为数据库管理者所有。■用户存储过程用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQLServer2008中，可以使用SQLServer对象资源管理器或T-SQL语言中的CREATEPROCEDURE命令来创建存储过程

Chapter 6 存储过程 存储过程概念 SQL Server 提供了一种方法，它可以将一些固定的操作集中起来由 SQL Server 数据库 服务器来完成，以实现某个任务，这种方法就是存储过程。 存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储 在数据库中。用户或应用程序通过指定存储过程的名字并给出参数来执行它，而且允许用户 声明变量、有条件执行以及其它强大的编程功能。 存储过程优点 ◼ 存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因 为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 ◼ 存储过程能够实现较快的执行速度 如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行，那么存储过程要 比批处理的执行速度快很多。 ◼ 存储过程能够保证数据的安全 存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权 限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问， 保证数据的安全。 ◼ 自动完成需要预先执行的任务 存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便 了用户的使用，可以自动完成一些需要预先执行的任务。 存储过程分类 SQL Server 中存储过程可以分为以下两类： ◼ 系统存储过程 系统存储过程是由 SQL Server 提供的存储过程，可以作为命令执行。系统过程以“sp_" 开头，在 Master 数据库中创建并保存在该数据库中，为数据库管理者所有。 ◼ 用户存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息） 的存储过程。在 SQL Server 2008 中，可以使用 SQL Server 对象资源管理器或 T-SQL 语言中 的 CREATE PROCEDURE 命令来创建存储过程

6.22存储过程的操作6.2.1创建存储过程可以使用T-SQL语言中的CREATEPROCEDURE命令来创建存储过程。语法格式如下:CREATEPROC]PROCEDURE】[schema_name.]procedure_name[number]/*定义过程名*//*定义参数的类型*/[@parameter[type_schemaname.]datatype][VARYING] [= default ][OUT]OUTPUT ] [READONLY] J[ .. ]/*定义参数的属性*ASs[:][...n]*执行的操作*/[:]参数说明：VARYING：指定由OUTPUT参数支持的结果集，仅应用于游标型参数。OUTPUT：用来声明存储过程的返回参数，其值可以返回给调用它的EXECUTE语句。6.2.2执行存储过程通过T-SQL语句中的EXECUTE命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。语法格式[{EXEC|EXECUTE]]{[@return status=]（module_name[number]l@module_name_var)[[@parameter=]value|@variable[OUTPUT]I[DEFAULT]][,..n]1[]6.2.3删除存储过程当不再使用一个存储过程时，就要把它从数据库中删除。使用DROPPROCEDURE语句可永久地删除存储过程。其语法格式如下DROP（PROC|PROCEDURE[schema_name.]procedure[，.n]6.3存储过程设计6.3.1不带参数的存储过程【例6-1】设计一个不带参数的存储过程：查询计算机专业学生的基本情况。IF EXISTS (SELECT *FROM sySobjects WHERE name='Proc 1'ANDtype-'p")DROPPROCEDUREPrOCGOCREATE PROCEDURE PrOCAS

存储过程的操作 创建存储过程 可以使用 T-SQL 语言中的 CREATE PROCEDURE 命令来创建存储过程。语法格式如 下： CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] /*定义过程名*/ [ { @parameter [ type_schema_name. ] data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ][ ，.n ] /*定义参数的属性*/ AS [;][ .n ] /*执行的操作*/ [;] 参数说明： ◼ VARYING：指定由 OUTPUT 参数支持的结果集，仅应用于游标型参数。 ◼ OUTPUT：用来声明存储过程的返回参数，其值可以返回给调用它的 EXECUTE 语句。 执行存储过程 通过 T-SQL 语句中的 EXECUTE 命令可以执行一个已定义的存储过程，EXEC 是 EXECUTE 的简写。语法格式： [ { EXEC | EXECUTE } ] { [ @return_status = ] { module_name [ ;number ] | @module_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] }] [ ，.n ] } [;] 删除存储过程 当不再使用一个存储过程时，就要把它从数据库中删除。使用 DROP PROCEDURE 语 句可永久地删除存储过程。其语法格式如下： DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ，.n ] 存储过程设计 不带参数的存储过程 【例 6-1】设计一个不带参数的存储过程：查询计算机专业学生的基本情况。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_1' AND type='P') DROP PROCEDURE Proc_1 GO CREATE PROCEDURE Proc_1 AS

SELECT *FROMXSBWHERE专业计算机GO存储过程定义后，执行存储过程P_2:EXECUTE dbo.Proc1GO6.3.2带参数的存储过程【例6-2】设计一个带参数的存储过程：查询某学生指定课程（模糊查询）的学分和成绩。IFEXISTS (SELECT * FROMsysobjeots WHERE name-'Proc 2ANDtype-'p')DROP PROCEDURE PrOC2GOCREATE PROCEDURE Proc 2@idchar(6),@cname Varchar(20)ASSELECTXSB.学号，XSB.姓名，KCB.课程编号，KCB.课程名称，KCB.学分，CJB.成绩FROMXSB,KCB,CJBWHEREXSB.学号-CJB.学号AND KCB.课程编号-CJB.课程编号AND XSB.学号-@idANDKCB.课程名称LIKE%+@cname+'GO存储过程定义后，执行存储过程P_2：EXECUTEdbo.Proc2*081101计算机GO或者：EXECUTEdbo.Proc_2@cname-计算机',@id-"081101GO或者：DECLARE @xhchar(8),@kc:char(20)SET @xh=-081101SET@kc=计算机基础EXECUTE dbo.Proc 2@xh,@kcGO6.3.3带OUTPUT参数的存储过程【例6-3】设计一个带OUTPUT参数的存储过程：返回某学生选课情况及其平均成绩，IF EXISTS (SELECT *FROMsysobjects WHERE name-'Proc 3' ANDtype-'p')DROP PROCEDURE PIOC_3GOCREATE PROCEDURE Proc 3 @id char(6),@aver int OUTPUTAS

SELECT * FROM XSB WHERE 专业='计算机' GO 存储过程定义后，执行存储过程 P_2： EXECUTE dbo.Proc_1 GO 带参数的存储过程 【例 6-2】设计一个带参数的存储过程：查询某学生指定课程（模糊查询）的学分和成绩。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_2' AND type='P') DROP PROCEDURE Proc_2 GO CREATE PROCEDURE Proc_2 @id char(6),@cname varchar(20) AS SELECT XSB.学号,XSB.姓名,KCB.课程编号,KCB.课程名称,KCB.学分,CJB.成绩 FROM XSB,KCB,CJB WHERE XSB.学号=CJB.学号 AND KCB.课程编号=CJB.课程编号 AND XSB.学号=@id AND KCB.课程名称 LIKE '%'+@cname+'%' GO 存储过程定义后，执行存储过程 P_2： EXECUTE dbo.Proc_2 '081101','计算机' GO 或者： EXECUTE dbo.Proc_2 @cname='计算机',@id='081101' GO 或者： DECLARE @xh char(8),@kc char(20) SET @xh='081101' SET @kc='计算机基础' EXECUTE dbo.Proc_2 @xh,@kc GO 带 OUTPUT 参数的存储过程 【例 6-3】设计一个带 OUTPUT 参数的存储过程：返回某学生选课情况及其平均成绩。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_3' AND type='P') DROP PROCEDURE Proc_3 GO CREATE PROCEDURE Proc_3 @id char(6),@aver int OUTPUT AS

SELECTXSB.学号，XSB.姓名，KCB.课程编号，KCB.课程名称，KCB.学分，CJB.成绩FROMXSB,KCB,CJBWHEREXSB.学号=CJB.学号ANDKCB.课程编号=CJB.课程编号ANDXSB.学号-@idSET @aVer=（SELECTAVG（CJB.成绩）FROMCJB WHERE CJB.学号-@id）GO执行存储过程P_3，并输出081101学生的平均成绩及成绩等级DECLARE @pj intEXECdbo.Proc3081101，@pjOUTPUT--带OUTPUT的参数可以在批处理中调用SELECT@pjAS平均成绩，‘成绩等级CASEWHEN@pj>=60THEN合格ELSE‘不合格ENDGO从此例可以看出，使用OUTPUT参数的目的是在调用批处理的其他语句中使用其返回值，对OUTPUT参数首先必须在CREATEPROCEDURE语句中先定义为OUTPUT参数。6.3.4存储过程执行状态值的返回无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码为0，否则一般是一个负数，这与失败的类型有关。按以下语法只要执行存储过程并用@returnstatus接收状态值即可。EXECUTE@return_status=procedurename除了系统自动返回的状态值外，还可以使用RETURN语句来自定义返回的状态值。RETURN语句的功能是从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。【例6-4】使用RETURN语句返回自定义的状态代码。在【例6-2】的基础上修改：如果执行存储过程时学生学号不存在，则返回状态码1；如果该生未选修某课程，则返回状态码2。IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_4'ANDtype='p")DROPPROCEDUREPrOC4GOCREATE PROCEDURE Proc 4 @id char(6),@cname varchar(20)ASBEGINIF NOT EXISTS(SELECT * FROM XSB WHERE XSB.学号=@idRETURN 1IFNOTEXISTS（SELECTFROMCJBWHERECJB.学号-@idANDCJB.课程编号IN(SELECTKCB.课程编号FROMKCB WHEREKCB.课程名称LIKE1%（+@cname+1)

SELECT XSB.学号,XSB.姓名,KCB.课程编号,KCB.课程名称,KCB.学分,CJB.成绩 FROM XSB,KCB,CJB WHERE XSB.学号=CJB.学号 AND KCB.课程编号=CJB.课程编号 AND XSB.学号=@id SET @aver=(SELECT AVG(CJB.成绩)FROM CJB WHERE CJB.学号=@id) GO 执行存储过程 P_3，并输出 081101 学生的平均成绩及成绩等级： DECLARE @pj int EXEC dbo.Proc_3 '081101',@pj OUTPUT -带OUTPUT的参数可以在批处理中调用 SELECT @pj AS '平均成绩','成绩等级'= CASE WHEN @pj>=60 THEN '合格' ELSE '不合格' END GO 从此例可以看出，使用 OUTPUT 参数的目的是在调用批处理的其他语句中使用其返回 值，对 OUTPUT 参数首先必须在 CREATE PROCEDURE 语句中先定义为 OUTPUT 参数。 存储过程执行状态值的返回 无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如 果存储过程执行成功，返回的结果码为 0，否则一般是一个负数，这与失败的类型有关。按 以下语法只要执行存储过程并用@return_status 接收状态值即可。 EXECUTE @return_status =procedure_name 除了系统自动返回的状态值外，还可以使用 RETURN 语句来自定义返回的状态值。 RETURN 语句的功能是从过程、批处理或语句块中无条件退出，不执行位于 RETURN 之后 的语句。 【例 6-4】使用 RETURN 语句返回自定义的状态代码。在【例 6-2】的基础上修改：如果执 行存储过程时学生学号不存在，则返回状态码 1；如果该生未选修某课程，则返回状态码 2。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_4' AND type='P') DROP PROCEDURE Proc_4 GO CREATE PROCEDURE Proc_4 @id char(6),@cname varchar(20) AS BEGIN IF NOT EXISTS(SELECT * FROM XSB WHERE XSB.学号=@id) RETURN 1 IF NOT EXISTS(SELECT * FROM CJB WHERE CJB.学号=@id AND CJB.课程编 号 IN (SELECT KCB.课程编号 FROM KCB WHERE KCB.课程名称 LIKE '%'+@cname+'%')

RETURN2SELECTXSB.学号，XSB.姓名，KCB.课程编号，KCB.课程名称，KCB.学分，CJB.成绩FROMXSB,KCB,CJBWHEREXSB.学号=CJB.学号ANDKCB.课程编号=CJB.课程编号ANDXSB.学号-@idAND KCB.课程名称LIKE%+@cname+%ENDGO执行上述存储过程，显示执行返回的状态值。以下执行过程返回状态值1：DECLARE @return status intEXEcuTE@return_status=dbo.Proc_4091101计算机SELEcT @return status As执行状态GO以下执行过程返回状态值2:DECLARE @return status intExEcuTE@return_status=dbo.Proc_4*081101!,离散数学SELEcT @return status As 执行状态GO以下执行过程返回状态值0：DECLARE @return status int4081101，计算机EXEcUTE @return status-dbo.Proc 4SELEcT @return status As执行状态GO从上例可以看出，我们在创建存储过程时，定义自己的状态码和错误信息，主要是为了掌握存储过程执行的状态。我们甚至还可以将存储过程执行状态返回值@return_status作为已知变量，设计如下执行存储过程的流程控制语句。DECLARE @id char(6),@cname varchar(20),@return status intSET@id-'081101-SET@cname='计算机！ExEcuTE @return status-dbo.Proc 4 @id,@cnameIF @return status-lPRINT‘该学号不存在！IF @return status-2PRINT该选课记录不存在！GO

) RETURN 2 SELECT XSB.学号,XSB.姓名,KCB.课程编号,KCB.课程名称,KCB.学分,CJB.成绩 FROM XSB,KCB,CJB WHERE XSB.学号=CJB.学号 AND KCB.课程编号=CJB.课程编号 AND XSB.学号=@id AND KCB.课程名称 LIKE '%'+@cname+'%' END GO 执行上述存储过程，显示执行返回的状态值。 以下执行过程返回状态值 1： DECLARE @return_status int EXECUTE @return_status=dbo.Proc_4 '091101','计算机' SELECT @return_status AS '执行状态' GO 以下执行过程返回状态值 2： DECLARE @return_status int EXECUTE @return_status=dbo.Proc_4 '081101','离散数学' SELECT @return_status AS '执行状态' GO 以下执行过程返回状态值 0： DECLARE @return_status int EXECUTE @return_status=dbo.Proc_4 '081101','计算机' SELECT @return_status AS '执行状态' GO 从上例可以看出，我们在创建存储过程时，定义自己的状态码和错误信息，主要是为了 掌握存储过程执行的状态。我们甚至还可以将存储过程执行状态返回值@return_status 作为 已知变量，设计如下执行存储过程的流程控制语句。 DECLARE @id char(6),@cname varchar(20),@return_status int SET @id='081101' SET @cname='计算机' EXECUTE @return_status=dbo.Proc_4 @id,@cname IF @return_status=1 PRINT '该学号不存在' IF @return_status=2 PRINT '该选课记录不存在' GO

6.4存储过程应用实例分析【例6-5】设计一个存储过程，实现对成绩表CJB插入数据工作，要求检查所插入数据是否满足实体完整性和参照完整性，且每个学生不重复选课。方法1：在存储过程中分情况进行PRINT语句的输出；使用该方法时需要用ELSEIF，并在每个多语句分支条件中需要BEGIN..END语句块。IF EXISTS (SELECT *FROM sysobjects WHERE name-'Proc_5'ANDtype="p')DROP PROCEDURE PrOC5GOCREATEPROCEDURE Proc5@id char(6)@cnum char(3)@ciintASBEGINIF @idISNULLOR @cnumISNULLPRINT请输入学号和课程编号！ELSE IFNOT EXISTS (SELECT * FROM XSB WHERE 学号-@id)PRINT该学号不存在！ELSEIF NOTEXISTS（SELECT *FROM KCB WHERE 课程编号-@Cnum)PRINT该课程编号不存在！ELSEIE EXISTS （SELECT *FROM CJB WHERE 学号-@id AND 课程编号=@cnum)PRINT该数据已存在！ELSEBEGININSERTINTOCJB VALUESs(@id,@cnum,@cj)PRINT该数据已插入成功！ENDENDGO接下来就可以采用调用存储过程的方式来插入学生选课记录：BEGIN TRAN--EXECUTEdbo.Proc5081101','101',85--EXECUTEdbo.Proc_5081234","101,85081101103,85-EXECUTEdbo.Proc_5--EXECUTEdbo.Proc5081101,212',85ROLLBACK方法2：存储过程中自定义返回值，在执行存储过程的批处理中根据返回值不同进行输出。注：存储过程中RETURN的是int类型的返回值，因此不能直接写RETURN‘某一语句

存储过程应用实例分析 【例 6-5】设计一个存储过程，实现对成绩表 CJB 插入数据工作，要求检查所插入数据是否 满足实体完整性和参照完整性，且每个学生不重复选课。 方法 1：在存储过程中分情况进行 PRINT 语句的输出； 使用该方法时需要用 ELSE IF，并在每个多语句分支条件中需要 BEGIN.END 语句块。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_5' AND type='P') DROP PROCEDURE Proc_5 GO CREATE PROCEDURE Proc_5 @id char(6),@cnum char(3),@cj int AS BEGIN IF @id IS NULL OR @cnum IS NULL PRINT '请输入学号和课程编号' ELSE IF NOT EXISTS (SELECT * FROM XSB WHERE 学号=@id) PRINT '该学号不存在' ELSE IF NOT EXISTS (SELECT * FROM KCB WHERE 课程编号=@cnum) PRINT '该课程编号不存在' ELSE IF EXISTS (SELECT * FROM CJB WHERE 学号=@id AND 课程编号 =@cnum) PRINT '该数据已存在' ELSE BEGIN INSERT INTO CJB VALUES (@id,@cnum,@cj) PRINT '该数据已插入成功' END END GO 接下来就可以采用调用存储过程的方式来插入学生选课记录： BEGIN TRAN -EXECUTE dbo.Proc_5 '081101','101',85 -EXECUTE dbo.Proc_5 '081234','101',85 -EXECUTE dbo.Proc_5 '081101','103',85 -EXECUTE dbo.Proc_5 '081101','212',85 ROLLBACK 方法 2：存储过程中自定义返回值，在执行存储过程的批处理中根据返回值不同进行输出。 注：存储过程中 RETURN 的是 int 类型的返回值，因此不能直接写 RETURN ‘某一语句’

IF EXISTS (SELECT *FROM Sysobjects WHERE name-'Proc 5' ANDtype=(p')DROP PROCEDURE PrOC5GOCREATE PROCEDURE Proc5@id char(6),@cnum char(3)@ciintASBEGINIF @id IS NULL OR @cnum IS NULLRETURN 1IF NOT EXISTS (SELECT★ FROM XSB WHERE 学号-@id)RETURN 2IFNOT EXISTS（(SELECT*FROM KCBWHERE课程编号=@Cnum)RETURN 3IFEXISTS（SELECT*FROMCJBWHERE学号-@idAND课程编号-@cnum）RETURN 4ELSEINSERT INTO CJB VALUES (@id,@cnum,@cj)END接下来在执行存储过程的批处理中根据返回值不同进行输出设定：BEGIN TRANDECLARE @status int--EXEcUTE@status=dbo.Proc5081101,101,85--EXECUTE@status=dbo.Proc5081234',101,85--ExEcUTE@status=dbo.Proc5081101','103',85--EXEcUTE@status=dbo.Proc 5081101',212',85IF@status=1 PRINT请输入学号和课程编号IF @status-2 PRINT该学号不存在IF@status=3PRINT该课程编号不存在IF@status=4 PRINT该数据已存在IF@status=0 PRINT该数据已插入成功ROLLBACK如果将本学期的选课数据（多行）一起插入成绩表CJB，则可以借助游标来实现。DECLARE @id char(6),@cid char(3),@cj intDECLARE Cur 1 CURSORFOR SELECT *FROMCJB NEWOPENcur1FETCH NEXT FROM cur 1 INTO @id,@cid,@cjWHILE @@FETCH STATUS-OBEGINDECLARE @status intEXEcUTE@status=dbo.Proc5@id,@cid,@cjIF @status-1PRINT请输入学号和课程编号IF@status=2 PRINT该学号不存在IF@status=3 PRINT该课程编号不存在！

IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_5' AND type='P') DROP PROCEDURE Proc_5 GO CREATE PROCEDURE Proc_5 @id char(6),@cnum char(3),@cj int AS BEGIN IF @id IS NULL OR @cnum IS NULL RETURN 1 IF NOT EXISTS (SELECT * FROM XSB WHERE 学号=@id) RETURN 2 IF NOT EXISTS (SELECT * FROM KCB WHERE 课程编号=@cnum) RETURN 3 IF EXISTS (SELECT * FROM CJB WHERE 学号=@id AND 课程编号=@cnum) RETURN 4 ELSE INSERT INTO CJB VALUES (@id,@cnum,@cj) END 接下来在执行存储过程的批处理中根据返回值不同进行输出设定： BEGIN TRAN DECLARE @status int -EXECUTE @status=dbo.Proc_5 '081101','101',85 -EXECUTE @status=dbo.Proc_5 '081234','101',85 -EXECUTE @status=dbo.Proc_5 '081101','103',85 -EXECUTE @status=dbo.Proc_5 '081101','212',85 IF @status=1 PRINT '请输入学号和课程编号' IF @status=2 PRINT '该学号不存在' IF @status=3 PRINT '该课程编号不存在' IF @status=4 PRINT '该数据已存在' IF @status=0 PRINT '该数据已插入成功' ROLLBACK 如果将本学期的选课数据（多行）一起插入成绩表 CJB，则可以借助游标来实现。 DECLARE @id char(6),@cid char(3),@cj int DECLARE cur_1 CURSOR FOR SELECT * FROM CJB_NEW OPEN cur_1 FETCH NEXT FROM cur_1 INTO @id,@cid,@cj WHILE @@FETCH_STATUS=0 BEGIN DECLARE @status int EXECUTE @status=dbo.Proc_5 @id,@cid,@cj IF @status=1 PRINT '请输入学号和课程编号' IF @status=2 PRINT '该学号不存在' IF @status=3 PRINT '该课程编号不存在

IF@status=4PRINT该数据已存在IF@status=0 PRINT该数据已插入成功FETCH NEXT FROM cur_1 INTO@id,@cid,@cjENDCLOSEcur_1DEALLOCATE Cur_1【例6-6】在学生信息管理中，为了维护数据的一致性，必须保证其平均成绩与成绩表中该生已经及格的课程的平均分相等，须逐个检查并修改某专业每个学生的平均成绩，是一项复杂的工作。我们可以通过游标来逐个检查学生的平均分及定位修改。但是游标在定义它的批处理结束时便离开作用域，故我们设计一个使用游标的存储过程，完成该项工作。在需要时该存储过程可以进行多次调用执行。IF EXISTS (SELECT *FROM sysobjects WHERE name='Proc 6ANDtype-'p")DROPPROCEDUREPrOC_6GOCREATE PROCEDURE PrOC 6ASBEGINDECLARE @id char(6)DECLARE Cur 2 CURSORFORSELECTXSB.学号FROMXSBOPEN cur2FETCH NEXT FROM cur 2 into @idWHILE @@FETCH STATUS -OBEGINDECLARE @aver intSET @aVer=（SELECT AVG（成绩）FROM CJB WHERE CJB.学号-@id）IE（SELECTXSB.平均成绩FROMXSB WHEREXSB.学号-@id）<>@aVerUPDATEXSBSETXSB.平均成绩=@aVerWHERE CURRENT OF Cur2FETCH NEXT FROMcur 2into @idENDCLOSE cur_2DEALLOCATE Cur 2END以后为了保证平均分数据的一致性，无需再创建游标，只需执行该存储过程即可。BEGIN TRANEXECUTE dbo.Proc 6ROLLBACK延伸：为该存储过程添加输入参数"专业"，按专业来核对学生的平均分。并对输入值的完整性进行判断

IF @status=4 PRINT '该数据已存在' IF @status=0 PRINT '该数据已插入成功' FETCH NEXT FROM cur_1 INTO @id,@cid,@cj END CLOSE cur_1 DEALLOCATE cur_1 【例 6-6】在学生信息管理中，为了维护数据的一致性，必须保证其平均成绩与成绩表中该 生已经及格的课程的平均分相等，须逐个检查并修改某专业每个学生的平均成绩，是一项复 杂的工作。我们可以通过游标来逐个检查学生的平均分及定位修改。但是游标在定义它的批 处理结束时便离开作用域，故我们设计一个使用游标的存储过程，完成该项工作。在需要时， 该存储过程可以进行多次调用执行。 IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_6' AND type='P') DROP PROCEDURE Proc_6 GO CREATE PROCEDURE Proc_6 AS BEGIN DECLARE @id char(6) DECLARE cur_2 CURSOR FOR SELECT XSB.学号 FROM XSB OPEN cur_2 FETCH NEXT FROM cur_2 into @id WHILE @@FETCH_STATUS =0 BEGIN DECLARE @aver int SET @aver=(SELECT AVG(成绩) FROM CJB WHERE CJB.学号=@id) IF (SELECT XSB.平均成绩 FROM XSB WHERE XSB.学号=@id)<>@aver UPDATE XSB SET XSB.平均成绩=@aver WHERE CURRENT OF cur_2 FETCH NEXT FROM cur_2 into @id END CLOSE cur_2 DEALLOCATE cur_2 END 以后为了保证平均分数据的一致性，无需再创建游标，只需执行该存储过程即可。 BEGIN TRAN EXECUTE dbo.Proc_6 ROLLBACK 延伸：为该存储过程添加输入参数“专业”，按专业来核对学生的平均分。并对输入值的 完整性进行判断

T*FROMname-'Proc7ANDIF EXISTS (SELECTIsysobjects WHEREtype-(p')DROPPROCEDUREPrOC_7GOCREATE PROCEDURE Proc7 @zy char(12)ASBEGINIF@zyIS NULLRETURN 1IF @Zy NOT IN(SELECT DISTINCT(专业）FROM XSB)RETURN 2DECLARE @id char(6)CURSORDECLAREcur2：FORSELECTXSB.学号FROMXSB WHERE专业-@ZyOPEN cur_2FETCH NEXT FROM cur2 into @idWHILE @@FETCH STATUS =OBEGINDECLARE @aver intSET @aVer=（SELECTAVG（成绩）FROMCJB WHERECJB.学号-@id)IE（SELECTXSB.平均成绩FROMXSBWHEREXSB.学号-@id)<>@aVerUPDATEXSBSETXSB.平均成绩=@aVerWHERE CURRENT OF Cur_2FETCH NEXT FROM cur_2into @idENDCLOSE cur 2DEALLOCATE Cur_2ENDGO执行该存储过程时可以根据状态值进行不同输出。DECLARE @return_status int,@zy char(12)SET @zy=计算机EXECUTE @return_status-DBO.Proc_7 @zy@return status-1 PRINT请输入专业IF@return_status=2PRINT该专业不存在IFIF@return status=oPRINT@zy+专业数据核对完成GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='Proc_7' AND type='P') DROP PROCEDURE Proc_7 GO CREATE PROCEDURE Proc_7 @zy char(12) AS BEGIN IF @zy IS NULL RETURN 1 IF @zy NOT IN(SELECT DISTINCT(专业) FROM XSB ) RETURN 2 DECLARE @id char(6) DECLARE cur_2 CURSOR FOR SELECT XSB.学号 FROM XSB WHERE 专业=@zy OPEN cur_2 FETCH NEXT FROM cur_2 into @id WHILE @@FETCH_STATUS =0 BEGIN DECLARE @aver int SET @aver=(SELECT AVG(成绩) FROM CJB WHERE CJB.学号=@id) IF (SELECT XSB.平均成绩 FROM XSB WHERE XSB.学号=@id)<>@aver UPDATE XSB SET XSB.平均成绩=@aver WHERE CURRENT OF cur_2 FETCH NEXT FROM cur_2 into @id END CLOSE cur_2 DEALLOCATE cur_2 END GO 执行该存储过程时可以根据状态值进行不同输出。 DECLARE @return_status int,@zy char(12) SET @zy='计算机' EXECUTE @return_status=DBO.Proc_7 @zy IF @return_status=1 PRINT '请输入专业' IF @return_status=2 PRINT '该专业不存在' IF @return_status=0 PRINT @zy+'专业数据核对完成' GO