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

OraclePL/SQLのファンクションで複数行返すサンプルです。

目次

サンプル 複数行返すファンクション
  ファンクションを呼び出す側

複数行返すファンクション

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

1.項目を定義

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

CREATE OR REPLACE TYPE "HR"."TYPE_SYAIN_REC" IS OBJECT (
  id       NUMBER(6,0),
  romaji   VARCHAR2(20)
);

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

2.行の配列を定義

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

CREATE OR REPLACE TYPE TYPE_SYAIN_ARRAY 
IS TABLE OF TYPE_SYAIN_REC;

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

3.ファンクションを作成

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

CREATE OR REPLACE FUNCTION getSyain
(
name1 IN VARCHAR
)
RETURN TYPE_SYAIN_ARRAY
IS
	REC1 TYPE_SYAIN_ARRAY := HR.TYPE_SYAIN_ARRAY(); --初期化

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

	syain_rec cur1%ROWTYPE;
	i INTEGER;
BEGIN
	i := 1;
	OPEN cur1;
		LOOP
			FETCH cur1 INTO syain_rec;
			    EXIT WHEN cur1%NOTFOUND;
			REC1.EXTEND;
			REC1(i) := TYPE_SYAIN_REC(syain_rec.id,syain_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(getSyain('田中'));

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

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

関連の記事

Oracle PL/SQLのファンクションのサンプル

△上に戻る