テーブル・ファンクション（正式にはパイプライン・テーブル・ファンクション）はとても便利です。結果セットをテーブルのように利用できる点については気に入っています。VIEWでは実現できない複雑なビジネスロジックを組み込むことが可能です。以下サンプルはあまり実用性はありませんが、参考になればどうぞ。
（以前パッケージでの実装が必要と記載しておりましたが、パッケージを使わずとも通常のファンクションでも実装可能でした）
スキーマ＋オブジェクト名をそれぞれ整形して分割する
要件および仕様
オブジェクト名（オブジェクト名のみ、スキーマ指定の場合もあり）をインプットに、スキーマ名とオブジェクト名に分割して返却する。以下表の例題参照。
| No. | INPUT値 | 返却カラム１ | 返却カラム２ | 補足 |
|---|---|---|---|---|
| １ | scott.emp | SCOTT | EMP | 小文字でのインプットは大文字へ変換後返却 |
| ２ | sCotT.”addR” | SCOTT | “addR” | ” “で囲んだ場合はそのまま返却。囲まれていない場合全て大文字に変換して返却 |
| ３ | “SCOTT”.”addR” | SCOTT | “addR” | ” “で囲まれたものが全て大文字なら” “を外して返却。 |
| ４ | pk_emp | null | PK_EMP | スキーマ修飾がない場合返却カラム１はNULL |
| ５ | SCOTT . emp | SCOTT | EMP | ピリオドの前後は詰めて返却 |
パッケージヘッダー部
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 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;
/
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;
/
