--创建调试环境 if object_id('tbl_t') is null print 'not exists' else drop table tbl_t create table tbl_a ( dspid varchar(20), shul int, pihao varchar(20) ) create table tbl_b ( dspid varchar(20), shul int, pihao varchar(20) ) --插入数据 insert into tbl_a(dspid,shul) select 'SP000000014', 21.00 union select 'SP000000014', 6.00 union select 'SP000000014', 35.00 union select 'SP000000014', 10.00 union select 'SP000000014', 23.00 union select 'SP000000015', 5.00 union select 'SP000000015', 5.00 union select 'SP000000015', 3.00 union select 'SP000000015', 8.00 union select 'SP000000015', 2.00 union select 'SP000000015', 4.00 union select 'SP000000015', 2.00 union select 'SP000000015', 7.00 union select 'SP000000016', 2.00 union select 'SP000000016', 3.00 union select 'SP000000016', 3.00 union select 'SP000000016', 3.00 union select 'SP000000016', 8.00 union select 'SP000000016', 2.00 union select 'SP000000016', 4.00 union select 'SP000000016', 2.00 union select 'SP000000016', 1.00 --select * from tbl_a insert into tbl_b select 'SP000000016', 50, '123' union select 'SP000000016', 20 , '012' union select 'SP000000016', 5 , '000' union select 'SP000000015', 12 , '541' union select 'SP000000015', 100 ,'145' union select 'SP000000014', 1000 ,'000'select * from tbl_a select * from tbl_b go --创建存储过程 --============================================================== if object_id('pro_t') is null print 'not exists' else drop proc pro_t --drop proc pro_t go create proc pro_t asDECLARE cur_b CURSOR keyset--定义B表的游标 FOR SELECT dspid,shul,pihao FROM tbl_b order by pihaoDECLARE @shul_a numeric(20,2) DECLARE @shul_b numeric(20,2) declare @dspid_b varchar(20)--保存b表的dispid字段值,以便后面取a表数据时用 declare @pihao varchar(20)OPEN cur_b --打开B表的游标 FETCH NEXT FROM cur_b INTO @dspid_b,@shul_b,@pihao WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN ------------------------ DECLARE cur_a CURSOR --定义A表的游标 FOR SELECT shul FROM tbl_a where pihao is null and dspid=@dspid_b FOR UPDATE of pihao open cur_a --打开A表的游标 FETCH NEXT FROM cur_a INTO @shul_a WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @shul_b=@shul_b-@shul_a --用b表中的数量前去a表中当前记录的数量
-- PRINT 'add user defined code here' -- eg. update tbl_a set pihao=@pihao where current of cur_a --select * from tbl_a where current of cur_a --PRINT 'updating record for ' + @name --UPDATE pubs.dbo.authors --SET phone = replace(phone, ' ', '-') --WHERE CURRENT OF cur_a if @shul_b<=0 --如果数量已经等于或小于0,说明当前批号的dspid已经完了,跳出循环 begin --set @shul_b=0 break end END FETCH NEXT FROM cur_a INTO @shul_a END CLOSE cur_a --关闭A表的游标 DEALLOCATE cur_a --销掉A表的游标 -------------- ------------------------------ END FETCH NEXT FROM cur_b INTO @dspid_b,@shul_b,@pihao ENDCLOSE cur_b --关闭B表的游标 DEALLOCATE cur_b --销掉B表的游标GOexec pro_t select * from tbl_a --============================================================== --删除表 drop table tbl_a drop table tbl_b--结果: /* not exists(所影响的行数为 16 行) (所影响的行数为 6 行)dspid shul pihao -------------------- ----------- -------------------- SP000000014 6 NULL SP000000014 10 NULL SP000000014 21 NULL SP000000014 23 NULL SP000000014 35 NULL SP000000015 2 NULL SP000000015 3 NULL SP000000015 4 NULL SP000000015 5 NULL SP000000015 7 NULL SP000000015 8 NULL SP000000016 1 NULL SP000000016 2 NULL SP000000016 3 NULL SP000000016 4 NULL SP000000016 8 NULL(所影响的行数为 16 行)dspid shul pihao -------------------- ----------- -------------------- SP000000014 1000 000 SP000000015 12 541 SP000000015 100 145 SP000000016 5 000 SP000000016 20 012 SP000000016 50 123(所影响的行数为 6 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)dspid shul pihao -------------------- ----------- -------------------- SP000000014 6 000 SP000000014 10 000 SP000000014 21 000 SP000000014 23 000 SP000000014 35 000 SP000000015 2 145 SP000000015 3 145 SP000000015 4 145 SP000000015 5 145 SP000000015 7 145 SP000000015 8 145 SP000000016 1 000 SP000000016 2 000 SP000000016 3 000 SP000000016 4 012 SP000000016 8 012(所影响的行数为 16 行) */
--创建调试环境
if object_id('tbl_t') is null
print 'not exists'
else
drop table tbl_t
create table tbl_a
(
dspid varchar(20),
shul int,
pihao varchar(20)
)
create table tbl_b
(
dspid varchar(20),
shul int,
pihao varchar(20)
)
--插入数据
insert into tbl_a(dspid,shul)
select
'SP000000014', 21.00
union select
'SP000000014', 6.00
union select
'SP000000014', 35.00
union select
'SP000000014', 10.00
union select
'SP000000014', 23.00
union select
'SP000000015', 5.00
union select
'SP000000015', 5.00
union select
'SP000000015', 3.00
union select
'SP000000015', 8.00
union select
'SP000000015', 2.00
union select
'SP000000015', 4.00
union select
'SP000000015', 2.00
union select
'SP000000015', 7.00
union select
'SP000000016', 2.00
union select
'SP000000016', 3.00
union select
'SP000000016', 3.00
union select
'SP000000016', 3.00
union select
'SP000000016', 8.00
union select
'SP000000016', 2.00
union select
'SP000000016', 4.00
union select
'SP000000016', 2.00
union select
'SP000000016', 1.00
--select * from tbl_a
insert into tbl_b
select
'SP000000016', 50, '123'
union select
'SP000000016', 20 , '012'
union select
'SP000000016', 5 , '000'
union select
'SP000000015', 12 , '541'
union select
'SP000000015', 100 ,'145'
union select
'SP000000014', 1000 ,'000'select * from tbl_a
select * from tbl_b
go
--创建存储过程
--==============================================================
if object_id('pro_t') is null
print 'not exists'
else
drop proc pro_t
--drop proc pro_t
go
create proc pro_t
asDECLARE cur_b CURSOR keyset--定义B表的游标
FOR SELECT dspid,shul,pihao FROM tbl_b order by pihaoDECLARE @shul_a numeric(20,2)
DECLARE @shul_b numeric(20,2)
declare @dspid_b varchar(20)--保存b表的dispid字段值,以便后面取a表数据时用
declare @pihao varchar(20)OPEN cur_b --打开B表的游标
FETCH NEXT FROM cur_b INTO @dspid_b,@shul_b,@pihao
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
------------------------
DECLARE cur_a CURSOR --定义A表的游标
FOR SELECT shul FROM tbl_a where pihao is null and dspid=@dspid_b
FOR UPDATE of pihao
open cur_a --打开A表的游标 FETCH NEXT FROM cur_a INTO @shul_a
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @shul_b=@shul_b-@shul_a --用b表中的数量前去a表中当前记录的数量
-- PRINT 'add user defined code here'
-- eg.
update tbl_a set pihao=@pihao where current of cur_a
--select * from tbl_a where current of cur_a
--PRINT 'updating record for ' + @name
--UPDATE pubs.dbo.authors
--SET phone = replace(phone, ' ', '-')
--WHERE CURRENT OF cur_a
if @shul_b<=0 --如果数量已经等于或小于0,说明当前批号的dspid已经完了,跳出循环
begin
--set @shul_b=0
break
end
END
FETCH NEXT FROM cur_a INTO @shul_a
END
CLOSE cur_a --关闭A表的游标
DEALLOCATE cur_a --销掉A表的游标
--------------
------------------------------
END
FETCH NEXT FROM cur_b INTO @dspid_b,@shul_b,@pihao
ENDCLOSE cur_b --关闭B表的游标
DEALLOCATE cur_b --销掉B表的游标GOexec pro_t
select * from tbl_a
--==============================================================
--删除表
drop table tbl_a
drop table tbl_b--结果:
/*
not exists(所影响的行数为 16 行)
(所影响的行数为 6 行)dspid shul pihao
-------------------- ----------- --------------------
SP000000014 6 NULL
SP000000014 10 NULL
SP000000014 21 NULL
SP000000014 23 NULL
SP000000014 35 NULL
SP000000015 2 NULL
SP000000015 3 NULL
SP000000015 4 NULL
SP000000015 5 NULL
SP000000015 7 NULL
SP000000015 8 NULL
SP000000016 1 NULL
SP000000016 2 NULL
SP000000016 3 NULL
SP000000016 4 NULL
SP000000016 8 NULL(所影响的行数为 16 行)dspid shul pihao
-------------------- ----------- --------------------
SP000000014 1000 000
SP000000015 12 541
SP000000015 100 145
SP000000016 5 000
SP000000016 20 012
SP000000016 50 123(所影响的行数为 6 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)dspid shul pihao
-------------------- ----------- --------------------
SP000000014 6 000
SP000000014 10 000
SP000000014 21 000
SP000000014 23 000
SP000000014 35 000
SP000000015 2 145
SP000000015 3 145
SP000000015 4 145
SP000000015 5 145
SP000000015 7 145
SP000000015 8 145
SP000000016 1 000
SP000000016 2 000
SP000000016 3 000
SP000000016 4 012
SP000000016 8 012(所影响的行数为 16 行)
*/