【PL/SQL】プロシージャ・ファンクションの一覧やソースの取得/出力/検索
ここではORACLE社が提供するPL/SQL言語でプロシージャ・ファンクションの一覧やソースの取得、出力、検索する方法を紹介しています。
プロシージャ・ファンクションの一覧
データベース上に作成したプロシージャやファンクションの一覧はDBA_PROCEDURESというディクショナリビューで確認することが出来ます。
DBA_PROCEDURESでは全てのプロシージャやファンクションの一覧を確認することが出来ます。
現行ユーザーがアクセスできるプロシージャやファンクションのみを参照する場合はALL_PROCEDURES、現行ユーザが所有するプロシージャやファンクションのみを参照する場合はUSER_PROCEDURESを参照します。
DBA_PROCEDURESは通常のテーブルを検索するようにSELECT文で情報を取得することが出来ます。
-- 全てのプロシージャやファンクションの一覧
SELECT * FROM DBA_PROCEDURES;
-- 現行ユーザがアクセスできるプロシージャやファンクションの一覧
SELECT * FROM ALL_PROCEDURES;
-- 現行ユーザが所有するプロシージャやファンクションの一覧
SELECT * FROM USER_PROCEDURES;DBA_PROCEDURESの主な列の意味は次の通りです。
・OWNER
プロシージャの所有者
※USER_PROCEDURESでは表示できません。
・OBJECT_NAME
オブジェクトの名前
・PROCEDURE_NAME
プロシージャの名前
・OBJECT_ID
オブジェクトのオブジェクト番号
・SUBPROGRAM_ID
一意のサブプログラム識別子
・OVERLOAD
一意の識別子のオーバーロード
・OBJECT_TYPE
オブジェクトの型名
・AGGREGATE
プロシージャが集計関数かどうか
・PIPELINED
プロシージャがパイプラインテーブルファンクションかどうか
・IMPLTYPEOWNER
実装タイプがある場合は、その所有者
・IMPLTYPENAME
実装タイプがある場合は、その名前
・PARALLEL
パラレルで使用可能かどうか
・DETERMINISTIC
DETERMINISTICであると宣言されているかどうか
プログラムの状態・ステータスの確認
プロシージャやファンクションのオブジェクトとしてのステータスは、DBA_OBJECTS、またはALL_OBJECTSを参照します。
現行ユーザーがアクセスできるプロシージャやファンクションのみを参照する場合はALL_OBJECTS、現行ユーザが所有するプロシージャやファンクションのみを参照する場合はUSER_OBJECTSを参照します。
-- 全てのプロシージャやファンクションのステータスを確認するSQL
SELECT OWNER,OBJECT_NAME,STATUS FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE' OR OBJECT_TYPE = 'FUNCTION';
-- 現行ユーザがアクセスできるプロシージャやファンクションのステータスを確認するSQL
SELECT OWNER,OBJECT_NAME,STATUS FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE' OR OBJECT_TYPE = 'FUNCTION';
-- 現行ユーザが所有するプロシージャやファンクションのステータスを確認するSQL
SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE' OR OBJECT_TYPE = 'FUNCTION';プロシージャ・ファンクションの引数の確認
プロシージャやファンクションの引数は、DESCコマンドを使うことで確認することが出来ます。
DESC プロシージャ・ファンクション名実行すると次のように結果を返します。
SQL> DESC FUNC2
FUNCTION FUNC2 RETURNS NUMBER
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P1 NUMBER IN
P2 NUMBER OUTプロシージャ・ファンクションのソースの取得
プロシージャやファンクションのソースは、DBA_SOURCEというディクショナリビューで取得することが出来ます。
DBA_SOURCEでは全てのプロシージャやファンクションのソースを確認することが出来ます。
現行ユーザーがアクセスできるプロシージャやファンクションのみを参照する場合はALL_SOURCE、現行ユーザが所有するプロシージャやファンクションのみを参照する場合はUSER_SOURCEを参照します。
-- 全てのプロシージャやファンクションのソースから取得
SELECT text FROM DBA_SOURCE
WHERE owner = 'ユーザ名' AND name = '名前' ORDER BY line;
-- 現行ユーザがアクセス可能なプロシージャやファンクションのソースから取得
SELECT text FROM DBA_SOURCE WHERE
WHERE owner = 'ユーザ名' AND name = '名前' ORDER BY line;
-- 現行ユーザが所有するプロシージャやファンクションのソースから取得
SELECT text FROM DBA_SOURCE WHERE name = '名前' ORDER BY line;実行すると次のようにソースを取得することが出来ます。SQL> SELECT text FROM USER_SOURCE WHERE name = 'FUNC1';
TEXT
--------------------------------------------------------------------------------
FUNCTION func1(p1 IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN p1 * 5;
END;プロシージャ・ファンクションのソースの出力
プロシージャやファンクションのソースは、DBA_SOURCEというディクショナリビューで取得することが出来ます。
SQLで取得した結果をSPOOLコマンドを使って、ファイルに出力することが出来ます。
「SPOOL ファイルパス」で出力を開始しています。
「SELECT text FROM USER_SOURCE WHERE name = ‘FUNC1’;」でソースを取得しています。
「spool off」でファイルへの出力を終了しています。
事前に行っている設定は次の通りです。状況に合わせて追加してください。
・set trimspool on
各行の出力の終わりから行末までの空白を出力しないようにしています。
・set pagesize 0
ヘッダー表示を非表示にしています。
SQL> set trimspool on
SQL> set pagesize 0
SQL>
SQL> spool C:\plsql\out\test.sql
SQL> SELECT text FROM USER_SOURCE WHERE name = 'FUNC1';
FUNCTION func1(p1 IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN p1 * 5;
END;
SQL> spool off出力されたファイルは次のようになります。
SQL> SELECT text FROM USER_SOURCE WHERE name = 'FUNC1';
FUNCTION func1(p1 IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN p1 * 5;
END;
SQL> spool off
プロシージャ・ファンクションのソースの検索
プロシージャやファンクションのソースは、DBA_SOURCEというディクショナリビューで確認できます。
DBA_SOURCEは通常のテーブルと同じようにSQLで検索することが出来ます。
現行ユーザーがアクセスできるプロシージャやファンクションのみを参照する場合はALL_SOURCE、現行ユーザが所有するプロシージャやファンクションのみを参照する場合はUSER_SOURCEを参照します。
ソースの内容から検索をしたい場合には次のように記述します。
SELECT name FROM DBA_SOURCE WHERE text like '%検索値%';実行すると次のように結果を返します。
SQL> SELECT name FROM USER_SOURCE WHERE text like '%p1 * 5%';
NAME
--------------------------------------------------------------------------------
FUNC2
FUNC1
