SQL Server游标使用练习

create table 库存(
wareid varchar(13),
makeno varchar(40),
storeqty numeric(16,6))


insert into 库存 select 'A','批号1',30
insert into 库存 select 'A','批号2',40
insert into 库存 select 'A','批号3',50


insert into 库存 select 'B','批号8',15
insert into 库存 select 'B','批号9',40
insert into 库存 select 'B','批号12',1000


insert into 库存 select 'C','批号1',60
insert into 库存 select 'C','批号4',120


create table 需求(
wareid varchar(13),
applyqty numeric(16,6)) 


insert into 需求 select 'A',80
insert into 需求 select 'B',50
insert into 需求 select 'C',180




create table 分配(
wareid varchar(13),
makeno varchar(40),
分配数量 numeric(16,6))


做一个循环，每循环一次，就去判断每个品种最老的批号的库存，是否需要分配、需要分配多少。这样的话，该循环的最大循环次数，取决于需求批号个数最多的那个品种。




我的代码：
declare cur_req cursor local for select wareid,applyqty from 需求
declare @wareid varchar(13)
declare @makeno varchar(40)
declare @applyqty numeric(16,6)
declare @storeqty numeric(16,6)
begin tran
 begin
open cur_req
fetch cur_req into @wareid, @applyqty
while @@FETCH_STATUS = 0
begin
--print @applyqty
 declare cur_ware_batch cursor local for select makeno, storeqty from 库存 where wareid = @wareid order by cast(substring(makeno,3, LEN(makeno)) as int)
 open cur_ware_batch
 fetch cur_ware_batch into @makeno, @storeqty
 while @@FETCH_STATUS = 0
begin
 --print @makeno +'-'+ cast(@storeqty as varchar(100))
 if @storeqty <= @applyqty
begin
 update 库存 set storeqty = storeqty-@storeqty where wareid =@wareid and makeno = @makeno
 insert into 分配(wareid, makeno, 分配数量) values(@wareid, @makeno, @storeqty)
 set @applyqty = @applyqty - @storeqty
 if @applyqty <= 0 break
end
 else
begin
 --print 'xxxx'
 update 库存 set storeqty = storeqty-@applyqty where wareid =@wareid and makeno = @makeno
 insert into 分配(wareid, makeno, 分配数量) values(@wareid, @makeno, @applyqty)
 break
end
 fetch cur_ware_batch into @makeno, @storeqty
     
end
close cur_ware_batch
deallocate cur_ware_batch
fetch cur_req into @wareid, @applyqty 
end
close cur_req
deallocate cur_req
commit
end

--Oracle答案:
DECLARE
 --求出能满足分配的每个商品的截止批次号
  CURSOR cur_dist_ware IS
    SELECT wareid, makeno, storeqty, applyqty, distsum, last_distsum
      FROM (SELECT wareid,
                   makeno,
                   storeqty,
                   applyqty,
                   distsum,
                   lag(distsum) over(PARTITION BY wareid ORDER BY makeno) last_distsum
              FROM (SELECT s.wareid,
                           s.makeno,
                           s.storeqty,
                           r.applyqty,
                           SUM(s.storeqty) over(PARTITION BY s.wareid ORDER BY s.makeno) distsum
                      FROM t_store s, t_requirement r
                     WHERE s.wareid = r.wareid))
     WHERE distsum - applyqty >= 0
       AND nvl(last_distsum, 0) - applyqty < 0;
BEGIN
  FOR rec IN cur_dist_ware LOOP
    --dbms_output.put_line(rec.wareid || '-' || rec.makeno);
    --将批次小于求出批次的库存全部插入分配表
    INSERT INTO t_dist
      (wareid, makeno, distqty)
      SELECT rec.wareid,
             st.makeno,
             CASE
               WHEN (st.storeqty - rec.applyqty) < 0 THEN
                st.storeqty
               ELSE
                (st.storeqty - rec.applyqty)
             END
        FROM t_store st
       WHERE st.wareid = rec.wareid
         AND st.makeno <= rec.makeno;
  
    --检查分配数量大于申请数量的情况
    MERGE INTO t_dist d
    USING (SELECT SUM(dist.distqty) sumd, dist.wareid
             FROM t_dist dist
            WHERE dist.wareid = rec.wareid
            GROUP BY dist.wareid
           HAVING SUM(distqty) > rec.applyqty) temp
    ON (d.wareid = temp.wareid AND d.makeno = rec.makeno)
    WHEN MATCHED THEN
      UPDATE SET d.distqty = d.distqty - (sumd - rec.applyqty);
    
    --减去库存
    UPDATE t_store t
       SET t.storeqty = t.storeqty -
                        (SELECT d.distqty
                           FROM t_dist d
                          WHERE d.wareid = t.wareid
                            AND d.makeno = t.makeno)
     WHERE t.wareid = rec.wareid
       AND t.makeno <= rec.makeno;
  END LOOP;

  COMMIT;
END;

--Oracle建表语句
create table t_store(
wareid varchar(13),
makeno number,
storeqty numeric(16,6));




insert into t_store select 'A',1,30 from dual;
insert into t_store select 'A',2,40 from dual;
insert into t_store select 'A',3,50 from dual;




insert into t_store select 'B',8,15 from dual;
insert into t_store select 'B','9',40 from dual;
insert into t_store select 'B','12',1000 from dual;




insert into t_store select 'C','1',60 from dual;
insert into t_store select 'C','4',120 from dual;




create table t_requirement(
wareid varchar(13),
applyqty numeric(16,6)) ;




insert into t_requirement select 'A',80 from dual;
insert into t_requirement select 'B',50 from dual;
insert into t_requirement select 'C',180 from dual;








create table t_dist(
wareid varchar(13),
makeno varchar(40),
distqty numeric(16,6));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1062421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-1062421/