数据库原理与应用(14)第14章 存储过程

第14章存储过程14.1概述存储过程可不仅可以提高应用程序的处理能力，降低编写数据库应用程序的难度，同时还可以提高应用程序的效率。归纳起来存储过程具有如下优点：执行速度快。采用模块化程序设计。减少网络通信量。保证系统的安全性。SQLServer2005提供了3种存储过程，即用户存储过程、系统存储过程和扩展存储过程。14.2创建存储过程14.2.1.使用SQLServer管理控制器创建存储过程通过一个简单的示例说明使用SQLServer管理控制器创建存储过程的操作步骤。【例14.1】使用SQLServer管理控制器创建存储过程maxdegree，用于输出所有学生的最高分。解：其操作步骤如下：（1）启动SQLServer管理控制器。在“对象资源管理器”中展开“LCB-PC”服务器节点。（2）展开“数据库”|“school”|“存储过程”节点，单击鼠标右键，在出现的快捷菜单中选择“新建存储过程”命令。（3）出现存储过程编辑窗口，其中含有一个存储过程模板，用户可以参照模板在其中输入存储过程的T-SQL语句，这里输入的语句如下（其中红字部分为主要输入的T-SQL语句）：setANSI_NULLSONsetQUOTED_IDENTIFIERONGOCREATEPROCEDUREmaxdegreeASBEGINSETNOCOUNTONSELECTMAX(分数)AS'最高分'FROMscore/*从score表中查询最高分*/ENDGO（4）单击工具栏中的“执行”按钮，将其保存在数据库中。此时选中“存储过程”节点，单击鼠标右键，在出现的快捷菜单中选择“刷新”命令，会看到“存储过程”的下方出现了maxdegree存储过程，如图14.1所示。这样就完成了maxdegree存储过程的创建过程。14.2.2使用CREATEPROCEDURE语句创建存储过程使用CREATEPROCEDURE语句的基本语法格式如下：CREATEPROC[EDURE]存储过程名[;number][{@parameter数据类型}=默认值][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASSQL语句[…n]【例14.2】编写一个程序，创建一个简单的存储过程stud_degree，用于检索所有学生的成绩记录。解：对应的程序如下：USEschoolGO--若存在存储过程stud_degree，则删除之IFEXISTS(SELECT*FROMsysobjectsWHEREname='stud_degree'ANDtype='P')DROPPROCEDUREstud_degreeGO--注意，CREATEPROCEDURE必须是一个批处理的第一个语句，故此GO不能缺--创建存储过程stud_degreeCREATEPROCEDUREstud_degreeASSELECTstudent.学号,student.姓名,course.课程名,score.分数FROMstudent,course,scoreWHEREstudent.学号=score.学号ANDcourse.课程号=score.课程号ORDERBYstudent.学号GO14.3执行存储过程可以使用EXECUTE或EXEC语句来执行存储在服务器上的存储过程，其完整语法格式如下：[EXEC[UTE]][@return_status=]{存储过程名[;number]|@procedure_name_var}[[@parameter=]{值|@variable[OUTPUT]|[DEFAULT]][,…n][WITHRECOMPILE]【例14.3】执行例14.1中创建的存储过程maxdegree并查看输出的结果。解：执行maxdegree存储过程的程序如下：USEschoolGOEXECmaxdegreeGO其执行结果如图14.2所示。从结果看到，查询的最高分为92。14.4存储过程的参数14.4.1在存储过程中使用参数在设计存储过程时可以带有参数，这样增加存储过程的灵活性。带参数的存储过程的一般格式如下：CREATEPROCEDURE存储过程名(参数列表)ASSQL语句在调用存储过程时，有两种传递参数的方式。第1种方式是在传递参数时，使传递的参数和定义时的参数顺序一致。其一般格式如下：EXEC存储过程名实参列表第2种方式是采用“参数=值”的形式，此时，各个参数的顺序可以任意排列。其一般格式如下：EXEC存储过程名参数1=值1,参数2=值2,…【例14.5】设计一个存储过程maxno，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。解：采用CREATEPROCEDURE语句设计该存储过程如下：USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='maxno'ANDtype='P')DROPPROCEDUREmaxnoGOCREATEPROCEDUREmaxno(@nochar(10))AS/*声明no为参数*/SELECTs.学号,s.姓名,c.课程名,sc.分数FROMstudents,coursec,scorescWHEREs.学号=@noANDs.学号=sc.学号ANDc.课程号=sc.课程号ANDsc.分数=(SELECTMAX(分数)FROMscoreWHERE学号=@no)GO采用第1种方式执行存储过程maxno的程序如下：USEschoolGOEXECmaxno'103'GO采用第2种方式执行存储过程maxno的程序如下：USEschoolGOEXECmaxno@no='103'GO14.4.2在存储过程中使用默认参数在设计存储过程时，可以为参数提供一个默认值，默认值必须为常量或者NULL。其一般格式如下：CREATEPROCEDURE存储过程名(参数1=默认值1,参数2=默认值2,…)ASSQL语句在调用存储过程时，如果不指定对应的实参值，则自动用对应的默认值代替。【例14.6】设计类似例14.5功能的存储过程maxno1，指定其默认学号为'101'。解：设计一个新的存储过程maxno1，对应的程序如下：USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='maxno1'ANDtype='P')DROPPROCEDUREmaxno1GOCREATEPROCEDUREmaxno1(@noint='101')AS/*声明no为参数*/SELECTs.学号,s.姓名,c.课程名,sc.分数FROMstudents,coursec,scorescWHEREs.学号=@noANDs.学号=sc.学号ANDc.课程号=sc.课程号ANDsc.分数=(SELECTMAX(分数)FROMscoreWHERE学号=@no)GO当不指定实参调用maxno1存储过程时，其结果如图14.5所示。当指定实参为'105'调用maxno1存储过程时，其结果如图14.6所示。结论：从执行结果可以看到，当调用存储过程时，没有指定参数值时就自动使用相应的默认值。14.4.3在存储过程中使用返回参数在创建存储过程时，可以定义返回参数。在执行存储过程时，可以将结果返回给返回参数。返回参数应用OUTPUT进行说明。【例14.7】创建一个存储过程average，它返回两个参数@st_name和＠st_avg，分别代表了姓名和平均分。并编写T-SQL语句执行该存储过程和查看输出的结果。解：建立存储过程average的程序如下：USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='average'ANDtype='P')DROPPROCEDUREaverageGOCREATEPROCEDUREaverage(@st_noint,@st_namechar(8)OUTPUT,/*返回参数*/@st_avgfloatOUTPUT/*返回参数*/)ASSELECT@st_name=student.姓名,@st_avg=AVG(score.分数)FROMstudent,scoreWHEREstudent.学号=score.学号GROUPBYstudent.学号,student.姓名HAVINGstudent.学号=@st_noGO执行该存储过程，来查询学号为“105”的学生姓名和平均分：DECLARE@st_namechar(10)DECLARE@st_avgfloatEXECaverage'105',@st_nameOUTPUT,@st_avgOUTPUTSELECT'姓名'=@st_name,'平均分'=@st_avgGO4.存储过程的返回值存储过程在执行后都会返回一个整型值（称为“返回代码”），指示存储过程的执行状态。如果执行成功，返回0；否则返回-1～-99之间的数值（例如-1表示找不到对象，-2表示数据类型错误，-5表示语法错误等）。也可以使用RETURN语句来指定一个返回值。【例14.9】编写一个程序，创建存储过程test_ret，根据输入的参数来判断返回值。并执行该存储过程和查看输出的结果。解：建立存储过程test_ret如下：USEtestGOIFEXISTS(SELECT*FROMsysobjectsWHEREname='test_ret'ANDtype='P')DROPPROCEDUREtest_retGOCREATEPROCtest_ret(@input_intint=0)AS/*指定默认参数值*/IF@input_int=0RETURN0--如果输入的参数等于0，则返回0IF@input_int0RETURN1000--如果输入的参数大于0，则返回1000IF@input_int0RETURN-1000--如果输入的参数小于0，则返回-1000GO执行该存储过程：USETestDECLARE@ret_intintEXEC@ret_int=test_ret1PRINT'返回值'PRINT'-------'PRINT@ret_intEXEC@ret_int=test_ret0PRINT@ret_intEXEC@ret_int=test_ret-1PRINT@ret_int14.5存储过程的管理14.5.1查看存储过程在创建存储过程后，它的名称就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用SQLServer管理控制器或系统存储过程来查看用户创建的存储过程。1.使用SQLServer管理控制器查看存储过程通过一个例子说明使用SQLServer管理控制器查看存储过程的操作步骤。【例14.10】使用SQLServer管理控制器查看例14.8所创建的存储过程stud1_degree。解：其操作步骤如下：（1）启动SQLServer管理控制器。在“对象资源管理器”中展开“LCB-PC”服务器节点。（2）展开“数据库”|“school”|“可编程性”|“存储过程”|“dbo.stud1_degree”节点，单击鼠标右键，在出现的快捷菜单中选择“编写存储过程脚本为(S)|CREATE到(C)|新查询编辑器窗口”命令。（3）在右边的编辑器窗口中出现存储过程stud_degree源代码，如图14.9所示。此时用户只能查看其代码。2.使用系统存储过程来查看存储过程SQLServer2005提供了如下系统存储过程用于查看用户创建的存储过程。（1）sp_help用于显示存储过程的参数及其数据类型，其语法如下：sp_help[[@objname=]name]其中，参数name为要查看的存储过程的名称。（2）sp_helptext用于显示存储过程的源代码，其语法如下：sp_helptext[[@objname=]name]其中，参数name为要查看的存储过程的名称。（3）sp_depends用于显示和存储过程相关的数据库对象，其语法