SQL server 2005存储过程

SQLServer2005实用教程电子工业出版社出版第8章存储过程3存储过程11.1存储过程综述11.2创建、执行、修改、删除简单存储过程11.3创建和执行含参数的存储过程11.4存储过程的重新编译11.5系统存储过程与扩展存储过程11.6案例中的存储过程4导言：存储过程例：查看某系（计算机系、商务技术系、机电系、人文系）的班级名称。createviewxb_bjmcasselect班级名称from班级where系部代码=（select系部代码from系部where系部名称=‘计算机系’）51、存储过程概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。存储过程综述6存储过程综述2、存储过程的特点存储过程同其它编程语言中的过程相似，有如下特点：接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。包含执行数据库操作（包括调用其它过程）的编程语句。向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。73、优点：1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。2、改良了执行性能：只在第一次执行时进行编译，以后执行无需重新编译，而一般SQL语句每执行一次就编译一次。3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。存储过程综述84、存储过程类型用户定义的存储过程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。系统存储过程：系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help。从物理意义上讲，系统存储过程存储在资源数据库中。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。存储过程综述9存储过程综述扩展存储过程：指SQLServer的实例动态加载和运行的DLL，这些DLL通常是用编程语言（例如：C）创建的。扩展存储过程以xp_为前缀。临时存储过程：以“#”和“##”为前缀的过程，“#”表示本地临时存储过程，“##”表示全局临时存储过程，它们存储在tempdb数据库中。远程存储过程：是在远程服务器的数据库中创建和存储的过程。这些存储过程可被各种服务器访问，向具有相应许可权限的用户提供服务。10创建存储过程时，需要注意下列事项：只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。创建简单存储过程11创建简单存储过程使用SSMS管理存储过程创建执行查看修改删除12使用SQL语句创建不带参数的存储过程语法格式如下：CREATEPROC[EDURE]procedure_name[;number][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。RECOMPILE：SQL不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：SQLServer加密使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。创建简单存储过程13创建简单存储过程number:是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。14例、在shop数据库中，创建一个查询存储过程up_GoodsType，要求该存储过程商品表中的类别信息。（1）在创建本例存储过程时，可以先在查询编辑器中编写实现存储过程功能的T-SQL语句。代码如下：useshopgoselectdistinct类别号from商品表创建简单存储过程15创建简单存储过程（2）调试该语句正确后，再创建存储过程。在查询编辑器中输入其完整的程序代码如下：useshopgocreateprocup_GoodsTypesasselectdistinct类别号from商品表go（3）单击“分析”按钮，进行语法检查；语法无误后，单击“执行”按钮，创建该存储过程。16对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：[EXEC[UTE]]procedure_name[number]注：如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。执行存储过程17执行存储过程例：在查询分析器中执行存储过程ST_PROC_BJ，其代码清单如下：USEshopGOEXECUTEup_GoodsTypeGO18查看简单存储过程使用sp_help查看存储过程的一般信息，包含存储过程的名称、拥有者、类型和创建时间，其语法格式为：sp_help存储过程名使用sp_helptext查看存储过程的定义信息，其语法格式为：sp_helptext存储过程名使用sp_depends查看存储过程的相关性，其语法格式为：sp_depends存储过程名19查看简单存储过程EXECsp_helpup_GoodsTypesEXECsp_helptextup_GoodsTypesEXECsp_dependsup_GoodsTypesGO20修改简单存储过程修改存储过程的T-SQL语句为ALTERPROCEDURE，其语法格式为：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]21删除存储过程使用DROPPROCEDURE语句删除存储过程DROPPROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下：DROPPROCEDURE存储过程名称[,…n]例：删除存储过程。代码如下：USEshopGODROPPROCEDUREup_GoodsTypesGO22使用SQL语句创建带参数的存储过程语法格式如下：CREATEPROC[EDURE]procedure_name[number][{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]创建和执行含参数的存储过程23OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。Default：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值即可执行过程。创建和执行含参数的存储过程24使用输入参数例：在shop数据库中，创建一个查询存储过程up_Good，要求该存储过程带一个输入参数，用于接收类别名称。执行该存储过程时，将根据输入的类别名称列出该类别的商品名称。代码如下：CREATEPROCup_Good@leibiemvarchar(30)ASSELECT商品名称FROM商品表WHERE类别号=(SELECT类别号FROM类别表WHERE类别名称=@leibiem)25使用输入参数执行带参数的存储过程，可以采用以下两种方式：按位置传递：在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义的参数的顺序一致。使用参数名称传递：在调用存储过程时，按“参数名=参数值”的形式给出参数值。采用此方式，参数如果多于一个时，给出的参数顺序可以与定义的参数顺序不一致。如：执行存储过程up_Good，查看“通信产品”的班级名称，代码如下：EXECup_Good‘通信产品’EXECup_Good@leibiem=‘通信产品’26使用输入参数--设置参数默认值对存储过程up_Good2进行修改，实现默认显示“通信产品”的“商品名称”的功能。代码如下：（1）createPROCup_Good2@leibiemvarchar(30)=‘通信产品’ASSELECT商品名称FROM商品表WHERE类别号=(SELECT类别号FROM类别表WHERE类别名称=@leibiem)27使用输入参数--设置参数默认值（2）Execup_Good2（3）Execup_Good2‘家用电器’28使用输出参数(返回值）在shop数据库中，创建一个查询存储过程up_jiagep，要求该存储过程带一个输出参数，用于返回平均价格，一般情况下，输出参数的数据类型要与它接收的确定值的类型一致。执行该存储过程时，将把所有商品的平均价格传递出来。代码如下：USEshopGOCREATEPROCup_jiagepj@pjjiagefloatOUTPUTASSELECT@pjjiage=AVG(价格)FROM商品表GO29使用输出参数（返回值）USEshopGODECLARE@pjfloatEXECUTEup_jiagepj@pjOUTPUTPRINT‘所有商品的平均价格为：’+STR(@pj)GO30使用return语句（返回值）USEshopGOCREATEPROCup_jiagepjASdeclare@pjjiagefloatSELECT@pjjiage=AVG(价格)FROM商品表return@pjjiageGO31使用return语句（返回值）USEshopGODECLARE@pjfloatEXECUTE@pj=up_jiagepjPRINT‘所有商品的平均价格为：’+STR(@pj)GO32使用多个参数在shop数据库中，创建一个存储过程up_PriceByGno，要求该存储过程能根据商品号返回对应商品的价格。代码如下：（1）创建存储过程useshopgocreateprocup_PriceByGno@bhchar(10),@pricefloatoutputasselect@price=价格from商品表where商品号=@bh33使用多个参数（2）执行存储过程useshopgodeclare@tempPricefloatexecuteup_PriceByGno‘0001’,@tempPriceoutputcreateprocup_PriceByGno@bhchar(10),@pricefloatoutput34存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，SQLServer将其从缓存中调出执行。有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，如果调用缓存中的存储过程，需要对它