储过程(SQL2000)

 

一．储过程：（ sq2000 ）

1 ．分类

a.       局部存储过程。用户自己创建的存储过程。

b.      系统存储过程，以 SP_ 为前缀的存储过程，用户可以在任何的数据库中执行系统存储过程，系统存储过程放在 master 数据库中，最好不要建立“ sp_” 为前缀的存储过程。

c.       临时存储过程：以 # （本地临时存储过程） ## （全局临时存储过程）为前缀 , 关闭 SQL2000

后，这些过程将不在存在， # 在创建它的会话中可用， ## 在所有会话中可用，

d.      在 sql2000 为部执行的 DLL 称为扩展存储过程，扩展存储过程的名称前缀是 ”xp_ “可将参数传递给扩展的存储过程，扩展存储过程可返回结果，可返回状态。

2 ．建存储过程

  语法：

   CREATE   PROC[DEURE]  存储过程的名称

   （参数定义部分）

   AS

（主体部分）

例：列出 Northwind 中 Products 的所有库存不 0 的产品

创建：

CREATE PROC my_ListStockProduct

AS

 SELECT *

 FROM Producs

 WHERE UnitsInstock>0

 

3 执行存储过程：

 

  语法：

   EXEC[UTE] 存储过程名 ( 参数定义部分 )

  例：

 

  EXEC my_ListStockProduct

4. 修改存储过程：

语法

 ALTER   PROC[UTE]  存储过程名称

（参数定义部分）

AS

( 主体部分 )

 

  例：

 ALTER   PROC  my_ListStockProduct

AS

SELECT *

 FROM Producs

 WHERE UnitsInstock=0 // OK 修改掉了

 

 

5 ．删除存储过程

语法

DROP PROC[UTE]  存储过程名称

例： DROP PROC my_ListStockProduct

4 ．参数化储过程：

a. 每个存储过程参数必须用唯一的名称定义。

b.参数名称必须以@字符开头

c. 存储过程的参数要定义数据类型，这与表中的列几乎是一样。（可以用 SQL2000 的任何一种数据类型 , 也可以是用户自定义数据类型）

 

例：

  创建一个存储过程 my_LisProductBySupplierID, 该存储过程得到指定供应商 ID 的产品 .

 

CREATE PROC my_LisProductBySupplierID

 @ SupplierID Integer -- 输入参数供应商的 ID

AS

 Select * 

 FROM SupplierID= @SupplierID

 

EXEC my_LisProducBySupplierID 1

 

 

6. 指定默定值 :

指定默参数的默忍值比较的重用，如果没有合适的默认值可以指定 NULL 作为参数的默认值。

 

例：

CREATE PROC my_LisProductBySupplierID2

@ my_LisProductBySupplierID=1 -- 输入参数供应商的 ID , 默认值为本 1

 

AS

 Select *

FROM my_LisProductBySupplierIDF =@ my_LisProductBySupplierID

 

在执行 EXEC my_listProductBySupplierID2 与 EXEC my_listProductBySupplierID2 1 效果是等同的，都返回供应商 ID 为 1 的产品。

 

7 。带输出参数的存储过程：

 a. 用关键字 OUTPUT 指定关键字

 b, 并且调用程序也必须指定 OUTPUT 关键字。

例：创建一个存储过程 my_GetCompanyName, 该存储过程得到指定供应商 ID 的公司名称

CREATE PROC my_GetCompanyName

@SupplierID Integer

@CompanyName nvarchar (40) OUTPUT  -- 输出参数公司名称

AS

 Select @CompanyName = CompanyName FROM Suppliers

 WHERE SupplierID = @ SupplierID

执行该存储过程，得到供应商 ID 为 1 的公司名称。

(declare: 声明变量时候用 )

DECLARE @ CName NVarChar (40)

   EXEC my_GetCompanyName 1,@CName OUTPUT --- 输出参数公司名称

   PRINT @CName

 

指定输入值给 OUTPUT 参数

例

 

DECLARE @ CName NVarChar (40)

Select @CName =’no company name’

   EXEC my_GetCompanyName 1,@CName    --- 没有指定关键字。

   PRINT @CName

那么最后 @CName 中的内容还是‘ no company name ’

 

7 ．存储过程中的错误处理：

在存储过程对错误进行处理，可以使用全局变量 @@ERROR , 它返回最后执行的 Transact-SQL 语句的错误代码。它为 integer 型的变量，在 master.dbo.sysmessages 系统表中可以查看与 @@ERROR 错误代码对应的错误文本信息。 @@ERROR 在每一个 Transact-SQL 执行后都会被重置，如果语句执行成功返回 0 ，如果失败将返回错误号。

 

例下面更新产品更新换代单价的存储过程 my_UpdatePrice, 由于单价字段设置为 Check 必须大于 0, 所以当单价设置小于 0 的时候会提示错误信息

 

CREATE PROC my_UpdatePrice

 @ProductID Integer,

 @UnitPrice   Money

AS

 UPDATE Products

SET UnitPrice = @UnitPrice

WHERE ProductID = @ProductID

IF @@ERROR= 547

RETURN ‘ 产品单价必须大于等于 0’

 

 

8 ．用户自定义错误信息

RAISERROR （错误号或错误字符串，错误级别，状态）

例：当单价小于 0 时，显示错误信息。

CREATE PROC my_UpdatePrice1

@ProductID Integer,

@UnitPrice Money

 

AS

 IF @UnitPrice>=0

 UPDATE Products

SET UnitPrice =@UnitPrice

WHERE ProductID=@ProductID

ELSE

RAISERROR (‘ 产品单价必须大于等于 0’ ,16,1)

如果这个时候执行 EXEC my_UpdatePrice1 1,-10 这样就会提示错误信息。