一.储过程:(
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.
每个存储过程参数必须用唯一的名称定义。
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
这样就会提示错误信息。