PL/SQLで配列を扱う(要素数0をうまく扱う)

最近会社で聞かれて答えたことをまとめてみました。

PL/SQLで配列を扱うとき、FIRST～LASTでループするような処理を作っていたら、配列の要素数が0のときにエラーになってしまった。どうすればよいか分からない、と聞かれました。

# 用語

配列と呼んでいるものは、Oracleの公式用語としてはコレクションです。

# コレクション・メソッド

コレクションの先頭データを取得するFIRST、コレクションの末尾データを取得するLASTなどは、コレクション・メソッドと呼ばれます。この用語を知っていないと [公式ドキュメント(11g)](http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/appdev.111/E05670-03/collection_method.htm) を検索することも容易ではありません。

コレクションメソッドを使って、0件でもうまく動くような、コレクションを一巡する処理を作成してみます。

# そもそもコレクションには3種類ある

PL/SQLには、コレクションと呼べるものが3種類あります。

* 結合配列
* ネストした表
* 可変長配列(VARRAY)

分類の詳細と特性については、[SHIFT the Oracle さんのサイト](https://www.shift-the-oracle.com/plsql/collection/) を見ていただくのが分かりやすいと思います。

# サンプルコードの解説

動作を理解するためのサンプルコードを末尾に置いてあります。このサンプルコードの動作は、以前作成した [Oracle Database 11g XE](https://qiita.com/szly/items/4b610f51a3172c04f5d2) を用いて動作確認しています。

T_ARRAY1が結合配列、T_ARRAY2がネストした表、T_ARRAY3が可変長配列(VARRAY)を表すデータ型です。これらのデータ型で宣言した変数が、V_ARRAY1～V_ARRAY3です。

このサンプルコードは、コレクションを一巡する処理に焦点を当てています。そのため、コレクションに値を入れる部分は、さほど凝ったことをしていません。EXTENDなどを使用していない、という意味です。

処理をみると分かりますが、コレクションの情報を出力する PRC_OUTPUT は3つとも同じコードです。引数の型のみが異なります。

言い換えるとこうなります。

コレクションは、宣言、初期化、代入、要素数の拡大の方法が、3種類それぞれ微妙に違います。しかし、コレクションを一巡して参照する処理は、どれも同じ書き方で書くことができます。

ループで回す場合、以下のどちらかの方法を使うことになります。

* FIRST ～ LAST でループして EXISTS で存在するもののみを処理する
* FIRST+NEXT で飛び飛びに処理する

前者は添え字が数字の場合に使うことができます。Javaに例えると、ArrayListをインデックス番号で辿るような方法です。

後者は添え字の種類を問わず使うことができます。Javaに例えると、LinkedListをリンク経由で辿るような方法です

なお、FIRST ～ LAST でループする場合、注意する点があります。

COUNT=0の場合、FIRST=LAST=NULLとなります。この場合、FIRST ～ LAST でループしようとすると、エラーになってしまいます(ORA-06502)。そのため、ループを COUNT>0 のIFで包む必要があります。

逆順に処理するときは、ループの REVERSE や、コレクションメソッドの PRIOR を使えばできます。でも、使うことがあるかは微妙です。

## 結合配列

結合配列は、他の言語で言うところの連想配列です。そのため、添え字は連続しているとは限りません。さらに、添え字は文字の可能性もあります。

飛び飛びの度合いが強いなら、FIRST+NEXTでループするのが効率的です。

結合配列では、"未初期化"＝"要素数0"です。未初期化の場合、COUNTメソッドは0を返します。

"ネストした表" や "可変長配列" では、NULLを代入することで再度、未初期化の状態にできます。しかし、結合配列には、NULLを代入できません(ORA-06550)。再初期化するには、未初期化の変数を用いて代入するという方法があります。

サンプルコードでは V_EMPTY が未初期化のままとなっている変数です。これをV_ARRAY1に代入することで、再度、未初期化状態に戻す処理としています。

## ネストした表

ネストした表は、1オリジンのコレクションです。

単純に作成するうえでは密なコレクションです。しかし、DELETEメソッドを用いて途中の要素を削除することができます。このようなことをすると疎なコレクションになります。

大量にDELETEして、よほど飛び飛びにならない限り、FIRST～LASTでループするのが効率的かつ分かりやすいでしょう。

ネストした表は、"未初期化"≠"要素数0"です。未初期化の場合、COUNTメソッドもエラーとなります(ORA-06531)。
必ず初期化してから使うようにすることで、COUNTのエラーを避けられます。
Effective Java 項目43「nullではなく空配列か空コレクションを返す」に似ているかもしれません。
初期化さえしておけば、結合配列の場合と同様に扱うことができます。

NULLを代入することで再度、未初期化の状態にできます。
しかし、先にも書いたように、NULLを代入するよりは要素数0のコレクションとするほうが使い勝手が良いでしょう。

## 可変長配列(VARRAY)

可変長配列は、1オリジンで密なコレクションです。

密なコレクションなので、FIRST～LASTでループするのが効率的かつ分かりやすいでしょう。

下のサンプルコードでは、処理を揃えるためにFIRST～LASTループ部分にEXISTSを入れてあります。しかし、密なコレクションなので、EXISTSはなくても動きます。

可変長配列は、"未初期化"≠"要素数0"です。未初期化の場合、COUNTメソッドもエラーとなります(ORA-06531)。このあたりの特性は、ネストした表に似ています。

DELETEメソッドを用いて一部の要素を削除することはできません(ORA-06550)。DELETEメソッドを引数なしで使用し、要素を全削除することは可能です。全削除後は、要素数0となります。

NULLを代入することで再度、未初期化の状態にできます。ここも、ネストした表に似ています。

# サンプルコード

```
DECLARE
	TYPE		T_ARRAY1	IS	TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;		-- 結合配列
	TYPE		T_ARRAY2	IS	TABLE OF VARCHAR2(100);								-- ネストされた表
	TYPE		T_ARRAY3	IS	VARRAY(10) OF VARCHAR2(100);						-- 可変長配列
	V_IDX		NUMBER;

	-- 結合配列
	PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY1 ) IS
	BEGIN
		-- COUNT/FIRST/LASTを表示
		BEGIN
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
				RETURN;
		END;
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
		-- FIRST/LAST/EXISTSを用いてループ
		--		添え字が数字ならこの方法でもできる
		--		NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
		IF V_ARRAY.COUNT > 0 THEN
			FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
				IF V_ARRAY.EXISTS ( V_IDX ) THEN
					DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
				END IF;
			END LOOP;
		END IF;
		-- FIRST/NEXTを用いてループ
		--		添え字が文字でも扱える
		V_IDX := V_ARRAY.FIRST;
		LOOP
			EXIT WHEN V_IDX IS NULL;
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
			V_IDX := V_ARRAY.NEXT(V_IDX);
		END LOOP;
	END;

	-- ネストした表
	PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY2 ) IS
	BEGIN
		-- COUNT/FIRST/LASTを表示
		BEGIN
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
				RETURN;
		END;
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
		-- FIRST/LAST/EXISTSを用いてループ
		--		添え字が数字ならこの方法でもできる
		--		NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
		IF V_ARRAY.COUNT > 0 THEN
			FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
				IF V_ARRAY.EXISTS ( V_IDX ) THEN
					DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
				END IF;
			END LOOP;
		END IF;
		-- FIRST/NEXTを用いてループ
		--		添え字が文字でも扱える
		V_IDX := V_ARRAY.FIRST;
		LOOP
			EXIT WHEN V_IDX IS NULL;
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
			V_IDX := V_ARRAY.NEXT(V_IDX);
		END LOOP;
	END;

	-- 可変長配列
	PROCEDURE PRC_OUTPUT ( V_ARRAY T_ARRAY3 ) IS
	BEGIN
		-- COUNT/FIRST/LASTを表示
		BEGIN
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || V_ARRAY.COUNT || ']' );
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.COUNT = [' || SQLERRM || ']');
				RETURN;
		END;
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.FIRST = [' || V_ARRAY.FIRST || ']' );
		DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY.LAST = [' || V_ARRAY.LAST || ']' );
		-- FIRST/LAST/EXISTSを用いてループ
		--		添え字が数字ならこの方法でもできる
		--		NUMBERでループするため、FIRSTやLASTがNULLの場合に処理できないので、COUNT>0で包む
		IF V_ARRAY.COUNT > 0 THEN
			FOR V_IDX IN V_ARRAY.FIRST .. V_ARRAY.LAST LOOP
				IF V_ARRAY.EXISTS ( V_IDX ) THEN
					DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
				END IF;
			END LOOP;
		END IF;
		-- FIRST/NEXTを用いてループ
		--		添え字が文字でも扱える
		V_IDX := V_ARRAY.FIRST;
		LOOP
			EXIT WHEN V_IDX IS NULL;
			DBMS_OUTPUT.PUT_LINE ( 'V_ARRAY(' || V_IDX || ') = [' || V_ARRAY(V_IDX) || ']' );
			V_IDX := V_ARRAY.NEXT(V_IDX);
		END LOOP;
	END;

BEGIN

	-- 結合配列
	DECLARE
		V_ARRAY		T_ARRAY1;
		V_EMPTY		T_ARRAY1;
	BEGIN
		DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
		DBMS_OUTPUT.PUT_LINE ( '■■■結合配列' );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■代入' );
		V_ARRAY(1) := '111';
		V_ARRAY(3) := '333';
		V_ARRAY(5) := '555';
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
		V_ARRAY := V_EMPTY;
		PRC_OUTPUT ( V_ARRAY );
		--
	END;

	-- ネストした表
	DECLARE
		V_ARRAY		T_ARRAY2;
	BEGIN
		DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
		DBMS_OUTPUT.PUT_LINE ( '■■■ネストした表' );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
		V_ARRAY := NULL;
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(空)' );
		V_ARRAY := T_ARRAY2();
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み' );
		V_ARRAY := T_ARRAY2( '111', '333', '555' );
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(途中DELETE)' );
		V_ARRAY := T_ARRAY2( '111', '333', '555' );
		V_ARRAY.DELETE(2);
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(全DELETE)' );
		V_ARRAY := T_ARRAY2( '111', '333', '555' );
		V_ARRAY.DELETE;
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
		V_ARRAY := T_ARRAY2( '111', '333', '555' );
		V_ARRAY := NULL;
		PRC_OUTPUT ( V_ARRAY );
		--
	END;

	-- 可変長配列(VARRAY)
	DECLARE
		V_ARRAY		T_ARRAY3;
	BEGIN
		DBMS_OUTPUT.PUT_LINE ( '■■■■■■■■■■■■■■■■■■■■' );
		DBMS_OUTPUT.PUT_LINE ( '■■■可変長配列(VARRAY)' );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■未初期化' );
		V_ARRAY := NULL;
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(空)' );
		V_ARRAY := T_ARRAY3();
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み' );
		V_ARRAY := T_ARRAY3( '111', '333', '555' );
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■初期化済み(全DELETE)' );
		V_ARRAY := T_ARRAY3( '111', '333', '555' );
		V_ARRAY.DELETE;
		PRC_OUTPUT ( V_ARRAY );
		--
		DBMS_OUTPUT.PUT_LINE ( '■■■■■再度、未初期化' );
		V_ARRAY := T_ARRAY3( '111', '333', '555' );
		V_ARRAY := NULL;
		PRC_OUTPUT ( V_ARRAY );
		--
	END;

END;
```

# 実行結果

```
■■■■■■■■■■■■■■■■■■■■
■■■結合配列
■■■■■未初期化
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■代入
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [5]
V_ARRAY(1) = [111]
V_ARRAY(3) = [333]
V_ARRAY(5) = [555]
V_ARRAY(1) = [111]
V_ARRAY(3) = [333]
V_ARRAY(5) = [555]
■■■■■再度、未初期化
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■■■■■■■■■■■■■■■■
■■■ネストした表
■■■■■未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■初期化済み(空)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■初期化済み
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
■■■■■初期化済み(途中DELETE)
V_ARRAY.COUNT = [2]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(3) = [555]
■■■■■初期化済み(全DELETE)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■再度、未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■■■■■■■■■■■■■■■■
■■■可変長配列(VARRAY)
■■■■■未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
■■■■■初期化済み(空)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■初期化済み
V_ARRAY.COUNT = [3]
V_ARRAY.FIRST = [1]
V_ARRAY.LAST = [3]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
V_ARRAY(1) = [111]
V_ARRAY(2) = [333]
V_ARRAY(3) = [555]
■■■■■初期化済み(全DELETE)
V_ARRAY.COUNT = [0]
V_ARRAY.FIRST = []
V_ARRAY.LAST = []
■■■■■再度、未初期化
V_ARRAY.COUNT = [ORA-06531: 参照しているコレクションは初期化されていません。]
```