7.2 システム固有の動的SQL

システム固有の動的SQLでは、EXECUTE IMMEDIATE文を使用してほとんどの動的SQL文を処理します。

動的SQL文が複数行を戻すSELECT文である場合、システム固有の動的SQLでは、次のいずれかを選択できます。

  • BULK COLLECT INTO句を指定してEXECUTE IMMEDIATE文を使用します。

  • OPEN FOR文、FETCH文およびCLOSE文を使用します。

SQLのカーソル属性は、システム固有の動的SQLのINSERT文、UPDATE文、DELETE文、MERGE文および単一行のSELECT文の後では、それらの文が静的SQL文に対して動作する場合と同様に動作します。SQLのカーソル属性の詳細は、「カーソルの概要」を参照してください。

ここでのトピック

7.2.1 EXECUTE IMMEDIATE文

EXECUTE IMMEDIATE文は、システム固有の動的SQLがほとんどの動的SQL文を処理する際に使用する手段です。

動的SQL文が自己完結型である場合(つまり、バインド変数のプレースホルダがなく、戻すことができる結果がエラーのみである場合)、EXECUTE IMMEDIATE文に句は不要です。

動的SQL文にバインド変数のプレースホルダが含まれている場合、各プレースホルダは、次のように、EXECUTE IMMEDIATE文の適切な句内に対応するバインド変数を持っている必要があります。

  • 動的SQL文が最大で1行を戻すことができるSELECT文である場合、アウトバインド変数(定義)をINTO句に、インバインド変数をUSING句に含めます。

  • 動的SQL文が複数行を戻すことができるSELECT文である場合、アウトバインド変数(定義)をBULK COLLECT INTO句に、インバインド変数をUSING句に含めます。

  • 動的SQL文が、RETURNING INTO句が指定されていないDML文(SELECT以外)である場合、すべてのバインド変数をUSING句に含めます。

  • 動的SQL文がRETURNING INTO句が指定されているDML文である場合、インバインド変数をUSING句に、アウトバインド変数をRETURNING INTO句に含めます。

  • 動的SQL文が無名PL/SQLブロックまたはCALL文である場合、すべてのバインド変数をUSING句に含めます。

    動的SQL文がサブプログラムを起動する場合は、次のことを確認します。

    • サブプログラムが、スキーマ・レベルで作成されているか、またはパッケージ仕様部で宣言および定義されていること。

    • サブプログラム・パラメータのプレースホルダに対応しているすべてのバインド変数のパラメータ・モードがそのサブプログラム・パラメータと同じであること、およびこのバインド変数のデータ型がそのサブプログラム・パラメータのデータ型と互換性があること。

    • いずれのバインド変数も予約語NULLではないこと。

      この制限を回避するには、例7-7に示すように、NULLを使用する場所に未初期化変数を使用します。

    • いずれのバインド変数も、SQLでサポートされないデータ型(文字列により索引付けされる連想配列など)を持たないこと。

      データ型がコレクションまたはレコード型である場合は、パッケージ仕様部で宣言されている必要があります。

ノート:

バインド変数はどのような順序でも評価できます。プログラムにより評価の順序が決定される場合は、プログラムで決定が行われる時点で動作は定義されません。

例7-4、例7-5および例7-6では、動的PL/SQLブロックは、PL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。コレクション型はSQLデータ型ではありません。各例では、コレクション型はパッケージ仕様部で宣言され、サブプログラムはパッケージ仕様部で宣言され、パッケージ本体で定義されます。

関連項目:

例7-1 動的PL/SQLブロックからのサブプログラムの起動

この例では、動的PL/SQLブロックは、スキーマ・レベルで作成されたサブプログラムを起動する無名PL/SQLブロックです。

-- Subprogram that dynamic PL/SQL block invokes:
CREATE OR REPLACE PROCEDURE create_dept (
  deptid IN OUT NUMBER,
  dname  IN     VARCHAR2,
  mgrid  IN     NUMBER,
  locid  IN     NUMBER
) AUTHID DEFINER AS
BEGIN
  deptid := departments_seq.NEXTVAL;

  INSERT INTO departments (
    department_id,
    department_name,
    manager_id,
    location_id
  )
  VALUES (deptid, dname, mgrid, locid);
END;
/
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
 -- Dynamic PL/SQL block invokes subprogram:
  plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

 /* Specify bind variables in USING clause.
    Specify mode for first parameter.
    Modes of other parameters are correct by default. */

  EXECUTE IMMEDIATE plsql_block
    USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

例7-2 BOOLEAN仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQL(SQLではなく)データ型BOOLEANの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。 

CREATE OR REPLACE PROCEDURE p (x BOOLEAN) AUTHID DEFINER AS
BEGIN
  IF x THEN
    DBMS_OUTPUT.PUT_LINE('x is true');
  END IF;
END;
/

DECLARE
  dyn_stmt VARCHAR2(200);
  b        BOOLEAN := TRUE;
BEGIN
  dyn_stmt := 'BEGIN p(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/

結果:

x is true

例7-3 RECORD仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQL(SQLではなく)データ型RECORDの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。レコード型はパッケージ仕様部で宣言され、サブプログラムはパッケージ仕様部で宣言され、パッケージ本体で定義されます。 

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE rec IS RECORD (n1 NUMBER, n2 NUMBER);
 
  PROCEDURE p (x OUT rec, y NUMBER, z NUMBER);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  PROCEDURE p (x OUT rec, y NUMBER, z NUMBER) AS
  BEGIN
    x.n1 := y;
    x.n2 := z;
  END p;
END pkg;
/
DECLARE
  r       pkg.rec;
  dyn_str VARCHAR2(3000);
BEGIN
  dyn_str := 'BEGIN pkg.p(:x, 6, 8); END;';
 
  EXECUTE IMMEDIATE dyn_str USING OUT r;
 
  DBMS_OUTPUT.PUT_LINE('r.n1 = ' || r.n1);
  DBMS_OUTPUT.PUT_LINE('r.n2 = ' || r.n2);
END;
/

例7-4 連想を持つサブプログラムの動的起動配列仮パラメータ

この例では、動的PL/SQLブロックは、PLS_INTEGERで索引付けされる連想配列のPL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

ノート:

このコンテキストで使用される連想配列は、PLS_INTEGERを使用して索引付けする必要があります。 

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE number_names IS TABLE OF VARCHAR2(5)
    INDEX BY PLS_INTEGER;
 
  PROCEDURE print_number_names (x number_names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_number_names (x number_names) IS
  BEGIN
    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(x(i));
    END LOOP;
  END;
END pkg;
/
DECLARE  
  digit_names  pkg.number_names;
  dyn_stmt     VARCHAR2(3000);
BEGIN
  digit_names(0) := 'zero';
  digit_names(1) := 'one';
  digit_names(2) := 'two';
  digit_names(3) := 'three';
  digit_names(4) := 'four';
  digit_names(5) := 'five';
  digit_names(6) := 'six';
  digit_names(7) := 'seven';
  digit_names(8) := 'eight';
  digit_names(9) := 'nine';
 
  dyn_stmt := 'BEGIN pkg.print_number_names(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING digit_names;
END;
/

例7-5 ネストした表仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、ネストした表のPL/SQLコレクション型の仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE names IS TABLE OF VARCHAR2(10);
 
  PROCEDURE print_names (x names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_names (x names) IS
  BEGIN
    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(x(i));
    END LOOP;
  END;
END pkg;
/
DECLARE
  fruits   pkg.names;
  dyn_stmt VARCHAR2(3000);
BEGIN
  fruits := pkg.names('apple', 'banana', 'cherry');
  
  dyn_stmt := 'BEGIN pkg.print_names(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING fruits;
END;
/

例7-6 VARRAY仮パラメータを持つサブプログラムの動的起動

この例では、動的PL/SQLブロックは、PL/SQLコレクション型VARRAYの仮パラメータを持つサブプログラムを起動する無名PL/SQLブロックです。

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
 
  TYPE foursome IS VARRAY(4) OF VARCHAR2(5);
 
  PROCEDURE print_foursome (x foursome);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print_foursome (x foursome) IS
  BEGIN
    IF x.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE 
      FOR i IN x.FIRST .. x.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(x(i));
      END LOOP;
    END IF;
  END;
END pkg;
/
DECLARE
  directions pkg.foursome;
  dyn_stmt VARCHAR2(3000);
BEGIN
  directions := pkg.foursome('north', 'south', 'east', 'west');
  
  dyn_stmt := 'BEGIN pkg.print_foursome(:x); END;';
  EXECUTE IMMEDIATE dyn_stmt USING directions;
END;
/

例7-7 USING句での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;
/

7.2.2 OPEN FOR文、FETCH文およびCLOSE文

動的SQL文が複数行を戻すSELECT文である場合は、システム固有の動的SQLを使用してその文を次のように処理できます。

  1. OPEN FOR文を使用して、カーソル変数を動的SQL文に関連付けます。OPEN FOR文のUSING句で、動的SQL文内の各プレースホルダにバインド変数を指定します。

    USING句に、リテラルNULLを含めることはできません。この制限を回避するには、例7-7に示すように、NULLを使用する場所に未初期化変数を使用します。

  2. FETCH文を使用して、結果セットの行を一度に1行、複数行またはすべて取り出します。

  3. CLOSE文を使用して、カーソル変数をクローズします。

コレクションが「コレクションの問合せ」に示す条件を満たしていれば、動的SQL文でコレクションを問い合せることができます。

関連項目:

例7-8 OPEN FOR文、FETCH文およびCLOSE文を使用したシステム固有の動的SQL

この例では、結果セットの行を一度に1行ずつ取り出して、マネージャであるすべての従業員を表示しています。

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind variable in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;
/

例7-9 システム固有の動的SQLを使用したコレクションの問合せ

この例は例6-30と似ていますが、コレクション変数v1がバインド変数である点が異なります。 

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  TYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));
  TYPE mytab IS TABLE OF rec INDEX BY pls_integer;
END;
/

DECLARE
  v1 pkg.mytab;  -- collection of records
  v2 pkg.rec;
  c1 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR 'SELECT * FROM TABLE(:1)' USING v1;
  FETCH c1 INTO v2;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;
/

7.2.3 動的SQL文内でのプレースホルダ名の繰返し

動的SQL文内でプレースホルダ名を繰り返す場合は、プレースホルダをバインド変数に関連付ける方法が動的SQL文の種類によって異なるということに注意してください。

ここでのトピック

7.2.3.1 動的SQL文が無名ブロックまたはCALL文でない

動的SQL文が無名PL/SQLブロックまたはCALL文を表していない場合、プレースホルダ名の繰返しは重要ではありません。

プレースホルダは、名前ではなく位置によって、USING句内のバインド変数に関連付けられます。

たとえば、次の動的SQL文では、:xという名前の繰返しは重要ではありません。 

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

対応するUSING句では、4つのバインド変数を指定する必要があります。それらは異なっていてもかまいません。たとえば: 

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

前述のEXECUTE IMMEDIATE文は、次のSQL文を実行します。 

INSERT INTO payroll VALUES (a, b, c, d)

:xが出現するたびに、それぞれに同じバインド変数を関連付けるには、そのバインド変数を繰り返す必要があります。たとえば: 

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

前述のEXECUTE IMMEDIATE文は、次のSQL文を実行します。 

INSERT INTO payroll VALUES (a, a, b, a)

7.2.3.2 動的SQL文が無名ブロックまたはCALL文

動的SQL文が無名PL/SQLブロックまたはCALL文を表している場合、プレースホルダ名の繰返しは重要です。

一意のプレースホルダ名が、それぞれUSING句内に対応するバインド変数を持っている必要があります。プレースホルダ名を繰り返す場合、それに対応するバインド変数を繰り返す必要はありません。そのプレースホルダ名に対するすべての参照がUSING句内の1つのバインド変数に対応します。

例7-10 動的PL/SQLブロックで繰り返されるプレースホルダ名

この例では、最初の一意のプレースホルダ名:xに対するすべての参照がUSING句内の最初のバインド変数aに関連付けられており、2番目の一意のプレースホルダ名:yがUSING句内の2番目のバインド変数bに関連付けられています。 

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;  -- calc_stats(a, a, b, a)
END;
/