SQL SERVER 存储过程的操作与管理

存储过程的操作与管理存储过程概述存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。在SQLServer中使用存储过程而不使用存储在客户端计算机本地的Transact-SQL程序的优点包括：（1）存储过程已在服务器注册。（2）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。（3）存储过程可以强制应用程序的安全性。（4）存储过程允许模块化程序设计。（5）存储过程是命名代码，允许延迟绑定。（6）存储过程可以减少网络通信流量。8.1创建存储过程在SQLServer中，可以使用两种方法创建存储过程：（1）使用创建存储过程模板创建存储过程；（2）利用SQLServer管理平台创建存储过程。当创建存储过程时，需要确定存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。（3）返回给调用者的状态值，以指明调用是成功还是失败。8.1创建存储过程•CREATEPROCEDURE的语法形式如下：CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITHprocedure_option[,...n][FORREPLICATION]AS{sql_statement[;][...n]|method_specifier}[;]procedure_option::=[ENCRYPTION][RECOMPILE]EXECUTE_AS_Clause]sql_statement::={[BEGIN]statements[END]}method_specifier::=EXTERNALNAMEassembly_name.class_name.method_name8.1.1使用模板创建存储过程（1）在SQLServer管理平台中，选择“视图（View）”菜单中的“模板资源资源管理器(TemplateExplorer)”，出现“模板资源管理器(TemplateExplorer)”窗口，选择“存储过程”中的“创建存储过程”选项，如图8-1所示。（2）在文本框中可以输入创建存储过程的Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。图8-1创建存储过程模板8.1.2使用管理平台创建存储过程（1）在SQLServer管理平台中，展开指定的服务器和数据库，然后展开程序，右击存储过程选项，在弹出的快捷菜单中依次选择“新建→存储过程…”选项，如图8-2所示，出现创建存储过程窗口。（2）在文本框中可以输入创建存储过程的Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。图8-2新建存储过程8.1.2使用管理平台创建存储过程例8-1创建一个带有SELECT语句的简单过程，该存储过程返回所有员工姓名，Email地址，电话等。该存储过程不使用任何参数程序清单如下。USEadventureworksGOCREATEPROCEDUREau_infor_allASSELECTlastname,firstname,emailaddress,phoneFROMperson.contactGO8.1.2使用管理平台创建存储过程例8-2创建一个存储过程，以简化对sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。程序清单如下：CREATEPROCEDURE[dbo].[pr1_sc_ins]@Param1char(10),@Param2char(2),@Param3realASBEGINinsertintosc(sno,cno,score)values(@Param1,@Param2,@Param3)END8.1.2使用管理平台创建存储过程例8-3创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值程序清单如下。USEAdventureWorks;GOCREATEPROCEDUREGetEmployees@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTLastName,FirstName,JobTitle,DepartmentFROMHumanResources.vEmployeeDepartmentWHEREFirstName=@firstnameANDLastName=@lastname;GO8.1.2使用管理平台创建存储过程例8-4下面的存储过程从表person.contact中返回指定的一些员工姓名及其电话。该存储过程对传递的参数进行模式匹配。如果没有提供参数，则使用预设的默认值（姓氏以字母D开头）程序清单如下。USEAdventureWorks;GOCREATEPROCEDUREau_infor2@lastnamevarchar(40)='D%',@firstnamevarchar(20)='%'ASSELECTfirstname,lastname,phoneFROMperson.contactWHEREfirstnameLIKE@firstnameANDlastnameLIKE@lastnameGO8.1.2使用管理平台创建存储过程例8-5以下示例显示有一个输入参数和一个输出参数的存储过程。存储过程中的第一个参数@sname将接收由调用程序指定的输入值(学生姓名)，第二个参数@sscore（成绩）将用于将该值返回调用程序。SELECT语句使用@sname参数获取正确的@sscore值，并将该值分配给输出参数。程序清单如下：CREATEPROCEDUREs_score@snamechar(8),@sscorerealoutputASSELECT@sscore=scorefromscjoinsons.sno=sc.snowheresn=@snameGO8.1.3执行存储过程•可以使用Transact-SQLEXECUTE语句来运行存储过程。存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接在表达式中使用。•执行存储过程必须具有执行存储过程的权限许可，才可以直接执行存储过程，直接执行存储过程可以使用EXECUTE命令来执行，语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE]8.1.3执行存储过程例8-6执行存储过程au_infor_all。au_infor_all存储过程可以通过以下方法执行：EXECUTE（EXEC）au_infor_all例8-7使用EXECUTE命令传递参数，执行例8-2定义的存储过程pr1_sc_ins。sc_ins存储过程可以通过以下方法执行：EXECpr1_sc_ins‘3130040101’,’c1’,85当然，在执行过程中变量可以显式命名：EXECsc_ins@Param1=’3130040101’,@Param2=’c1’,@Param3=85例8-8执行例8-3定义的存储过程GetEmployees。GetEmployees存储过程可以通过以下方法执行：EXECUTE（EXEC）GetEmployees'Dull','Ann'或者EXECUTE（EXEC）GetEmployees@lastname='Dull',@firstname='Ann'或者EXECUTE（EXEC）GetEmployees@firstname='Ann',@lastname='Dull'8.2查看、修改和删除存储过程8.2.1查看存储过程8.2.2修改存储过程8.2.3重命名和删除存储过程8.2.1查看存储过程（1）使用SQLServer管理平台查看用户创建的存储过程。在SQLServer管理平台中，展开指定的服务器和数据库，选择并依次展开“程序→存储过程”，然后右击要查看的存储过程名称，如图8-3所示，从弹出的快捷菜单中，选择“创建存储过程脚本为→CREATE到→新查询编辑器窗口”，则可以看到存储过程的源代码。图8-3查看存储过程8.2.1查看存储过程（2）使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下：•sp_help，用于显示存储过程的参数及其数据类型，其语法为：sp_help[[@objname=]name]，参数name为要查看的存储过程的名称。•sp_helptext，用于显示存储过程的源代码，其语法为：sp_helptext[[@objname=]name]，参数name为要查看的存储过程的名称。•sp_depends，用于显示和存储过程相关的数据库对象，其语法为：sp_depends[@objname=]’object’，参数object为要查看依赖关系的存储过程的名称。•sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为：sp_stored_procedures[[@sp_name=]'name'][,[@sp_owner=]'owner'][,[@sp_qualifier=]'qualifier']其中，[@sp_name=]'name'用于指定返回目录信息的过程名；[@sp_owner=]'owner'用于指定过程所有者的名称；[@qualifier=]'qualifier'用于指定过程限定符的名称。8.2.2修改存储过程•存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。•修改存储过程语法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]8.2.2修改存储过程例8-9创建了一个名为proc_person的存储过程，该存储过程包含姓名和Email地址信息。然后，用ALTERPROCEDURE重新定义了该存储过程，使之只包含姓名信息，并使用ENCRYPTION关键字使之无法通过查看syscomments表来查看存储过程的内容。程序清单如下。USEadventureworksGO/*创建一个存储过程，该存储过程包含姓名和Email地址信息*/CREATEPROCEDUREproc_personASSELECTfirstname,lastname,emailaddressFROMperson.contactORDERBYlastname,firstnameGO8.2.2修改存储过程下面对该存储过程进行重新定义。使之只包含姓名信息，并使用ENCRYPTION关键字使之无法通过查看syscomments表来查看存储过程的内容。程序清单如下：ALTERPROCEDUREproc_personWITHENCRYPTIONASSELECTfirstname,lastnameFROMperson.contactORDERBYlastname,firstnameGO8.2.3重命名和删除存储过程1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_re