Oracle PL/SQL ファンクションとTYPEで複数行返す

サンプル	Typeを作成する
	複数行返すファンクション
	ファンクションを呼び出す側
	Typeを削除する

Typeを作成する

最初にファンクションで使用するTYPEのオブジェクトとネストした表を作成します。

１．項目を定義

TYPEのオブジェクトで項目を定義します。

CREATE OR REPLACE TYPE TYPE_employee_REC IS OBJECT (
  id       NUMBER(6,0),
  romaji   VARCHAR2(20)
);

idとromajiの2項目を定義しています。

２．行の配列を定義

TYPEで上記オブジェクトの行の配列（テーブル）を作成します。ネストした表です。

CREATE OR REPLACE TYPE TYPE_employee_ARRAY 
IS TABLE OF TYPE_employee_REC;

2行目のTYPE_employee_RECは、上記のTYPEとひも付きます。

複数行返すファンクションを作成する

上記のTYPEを利用してファンクションを作成します。

CREATE OR REPLACE FUNCTION get_employee
(
name1 IN VARCHAR
)
RETURN TYPE_employee_ARRAY
IS
	REC1 TYPE_employee_ARRAY := TYPE_employee_ARRAY(); --初期化

	CURSOR cur1 IS
	SELECT id,romaji FROM employee
	WHERE name = name1
	ORDER BY id;

	employee_rec cur1%ROWTYPE;
	i INTEGER;
BEGIN
	i := 1;
	OPEN cur1;
		LOOP
			FETCH cur1 INTO employee_rec;
			    EXIT WHEN cur1%NOTFOUND;
			REC1.EXTEND;
			REC1(i) := TYPE_employee_REC(employee_rec.id,employee_rec.romaji);
			i := i + 1;
		END LOOP;
	CLOSE cur1;
  RETURN REC1;
END;

3行目は、ファンクションの引数です。
5行目は、戻り値です。独自に定義した配列です。
7行目は、配列を初期化しています。
9～11行目は、カーソルの宣言でselect文があります。
19行目からはループ文です。取得したレコード分繰り返します。
22行目のEXTENDは、要素を追加します。
23行目は、配列の変数に値をセットしています。

ファンクションを呼び出す側

上記で作成したファンクションを実行します。

SQL> SELECT * FROM TABLE(get_employee('田中'));

        ID ROMAJI
---------- ----------------------------------------
         1 tanaka1
         2 tanaka2

select文のfromの後のtableのかっこの中にファンクションと引数を指定します。

Typeを削除する

DROP TYPE TYPE_employee_REC Force;

DROP TYPE TYPE_employee_ARRAY Force;

Forceをつけて削除します。