OracleのEXECUTE IMMEDIATE 動的SQL【PL/SQL】

動的SQL　EXECUTE IMMEDIATE（PL/SQL）

動的SQL　EXECUTE IMMEDIATE（PL/SQL）

OracleのPL/SQLで動的SQL（SQL文を変数で作成）を実行するには、「EXECUTE IMMEDIATE」で実行します。

テーブル名、列名、値などが可変で決まった値ではなくプログラム中で組み立てていく場合に動的SQLは便利です。

EXECUTE IMMEDIATE 構文

-- 動的SQLを実行する(バインド変数なし)
EXECUTE IMMEDIATE　{SQL};

-- 動的SQLを実行する(バインド変数あり)
EXECUTE IMMEDIATE　{SQL} USING {値};

-- 動的SQLを実行する(SELECT)
EXECUTE IMMEDIATE　{SQL} INTO {変数};

「EXECUTE IMMEDIATE」以降にSQLを記述します。

例１. バインド変数なし

CREATE OR REPLACE PROCEDURE proInsertEmp
IS
  vSql VARCHAR2(1000);
  vTbl VARCHAR2(20); 
  vEmpNo VARCHAR2(10); 
  vEmpName VARCHAR2(100); 
BEGIN
  vSql := '';
  vTbl := 'emp';
  vEmpNo := 'A001';
  vEmpName := '山田太郎'; 

  -- 動的SQLを作成する 
  vSql := 'INSERT INTO '
  vSql := vSql || vTbl;
  vSql := vSql || ' (empno,empname)';
  vSql := vSql || ' VALUES ('
  vSql := vSql || vEmpNo 
  vSql := vSql || vEmpName 
  vSql := vSql || ')'; 

  -- 動的SQLを実行する 
  EXECUTE IMMEDIATE vSql;
  COMMIT;
END;

ここでは事前に変数「vSql」にSQL文を文字列で作成しておきます。

テーブル名が変数「vTbl 」、INSERTする値が変数「vEmpNo 」「vEmpName 」として事前に定義しています。

その変数を利用してSQL文を組み立て、変数「vSql」としました。完成したSQL変数「vSqlを「EXECUTE IMMEDIATE」で実行するだけです。

例２. バインド変数あり

CREATE OR REPLACE PROCEDURE proInsertEmp
IS
  vSql VARCHAR2(1000);
  vTbl VARCHAR2(20); 
  vEmpNo VARCHAR2(10); 
  vEmpName VARCHAR2(100); 
BEGIN
  vSql := '';
  vTbl := 'emp';
  vEmpNo := 'A001';
  vEmpName := '山田太郎'; 

  -- 動的SQLを作成する 
  vSql := 'INSERT INTO ' 
  vSql := vSql || vTbl; 
  vSql := vSql || ' (empno,empname)';
  vSql := vSql || ' VALUES (:ENO, :ENAME)';
  
  -- 動的SQLを実行する 
  EXECUTE IMMEDIATE vSql USING vEmpNo, vEmpName;
  COMMIT; 
END;

こちらはバインド変数で動的SQLを作成するパターンです。

SQLにバインド変数（:ENO,:ENAME）を定義しておき、「EXECUTE IMMEDIATE」で実行する際に「USING」のあとに値をそれぞれ割り当てます。

ここではバインド変数「:ENO」にvEmpNoをセットし、バインド変数「:ENAME」に「vEmpName」をセットしました。

注意点としては、「USING」のあとに記述する順番はバインド変数の定義順になります。

例３. SELECTの結果を取得

CREATE OR REPLACE PROCEDURE proInsertEmp
IS
  vSql VARCHAR2(1000);
  vTbl VARCHAR2(20);
  vEmpNo VARCHAR2(10); 
  vEmpName VARCHAR2(100); 
BEGIN
  vSql := '';
  vTbl := 'emp';
  vEmpNo := 'A001';
  vEmpName := ''; 

  -- 動的SQLを作成する(SELECT)
  vSql := 'SELECT empname FROM ';
  vSql := vSql || vTbl; 
  vSql := vSql || ' WHERE empno = :ENO';

  -- 動的SQLを実行する(SELECT)
  EXECUTE IMMEDIATE vSql INTO vEmpName USING vEmpNo;

  -- vEmpNoを使う処理を書く

END;

ここでは動的SQL「vSql」を作成し、SELECTの結果を取得しました。

「EXECUTE IMMEDIATE」で実行したSELECTの結果は「INTO」のあとに定義された変数「vEmpName」で取得できます。

例4. SELECTの結果を取得(複数レコード)

CREATE OR REPLACE PROCEDURE proInsertEmp
IS
  vSql VARCHAR2(1000);
  vTbl VARCHAR2(20);
  vEmpNo VARCHAR2(10); 
  vEmpName VARCHAR2(100); 
  TYPE cur_typ IS REF CURSOR;
  emp_cur cur_typ;
BEGIN
  vSql := '';
  vTbl := 'emp';
  vEmpNo := 'A001';
  vEmpName := '';

  -- 動的SQLを作成する 
  vSql := 'SELECT empname FROM ';
  vSql := vSql || vTbl; 
  vSql := vSql || ' WHERE empno > :ENO';

  -- カーソルオープン
  OPEN emp_cur FOR vSql USING vEmpNo; 
  
  LOOP
    FETCH emp_cur INTO vEmpName;
    EXIT WHEN emp_cur%NOTFOUND;

    --vEmpNoを使う処理を書く

  END LOOP;

  -- カーソルクローズ
  CLOSE emp_cur;
END;

ここでは動的SQL「vSql」を作成し、SELECTの結果を取得するパターンです。例３ではSELECTの結果は単一行でしたが、ここでは複数行です。

複数行のSELECTはカーソルを使用します。カーソルを使用してループで回します。

ループ内でカーソル変数「emp_cur」から「vEmpName」へ取得しました。

SQL文を動的に作成して実行する「EXECUTE IMMEDIATE」の作成例を４つ紹介しました。

以上、Oracleの動的SQLを実行する「EXECUTE IMMEDIATE」でした。