10-20 LIFOで在庫単価の計算

SQLパズル

在庫テーブル
SEQ   区分  個数   入荷単価
---   ----  ----   --------
 1    入庫     9        100
 2    入庫     5        110
 3    出庫    -5       null
 4    返品    -6       null
 5    入庫     5        120
 6    返納     3       null
 7    入庫     5        100

後入先出法で在庫単価を求める。

在庫には入庫、出庫、返品、返納があります。
SEQ順に処理するとして返品は最も古い入庫個数から減らします。
返納は最も新しい入庫個数を増やします。

SEQから入荷単価までをフィールドに持つとして在庫数と在庫単価を求めます。

出力結果
SEQ   区分  個数   入荷単価    在庫数   在庫単価
---   ----  ----   --------   ------   ---------------------------------
 1    入庫     9        100       9    100
 2    入庫     5        110      14    (9*100 + 5*110) / 14 = 103.571...
 3    出庫    -5       null       9    (9*100 + (5-5)*110) / 9 = 100
 4    返品    -6       null       3    (3*100) / 3 = 100
 5    入庫     5        120       8    (3*100 + 5*120) / 8 = 112.5
 6    返納     3        120      11    (3*100 + (5+3)*120) / 11 = 114.5454...
 7    入庫     5        100      16    (3*100 + 8*120 + 5*100) / 16 = 110

データ作成スクリプト

前のSQLパズルと同じ

SQL

create or replace type 在庫RecodeType as object(
SEQ number(1),
区分 char(4),
個数 number(1),
入荷単価 number(3),
在庫数   number(2),
在庫単価 number(5,2));
/

create or replace type 在庫RecodeTypeSet as table of 在庫RecodeType;
/

create or replace function 在庫Record return 在庫RecodeTypeSet PipeLined is
    out_rec 在庫RecodeType := 在庫RecodeType(NULL,NULL,NULL,NULL,NULL,NULL);

    --単価データを保存する配列
    type 単価DataTypeArray is table of number(3) index by binary_integer;
    単価Array 単価DataTypeArray;
    単価ArrayInsPointer binary_integer :=0;

    総単価 number(5);

begin
    for rec in (select SEQ,区分,個数,
                decode(区分,'返納',Last_Value(入荷単価 ignore nulls)
                                   over(order by SEQ),入荷単価) as 入荷単価,
                sum(個数) over(order by SEQ) as 在庫数
                from 在庫
                order by SEQ) Loop

        if rec.区分 in('入庫','返納') then --配列に入れる
            for i in 1..rec.個数 Loop
                単価ArrayInsPointer := 単価ArrayInsPointer+1;
                単価Array(単価ArrayInsPointer) := rec.入荷単価;
            end Loop;
        else  --配列から取り去る
            for i in 1..abs(rec.個数) Loop
                単価Array.delete(単価ArrayInsPointer);
                単価ArrayInsPointer := 単価ArrayInsPointer-1;
            end Loop;
        end if;

        out_rec.SEQ := rec.SEQ;
        out_rec.区分 := rec.区分;
        out_rec.個数 := rec.個数;
        out_rec.在庫数 := rec.在庫数;
        out_rec.入荷単価 := rec.入荷単価;

        総単価 := 0;
        for i in 1..単価ArrayInsPointer Loop
            総単価 := 総単価 + 単価Array(i);
        end Loop;
        out_rec.在庫単価 := 総単価/rec.在庫数;

        pipe row(out_rec);
    end Loop;
end;
/

sho err

select * from table(在庫Record);

解説

配列を使って、LIFOのデータ構造を実現させてます。

7-54 引き当て処理
10-19 FIFOで在庫単価の計算

PL/SQLのコレクションおよびレコードの使用

スタック (stack)の資料
待ち行列，キュー(queue)の資料