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<sq]_statement>[:][...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 <sql_statement> [;][ .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