存储过程的概念
SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成,以实现某个任务,这种方法就是存储过程。
在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。
5.5.1 创建存储过程
在SQL Server中,可以使用三种方法创建存储过程 :
①使用创建存储过程向导创建存储过程。
②利用SQL Server 企业管理器创建存储过程。
③使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。
创建存储过程时,需要确定存储过程的三个组成部分:
①所有的输入参数以及传给调用者的输出参数。
②被执行的针对数据库的操作语句,包括调用其它存储过程的语句。
③返回给调用者的状态值,以指明调用是成功还是失败。
1. 使用创建存储过程向导创建存储过程
在企业管理器中,选择工具菜单中的向导选项,选择“创建存储过程向导”,则出现欢迎使用创建存储过程向导对话框
2. 使用SQL Server 企业管理器创建存储过程
(1)在SQL Server企业管理器中,选择指定的服务器和数据库,用右键单击要创建存储过程的数据库,在弹出的快捷菜单中选择“新建”选项,再选择下一级菜单中的“存储过程…”选项,或者用右键单击存储过程图标,从弹出的快捷菜单中选择“新建存储过程…”选项,如图5-78所示。均会出现创建存储过程对话框
(2)在文本框中可以输入创建存储过程的T_SQL语句,单击“检查语法”,则可以检查语法是否正确;单击“确定”按钮,即可保存该存储过程。如果要设置权限,单击“权限…”按钮
3. 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程
创建存储过程前,应该考虑下列几个事项:
①不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。
②创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。
③存储过程是数据库对象,其名称必须遵守标识符规则。
④只能在当前数据库中创建存储过程。
⑤一个存储过程的最大尺寸为128M。
使用CREATE PROCEDURE创建存储过程的语法形式如下:
CREATEPROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT] ][,...n] WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS sql_statement [ ...n ]
用CREATE PROCEDURE创建存储过程的语法参数的意义如下:
procedure_name:用于指定要创建的存储过程的名称。
number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。
@parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。
data_type:用于指定参数的数据类型。
VARYING:用于指定作为输出OUTPUT参数支持的结果集。
Default:用于指定参数的默认值。
OUTPUT:表明该参数是一个返回参数。
RECOMPILE:表明 SQL Server 不会保存该存储过程的执行计划 。
ENCRYPTION :表示 SQL Server 加密了 syscomments 表,该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。
FOR REPLICATION:用于指定不能在订阅服务器上执行为复制创建的存储过程。
AS:用于指定该存储过程要执行的操作。
sql_statement:是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。
5.5.2 执行存储过程
直接执行存储过程可以使用EXECUTE命令来执行,其语法形式如下:
[[EXEC[UTE]] { [@return_status=] {procedure_name[;number]|@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]} [,...n][ WITH RECOMPILE ]