Oracle パイプライン・テーブル・ファンクション サンプル

テーブル・ファンクション（正式にはパイプライン・テーブル・ファンクション）はとても便利です。結果セットをテーブルのように利用できる点については気に入っています。VIEWでは実現できない複雑なビジネスロジックを組み込むことが可能です。以下サンプルはあまり実用性はありませんが、参考になればどうぞ。
（以前パッケージでの実装が必要と記載しておりましたが、パッケージを使わずとも通常のファンクションでも実装可能でした）

スキーマ＋オブジェクト名をそれぞれ整形して分割する

要件および仕様

オブジェクト名（オブジェクト名のみ、スキーマ指定の場合もあり）をインプットに、スキーマ名とオブジェクト名に分割して返却する。以下表の例題参照。

パッケージヘッダー部

CREATE OR REPLACE PACKAGE TESTPKG
IS
  /*
   * レコード型の宣言 (テーブル・ファンクションの行の型) 
   */
  TYPE LIST_TBNAME_TYPE  IS RECORD
  (   
        SCHEMA     VARCHAR2(30),
        TBLNAME    VARCHAR2(30)
  );
  /*
   *  コレクション型の宣言 (テーブル・ファンクションの戻り値)
   */ 
  TYPE LIST_TBNAME_RETURN IS TABLE OF LIST_TBNAME_TYPE;
  /* SQLで利用しているテーブルの一覧を返す */
  FUNCTION SCMTBL_SPLIT(P_OBJNAME IN VARCHAR2) RETURN LIST_TBNAME_RETURN PIPELINED;

END;
/

パッケージボディー部

CREATE OR REPLACE PACKAGE BODY TESTPKG AS

FUNCTION shape_name(P_OBJNAME IN VARCHAR2 )  RETURN VARCHAR2;  

FUNCTION SCMTBL_SPLIT
(
  P_OBJNAME IN VARCHAR2
) return LIST_TBNAME_RETURN PIPELINED
IS

rec         LIST_TBNAME_TYPE;    -- 1行のレコード変数
bf          varchar2(80);
scname      varchar2(34);
tbname      varchar2(34);

BEGIN
	-- 空白を除去
    bf := regexp_replace(P_OBJNAME, '[ *]','',1);

    -- スキーマ名指定があるかどうか
    if (REGEXP_INSTR(P_OBJNAME,'\.',1,1,0,'c') > 0 ) then 
        --スキーマ名抽出
        -- まず「.」以降の文字列を抽出し、REPLACEで「.」を外す
        scname := REPLACE(REGEXP_SUBSTR(bf,'[a-zA-Z0-9._$"]+\.',1,1,'i'),'.','');
        rec.SCHEMA  := shape_name(scname);
        -- テーブル名抽出
        -- まず「.」以降の文字列を抽出し、substrで「.」を外す
        tbname := substr(REGEXP_SUBSTR(bf,'\.[a-zA-Z0-9._$"]+',1,1,'i'),2);
        rec.TBLNAME := shape_name(tbname);
    else
        rec.SCHEMA  := null;
        rec.TBLNAME := shape_name(P_OBJNAME);
    end if;
	PIPE ROW(rec);
    RETURN ;

end SCMTBL_SPLIT;


/* オブジェクト名の整形 */
FUNCTION  shape_name
( 
  P_OBJNAME IN VARCHAR2
)
RETURN varchar2
IS

BEGIN
   -- 小文字を含む文字がダブルクォーテーションで囲まれている場合はそのまま
    if ( REGEXP_INSTR(P_OBJNAME,'["]',1,1,0,'c')  > 0 ) and  REGEXP_INSTR(P_OBJNAME,'[a-z]',1,1,0,'c') > 0 then
        return(P_OBJNAME);
    else
        -- ダブルクォーテーションを外して大文字化
        return(upper(REPLACE(P_OBJNAME,'"','')));
    end if;
END;

END TESTPKG;
/

実行例

SQL> col SCHEMA for a16
SQL> col TBLNAME for a16
SQL> select * from table(TESTPKG.SCMTBL_SPLIT(p_objname => 'scott.emp'));

SCHEMA           TBLNAME
---------------- ----------------
SCOTT            EMP

SQL> select * from table(TESTPKG.SCMTBL_SPLIT(p_objname => 'sCotT."addR"'));

SCHEMA           TBLNAME
---------------- ----------------
SCOTT            "addR"

SQL> select * from table(TESTPKG.SCMTBL_SPLIT(p_objname => '"SCOTT"."addR"'));

SCHEMA           TBLNAME
---------------- ----------------
SCOTT            "addR"

SQL> select * from table(TESTPKG.SCMTBL_SPLIT(p_objname => 'pk_emp'));

SCHEMA           TBLNAME
---------------- ----------------
                 PK_EMP

SQL> select * from table(TESTPKG.SCMTBL_SPLIT(p_objname => 'SCOTT   .    emp'));

SCHEMA           TBLNAME
---------------- ----------------
SCOTT            EMP

SQL>