Oracle テーブル・ファンクション

事前に２つのTYPEを使い列、テーブルの定義が必要となり少し手間ですが、ファンクションの戻り値をテーブル形式にする事ができます。以下は簡単な例題となっていますが、VIEWでは実現できないような複雑なロジックも実装できて便利です。

検証環境作成

-- テーブルの作成
-- 
create table fruit
(
fid number,
name varchar2(40),
price number,
discount number
);
-- データ投入
--
insert into fruit(fid,name,price,discount) values(101,'バナナ',200,10);
insert into fruit(fid,name,price,discount) values(102,'リンゴ',100,15);
insert into fruit(fid,name,price,discount) values(103,'メロン',300,70);
insert into fruit(fid,name,price,discount) values(104,'ブドウ',260,40);

----------------------------------------------
--typeの作成（レコードイメージ）
-- create or replaceとしているけれど、このtypeを使い以下fruit_arrを
-- 作成した後（関係づけができた後）はcreate or replaceはエラーになります。
-- むりやり削除するなら forceオプション付けると可能

create or replace TYPE fruit_rec as object (
  fid number,
  name varchar2(40),
  price number,
  discount number
);

--typeの作成（上記typeをレコードにみたてたテーブルイメージ）
--
create or replace type fruit_arr as table of fruit_rec
;

-- type を削除するなら以下
drop type fruit_rec force
;

テーブルファンクション例

販売価格のfrom,toを指定し、fruitテーブルのデータを出力します。

CREATE OR REPLACE FUNCTION SOONI.fnc_fruit
(
   from_price  IN number
  ,to_price    IN number
)
RETURN fruit_arr
IS

result_rec fruit_arr := fruit_arr();

cursor cur(from_price in number,to_price in number) is
select  fid,name,price,discount
from fruit
where price >= from_price and price <= to_price
order by price
;
fruit_tbl_rec cur%ROWTYPE;
i number;
begin
i := 1;
for  fruit_tbl_rec in cur(from_price,to_price) loop
    exit when cur%NOTFOUND;

    result_rec.extend;
    result_rec(i) := 
            fruit_rec(
            fruit_tbl_rec.fid,
            fruit_tbl_rec.name,
            fruit_tbl_rec.price,
            fruit_tbl_rec.discount
            );
    i := i + 1;
end loop;return result_rec;
END;
/

実行例

-- まずはテストのデータを確認
--
SQL> select * from fruit order by price;

       FID NAME            PRICE   DISCOUNT
---------- ---------- ---------- ----------
       102 リンゴ            100         15
       101 バナナ            200         10
       104 ブドウ            260         40
       103 メロン            300         70

SQL>
--
-- 12.1までは table句が必要ですが、12.2以降のバージョンではtable句は不要です
-- 100円以上260円以下の一覧出力
--
SQL> select * from table(fnc_fruit(100,260));

       FID NAME            PRICE   DISCOUNT
---------- ---------- ---------- ----------
       102 リンゴ            100         15
       101 バナナ            200         10
       104 ブドウ            260         40

--
-- 結果セットをさらにwhere句を使い条件で絞る事も可能です
--
SQL> select * from table(fnc_fruit(100,260)) where name ='バナナ';

       FID NAME            PRICE   DISCOUNT
---------- ---------- ---------- ----------
       101 バナナ            200         10

SQL>

パイプライン・テーブル・ファンクションは以下です

大量な結果セットを返却するようなシーンでは活躍します。