oracle

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

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

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

要件および仕様

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

No.INPUT値返却カラム1返却カラム2補足
scott.empSCOTTEMP小文字でのインプットは大文字へ変換後返却
sCotT.”addR”SCOTT“addR”” “で囲んだ場合はそのまま返却。囲まれていない場合全て大文字に変換して返却
“SCOTT”.”addR”SCOTT“addR”” “で囲まれたものが全て大文字なら” “を外して返却。
pk_empnullPK_EMPスキーマ修飾がない場合返却カラム1はNULL
SCOTT . empSCOTTEMPピリオドの前後は詰めて返却
変換例
パッケージヘッダー部
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>
スポンサーリンク
コピペで使う