[Oracle] 動的SQLの実行（その１）

動的SQLとは、プロシージャやPL/SQLブロックの内部などで動的に組み立てて実行するSQLのことです。

OracleではEXECUTE IMMEDIATE文を使用することで動的SQLを実行することができます。

動的SQLを使う状況として考えられるのは、そのSQLで使用するテーブル名やカラム名などのオブジェクト名や検索条件が実行直前まで未確定で、静的SQLとして記述できないような場合、あるいはプロシージャ内でDDL文などを実行する場合などがあります。

以下に動的SQLの具体的な使用方法を示します。

DECLARE
  wk_indexname  VARCHAR2(30);
  wk_sql        VARCHAR2(1000);
  CURSOR index_cur IS
    SELECT INDEX_NAME
      FROM USER_INDEXES 
      WHERE INDEX_NAME = 'IX_TABLE1';
BEGIN
  OPEN index_cur;
  FETCH index_cur INTO wk_indexname;
  CLOSE index_cur;
  IF wk_indexname = 'IX_TABLE1' THEN
    wk_sql := 'ALTER INDEX IX_TABLE1 REBUILD';
    EXECUTE IMMEDIATE wk_sql;
  END IF;
END;
/

この例では'IX_TABLE1'というインデックスが存在する場合、それを再作成しています。

この時、インデックスを再作成するSQL文（ALTER INDEX文）を直接実行するのではなく、動的SQLとして編集してEXECUTE IMMEDIATE文を使って実行しています。

ALTER TABLE文を直接記述すると文法エラーとなるからです。

もう１つ例を示します。

DECLARE
  wk_indexname  VARCHAR2(30);
  wk_sql        VARCHAR2(1000);
  CURSOR index_cur IS
    SELECT INDEX_NAME 
      FROM USER_INDEXES 
      WHERE INDEX_TYPE = 'NORMAL' 
        AND TEMPORARY = 'N';
BEGIN
  OPEN index_cur;
  LOOP
    FETCH index_cur INTO wk_indexname;
    EXIT WHEN index_cur%NOTFOUND;
    wk_sql := 'ALTER INDEX ' || wk_indexname|| ' REBUILD';
    EXECUTE IMMEDIATE wk_sql;
  END LOOP;
END;
/

この例ではユーザーが持つすべてのテーブルのインデックスを再作成しています。

ALTER INDEX文の場合、インデックス名を指定する必要がありますが、この例のようにカーソルを使ってインデックス名を順番に取得し、インデックス名を埋め込んだALTER INDEX文をその都度編集して実行すれば、コードの中に全インデックス分のALTER INDEX文を記述する必要がないし、インデックスの数が増えたり減ったりしてもコードを変更する必要もなくなります。

１つ目の例は、仕様上やむを得ず動的SQLを使っている例ですが、２つ目の例は、動的SQLのメリットを活かした例と言えます。

【関連エントリ】
[Oracle] 動的SQLの実行（その１） 2005/01/18
[Oracle] 動的SQLの実行（その２） 2005/01/19
[Oracle] 動的SQLでDDL文を実行しているPL/SQLでORA-6508エラーが発生 2005/02/15