ヘッダーをスキップ

Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス
10g リリース2(10.2)

B19257-01
目次
目次
索引
索引

戻る 次へ

7 システム固有の動的SQLを使用したSQL操作の実行

この章では、PL/SQLでシステム固有の動的SQL(動的SQL)を使用して、SQL文を実行時に記述および処理することによってプログラムをより柔軟に使用できるようにする方法を説明します。

動的SQLでは、データ定義文およびデータ制御文を含む、ほぼすべてのタイプのSQL文を直接実行できます。表名、WHERE句およびその他の情報が事前に不明でも、文を記述できます。

この章の項目は、次のとおりです。

動的SQLの詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。

PL/SQLで動的SQLを使用する場合

動的SQLを使用すると、SQL文を実行時に動的に作成できます。コンパイル時にSQL文が完全ではない場合があるため、動的SQLを使用することによってより汎用的で柔軟なアプリケーションを作成できます。

動的SQL文を処理するには、ほとんどの場合EXECUTE IMMEDIATE文を使用します。複数行の問合せ(SELECT文)を処理する場合は、OPEN-FOR文、FETCH文およびCLOSE文を使用します。

動的SQLは、次のような場合に必要になります。

古いコードでDBMS_SQLパッケージを使用している場合、この章で説明されているとおりEXECUTE IMMEDIATEおよびOPEN-FORを使用すると、通常パフォーマンスが向上し、コードが読みやすくなり、オブジェクトおよびコレクションのサポートなどの追加機能が提供されます。

動的SQLとDBMS_SQLの比較は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。DBMS_SQLパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。


注意:

EXECUTE IMMEDIATEおよびOPEN-FOR文を使用するシステム固有の動的SQLは、DBMS_SQLパッケージよりも高速で、コーディングが少なくてすみます。ただし、次の場合にはDBMS_SQLパッケージを使用する必要があります。

  • 入力変数または出力変数の数(問合せで返される列値の数など)が不明な場合。動的SQL文(動的SQLのメソッド4)で使用します。

  • 動的コードが大きすぎて32Kバイト内のVARCHAR2変数を使用できない場合。

 

PL/SQLでのEXECUTE IMMEDIATE文の使用

EXECUTE IMMEDIATE文は、動的SQL文または無名PL/SQLブロックを準備(解析)し、即時に実行します。EXECUTE IMMEDIATEの主な引数は、実行するSQL文を含む文字列です。連結を使用して文字列を記述するか、または事前定義の文字列を使用できます。

複数行の問合せの場合を除いて、動的文字列には任意のSQL文または任意のPL/SQLブロックを含めることができます。また、バインド引数のプレースホルダ(接頭辞にコロンが付いた任意の名前)を含めることもできます。この場合、INTO句、USING句およびRETURNING INTO句を使用して、プレースホルダに対応するPL/SQL変数を指定します。

動的文字列に単一のSQL文を記述する場合、引用符の内側の最後にセミコロン(;)を置かないでください。無名PL/SQLブロックを記述する場合、各PL/SQL文の文末および無名ブロックの最後にセミコロンを置きます。文字列リテラルの終了直前、もう一つは終了一重引用符の後にセミコロンを置くことになります。

プレースホルダは、WHERE句の条件テストなど、SQL文の中で変数を置き換えることができる場所にのみ使用できます。スキーマ・オブジェクトの名前としてプレースホルダを使用することはできません。正しい方法は、「パラメータとしてのスキーマ・オブジェクト名の引渡し」を参照してください。

INTO句は単一行の問合せの場合に使用され、検索された列の値を入れる変数またはレコードを指定します。問合せによって取り出された値それぞれに対して、INTO句の中に、対応する型互換性変数が存在している必要があります。

RETURNING INTO句は、RETURNING句のある(BULK COLLECT句のない)DML文の場合にのみ使用され、列の値が戻される変数を指定します。DML文によって戻された値それぞれに対して、RETURNING INTO句の中に、対応する型互換性変数が存在している必要があります。

バインド引数は、すべてUSING句に入れることができます。デフォルトのパラメータ・モードはINです。RETURNING句を持つDML文の場合は、パラメータ・モードを指定しなくても、OUT引数をRETURNING INTO句に入れることができます。USING句とRETURNING INTO句の両方を使用する場合、USING句にはIN引数のみを含めることができます。

実行時に、バインド引数は動的文字列内の対応するプレースホルダを置き換えます。すべてのプレースホルダをUSING句内またはRETURNING INTO句内(あるいはその両方)のバインド引数に関連付ける必要があります。数値リテラル、文字リテラルおよび文字列リテラルはバインド引数として使用できますが、ブール・リテラル(TRUEFALSEおよびNULL)は使用できません。動的文字列にNULLを渡すには、回避策を使用する必要があります。「動的SQLへのNULLの引渡し」を参照してください。

動的SQLはすべてのSQLデータ型をサポートしています。たとえば、定義変数やバインド引数をコレクション、LOB、オブジェクト型のインスタンスおよびrefとすることができます。

通常、動的SQLはPL/SQL固有の型をサポートしていません。たとえば、定義変数やバインド引数をブールまたは結合配列にすることはできません。例外として、PL/SQLレコードをINTO句に入れることができます。

動的SQL文は、バインド引数の新しい値を使用して繰り返し実行できます。ただし、EXECUTE IMMEDIATEは実行のたびに動的文字列を準備するため、オーバーヘッドが発生します。

EXECUTE IMMEDIATEの詳細は、「EXECUTE IMMEDIATE文」を参照してください。

例7-1 に、動的SQLの使用例をいくつか示します。

例7-1    動的SQLの例

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER, 
                             emp_column VARCHAR2, amount NUMBER) IS
   v_column VARCHAR2(30);
   sql_stmt  VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
  SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' 
               || v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column 
                        || ' = ' || column_value);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

DECLARE
   plsql_block       VARCHAR2(500);
BEGIN
-- note the semi-colons (;) inside the quotes '...'
  plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
  EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
  EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
      USING 112, 'EMPLOYEE_ID', 10;
END;
/

DECLARE
   sql_stmt          VARCHAR2(200);
   v_column          VARCHAR2(30) := 'DEPARTMENT_ID';
   dept_id           NUMBER(4) := 46;
   dept_name         VARCHAR2(30) := 'Special Projects';
   mgr_id            NUMBER(6) := 200;
   loc_id            NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
  sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
      USING dept_id;
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
  EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/

例7-2では、スタンドアロン・プロシージャはデータベース表の名前とオプションのWHERE句条件を受け入れます。条件を省略すると、プロシージャは表の中のすべての行を削除します。条件が省略されていなければ、プロシージャは条件を満たす行のみを削除します。

例7-2    表名およびWHERE句を受け入れる動的SQLプロシージャ

CREATE TABLE employees_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition  IN VARCHAR2 DEFAULT NULL) AS
   where_clause  VARCHAR2(100) := ' WHERE ' || condition;
   v_table      VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
  SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
  delete_rows('employees_temp', 'employee_id = 111');
END;
/

動的SQL文字列におけるバインド変数のパラメータ・モードの指定

USING句を使用すると、モードはデフォルトでINに設定されるため、入力バインド引数のパラメータ・モードを指定する必要はありません。

RETURNING INTO句を使用すると、パラメータ・モードはOUTになるため、出力バインド引数のパラメータ・モードを指定できません。

次のようにより複雑な場合は、パラメータ・モードを指定する必要があります。ここでは、動的PL/SQLブロックからプロシージャをコールしています。

CREATE PROCEDURE create_dept (
   deptid IN OUT NUMBER,
   dname  IN VARCHAR2,
   mgrid  IN NUMBER,
   locid  IN NUMBER) AS
BEGIN
   SELECT departments_seq.NEXTVAL INTO deptid FROM dual;
   INSERT INTO departments VALUES (deptid, dname, mgrid, locid);
END;
/

動的PL/SQLブロックからプロシージャをコールするには、例7-3で示すように、仮パラメータdeptidに対応付けられたバインド引数に、IN OUTモードを指定する必要があります。

例7-3    IN OUTバインド引数を使用した置換の指定

DECLARE
   plsql_block VARCHAR2(500);
   new_deptid  NUMBER(4);
   new_dname   VARCHAR2(30) := 'Advertising';
   new_mgrid   NUMBER(6) := 200;
   new_locid   NUMBER(4) := 1700;
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

PL/SQLでのバルク動的SQLの使用

バルクSQLでは、個々の要素ではなくコレクション全体がやり取りされます。この手法では、PL/SQLエンジンとSQLエンジンの間のコンテキスト切替えの回数を最小限に抑えることによって、パフォーマンスを向上させます。すべての反復でSQL文を発行するループではなく、1つの文を使用できます。

次のコマンド、句およびカーソル属性を使用すると、アプリケーションでバルクSQL文を作成して、実行時に動的に実行できます。

BULK FETCH
BULK EXECUTE IMMEDIATE
FORALL
COLLECT INTO
RETURNING INTO
%BULK_ROWCOUNTカーソル属性

前述の文、句およびカーソル属性の静的バージョンについては、「バルクSQLによる、DML文および問合せのループ・オーバーヘッドの削減」を参照してください。背景情報も記載されています。

動的SQLとバルクSQLの使用

バルク・バインドを使用すると、SQL文の変数を値のコレクションにバインドできます。コレクション型には、任意のPL/SQLコレクション型(索引付き表、ネストした表またはVARRAY)を使用できます。コレクション要素には、CHARDATENUMBERなどのSQLデータ型が必要です。バルク動的バインドがサポートされる文は、EXECUTE IMMEDIATEFETCHおよびFORALLです。

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE文にBULK COLLECT INTO句を使用すると、問合せの結果セットの各列の値を個別のコレクションに格納できます。

EXECUTE IMMEDIATE文にRETURNING BULK COLLECT INTO句を使用すると、INSERT文、UPDATE文またはDELETE文の結果をコレクション・セットに格納できます。

FETCH

FETCH文にBULK COLLECT INTO句を使用すると、カーソルの各列からフェッチした値を個別のコレクションに格納できます。

FORALL

FORALL文には、EXECUTE IMMEDIATE文にRETURNING BULK COLLECT INTOを指定して含めることができます。INSERT文、UPDATE文またはDELETE文のすべての結果を、コレクション・セットに格納できます。

USING句を使用して、EXECUTE IMMEDIATE文に添字付きのコレクション要素を渡すことができます。添字付きの要素は、EXECUTE IMMEDIATEの文字列引数に直接連結できません。たとえば、表名のコレクションを構築し、それぞれの反復が異なる表に適用されるFORALL文を記述することはできません。

バルク動的バインドの例

この項では動的バルクのバインの例を示します。BULK COLLECT INTO句を使用して、動的問合せの定義変数をバインドできます。例7-4で示すように、この句をバルクFETCH文またはバルクEXECUTE IMMEDIATE文に使用できます。

例7-4    BULK COLLECT INTO句を使用した動的SQL

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(25);
   emp_cv EmpCurTyp;
   empids NumList;
   enames NameList;
   sals   NumList;
BEGIN
   OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
   FETCH emp_cv BULK COLLECT INTO empids, enames;
   CLOSE emp_cv;
   EXECUTE IMMEDIATE 'SELECT salary FROM employees'
      BULK COLLECT INTO sals;
END;
/

出力バインド変数を使用できるのは、INSERTUPDATEおよびDELETE文のみです。例7-5で示すように、EXECUTE IMMEDIATE文にRETURNING BULK COLLECT INTO句を指定して、変数をバルク・バインドします。

例7-5    RETURNING BULK COLLECT INTO句を使用した動的SQL

DECLARE
   TYPE NameList IS TABLE OF VARCHAR2(15);
   enames    NameList;
   bonus_amt NUMBER := 50;
   sql_stmt  VARCHAR(200);
BEGIN
   sql_stmt := 'UPDATE employees SET salary = salary + :1 
                RETURNING last_name INTO :2';
   EXECUTE IMMEDIATE sql_stmt
      USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/

SQL文中で入力変数をバインドするには、例7-6で示すように、FORALL文とUSING句を使用できます。SQL文を問合せにすることはできません。

例7-6    FORALL文中の動的SQL

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(15);
   empids NumList;
   enames NameList;
BEGIN
   empids := NumList(101,102,103,104,105);
   FORALL i IN 1..5
      EXECUTE IMMEDIATE
        'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
         RETURNING last_name INTO :2'
         USING empids(i) RETURNING BULK COLLECT INTO enames;
END;
/

PL/SQLでの動的SQL使用のガイドライン

この項では、動的SQLを十分に活用する方法、および頻繁に発生する問題を回避する方法について説明します。


注意:

PL/SQLで動的SQLを使用する場合は、SQLインジェクションのリスクを認識しておく必要があります。SQLインジェクションによって、セキュリティの問題が発生する可能性があります。SQLインジェクションおよび発生する可能性がある問題の詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。SQLインジェクションについては、Oracle Technology Networkのhttp://www.oracle.com/technologyで検索することもできます。 


動的SQLを使用した動的問合せの構築

動的な複数行の問合せを処理するには、OPEN-FOR文、FETCH文およびCLOSE文の3つの文を使用します。まず、OPEN-FOR文でカーソル変数を複数行問合せ用にオープンします。次に、FETCH文で結果セットから一度に1行ずつ行を取り出します。すべての行が処理された後に、CLOSE文でカーソル変数をクローズします。カーソル変数の詳細は、「カーソル変数(REF CURSOR)の使用」を参照してください。

動的SQLでのセミコロンの使用または省略

単一のSQL文を1つの文字列として記述する場合、文末にセミコロンを置かないでください。

無名PL/SQLブロックを記述する場合、各PL/SQL文の文末および無名ブロックの最後にセミコロンを置きます。次に例を示します。

BEGIN
   EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/

バインド変数を使用した動的SQLのパフォーマンスの向上

PL/SQLにINSERT文、UPDATE文、DELETE文およびSELECT文を直接コーディングすると、PL/SQLは、これらの文がSQLで効率的に動作するように、変数を自動的にバインド変数に変換します。このような文を動的SQLに記述した場合、同等のパフォーマンスを得るには、自分でバインド変数を指定する必要があります。

次の例では、Oracleはemp_idの個別値ごとに異なるカーソルをオープンしています。この場合、各文が解析およびキャッシュされるため、リソースの競合およびパフォーマンスの低下が発生する可能性があります。

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM employees WHERE employee_id = ' || TO_CHAR(emp_id);
END;
/

バインド変数を使用すると、パフォーマンスが向上します。これによって、Oracleはemp_idの異なる値に対して同じカーソルを再利用できるようになります。

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
END;
/

パラメータとしてのスキーマ・オブジェクト名の引渡し

任意のデータベース表の名前を受け入れ、その表をスキーマから削除するプロシージャが必要だとします。この場合、オブジェクト名を含む文で文字列を記述し、EXECUTE IMMEDIATEを使用してその文を実行する必要があります。

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

USING句を使用して、表名をバインド変数として渡すのではなく、連結を使用して文字列を記述します。

さらに、名前が実行時まで不明なプロシージャをコールする必要がある場合は、そのプロシージャを指定するパラメータを渡すことができます。たとえば、次のプロシージャでは、実行時にプロシージャ名を指定して、別のプロシージャ(drop_table)をコールできます。

CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'CALL "' || proc_name || '" ( :proc_name )' using table_name;
END;
/

drop_tableで表を削除する場合は、次のようにプロシージャを実行できます。プロシージャ名が大文字である点に注意してください。

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
BEGIN 
  run_proc('DROP_TABLE', 'employees_temp'); 
END;
/

動的SQLでの重複するプレースホルダの使用

動的SQL文のプレースホルダは、USING句内のバインド引数に、名前ではなく位置によって対応付けられます。プレースホルダの順序を、:a、:a、:b、:bのように指定した場合、USING句に4つの項目を含める必要があります。たとえば、次のような動的文字列があるとします。

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

xという名前が繰り返されていることは重要ではありません。4つの異なるバインド変数を使用して、対応するUSING句をコーディングできます。

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

動的SQL文がPL/SQLブロックを表している場合、重複するプレースホルダには異なる規則が適用されます。一意の各プレースホルダは、USING句の1つの項目にマップされます。同じプレースホルダが2回以上指定されている場合、その名前に対するすべての参照がUSING句内の1つのバインド引数に対応します。例7-7では、プレースホルダxに対するすべての参照が最初のバインド引数aに対応し、2番目の一意のプレースホルダyが2番目のバインド引数bに対応しています。

例7-7    動的SQLでの重複するプレースホルダの使用

CREATE PROCEDURE calc_stats(w NUMBER, x NUMBER, y NUMBER, z NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
   a NUMBER := 4;
   b NUMBER := 7;
   plsql_block VARCHAR2(100);
BEGIN
   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
   EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/

動的SQLでのカーソル属性の使用

SQLのカーソル属性%FOUND%ISOPEN%NOTFOUNDおよび%ROWCOUNTは、動的SQLの中でINSERT文、UPDATE文、DELETE文または1行のSELECT文を発行した場合に機能します。

BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

同様に、カーソル変数名にカーソル属性を追加すると、複数行の問合せの実行に関する情報が戻されます。

例7-8    明示カーソルの%ROWCOUNTへのアクセス

DECLARE
  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  rec_tab emp_tab;
  rows_fetched NUMBER;
BEGIN
  OPEN c1 FOR 'SELECT * FROM employees';
  FETCH c1 BULK COLLECT INTO rec_tab;
  rows_fetched := c1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));
END;
/

カーソル属性の詳細は、「PL/SQLでのカーソルの管理」を参照してください。

動的SQLへのNULLの引渡し

リテラルNULLは、USING句の中では使用できません。この制限を回避するには、キーワードNULLを未初期化の変数で置き換えます。

CREATE TABLE employees_temp AS SELECT * FROM EMPLOYEES;
DECLARE
   a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;
END;
/

動的SQLでのデータベース・リンクの使用

PL/SQLサブプログラムは、データベース・リンクを使用してリモート・データベースにあるオブジェクトを参照する動的SQL文を実行できます。

CREATE PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM departments@' || db_link ||
      ' WHERE department_id = :num' USING dept_id;
END;
/
-- delete department id 41 in the departments table on the remote DB hr_db
CALL delete_dept('hr_db', 41); 

リモート・プロシージャ・コール(RPC)のターゲットは、動的SQL文を含むことができます。たとえば、表内の行の数を戻す次のスタンドアロン・ファンクションが、ロンドンのhr_dbデータベースに常駐するとします。

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER AS
   rows NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
   RETURN rows;
END;
/
-- From an anonymous block, you might call the function remotely, as follows:
DECLARE
   emp_count INTEGER;
BEGIN
   emp_count := row_count@hr_db('employees');
   DBMS_OUTPUT.PUT_LINE(emp_count);
END;
/

動的SQLでの実行者権限の使用

動的SQLを使用すると、スキーマ管理プロシージャを記述できます。このプロシージャは、1つのスキーマ内に集中させ、他のスキーマからコールしたり、他のスキーマ内のオブジェクトに対して操作を実行することができます。たとえば、このプロシージャを使用して、すべての種類のデータベース・オブジェクトを削除できます。

CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
  AUTHID CURRENT_USER AS
BEGIN
   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/

このプロシージャが、HRスキーマの一部であると想定します。AUTHID句を指定しない場合、プロシージャは、どのスキーマからコールされたかにかかわらず常にHRスキーマ内のオブジェクトを削除します。オブジェクトの完全修飾名を渡した場合でも、このプロシージャは他のスキーマを変更する権限を持ちません。

AUTHID句を使用することで、これらの制限を回避できます。これによって、プロシージャは起動したユーザーの権限で実行され、未修飾の参照はそのスキーマ内のオブジェクトを参照します。

詳細は、「実行者権限と定義者権限の使用(AUTHID句)」を参照してください。

動的SQLでのRESTRICT_REFERENCESプラグマの使用

SQL文からコールされるファンクションは、副作用を制御するための特定の規則に従う必要があります。(「PL/SQLサブプログラムの副作用の制御」を参照。)この規則に違反していないかどうかを確認するには、RESTRICT_REFERENCESプラグマを使用できます。プラグマは、ファンクションがデータベース表またはパッケージ変数に対する読込みまたは書込みを行っていないことを示します(詳細は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照。)

ファンクション本体に動的INSERT文、UPDATE文またはDELETE文が含まれている場合、そのファンクションは常にデータベースへの書込み禁止状態WNDS)およびデータベースに読込み禁止状態RNDS)の規則に違反します。PL/SQLでは、これらの副作用を自動的に検出できません。これは、動的SQL文がコンパイル時ではなく実行時にチェックされるためです。EXECUTE IMMEDIATE文内では、INTO句のみがコンパイル時にRNDSの規則に違反していないかどうかがチェックされます。

動的SQLでのデッドロックの回避

まれに、SQLデータ定義文の実行によってデッドロックが発生することがあります。たとえば、次のプロシージャは自身を削除しようとしているため、デッドロックが発生します。デッドロックを回避するには、サブプログラムまたはパッケージを、使用中にALTERで変更したり、DROPで削除しないでください。

CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock!
END;
/

USING句の下位互換性

動的INSERT文、UPDATE文またはDELETE文にRETURNING句がある場合、出力バインド引数をRETURNING INTO句またはUSING句に入れることができます。新しいアプリケーションでは、RETURNING INTO句を使用します。古いアプリケーションでは、USING句を引き続き使用できます。

PL/SQLのレコードおよびコレクションでの動的SQLの使用

レコードおよびコレクションで動的SQLを使用できます。例7-9で示すように、動的な複数行の問合せの結果セットから行をフェッチしてレコードに入れることができます。

例7-9    レコードへの動的SQLフェッチ

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  employees%ROWTYPE;
   sql_stmt VARCHAR2(200);
   v_job   VARCHAR2(10) := 'ST_CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
   OPEN emp_cv FOR sql_stmt USING v_job;
   LOOP
     FETCH emp_cv INTO emp_rec;
     EXIT WHEN emp_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' ||
                           emp_rec.job_id);
   END LOOP;
   CLOSE emp_cv;
END;
/

オブジェクト型を使用した動的SQLの使用例は、「オブジェクトでの動的SQLの使用」を参照してください。


戻る 次へ
Oracle
Copyright © 2005 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引