--给个例子参考IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Place_OrderPrice' AND type = 'P') DROP PROCEDURE Place_OrderPrice GOCREATE Procedure Place_OrderPrice ASdeclare @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_gwhile (@@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 endclose cur1 deallocate cur1 print '执行完毕' end--其实你还应该考虑当一张收据上的金额没有完全结算形成预付款时应该怎样处理
其实就象写两个FOR循环一样的,注意循环的位置不要错了
copy from 联机帮助B. 使用嵌套游标生成报表输出 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @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_idOPEN authors_cursorFETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameWHILE @@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 ENDCLOSE 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
嵌套游标 ---------------------------- 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 3insert 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_SCprint @T
FROM sysobjects
WHERE name = N'Place_OrderPrice'
AND type = 'P')
DROP PROCEDURE Place_OrderPrice
GOCREATE Procedure Place_OrderPrice
ASdeclare @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_gwhile (@@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
endclose cur1
deallocate cur1
print '执行完毕'
end--其实你还应该考虑当一张收据上的金额没有完全结算形成预付款时应该怎样处理
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。SET NOCOUNT ONDECLARE @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_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@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
ENDCLOSE 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
非要处理循环用表的DML 来代替循环。数据量大了。优化是个大问题。
----------------------------
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 3insert 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_SCprint @T