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

第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语句的基本语法格式如下：CREATEPROCEDURE存储过程名;numberparameter数据类型=默认值OUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASSQL语句n,【例14.2】编写一个程序，创建一个简单的存储过程stud_degree，用于检索所有学生的成绩记录。解：对应的程序如下：,USEschoolGO-若存在存储过程stud_degree，则删除之IFEXISTS(SELECT*FROMsysobjectsWHEREname=stud_degreeANDtype=P)DROPPROCEDUREstud_degreeGO-注意，CREATEPROCEDURE必须是一个批处理的第一个语句，故此GO不能缺-创建存储过程stud_degreeCREATEPROCEDUREstud_degreeASSELECTstudent.学号,student.姓名,course.课程名,score.分数FROMstudent,course,scoreWHEREstudent.学号=score.学号ANDcourse.课程号=score.课程号ORDERBYstudent.学号GO,14.3执行存储过程可以使用EXECUTE或EXEC语句来执行存储在服务器上的存储过程，其完整语法格式如下：EXECUTEreturn_status=存储过程名;number|procedure_name_varparameter=值|variableOUTPUT|DEFAULT,nWITHRECOMPILE,【例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=maxnoANDtype=P)DROPPROCEDUREmaxnoGOCREATEPROCEDUREmaxno(nochar(10)AS/*声明no为参数*/SELECTs.学号,s.姓名,c.课程名,sc.分数FROMstudents,coursec,scorescWHEREs.学号=noANDs.学号=sc.学号ANDc.课程号=sc.课程号ANDsc.分数=(SELECTMAX(分数)FROMscoreWHERE学号=no)GO,采用第1种方式执行存储过程maxno的程序如下：USEschoolGOEXECmaxno103GO采用第2种方式执行存储过程maxno的程序如下：USEschoolGOEXECmaxnono=103GO,14.4.2在存储过程中使用默认参数在设计存储过程时，可以为参数提供一个默认值，默认值必须为常量或者NULL。其一般格式如下：CREATEPROCEDURE存储过程名(参数1=默认值1,参数2=默认值2,)ASSQL语句在调用存储过程时，如果不指定对应的实参值，则自动用对应的默认值代替。,【例14.6】设计类似例14.5功能的存储过程maxno1，指定其默认学号为101。解：设计一个新的存储过程maxno1，对应的程序如下：,USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname=maxno1ANDtype=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=averageANDtype=P)DROPPROCEDUREaverageGOCREATEPROCEDUREaverage(st_noint,st_namechar(8)OUTPUT,/*返回参数*/st_avgfloatOUTPUT/*返回参数*/)ASSELECTst_name=student.姓名,st_avg=AVG(score.分数)FROMstudent,scoreWHEREstudent.学号=score.学号GROUPBYstudent.学号,student.姓名HAVINGstudent.学号=st_noGO,执行该存储过程，来查询学号为“105”的学生姓名和平均分：DECLAREst_namechar(10)DECLAREst_avgfloatEXECaverage105,st_nameOUTPUT,st_avgOUTPUTSELECT姓名=st_name,平均分=st_avgGO,4.存储过程的返回值存储过程在执行后都会返回一个整型值（称为“返回代码”），指示存储过程的执行状态。如果执行成功，返回0；否则返回-1-99之间的数值（例如-1表示找不到对象，-2表示数据类型错误，-5表示语法错误等）。也可以使用RETURN语句来指定一个返回值。,【例14.9】编写一个程序，创建存储过程test_ret，根据输入的参数来判断返回值。并执行该存储过程和查看输出的结果。解：建立存储过程test_ret如下：,USEtestGOIFEXISTS(SELECT*FROMsysobjectsWHEREname=test_retANDtype=P)DROPPROCEDUREtest_retGOCREATEPROCtest_ret(input_intint=0)AS/*指定默认参数值*/IFinput_int=0RETURN0-如果输入的参数等于0，则返回0IFinput_int0RETURN1000-如果输入的参数大于0，则返回1000IFinput_int0RETURN-1000-如果输入的参数小于0，则返回-1000GO,执行该存储过程：USETestDECLAREret_intintEXECret_int=test_ret1PRINT返回值PRINT-PRINTret_intEXECret_int=test_ret0PRINTret_intEXECret_int=test_ret-1PRINTret_int,14.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_helpobjname=name其中，参数name为要查看的存储过程的名称。（2）sp_helptext用于显示存储过程的源代码，其语法如下：sp_helptextobjname=name其中，参数name为要查看的存储过程的名称。,（3）sp_depends用于显示和存储过程相关的数据库对象，其语法如下：sp_dependsobjname=object其中，参数object为要查看依赖关系的存储过程的名称。（4）sp_stored_procedures用于返回当前数据库中的存储过程列表，其语法如下：sp_stored_proceduresp_name=name,sp_owner=owner,sp_qualifier=qualifier,【例14.11】使用相关系统存储过程查看例14.2所创建的存储过程stud_degree的相关内容。解：对应的程序如下：USEschoolGOEXECsp_helpstud_degreeEXECsp_helptextstud_degreeEXECsp_dependsstud_degree,14.5.2修改存储过程在创建存储过程之后，用户可以对其进行修改。可以使用SQLServer管理控制器或使用ALTERPROCEDURE语句修改用户创建的存储过程。1.使用SQLServer管理控制器修改存储过程通过一个例子说明使用SQLServer管理控制器修改存储过程的操作步骤。【例14.12】使用SQLServer管理控制器修改例14.2所创建的存储过程stud_degree。解：其操作步骤如下：（1）启动SQLServer管理控制器。在“对象资源管理器”中展开“LCB-PC”服务器节点。（2）展开“数据库”|“school”|“可编程性”|“存储过程”|“dbo.stud_degree”节点，单击鼠标右键，在出现的快捷菜单中选择“修改”命令。,（3）此时右边的编辑器窗口出现stud_degree存储过程的源代码（将“CREATEPROCEDURE”改为“ALTERPROCEDURE”），如图14.11所示，用户可以直接进行修改。修改完毕，单击工具栏中的“执行”按钮执行该存储过程，从而达到修改的目的。,2.使用ALTERPROCEDURE语句修改存储过程使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器，其语法形式如下：ALTERPROCEDURE存储过程名参数列表ASSQL语句当使用ALTERPROCEDURE语句时，如果在CREATEPROCEDURE语句中使用过参数，那么在ALTERPROCEDURE语句中也应该使用这些参数。每次只能修改一个存储过程。,【例14.13】编写一个程序，先创建一个存储过程studproc，输出“1031”班的所有学生，利用sysobjects和syscomments两个系统表输出该存储过程的id和text列。然后利用ALTERPROCEDURE语句修改该存储过程，将其改为加密方式，最后再输出该存储过程的id和text列。解：创建存储过程studproc的语句如下：USEschoolGOIFEXISTS(SELECT*FROMsysobjectsWHEREname=studprocANDtype=P)DROPPROCEDUREstudprocGOCREATEPROCEDUREstudprocASSELECT*FROMstudentWHERE班号=1031GO,通过以下语句输出studproc存储过程的id和text列：SELECTsysobjects.id,syscomments.textFROMsysobjects,syscommentsWHERE=studprocANDsysobjects.type=PANDsysobjects.id=syscomments.id其执行结果如图14.12所示。,修改该存储过程的语句如下：USEschoolGOALTERPROCEDUREstudprocWITHENCRYPTIONASSELECT*FROMstudentWHERE班号=1031GO再次执行前面的输出studproc存储过程的id和text列的语句，其执行结果如图14.13所示。从中看到，加密过的存储过程查询出的源代码是空值，从而起到保护源程序的作用。,14.5.3重命名存储过程重命名存储过程也有两种方法：使用SQLServer管理控制器或使用系统存储过程。1.使用SQLServer管理控制器重命名存储过程通过一个例子说明使用SQLServer管理控制器重命名存储过程的操作步骤。【例14.14】使用SQLServer管理控制器将存储过程studproc重命令为studproc1。解：其操作步骤如下：（1）启动SQLServer管理控制器。在“对象资源管理器”中展开“LCB-PC”服务器节点。（2）展开“数据库”|“school”|“可编程性”|“存储过程”|“dbo.studproc”节点，单击鼠标右键，在出现的快捷