　 HOME　>>　Tips　>>　Oracle

プロシージャで動的にSQLを作成し、順に実行する方法

2016/7/1

文書番号：20531

SQL で USER_TABLES を参照してテーブルの一覧を取得してそのテーブル名で統計情報更新するSQLを組み立てて順に実行する。　とか、

同じくSQL で USER_INDEXES を参照してインデックスの一覧を取得してインデックスを再構築するSQL を組み立てて順に実行する。

といったことを簡単に実行する手順（SQL）です。

【操作手順】

SELECT 文でSQL を組み立て、カーソルにしてEXECで順次実行していきます。
例としてインデックスの一覧を取得してインデックスを再構築するSQLを実行します。


DECLARE

    --SQLを作成するカーソル定義
    CURSOR CUR_COMMAND_LIST IS
      SELECT 'ALTER INDEX  ' || INDEX_NAME || ' REBUILD' AS cmd
      FROM   USER_INDEXES

    --レコード変数を定義
    REC_COMMAND_LIST CUR_COMMAND_LIST%ROWTYPE;

BEGIN

    --カーソルオープン
    OPEN CUR_COMMAND_LIST;

    LOOP

	    --1行フェッチする。
        FETCH CUR_COMMAND_LIST INTO REC_COMMAND_LIST;
	    --データが存在しない場合は終了
        EXIT WHEN CUR_COMMAND_LIST%NOTFOUND;


	    --SQLを実行
        EXECUTE IMMEDIATE REC_COMMAND_LIST.cmd;
    END LOOP;

    --カーソルを閉じる
    CLOSE CUR_COMMAND_LIST;

END;
/

上記のコマンドをバッチ実行する方法
上記のコマンドの最後に「exit」を付加し、「command.sql」というファイル名で保存します。
※「exit」がないとSQL*Plus でコマンドの待ち状態になるのでいつまでたっても終了しません。
※ファイル名は環境に合わせて設定してください。



DECLARE

    --SQLを作成するカーソル定義
    CURSOR CUR_COMMAND_LIST IS
      SELECT 'ALTER INDEX  ' || INDEX_NAME || ' REBUILD' AS cmd
      FROM   USER_INDEXES

    --レコード変数を定義
    REC_COMMAND_LIST CUR_COMMAND_LIST%ROWTYPE;

BEGIN

    --カーソルオープン
    OPEN CUR_COMMAND_LIST;

    LOOP

	    --1行フェッチする。
        FETCH CUR_COMMAND_LIST INTO REC_COMMAND_LIST;
	    --データが存在しない場合は終了
        EXIT WHEN CUR_COMMAND_LIST%NOTFOUND;


	    --SQLを実行
        EXECUTE IMMEDIATE REC_COMMAND_LIST.cmd;
    END LOOP;

    --カーソルを閉じる
    CLOSE CUR_COMMAND_LIST;

END;
/

exit

上記のSQLファイルを実行するバッチファイル（ファイル名：exec.bat）を作成します。
ダブルクリックで実行したり、タスクなどで定期的に実行することができます。



SQLPLUS ユーザー名/パスワード@接続文字列 @ファイルのフルパス

例：上記で作成した「command.sql」が「c:\cmd」フォルダーにある場合
SQLPLUS USER01/PASS01@ORCL @c:\cmd\command.sql

動的に実行するSQLの例


    --インデックスの一覧を取得し、インデックスの再構築を順に実行する
    CURSOR CUR_COMMAND_LIST IS
      SELECT 'ALTER INDEX  ' || INDEX_NAME || ' REBUILD' AS cmd
      FROM   USER_INDEXES


    --テーブルの一覧を取得し、統計情報の更新を順に実行する
    CURSOR CUR_COMMAND_LIST IS
      SELECT ' ANALYZE TABLE  ' || TABLE_NAME || ' COMPUTE STATISTICS;' AS cmd
      FROM   USER_TABLES


    --スキーマを指定してセッションの一覧を取得し、順に強制終了する
    CURSOR CUR_COMMAND_LIST IS
      SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE' AS cmd
      FROM   V$SESSION
      WHERE  SCHEMANAME = '＜スキーマ名＞';

▲ページのトップへ