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/，如需轉載，請註明出處，否則將追究法律責任。