SQL第9章存储过程

1第9章存储过程9.1存储过程概9.2创建管理简单存储过9.3创建管理带参的存储过9.4其他存储过29.1存储过程概述1.存储过程概念存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。2.存储过程类型系统存储过程扩展存储过程用户自定义存储过程系统存储过程存储以SP_为前缀,是由SQLServer2005自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。3第9章存储过程9.1存储过程概述9.2创建管理简单存储过程9.3创建管理带参的存储过程9.4其他存储过程49.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程59.2.1无参存储过程的创建使用SQL语句创建存储过程1）语法格式如下：CREATEPROC[EDURE]procedure_nameASsql_statement[...n]2）语法注释：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。69.2.1无参存储过程的创建例1：在PUBS数据库中，创建一个名称为pr_searchorddate的存储过程，该存储过程将查询出sales表中订购日期ord_date在1994年以后的记录信息。代码如下：CREATEPROCpr_searchorddateASSELECT*FROMsalesWHEREord_date='1994-1-1'GO7创建存储过程的注意事项只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。89.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程99.2.2无参存储过程的执行对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：EXEC[UTE]procedure_name对上例的存储过程pr_searchorddate的执行语句如下：EXECUTEpr_searchorddate109.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程119.2.3查看存储过程1.使用对象资源管理器查看存储过程2.使用系统存储过程查看存储过程信息在SQLServer中，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。1）使用sp_helptext查看存储过程的文本信息.其语法格式为：sp_helptext存储过程名2）使用sp_depends查看存储过程的相关性.其语法格式为：sp_depends存储过程名3）使用sp_help查看存储过程的一般信息.其语法格式为：sp_help存储过程名129.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程139.2.4修改存储过程修改存储过程语法格式为：ALTERPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement149.2.4修改存储过程例14：修改存储过程pr_searchempl,查询出authors表中state字段为某个州的员工信息。其程序清单如下：ALTERPROCpr_searchempl@statechar(2)AsSelect*FromauthorsWherestate=@stateGO159.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程169.2.5编译存储过程在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQLServer提供三种重新编译存储过程的方法：1、在建立存储过程时设定重新编译语法格式：CREATEPROCEDUREprocedure_nameWITHRECOMPILEASsql_statement2、在执行存储过程时设定重编译语法格式：EXECUTEprocedure_nameWITHRECOMPILE3、通过使用系统存储过程设定重编译语法格式为：EXECsp_recompileOBJECT179.2创建管理简单的存储过程9.2.1无参存储过程的创建9.2.2无参存储过程的执行9.2.3查看存储过程9.2.4修改存储过程9.2.5编译存储过程9.2.6删除存储过程189.2.6删除存储过程1.使用SSMS对象资源管理器删除存储过程2.使用DROPPROCEDURE语句删除存储过程DROPPROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下：DROPPROCEDURE存储过程名称[,n]例15：删除存储过程pr_searchemplUSEPUBSGODROPPROCEDUREpr_searchemplGO199.2.6删除存储过程例16：建立一个带参的存储过程，用于删除PUBS数据库sales表中stor_id为某某值（如‘6380’）的所有记录。实现的T-SQL语句:USEpubsGOCREATEPROCdeletesales@stor_idchar(4)ASDELETEsalesWHEREstor_id=@stor_idGO209.2.6删除存储过程存储过程的执行如下：Execdeletesales‘6380’GO21第9章存储过程9.1存储过程概述9.2创建管理简单存储过程9.3创建管理带参的存储过程9.4其他存储过程229.3创建管理带参的存储过程9.3.1创建带参存储过程的语法9.3.2创建/执行带输入参数的存储过程9.3.3创建/执行带输出参数的存储过程9.3.4存储过程的返回值239.3.1创建带参存储过程的语法1）语法格式如下：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement249.3.1创建带参存储过程的语法2）语法注释：parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。259.3创建管理带参的存储过程9.3.1创建带参存储过程的语法9.3.2创建/执行带输入参数的存储过程9.3.3创建/执行带输出参数的存储过程9.3.4存储过程的返回值269.3.2创建/执行带输入参数的存储过程例2：在PUBS数据库中创建一个存储过程pr_searchempl,查询出authors表中state字段为某个州且姓中包含某字符串的所有的员工信息。CREATEPROCpr_searchempl@statechar(2),@strvarchar(40)AsSelect*FromauthorsWherestate=@stateandau_lnamelike‘%’+@str+’%’279.3.2创建/执行带输入参数的存储过程语法格式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]对上例的存储过程pr_searchempl的执行语句如下：EXECUTEpr_searchempl‘CA’,’hi’289.3.2创建/执行带输入参数的存储过程例3：查询出northwind数据库中城市值为某某值并且雇佣时间在某某日期之后的所有员工的基本信息。实现的T-SQL语句:USEnorthwindGOCREATEPROCp_getEmployee@citynvarchar(15),@hiredatedatetimeASSELECT*FROMemployeesWHEREcity=@cityANDhiredate=@hiredateGO299.3.2创建/执行带输入参数的存储过程执行带输入参数的存储过程按位置传递参数值在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。通过参数名传递参数值在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。309.3.2创建/执行带输入参数的存储过程在输入参数中使用默认值在执行存储过程p_getEmployee时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时给输入参数指定默认值。319.3.2创建/执行带输入参数的存储过程按位置传递参数值EXECp_getEmployee‘london’,’1994-1-1’通过参数名传递参数值EXECp_getEmployee@city='london',@hiredate='1994-1-1'或EXECp_getEmployee@hiredate='1994-1-1',@city='london'329.3.2创建/执行带输入参数的存储过程例4：对存储过程p_getEmployee进行修改，指定城市默认值为‘london’，指定雇佣日期为1990年1月1日。USEnorthwindGOALTERPROCp_getEmployee@citynvarchar(15)=’london’,@hiredatedatetime=’1990-1-1’ASSELECT*FROMemployeesWHEREcity=@cityANDhiredate=@hiredateGOEXECp_getEmployee339.3创建管理带参的存储过程9.3.1创建带参存储过程的语法9.3.2创建/执行带输入参数的存储过程9.3.3创建/执行带输出参数的存储过程9.3.4存储过程的返回值349.3.3创建/执行带输出参数的存储过程例5：在PUBS数据库中创建一个存储过程pr_titleprice,统计出titles表中pub_id字段为某编号的书籍总价格。CREATEPROCpr_titleprice@pub_idchar(4),@spricemoneyoutputAsSelect@sprice=sum(price)FromtitlesWherepub_id=@pub_id存储过程的执行:declare@ssm