Oracle ストアドファンクションで配列を返却

Oracleの配列を現場ではなかなかお目にかからないのは私だけでしょうか、、配列と言っても「横に持つ」のではなく「縦に持つ」んですよね。（マニュアルではコレクション型とされています）可変サイズである事は当然だと思いますが、事前に最大サイズを指定しないといけないのはなんとも窮屈です。

事前にTYPEで配列（VARRAY）を定義します。

VARCHAR2型の入れ物を６つ用意します。

create or replace type  rtyp_wardcity is varray(6) of varchar2(20 char)
;

戻り値に配列（VARRAY）を使ったファンクションを作成します

都道府県名を指定すると、属する「区」を配列で返却する単純なプログラムです。

CREATE OR REPLACE FUNCTION DABADA.fnc_wardcity
(
  p_prefectures  IN varchar2
)
RETURN rtyp_wardcity
IS

vrec rtyp_wardcity; -- 事前にtypeで定義したコレクション

cursor cur is
select ward from wardcity
where prefectures = p_prefectures order by ward;

wardcity_rec cur%ROWTYPE;
i number;

begin
vrec := rtyp_wardcity();  -- 初期化　※個人的にはちょっと違和感残る構文

i := 1;
open cur;
loop
	fetch cur into wardcity_rec;
	exit when cur%NOTFOUND;
	vrec.extend();
	vrec(i) := wardcity_rec.ward;
	i := i + 1;
end loop;
close cur;
return vrec;
END;
/

実行例

この通り配列といっても「縦」になる点残念なところです。
※このあたりPostgreSQLではイメージ通りの動作してくれます。

-- 以下実行例ですが、通常のファンクションの呼び方と異なります。
-- 今回検証した環境は 12.1.0 だったので select * from table() とする必要ありますが
-- 12.2以降のバージョンではtable句が不要になり 
-- select * from fnc_wardcity('千葉県');
-- で実行できるようになっています。（テーブル・ファンクションの呼び出し方と同じです）
-- 
SQL> select * from table(fnc_wardcity('千葉県'));

COLUMN_VALUE
--------------------------------------------------------------------------------
千葉市中央区
千葉市稲毛区
千葉市緑区
千葉市美浜区
千葉市花見川区
千葉市若葉区

6行が選択されました。

-- 広島市には８つの区が存在します。そのため今回配列を６つしか用意していなかったので
-- エラーとなりました。
--
SQL> select * from table(fnc_wardcity('広島県'));
select * from table(fnc_wardcity('広島県'))
                    *
行1でエラーが発生しました。:
ORA-06532: サブスクリプトが有効範囲外です。
ORA-06512: "DABADA.FNC_WARDCITY", 行25


SQL>

テストデータ

create table wardcity
(
    sid         NUMBER GENERATED BY DEFAULT AS IDENTITY 
                INCREMENT BY 1 START WITH 1 CACHE 100,
    prefectures varchar2(4 char),
    ward        varchar2(20 char)
);

insert into wardcity(prefectures,ward) values  ('千葉県','千葉市中央区');
insert into wardcity(prefectures,ward) values  ('千葉県','千葉市稲毛区');
insert into wardcity(prefectures,ward) values  ('千葉県','千葉市緑区');
insert into wardcity(prefectures,ward) values  ('千葉県','千葉市美浜区');
insert into wardcity(prefectures,ward) values  ('千葉県','千葉市花見川区');
insert into wardcity(prefectures,ward) values  ('千葉県','千葉市若葉区');
insert into wardcity(prefectures,ward) values  ('宮城県','仙台市太白区');
insert into wardcity(prefectures,ward) values  ('宮城県','仙台市宮城野区');
insert into wardcity(prefectures,ward) values  ('宮城県','仙台市泉区');
insert into wardcity(prefectures,ward) values  ('宮城県','仙台市若林区');
insert into wardcity(prefectures,ward) values  ('宮城県','仙台市青葉区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市中区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市佐伯区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市南区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市安佐北区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市安佐南区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市安芸区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市東区');
insert into wardcity(prefectures,ward) values  ('広島県','広島市西区');