(完整版)SQLServer存储过程的基本概念以及语法汇总

SQL Server存储过程的基本概念以及语法【转】

存储过程的概念

SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server 数据库服务器来完成,以实现某个任务,这种方法就是存储过程。

存储过程是SQL语句和可选控制流语句的预编译集合,存储在数据库中,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程功能。

在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。

可以出于任何使用SQL语句的目的来使用存储过程,它具有以下优点:

可以在单个存储过程中执行一系列SQL语句。

可以从自己的存储过程内引用其他存储过程,这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快,而且减少网络通信的负担。

安全性更高。

创建存储过程

在SQL Server中,可以使用三种方法创建存储过程:

①使用创建存储过程向导创建存储过程。

②利用SQL Server 企业管理器创建存储过程。

③使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。

下面介绍使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程

创建存储过程前,应该考虑下列几个事项:

①不能将 CREATE PROCEDURE 语句与其它SQL语句组合到单个批处理中。

②存储过程可以嵌套使用,嵌套的最大深度不能超过32层。

③创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。

④存储过程是数据库对象,其名称必须遵守标识符规则。

⑤只能在当前数据库中创建存储过程。

⑥一个存储过程的最大尺寸为128M。

使用CREATE PROCEDURE创建存储过程的语法形式如下:

QUOTE:

CREATE PROC[EDURE]procedure_name[;number][;number]

[{@parameter data_type}[VARYING][=default][OUTPUT]][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement [ ...n ]

用CREATE PROCEDURE创建存储过程的语法参数的意义如下:

procedure_name:用于指定要创建的存储过程的名称。

number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。

@parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。

data_type:用于指定参数的数据类型。

VARYING:用于指定作为输出OUTPUT参数支持的结果集。

Default:用于指定参数的默认值。

OUTPUT:表明该参数是一个返回参数。

例如:下面创建一个简单的存储过程productinfo,用于检索产品信息。

USE Northwind

if exists(select name from sysobjects

where name='productinfo' and type = 'p'

drop procedure productinfo

GO

create procedure productinfo

as

select * from products

GO

通过下述sql语句执行该存储过程:execute productinfo

即可检索到产品信息。

执行存储过程

直接执行存储过程可以使用EXECUTE命令来执行,其语法形式如下:

[[EXEC[UTE]]

{ [@return_status=]

{procedure_name[;number]|@procedure_name_var} [[@parameter=]{v

alue|@variable[OUTPUT]|[DEFAULT]}

[,...n]

[ WITH RECOMPILE ]

使用 EXECUTE 命令传递单个参数,它执行 showind 存储过程,以 titles 为参数值。showind 存储过程需要参数 (@tabname,它是一个表的名称。其程序清单如下:

EXEC showind titles

当然,在执行过程中变量可以显式命名:

EXEC showind @tabname = titles

如果这是 isql 脚本或批处理中第一个语句,则 EXEC 语句可以省略:

showind titles或者showind @tabname = titles

下面的例子使用了默认参数

USE Northwind

GO

CREATE PROCEDURE insert_Products_1

( @SupplierID_2 int,

@CategoryID_3 int,

@ProductName_1 nvarchar(40='无'

AS INSERT INTO Products

(ProductName,SupplierID,CategoryID

VALUES

(@ProductName_1,@SupplierID_2,@CategoryID_3

GO

exec insert_Products_1 1,1

Select * from Products where SupplierID=1 and CategoryID=1 GO

下面的例子使用了返回参数

USE Northwind

GO

CREATE PROCEDURE query_products

( @SupplierID_1 int,

@ProductName_2 nvarchar(40 output

AS

select @ProductName_2 = ProductName from products

where SupplierID = @SupplierID_1

执行该存储过程来查询SupplierID为1的产品名:

declare @product nvarchar(40

exec query_products 1,@product output

select '产品名'= @product

go

查看存储过程

存储过程被创建之后,它的名字就存储在系统表sysobjects中,它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。

使用企业管理器查看用户创建的存储过程

在企业管理器中,打开指定的服务器和数据库项,选择要创建存储过程的数据库,单击存储过程文件夹,此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程,从弹出的快捷菜单中选择属性选项,此时便可以看到存储过程的源代码。

使用系统存储过程来查看用户创建的存储过程

可供使用的系统存储过程及其语法形式如下:

sp_help:用于显示存储过程的参数及其数据类型

sp_help [[@objname=] name]

参数name为要查看的存储过程的名称。

sp_helptext:用于显示存储过程的源代码

sp_helptext [[@objname=] name]

参数name为要查看的存储过程的名称。

sp_depends:用于显示和存储过程相关的数据库对象

sp_depends [@objname=]’object’

参数object为要查看依赖关系的存储过程的名称。

sp_stored_procedures:用于返回当前数据库中的存储过程列表

修改存储过程

存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程,但不会更改权限,也不影响相关的存储过程或触发器。其语法形式如下:

ALTERPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]][,...n] [WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS

sql_statement [ ...n ]

重命名和删除存储过程

1. 重命名存储过程

修改存储过程的名称可以使用系统存储过程sp_rename,其语法形式如下:

sp_rename 原存储过程名称,新存储过程名称

另外,通过企业管理器也可以修改存储过程的名称。

删除存储过程

删除存储过程可以使用DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除,其语法形式如下:

drop procedure {procedure} [,…n]

当然,利用企业管理器也可以很方便地删除存储过程。

存储过程的重新编译

在我们使用了一次存储过程后,可能会因为某些原因,必须向表中新增加数据列或者为表新添加索引,从而改变了数据库的逻辑结构。这时,需要对存储过程进行重新编译,SQL Server 提供三种重新编译存储过程的方法:

1、在建立存储过程时设定重新编译

语法格式:

CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement

2、在执行存储过程时设定重编译

语法格式: EXECUTE procedure_name WITH RECOMPILE

3、通过使用系统存储过程设定重编译

语法格式为: EXEC sp_recompile OBJECT

系统存储过程与扩展存储过程

1.系统存储过程

系统存储过程存储在master数据库中,并以sp_为前缀,主要用来从系统表中获取信息,为系统管理员管理SQL Server提供帮助,为用户查看数据库对象提供方便。比如用来查

看数据库对象信息的系统存储过程 sp_help、显示存储过程和其它对象的文本的存储过程 sp_helptext 等。 2.扩展存储过程：扩展存储过程以 xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL 文件所包含的函数中实现逻辑，从而扩展了 Transact-SQL 的功能，并且可以象调用 Transact-SQL 过程那样从 Transact-SQL 语句调用这些函数。例: 利用扩展存储过程 xp_cmdshell 为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。执行代码： use master exec xp_cmdshell 'dir *.exe' 执行结果返回系统目录下的文件内容文本信息。最后给大家举一个例子： QUOTE: /** 1、在 Northwind 数据库中，创建一个带查询参数的存储过程，要求在输入一个定购金额总额＠total 时，查询超出该值的所有产品的相关信息，包括产品名称和供应商名称、单位数量、单价、以及该产品的定购金额总额，并通过一个输出参数返回满足查询条件的产品数 **/ IF exists (select * from SysObjects where name='more_than_total' and type='p' drop procedure more_than_total go CREATE PROCEDURE More_Than_Total @total money = 0 AS Declare @amount smallint BEGIN select distinct P.productName, S.contactName, P.UnitPrice from Products P inner join [order Details] O

on p.productID=o.productID inner join suppliers s on p.supplierID=s.SupplierID where O.productID in (select productID from [order Details] group by productId having sum(quantity*unitprice>@total END GO