10-347 表関数で配分値の割当

SQLパズル

BoxTable
Box  BoxGroup  MaxQty
---  --------  ------
  1         1      10
  2         1      15
  3         1      40
  4         1      45
  5         2      15
  6         2      20
  7         2      20
  8         3      20

配分値60を、BoxGroupが1のBoxに、MaxQtyの降順で1ずつ順番に割り当てる。
ただし、割当がMaxQtyを超えることはない。

出力結果
Box  BoxGroup  MaxQty  Assigned_From_60
---  --------  ------  ----------------
  1         1      10                10
  2         1      15                15
  3         1      40                17
  4         1      45                18
  5         2      15                 0
  6         2      20                 0
  7         2      20                 0
  8         3      20                 0

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table BoxTable(Box,BoxGroup,MaxQty) as
select 1,1,10 from dual union all
select 2,1,15 from dual union all
select 3,1,40 from dual union all
select 4,1,45 from dual union all
select 5,2,15 from dual union all
select 6,2,20 from dual union all
select 7,2,20 from dual union all
select 8,3,20 from dual;

SQL

create or replace type Print347Type as object(
Box            number(2),
BoxGroup       number(2),
MaxQty         number(2),
AssignedFrom60 number(2));
/

create or replace type Print347TypeSet as table of Print347Type;
/

create or replace function PrintR return Print347TypeSet PipeLined IS
    outR Print347Type := Print347Type(NULL,NULL,NULL,NULL);
    cursor cur is select Box,BoxGroup,MaxQty,0 as AssignedFrom60
                  from BoxTable order by MaxQty desc;
    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;

    assignValue pls_Integer :=60;
begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;

    while (assignValue > 0) loop
        for I in 1..saveData.Count Loop
            if saveData(I).BoxGroup = 1 then
                if saveData(I).AssignedFrom60 < saveData(I).MaxQty then
                    saveData(I).AssignedFrom60 := saveData(I).AssignedFrom60+1;
                    assignValue := assignValue-1;
                    exit when not (assignValue > 0);
                end if;
            end if;
        end Loop;
    end Loop;

    for I in 1..saveData.Count Loop
        outR.Box            := saveData(I).Box;
        outR.BoxGroup       := saveData(I).BoxGroup;
        outR.MaxQty         := saveData(I).MaxQty;
        outR.AssignedFrom60 := saveData(I).AssignedFrom60;
        pipe row(outR);
    end Loop;
end;
/

sho err

select * from table(PrintR)
order by BoxGroup,Box;

解説

表関数が効果的な、典型的な使用例ですね。
下記のパッケージでtype宣言する方法も含めて
私が、表関数を作成する時のテンプレとします :-)

create or replace Package PackTest Is
    type PrintType is record(
    ID  number(3),
    --ID  テーブル名.列名%type, %type属性も使用可能
    Val char(2));

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintR return PackTest.PrintTypeSet PipeLined IS
    outR PackTest.PrintType;
begin
    for I in 1..5 Loop
        outR.ID  := I;
        outR.Val := case when mod(I,2)=0 then 'AA' else 'BB' end;
        pipe row(outR);
    end Loop;
end;
/

sho err

select * from table(PrintR);