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

Question

環境: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_KEYTEST_TABLE.KEY1%TYPE
)
RETURN TABLE IS retTbl TABLE;
というイメージで、[KEY1]値を受け取り、該当するレコードをテーブルとして返す関数を組みたいのですが、調べがつきません。

どなたかよろしくお願いいたします。

iwashimizu · Accepted Answer

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;
/

iwashimizu · Answer

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;
/

taka_tetsu · Answer

>SQL-Serverで以前やっていたのですが、そういうことを
>Oracleでもできると聞いたことがあるので、
>質問をしてみたのですが、誤情報であったらすいません。

SQL Serverの処理に一番近いのが、一時表用の領域に一時テーブルを作るものだと思いますけど。



で、メモリ上に確保した配列を返すのが、

>PL/SQL表を返す

です。

ただし、あくまでも配列なので、フェッチのようなことはできません。

taka_tetsu · Answer

>私といたしましては「一時テーブル」がよいのかな？と
>思っているのですが、どの方法でも結構です。 

結構ですって・・・
何を期待しているのでしょうか？


一時テーブルを作成する場合、8iでは一時テーブル用の表領域を準備し、そこに一時テーブルを作成すれば、消し忘れを防ぐことが可能です。

こんな表領域が準備できないのでしたら、PL/SQL内で空テーブルを作成するなり、CREATE TABLE AS SELECTで抽出結果のテーブルを作ったりすることになるでしょう。

ここでの注意点は、
・途中で異常終了した場合に一時テーブルが残る可能性がある
・固定の名称でテーブルを作成した場合、複数セッションから同時に該当の処理が行われた場合に正しく動作しない。
というところですかね。

要件を考慮しながらがんばってみてください。

taka_tetsu · Answer

フィルタリングの方法が書いてないのでなんともいえませんが、

カーソルを返す
か、
PL/SQL表を返す

でしょうかね？

あとは、フィルタリングした結果を一時的なテーブルに書き込んで、そのテーブル名を返すとか
(動的SQLがめんどくさそう・・・)

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

CREATE OR REPLACE PACKAGE test_pkg AS

No.4はPL/SQL表を使用する例でしたが、これだと

>SQL-Serverで以前やっていたのですが、そういうことを

>私といたしましては「一時テーブル」がよいのかな？と

この回答への補足

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング