嵌套的游标怎么写?

[New919]

高手写个大概的样子给我看看 谢谢

[点点星灯]

--给个例子参考

IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'Place_OrderPrice'
AND type = 'P')
DROP PROCEDURE Place_OrderPrice
GO

CREATE Procedure Place_OrderPrice
AS

declare @gatheringid numeric(12)
declare @notecode varchar(12)
declare @rederamount numeric(12,2)
declare @updatedate_g datetime
declare @sendformid numeric(12)
declare @sendformcode varchar(32)
declare @sendformnumber numeric(12,2)
declare @sendfromupdatedate datetime
declare @balance_num numeric(12,2)
declare @balance_price numeric(12,2)
declare @inum_pay numeric(12,2)

begin
declare cur1 cursor
for SELECT gatheringid,notecode,rederamount,updatedate from gathering_info_t where isvalid='1' and rederamount>0 order by updatedate
open cur1
fetch next from cur1 INTO @gatheringid,@notecode,@rederamount,@updatedate_g

while (@@fetch_status=0 )
begin
--取开票信息
declare cur2 cursor
for SELECT sendformid,sendformcode,sendformnumber,updatedate from sendform_info_t where isvalid='1' order by updatedate
open cur2
fetch next from cur2 INTO @sendformid,@sendformcode,@sendformnumber,@sendfromupdatedate

while (@@fetch_status=0 and @rederamount>0)
begin
--取本张发票已结算数量
select @inum_pay=isnull(sum(balancenumber),0)
from place_orderprice_t
where sendformid=@sendformid
set @balance_num=@balance_num-@inum_pay

select @balance_price=orderprice from old_orderprice_t where startdate<=@updatedate_g and @updatedate_g<enddate
if @rederamount/@balance_price>=@sendformnumber
begin
set @balance_num=@sendformnumber
end
else
begin
set @balance_num=@rederamount/@balance_price
end
if @balance_num>0
begin
insert into place_orderprice_t(sendformid,balancenumber,gatherid,orderprice) values(@sendformid,@balance_num,@gatheringid,@balance_price)
---update gathering_info_t set rederamount=rederamount-(@balance_price*@balance_num)
set @rederamount=@rederamount-(@balance_price*@balance_num)

end



fetch next from cur2 into @sendformid,@sendformcode,@sendformnumber,@sendfromupdatedate
end
close cur2
deallocate cur2
fetch next from cur1 INTO @gatheringid,@notecode,@rederamount,@updatedate_g
end

close cur1
deallocate cur1
print '执行完毕'
end

--其实你还应该考虑当一张收据上的金额没有完全结算形成预付款时应该怎样处理

[点点星灯]

其实就象写两个FOR循环一样的，注意循环的位置不要错了

[pengda1i]

copy from 联机帮助

B. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname

PRINT @message

-- Declare an inner cursor based
-- on au_id from the outer cursor.

DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor

OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title

IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title

END

CLOSE titles_cursor
DEALLOCATE titles_cursor

-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------

----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?

----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear

[pengda1i]

我觉得联机帮助的用法非常标准，为什么楼主不自己去找呢

其它的例子还需要说明表结构，麻烦哦

[zlp321002]

一般不要在数据库处理循环。
非要处理循环用表的DML 来代替循环。数据量大了。优化是个大问题。

[中国风]

楼主，首先你要看一下介绍游标的书（一般就几页）,学习一下游标的语法，定义游标、打开游标、定义循环语句while @@FETCH_STATUS=（0\1\2）0为成功、1失败、2无效,然后begin .....end、关闭游标、释放游标。楼主你学习一天就无敌了!!

[$扫地僧$]

嵌套游标
----------------------------
create table A
(
id int
)

create table A_i
(
id int,
id_i int,
T_name varchar(10)
)

insert A select 1
insert A select 2
insert A select 3

insert A_i select 1,1,'A'
insert A_i select 1,2,'B'
insert A_i select 1,3,'C'
insert A_i select 2,1,'D'
insert A_i select 2,2,'E'
insert A_i select 3,1,'F'
insert A_i select 3,3,'G'
insert A_i select 3,4,'H'

select * from A
select * from A_i

-------
用游标实现 ABCDEFGH 两层的

Declare T_SC cursor for Select id from A
Declare @Id int
Declare @Id_i int
Declare @T_name varchar(10)
Declare @T varchar(8000)
set @T=''
open T_SC
fetch next from T_SC into @Id
while @@FETCH_STATUS=0
begin
Declare T_SC1 cursor for Select T_name from A_i where id=@Id ---内部游标
open T_SC1
fetch next from T_SC1 into @T_name
while @@FETCH_STATUS=0
begin
set @T=@T+@T_name
fetch next from T_SC1 into @T_name
end
close T_SC1
deallocate T_SC1
fetch next from T_SC into @Id
end
close T_SC
deallocate T_SC

print @T