10-223 セレクションソートで列をソート

SQLパズル

SelectionSortTest
 A   B   C   D   E
--  --  --  --  --
15  20  10  18  99
16  17  13  20  11
18  23  40  29  33
30  31  32  33  34
44  43  42  41  40
54  51  52  53  50
63  64  62  61  60
74  73  72  70  71
88  88  88  88  99
99  99  99  99  99

同じ行で、列の昇順にソートする。

出力結果
 A   B   C   D   E
--  --  --  --  --
10  15  18  20  99
11  13  16  17  20
18  23  29  33  40
30  31  32  33  34
40  41  42  43  44
50  51  52  53  54
60  61  62  63  64
70  71  72  73  74
88  88  88  88  99
99  99  99  99  99

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

データ作成スクリプト

create table SelectionSortTest(A,B,C,D,E) as
select 15,20,10,18,99 from dual union
select 16,17,13,20,11 from dual union
select 18,23,40,29,33 from dual union
select 30,31,32,33,34 from dual union
select 44,43,42,41,40 from dual union
select 54,51,52,53,50 from dual union
select 63,64,62,61,60 from dual union
select 74,73,72,70,71 from dual union
select 88,88,88,88,99 from dual union
select 99,99,99,99,99 from dual;

SQL

create or replace type SelectionDataType as object(
A number(2),
B number(2),
C number(2),
D number(2),
E number(2));
/

create or replace type SelectionDataTypeSet as table of SelectionDataType;
/

create or replace function SelectionSort return SelectionDataTypeSet PipeLined IS
    type ValArrayDefine is table of number(2) index by binary_integer;
    ValArray ValArrayDefine;

    WorkMinNo binary_integer;
    out_rec SelectionDataType;

    work number(2);
begin
    out_rec := SelectionDataType(NULL,NULL,NULL,NULL,NULL);

    for rec in (select A,B,C,D,E
                  from SelectionSortTest
                 order by A,B,C,D,E) Loop

        ValArray(1) := rec.a;
        ValArray(2) := rec.b;
        ValArray(3) := rec.c;
        ValArray(4) := rec.d;
        ValArray(5) := rec.e;

        for i in 1..5 Loop
            WorkMinNo := i;
            for J in i+1..5 Loop
                if ValArray(WorkMinNo) > ValArray(J) then
                    WorkMinNo := J;
                end if;
            end Loop;

            work := ValArray(WorkMinNo);
            ValArray(WorkMinNo) := ValArray(i);
            ValArray(i) := work;
        end Loop;

        out_rec.a := ValArray(1);
        out_rec.b := ValArray(2);
        out_rec.c := ValArray(3);
        out_rec.d := ValArray(4);
        out_rec.e := ValArray(5);

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

sho err

select * from table(SelectionSort);

解説

セレクションソート(最小値選択法)を使って、ソートしてます。

セレクションソート - Wikipedia

---