PL/SQL De SORT Tweet
なんと、ストアドプロシージャ内で配列をソートしてほしいという要求。
Oracle7のころからPL/SQLで開発をしてきてはいるが、そんな要求、はじめてじゃ！
え。。。。なんで？
データを問い合わせるときに ORDER BY句でソートしちゃえば早いじゃん。
（扱うデータは表から問い合わせたものでは無く、呼び出し元から引数で渡される配列ということらしい。 ）
え？、呼出し元って、javaじゃないの？ java側でできるでしょう、そんなこと？。。。
まぁ、あちらさんにもいろいろと事情があるようで。。。。。そのシステムでは必要だ、というのだからサンプルだけは作ってあげましょう。
（ちなみに、Oracle8のころに、こんな事言われたら、即、荷物纏めて撤収してたと思うぞ。。。本気で。）
ということで、Oracle10gを利用する前提で考えてみたが、マニュアルなどの資料では、Oracle8i 8.1.6以降であればできるとなっている。
（以降のサンプルで示すTABLE演算子や、CAST関数が登場するのは、Oracle8i 8.1.6のSQLリファレンスマニュアルから登場している。）
珍しい要求だったこともあり備忘録として書き留めておく。
尚、PL/SQLの言語仕様上、コレクションの要素数に上限はないタイプのコレクションもあるので、設定する要素のサイズや、要素数によっては、それ相当のメモリが消費されることも頭の片隅に置いておくこと。
PL/SQLで扱える３タイプのコレクションの簡単な特徴は以下の通り。
（１）結合配列
Oracle7のころからあるコレクション。索引付き表とも呼ばれる。添字には任意の数字、文字列を利用できる。Perlの連想配列や、JavaのHashTableなどをイメージすればわかりやすいだろう。任意の数の要素を持てる。
この配列でVARCHAR2などの文字型を添字にした場合には要素数の上限は無くなる。binary_integerなどの数値型を利用した場合には各数値型の範囲が要素数の上限となる。
（２）ネスト表
Oracle8以降で登場したコレクション。定義は結合配列に似ているが、配列の添字は１から始まる連続した正の整数のみ（１〜2147483647）であるため要素数には上限がある。結合配列と同様に任意の数の要素を持てる。また、結合配列とは異なり表に格納することもできる。
（３）VARRAY表
Oracle8以降で登場したコレクション。配列の添字はネスト表と同じであるが、要素は固定数保持することができる点が異なる。ネスト表と同様に表に格納することもできる。
Oracle8までならば、PL/SQLの結合配列をソートしろなんてことになると、ロジックをガリガリ書いてみたり、場合によっては、一旦、作業用表へ格納後、取り出す際にORDER BYでソートといった方法が考えられる。いずれにしても、あまりイケてないわけ。
そこで、ネスト表とオブジェクト型を利用し、PL/SQLでコレクションをソートするという方法の簡単な例。（この例ではプロシージャにはしていない）
Oracle10g R1を使用。
※ソート条件として、同値の場合は、元のデータ順が維持されること。（つまり、安定ソートが条件。ちなみに、クイックソートは不安定ソート。）
このソートは、コレクション（ネスト表又はVARRAY）のネスト解除と呼ばれる方法を利用して行う。（マニュアル「SQLリファレンス 10g リリース１」SELECT文の table_collection_expression P19-22 などを参照のこと。）
（１）オブジェクト型の作成
2つの属性を持たせ、user_idまたは、user_nameいずれかでソートすることにする。
SCOTT> l
1 create type UserInfo_typ as object
2 (
3 user_id number(5),
4 user_name varchar2(40)
5* )
SCOTT> /
型が作成されました。
（２）コレクションの定義。
UserInfo_typ型のネスト表を定義した。
SCOTT> create type UserList_typ as table of UserInfo_typ;
2 /
型が作成されました。
（３）コレクション UserList_typを user_nameで昇順にソートする。
'みた'というデータが２件存在するが、元の順序が変わらない安定したソートである点に注目。
SCOTT> set timi on
SCOTT> set serveroutput on
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_name
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
1=あいだ
8=おだ
2=さえき
5=みた
6=みた
3=やおか
NULL=NULL
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.01
SCOTT>
（４）コレクション UserList_typを user_nameで降順にソートする。
'みた'というデータが２件存在するが、元の順序が変わらない安定したソートである点に注目。
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_name DESC
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
NULL=NULL
3=やおか
5=みた
6=みた
2=さえき
8=おだ
1=あいだ
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.03
（５）コレクション UserList_typを user_idで降順にソートする。
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_id DESC
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
NULL=NULL
8=おだ
6=みた
5=みた
3=やおか
2=さえき
1=あいだ
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.03
（６）コレクション UserList_typを user_idで昇順にソートする。
SCOTT> l
1 DECLARE
2 UserList UserList_typ := UserList_typ(
3 UserInfo_typ(1,'あいだ'),
4 UserInfo_typ(2,'さえき'),
5 UserInfo_typ(3,'やおか'),
6 UserInfo_typ(8,'おだ'),
7 UserInfo_typ(5,'みた'),
8 UserInfo_typ(6,'みた'),
9 UserInfo_typ(null,null)
10 );
11 BEGIN
12 FOR user_rec IN (
13 SELECT *
14 FROM
15 TABLE(CAST(UserList as UserList_typ))
16 ORDER BY
17 user_id
18 )
19 LOOP
20 dbms_output.put_line(
21 nvl(to_char(user_rec.user_id),'NULL') ||
22 '=' ||
23 nvl(user_rec.user_name,'NULL')
24 );
25 END LOOP;
26* END;
SCOTT> /
1=あいだ
2=さえき
3=やおか
5=みた
6=みた
8=おだ
NULL=NULL
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.03
となる。
ソートはOracleにお任せなので、特に小難しいロジックを書く事も無く、PL/SQL内で、簡単にコレクションをソートすることができる。
小難しいロジックの記述はないので単体テストは簡単だろう。
しかし、もし、このような方法を採用するのであれば、本番で想定されるコレクションのサイズ、プログラムの並列実行数でのメモリ消費量のチェックやシステム全体への影響などを確認するための十分な負荷テストが必要だと考える。（Oracle側で Resource Managerや、Profileを使用し、リソース制限をしていれば最悪の事態は防げるが、このような要求をしてくる所にかぎって、Resource ManagerやProfileによるリソース管理が行われていない、ということが多いように感じる。）
この例では、SQL*Plusで実行した例を載せたのだが、当初、Oracle SQL Developer 1.0で試していたものがあるので次回は、その時に気付いたSQL Developerの問題について書く予定。
| 固定リンク | 0
コメント