PL/SQLでフィルタ後のテーブルを返す関数

質問

環境:Oracle81740

タイトル通りの質問ですが、、、

以下のテーブルがあったとします。
CREATE TABLE PUBLIC.TEST_TABLE
(
KEY1 CHAR(20) NOT NULL,
KEY2 CHAR(20) NOT NULL,
VALUE1 VARCHAR2(256),
CONSTRAINT GENERAL_TABLE_PK PRIMARY KEY (KEY1, KEY2)
)

そこで本題ですが、
(構文がめちゃくちゃだとは思いますが、読み取ってください^^;)
FUNCTION TEST_GET
(
IN_KEY TEST_TABLE.KEY1%TYPE
)
RETURN TABLE IS retTbl TABLE;
というイメージで、[KEY1]値を受け取り、該当するレコードをテーブルとして返す関数を組みたいのですが、調べがつきません。

どなたかよろしくお願いいたします。

回答No.4

iwashimizu · ベストアンサー

CREATE OR REPLACE PACKAGE test_pkg AS
TYPE test_rec_type IS TEST_TABLE%ROWTYPE;
TYPE test_tbl_type IS TABLE OF test_rec INDEX BY BINARY_INTEGER;
FUNCTION test_get(key1 IN test_table.key1%TYPE) RETURN test_tbl_type;
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
FUNCTION test_get(in_key1 IN test_table.key1%TYPE) RETURN test_tbl_type
IS
CURSOR test_cur (in_key1 IN test_table.key1%TYPE) IS
SELECT * FROM test_table WHERE key1 = in_key1;
test_rec test_cur%ROWTYPE;
out_tbl test_tbl_type;
out_tbl_index BINARY_INTEGER := 1;
BEGIN
OPEN test_cur(in_key1);
LOOP
FETCH test_cur INTO test_rec;
EXIT WHEN test_cur%NOTFOUND;
out_tbl(out_tbl_index).key1 := test_rec.key1
out_tbl(out_tbl_index).key2 := test_rec.key2
out_tbl(out_tbl_index).value1 := test_rec.value1
out_tbl_index := out_tbl_index + 1;
END;
CLOSE test_cur;
RETURN out_tbl;
END test_get;
END test_pkg;
/

お礼

モロの回答ありがとうございます。

回答No.5

iwashimizu

No.4はPL/SQL表を使用する例でしたが、これだと
結果に対して検索はできないです。
下記の例はオブジェクト型のテーブルの例です。
結果に対して検索できます。
ご質問のFUNCTION機能は書いていませんが、
参考になると思います。

-----------------------------------------------
SET SERVEROUTPUT ON

CREATE TABLE employees (id NUMBER, name VARCHAR2(50), gender VARCHAR2(10));
INSERT INTO employees values (1,'Taro','Male');
INSERT INTO employees values (2,'Jiro','Male');
INSERT INTO employees values (3,'Saburo','Male');
INSERT INTO employees values (4,'Hanako','Female');
COMMIT;

CREATE OR REPLACE TYPE employee_type
AS OBJECT (id NUMBER, name VARCHAR2(50), gender VARCHAR2(10))
/
CREATE OR REPLACE TYPE employee_table_type AS TABLE OF employee_type
/

DECLARE
employee_table employee_table_type;
CURSOR employee_table_cur IS
SELECT * FROM TABLE (CAST(employee_table AS employee_table_type)) tbl;
employee_table_rec employee_table_cur%ROWTYPE;
BEGIN
SELECT employee_type
BULK COLLECT INTO employee_table
FROM (
SELECT employee_type(id, name, gender) employee_type
FROM employees
WHERE gender = 'Male'
);
--
OPEN employee_table_cur;
LOOP
FETCH employee_table_cur INTO employee_table_rec;
EXIT WHEN employee_table_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(employee_table_rec.id||','||employee_table_rec.name);
END LOOP;
END;
/

お礼

>No.4はPL/SQL表を使用する例でしたが、これだと
>結果に対して検索はできないです。
いえいえ、あらかじめ条件で絞った一時テーブルが取得できたら、それに対して細かく扱うことはいたしません。
前方にカーソルを進めて利用するだけなので、全然問題無しです。

また、＃5のこの発言も参考資料として、大変助かります。

ありがとうございました。

回答No.3

taka_tetsu

>SQL-Serverで以前やっていたのですが、そういうことを
>Oracleでもできると聞いたことがあるので、
>質問をしてみたのですが、誤情報であったらすいません。

SQL Serverの処理に一番近いのが、一時表用の領域に一時テーブルを作るものだと思いますけど。

で、メモリ上に確保した配列を返すのが、

>PL/SQL表を返す

です。

ただし、あくまでも配列なので、フェッチのようなことはできません。

お礼

ありがとうございます。

>SQL Serverの処理に一番近いのが、一時表用の領域に一時テーブルを作るものだと思いますけど。
#2の補足を書いた後に、私の早合点であって、同じことを意とした発言であることが理解できました。
すいません。

その一時テーブルを作成する方法が知りたかったのです^^;

ありがとうございました。

回答No.2

taka_tetsu

>私といたしましては「一時テーブル」がよいのかな？と
>思っているのですが、どの方法でも結構です。

結構ですって・・・
何を期待しているのでしょうか？

一時テーブルを作成する場合、8iでは一時テーブル用の表領域を準備し、そこに一時テーブルを作成すれば、消し忘れを防ぐことが可能です。

こんな表領域が準備できないのでしたら、PL/SQL内で空テーブルを作成するなり、CREATE TABLE AS SELECTで抽出結果のテーブルを作ったりすることになるでしょう。

ここでの注意点は、
・途中で異常終了した場合に一時テーブルが残る可能性がある
・固定の名称でテーブルを作成した場合、複数セッションから同時に該当の処理が行われた場合に正しく動作しない。
というところですかね。

要件を考慮しながらがんばってみてください。

補足

>一時的なテーブル
>表領域に

いや、そういうことじゃないと思ったのですが^^;

プロシージャ内で有効な#TmpTable(仮)というようなテーブルを、利用するという意味に受け取っておりました。
要するに物理的に存在するテーブルではなく、本当の一時的にしか存在しないメモリ上のテーブルという意味ではなかったのですね^^;

SQL-Serverで以前やっていたのですが、そういうことをOracleでもできると聞いたことがあるので、質問をしてみたのですが、誤情報であったらすいません。

回答No.1

taka_tetsu

フィルタリングの方法が書いてないのでなんともいえませんが、

カーソルを返す
か、
PL/SQL表を返す

でしょうかね？

あとは、フィルタリングした結果を一時的なテーブルに書き込んで、そのテーブル名を返すとか
(動的SQLがめんどくさそう・・・)

お礼

さっそくありがとうございます。

>カーソルを返す
>PL/SQL表を返す
>一時テーブル
私といたしましては「一時テーブル」がよいのかな？と思っているのですが、どの方法でも結構です。

ちなみにこの関数をコールする側では、その関数で得た表をカーソルにセットして使用するつもりです。
参照するだけで、更新はいたしません。それと、この[TEST_TABLE]は、他のところからもよく参照されるテーブルです。
以上が特徴なのです。

よろしくお願いいたします。