sql sever 数据库 入门-存储过程

1、5.5 存储过程,5.5.1 创建存储过程 5.5.2 执行存储过程 5.5.3 查看和修改存储过程 5.5.4 重命名和删除存储过程,存储过程的概念,SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,5.5.1 创建存储过程,在SQL Server中，可以使用三种方法创建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用Transact-SQL语句中的C

2、REATE PROCEDURE命令创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,1. 使用创建存储过程向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”（如图3-1所示），则出现欢迎使用创建存储过程向导对话框，如图5-71所示。根据图5-71-图5-76提示可完成创建存储过程。,图3-1 新建SQL Server组,图5-71 欢迎使用创建存储过程向导对话框,图5-72 选择数据库对话框,图

3、5-73 选择数据库对象对话框,图5-74 完成创建存储过程向导对话框,图5-75 编辑存储过程属性对话框,图5-76 编辑存储过程SQL对话框,2. 使用SQL Server 企业管理器创建存储过程,（）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，如图5-77所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图5-78所示。均会出现创建存储过程对话框，如图5-79所示。 （）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”

4、，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮，如图5-80所示。,图5-77 选择新建存储过程对话框（1）,图5-78 选择新建存储过程对话框（2）,图5-79 新建存储过程对话框,图5-80 设置权限对话框,3. 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规

5、则。 只能在当前数据库中创建存储过程。 一个存储过程的最大尺寸为128M。,使用CREATE PROCEDURE创建存储过程的语法形式如下：,CREATEPROCEDUREprocedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITHRECOMPILE|ENCRYPTION |RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement .n ,用CREATE PROCEDURE创建存储过程的语法参数的意义如下：,procedure_name：用于指定要创建的存储过程的

6、名称。 number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 VARYING：用于指定作为输出OUTPUT参数支持的结果集。 Default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。,RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划 。 ENCRYPTION ：表示 SQL Server 加密了 syscommen

7、ts 表，该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。 FOR REPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。 AS：用于指定该存储过程要执行的操作。 sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。,5.5.2 执行存储过程,直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下： EXECUTE return_status=procedure_name;number |procedure_name_var parameter=value|variableOUTPUT

8、 |DEFAULT,.n WITH RECOMPILE ,例子5-27：,使用 EXECUTE 命令传递单个参数，它执行 showind 存储过程，以 titles 为参数值。showind 存储过程需要参数 (tabname)，它是一个表的名称。其程序清单如下： EXEC showind titles 当然，在执行过程中变量可以显式命名： EXEC showind tabname = titles 如果这是 isql 脚本或批处理中第一个语句，则 EXEC 语句可以省略： showind titles或者showind tabname = titles,5.5.3 查看和修改存储过程,查看存

9、储过程 存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,（1）使用企业管理器查看用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。,（2）使用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下： sp_help：用于显示存储过程的参数及其数据类

10、型 sp_help objname= name 参数name为要查看的存储过程的名称。 sp_helptext：用于显示存储过程的源代码 sp_helptext objname= name 参数name为要查看的存储过程的名称。 sp_depends：用于显示和存储过程相关的数据库对象 sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。 sp_stored_procedures：用于返回当前数据库中的存储过程列表,2. 修改存储过程,存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行

11、 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下： ALTERPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION ASsql_statement .n ,例子5-29：,创建了一个名为 Oakland_authors 的过程，默认情况下，该过程包含所有来自加利福尼亚州奥克兰市的作者。随后授予了权限。然后，当该过程需更改为能够检索所有来自加利福尼亚州的作者时，用 ALTER PROCEDURE 重新定义了该存储过程。其程序清单如下：,5.5.4 重命名和删除存储过程,1. 重命名存储过程 修改存储过程的名称可以使用系统存储过程sp_rename，其语法形