创建五种存储过程及游标的使用

–创建存储过程

–A. 创建使用参数的存储过程
CREATE PROC au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM table1
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

–B. 创建使用参数默认值的存储过程
CREATE PROC pub_info2
@pubname varchar(40) = ‘Algodata Infosystems’
AS
SELECT au_lname, au_fname, pub_name
FROM table2
WHERE @pubname = p.pub_name

–C. 执行用显式值替代参数默认值的存储过程

–在下例中,存储过程 showind2 的 @table 参数默认值是 titles。

CREATE PROC showind2
@table varchar(30) = ‘titles’
AS
SELECT
TABLE_NAME = table2.name,
INDEX_NAME = table1.name,
INDEX_ID = indid
FROM table1 INNER JOIN table2
ON table2.id = table1.id
WHERE table2.name = @table

–D. 使用参数默认值 NULL 创建存储过程

–参数默认值可以是 NULL 值。在这种情况下,如果未提供参数,则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。

–过程定义还可指定当不给出参数时要采取的其它某种措施。例如:

CREATE PROC showind3
@table varchar(30) = NULL
AS
IF @table IS NULL
PRINT ‘Give a table name’
ELSE
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name = @table

–E. 使用包含通配符的参数默认值创建存储过程

–如果存储过程将参数用于 LIKE 关键字,那么默认值可包括通配符(%、_、[] 和 [^])。例如,可将 showind 修改为当不提供参数时显示有关系统表的信息:

CREATE PROC showind4
@table varchar(30) = ‘sys%’
AS
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name LIKE @table

–创建游标

–新建游标
declare youbiao cursor
for
select SubID,SubType,SubMoney,SubYear,ChargeDep
from table2
where pid=@feeStandard
–打开游标
open youbiao
–从游标里取出数据给 变量 赋值
fetch next from youbiao
into @subID,@subType,@subMoney,@subYear,@chargeDep
begin
–判断游标的状态
— 0 fetch语句成功
— -1 fetch语句失败或此行不在结果集中
— -2 被提取的行不存在
while @@fetch_status = 0

begin
set @stuPayData = (select isnull(count(*),0) from table1 where SubYear=@subYear and SubID=@subID and StuID=@studentID )
if(@stuPayData = 0)
begin
insert into table1(StuID, DepName, ClassName, FeeID, SubID, SubType, ShouldPayment, SubYear, ChargeDep, CUser, CTime)
select @studentID, DepName, ClassName, @feeStandard, @subID, @subType, @subMoney, @subYear, @chargeDep, @cuser, getdate() from StudentInfo where id=@studentID
end

–用游标去取下一条记录
fetch next from youbiao into @subID,@subType,@subMoney,@subYear,@chargeDep

end

end
–关闭游标
close youbiao
–撤销游标
deallocate youbiao

END

–在存储过程中使用游标调用其他存储过程

declare cur_name cursor for select type from table1;  –定义游标
declare @type varchar(50);
open cur_name;
fetch next from cur_name into @type;
while @@fetch_status =0
begin
if @type = ‘其他存储过程’
begin
exec 其他存储过程;
end
else if @type = 函数
begin
select dbo.函数();
end
fetch next from cur_name into @type;
end
close cur_name;
deallocate cur_name;

标签

发表评论

评论已关闭。

评论列表(1)

  • aime

    2013.11.14 13:11

    :mrgreen:哈哈哈 :mrgreen: