152 DBMS_SQL

DBMS_SQLパッケージでは、動的SQLを使用するためのインタフェースを提供し、PL/SQLを使用したデータ操作言語(DML)文やデータ定義言語(DDL)文の解析を可能にします。

たとえば、DBMS_SQLパッケージが提供するPARSEプロシージャを使用することによって、ストアド・プロシージャ内からDROP TABLE文を入力できます。

この章のトピックは、次のとおりです:

参照:

ネイティブ動的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

152.1 DBMS_SQLの概要

Oracleを使用すると、動的SQLを使用するストアド・プロシージャおよび無名PL/SQLブロックを記述できます。動的SQL文は、ユーザーのソース・プログラムに埋め込まれておらず、実行時にプログラムに入力されるか、またはプログラムによって作成されるように、文字列で格納されています。これによって、さらに汎用的なプロシージャを作成できます。たとえば、この動的SQLによって、実行時まで名前がわからない表で動作するプロシージャを作成できます。

ネイティブ動的SQLはDBMS_SQLに代わるものであり、動的SQL文をPL/SQLブロックに直接設定できます。ほとんどの場合、ネイティブ動的SQLは、DBMS_SQLと比べると簡単に使用でき、より高パフォーマンスです。ただし、ネイティブ動的SQL自体に、次の制限があります。

  • メソッド4(不明数の入力または出力を含む動的SQL文)はサポートされていません。

  • DBMS_SQLを使用する場合のみに実行可能なタスクがいくつかあります。DBMS_SQLが必要なタスクの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

ストアド・プロシージャ内から動的SQLを使用する機能は一般的に、Oracle Call Interface(OCI)の手順に従っています。

PL/SQLは、Cなどの他の一般的なプログラム言語とは、多少異なります。たとえば、ユーザーはアドレス(ポインタとも呼ばれます)をPL/SQLで参照できません。そのため、Oracle Call InterfaceとDBMS_SQLパッケージの間には、いくつか相違点があります。相違点は、次のとおりです。

  • OCIはアドレスでバインドし、DBMS_SQLパッケージは値でバインドします。

  • DBMS_SQLでは、無名ブロックのOUTパラメータの値を取得するためにVARIABLE_VALUEをコールする必要があります。また、行をフェッチして、行内の列の値をプログラムに取得した後で、COLUMN_VALUEをコールする必要があります。

  • 現行のリリースのDBMS_SQLパッケージは、CANCELカーソル・プロシージャを提供していません。

  • NULLはPL/SQL変数の値として完全にサポートされているため、インジケータ変数は不要です。

152.2 DBMS_SQLのセキュリティ・モデル

DBMS_SQLは、SYS所有のパッケージであり、AUTHID CURRENT_USERでコンパイルされます。無名PL/SQLブロックからコールされたすべてのDBMS_SQLサブプログラムは、現行のユーザーの権限を使用して実行されます。

参照:

実行者権限または定義者権限の使用方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

オープン・カーソル番号による悪意のあるアクセスまたは誤ったアクセスの防止

オープン・カーソルを示していないカーソル番号を使用してDBMS_SQLサブプログラムをコールすると、エラーORA-29471が発生します。このエラーが発生すると、アラート・ログにアラートが発行され、セッションが存続している間DBMS_SQLは操作不能になります。

IS_OPENファンクションに対するコール内のカーソル番号の実際の値が、セッション内で現在オープンされているカーソルを示している場合、戻り値はTRUEになります。実際の値がNULLの場合、戻り値はFALSEになります。これら以外の場合は、ORA-29471エラーが発生します。

カーソルの不適切な使用の防止

カーソルは、既存のカーソルを破壊するセキュリティ侵害から保護されています。

バインド時および実行時にチェックが行われます。チェックは、必要に応じてDBMS_SQLサブプログラムをコールするたびに実行することもできます。次のことがチェックされます。

  • current_userが、サブプログラムをコールした時点と最新の解析をコールした時点で同じであること。

  • サブプログラムのコール時に有効化されたロールが、最新の解析のコール時に有効化されたロールと同一であるかどうか。

  • コンテナが、サブプログラムをコールした時点と最新の解析をコールした時点で同じであること。

定義者の権限サブプログラムを使用した場合との整合性を維持するために、ロールは適用されません。

いずれかのチェックが正常に実行されなかった場合は、ORA-29470エラーが発生します。

チェックが実行されるタイミングを定義するためのメカニズムは、許容値がNULL1および2の仮パラメータsecurity_levelを取るOPEN_CURSORサブプログラムの新しいオーバーロードです。

  • security_level = 1(またはNULL)の場合、チェックはバインド時および実行時にのみ行われます。

  • security_level = 2の場合、チェックは常に行われます。

アップグレードに関する考慮事項

このセキュリティ体制は、以前のリリースよりも厳しくなっています。したがって、DBMS_SQLを使用すると、アップグレード時にランタイム・エラーが発生する可能性があります。

152.3 DBMS_SQLの定数

DBMS_SQL Constantsパッケージは、PARSEプロシージャlanguage_flagパラメータとともに使用される定数を提供します。

これらの定数を、次の表に示します。

表152-1 DBMS_SQLの定数

名前 タイプ 説明

V6

INTEGER

0

Oracle Databaseバージョン6の動作を指定します。

NATIVE

INTEGER

1

プログラムの接続先のデータベースの通常の動作を指定します。

V7

INTEGER

2

Oracle Databaseバージョン7の動作を指定します。

FOREIGN_SYNTAX

INTEGER

4294967295

Oracle以外のデータベースの構文および動作を指定します。解析するSQL文は、まず、データベース・セッションで設定されたSQL翻訳プロファイルを使用して翻訳する必要があります。SQL翻訳プロファイルは、SQL文をOracleに翻訳する方法を指示するデータベース・スキーマ・オブジェクトです。プロファイルが設定されていない場合、エラーが発生します。

152.4 DBMS_SQLの使用上の注意

次の使用上の注意は、問合せの処理、更新の処理、使用と削除、およびエラーの特定について説明しています。

問合せの処理

動的SQLを使用して問合せを処理する場合は、次のステップを実行する必要があります。

  1. DEFINE_COLUMNプロシージャDEFINE_COLUMN_LONGプロシージャまたはDEFINE_ARRAYプロシージャをコールして、SELECT文が戻す値を受け入れる変数を指定します。

  2. EXECUTEファンクションをコールして、SELECT文を実行します。

  3. FETCH_ROWSファンクション(またはEXECUTE_AND_FETCH)をコールして、問合せに一致した行を取得します。

  4. COLUMN_VALUEプロシージャまたはCOLUMN_VALUE_LONGプロシージャをコールして、問合せに関してFETCH_ROWSファンクションが取得した列の値を判別します。PL/SQLプロシージャへのコールを含んだ無名ブロックを使用した場合は、VARIABLE_VALUEプロシージャをコールして、これらのプロシージャの出力変数に割り当てられた値を取得します。

更新、挿入および削除の処理

動的SQLを使用してINSERTUPDATEまたはDELETEを処理する場合は、次のステップを実行する必要があります。

  1. EXECUTEファンクションをコールして、INSERT文、UPDATE文またはDELETE文を実行します。

  2. 文にreturning句がある場合は、VARIABLE_VALUEプロシージャをコールして出力変数に割り当てられた値を取得します。

エラーの位置

DBMS_SQLパッケージには、セッションで最後に参照されたカーソルの情報を取得するための追加ファンクションがあります。これらのファンクションが戻す値は、SQL文の実行直後にのみ有効です。また、エラーを検出するファンクションは、特定のDBMS_SQLコール後にのみ意味を持ちます。たとえば、PARSEプロシージャの1つを呼び出した直後には、LAST_ERROR_POSITIONファンクションをコールします。

152.5 DBMS_SQL実行フロー

これらのファンクションは、DBMS_SQL実行フローを構成します。

152.5.1 OPEN_CURSOR

SQL文を処理するためには、オープン・カーソルが必要です。OPEN_CURSORファンクションをコールすると、ユーザーはOracleが保持している有効なカーソルを示すデータ構造のカーソルID番号を受け取ります。

これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQLパッケージでのみ使用されます。

152.5.2 PARSE

SQL文はすべて、PARSEプロシージャをコールして解析する必要があります。文を解析することによって、その文の構文がチェックされ、プログラム内のカーソルに関連付けられます。

DML文またはDDL文はすべて解析できます。DDL文は解析時に実行され、暗黙のコミットを実行します。

図152-1に、DBMS_SQLの実行フローを示します。

図152-1 DBMS_SQL実行フロー

図152-1の説明が続きます
「図152-1 DBMS_SQL実行フロー」の説明

152.5.3 BIND_VARIABLE、BIND_VARIABLE_PKGまたはBIND_ARRAY

多くのDML文では、プログラム内のデータをOracleに入力することが必要です。実行時に提供する入力データを含んでいるSQL文を定義する場合は、SQL文内のプレースホルダを使用して、データの提供場所にマークを付ける必要があります。

SQL文内の各プレースホルダに対してBIND_ARRAYプロシージャBIND_VARIABLEプロシージャBIND_VARIABLE_PKGプロシージャのいずれか1つをコールして、プログラム内の変数の値(または配列の値)をプレースホルダに提供する必要があります。SQL文が引き続き実行されると、Oracleは、ユーザーのプログラムが入力変数と出力変数、またはバインド変数に設定したデータを使用します。

DBMS_SQLは、その都度異なるバインド変数を使用してDML文を繰り返し実行できます。BIND_ARRAYプロシージャを使用すると、スカラーのコレクションをバインドでき、それぞれの値はEXECUTEごとに1回だけ入力変数として使用されます。これは、OCIがサポートする配列インタフェースに類似しています。

プレースホルダーにバインドされた値のデータ・タイプは、PL/SQL専用のデータ・タイプであってはなりません。

152.5.4 DEFINE_COLUMN、DEFINE_COLUMN_LONG、またはDEFINE_ARRAY

DEFINE_COLUMNDEFINE_COLUMN_LONGおよびDEFINE_ARRAYプロシージャは、問合せのSELECT値を受け取る変数を指定します。

SELECT文内で選択されている行の列は、選択リスト内での相対位置(左から右)によって識別されます。問合せの場合は、定義プロシージャ(DEFINE_COLUMNプロシージャDEFINE_COLUMN_LONGプロシージャまたはDEFINE_ARRAYプロシージャ)の1つをコールして、SELECT値を受け入れる変数を指定する必要があり、これはINTO句が静的問合せに対して行う方法とほとんど同じです。

DEFINE_COLUMNを使用してLONG列以外の列を定義するのと同じ方法で、DEFINE_COLUMN_LONGプロシージャを使用してLONG列を定義します。COLUMN_VALUE_LONGプロシージャを使用してLONG列からフェッチする前に、DEFINE_COLUMN_LONGをコールする必要があります。

DEFINE_ARRAYプロシージャを使用して、行を単一のSELECT文でフェッチするPL/SQLコレクションを定義します。DEFINE_ARRAYは、1回のフェッチで複数行をフェッチするインタフェースを提供します。COLUMN_VALUEプロシージャで行をフェッチする前に、DEFINE_ARRAYをコールする必要があります。

152.5.5 EXECUTE

EXECUTEファンクションをコールして、SQL文を実行します。

152.5.6 FETCH_ROWSまたはEXECUTE_AND_FETCH

FETCH_ROWSファンクションで問合せを満たす行を取得します。フェッチで行を取得できなくなるまで、フェッチを連続実行して別の行を取得します。単一の実行に対してEXECUTEをコールしている場合は、EXECUTEファンクションをコールして次にFETCH_ROWSをコールするかわりに、EXECUTE_AND_FETCHファンクションをコールするほうが効率的な場合があります。

152.5.7 VARIABLE_VALUE、VARIABLE_VALUE_PKG、COLUMN_VALUE、またはCOLUMN_VALUE_LONG

コールのタイプにより、使用するプロシージャまたはファンクションが決まります。

問合せの場合は、COLUMN_VALUEプロシージャをコールして、FETCH_ROWSファンクションで取得する列の値を決定します。

returning句によるPL/SQLプロシージャまたはDML文へのコールを含む無名ブロックの場合は、VARIABLE_VALUEプロシージャまたはVARIABLE_VALUE_PKGプロシージャをコールして、文の実行時に出力変数に割り当てられた値を取得します。

LONGデータベース列(サイズは最大2GBまで可能)の一部のみをフェッチするには、DEFINE_COLUMN_LONGプロシージャを使用します。列値へのオフセット(バイト単位)とフェッチするバイト数を指定できます。 

152.5.8 CLOSE_CURSOR

セッションでカーソルが不要な場合は、CLOSE_CURSORプロシージャをコールしてカーソルをクローズします。Oracle Open Gatewayを使用している場合は、これ以外のときにもカーソルのクローズが必要になる場合があります。追加情報は、Oracle Open Gatewayに関連するドキュメントを参照してください。

152.6 DBMS_SQLの例外

この例外は、指定したOUTパラメータ(要求した値を設定するパラメータ)のタイプがその値のタイプと異なる場合、COLUMN_VALUEプロシージャまたはVARIABLE_VALUEプロシージャで発生します。

inconsistent_type EXCEPTION;
  pragma exception_init(inconsistent_type, -6562);

152.7 DBMS_SQLの例

次のプロシージャ例では、DBMS_SQLパッケージを使用します。

例: DBMS_SQLデモの使用方法

この例では、文のテキストがコンパイル時に判明しているため、動的SQLは必要ありませんが、パッケージの基礎となる基本概念を示します。

DEMOプロシージャは、DEMOの実行時に指定した給与よりも高い給与のすべての従業員をEMP表から削除します。

CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
                   DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

例2

次のプロシージャの例では、SQL文が渡され、そのSQL文を解析して実行します。

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
    cursor_name INTEGER;
    ret INTEGER;
BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
  

DDL文はPARSEをコールして実行され、暗黙のコミットが実行されます。

   DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);
   ret := DBMS_SQL.EXECUTE(cursor_name);
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

このようなプロシージャを作成すると、次の操作を実行できます。

  • コール元のプログラムによって、実行時にSQL文を動的に生成できます。

  • SQL文は、DDL文またはバインドなしのDMLでかまいません。

たとえば、このプロシージャの作成後に、次のコールを行うことができます。

exec('create table acct(c1 integer)');

次の例のように、このプロシージャはリモートでコールすることもできます。これによって、リモートDDLを実行できます。

exec@domain.com('CREATE TABLE acct(c1 INTEGER)');

例3

次のプロシージャの例は、コピー元表とコピー先表の名前が渡され、コピー元表からコピー先表に行をコピーします。このプロシージャの例は、コピー元表とコピー先表にはいずれも次の列があることを前提としています。

id        of type NUMBER
name      of type VARCHAR2(30)
birthdate of type DATE

このプロシージャでは、動的SQLの使用は必要ありませんが、このパッケージの概念が示されています。

CREATE OR REPLACE PROCEDURE copy ( 
     source      IN VARCHAR2, 
     destination IN VARCHAR2) IS 
     id_var             NUMBER; 
     name_var           VARCHAR2(30); 
     birthdate_var      DATE; 
     source_cursor      INTEGER; 
     destination_cursor INTEGER; 
     ignore             INTEGER; 
  BEGIN 
 
  -- Prepare a cursor to select from the source table: 
     source_cursor := dbms_sql.open_cursor; 
     DBMS_SQL.PARSE(source_cursor, 
         'SELECT id, name, birthdate FROM ' || source, 
          DBMS_SQL.NATIVE); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var); 
     ignore := DBMS_SQL.EXECUTE(source_cursor); 
 
  -- Prepare a cursor to insert into the destination table: 
     destination_cursor := DBMS_SQL.OPEN_CURSOR; 
     DBMS_SQL.PARSE(destination_cursor, 
                  'INSERT INTO ' || destination || 
                  ' VALUES (:id_bind, :name_bind, :birthdate_bind)', 
                   DBMS_SQL.NATIVE); 
 
  -- Fetch a row from the source table and insert it into the destination table: 
     LOOP 
       IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN 
         -- get column values of the row 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var); 
 
  -- Bind the row into the cursor that inserts into the destination table. You 
  -- could alter this example to require the use of dynamic SQL by inserting an 
  -- if condition before the bind. 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var); 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var); 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind', 
                                                                   birthdate_var); 
        ignore := DBMS_SQL.EXECUTE(destination_cursor); 
      ELSE 
 
  -- No more rows to copy: 
        EXIT; 
      END IF; 
    END LOOP; 
 
  -- Commit and close all cursors: 
     COMMIT; 
     DBMS_SQL.CLOSE_CURSOR(source_cursor); 
     DBMS_SQL.CLOSE_CURSOR(destination_cursor); 
   EXCEPTION 
     WHEN OTHERS THEN 
       IF DBMS_SQL.IS_OPEN(source_cursor) THEN 
         DBMS_SQL.CLOSE_CURSOR(source_cursor); 
       END IF; 
       IF DBMS_SQL.IS_OPEN(destination_cursor) THEN 
         DBMS_SQL.CLOSE_CURSOR(destination_cursor); 
       END IF; 
       RAISE; 
  END; 
/ 

例4: RETURNING句

この句を使用すると、INSERTUPDATEおよびDELETEの各文でバインド変数内の式の値を戻すことができます。

単一行が挿入、更新または削除された場合は、DBMS_SQL.BIND_VARIABLEを使用してこれらのアウトバインドをバインドします。これらのバインド変数内の値を取得するには、DBMS_SQL.VARIABLE_VALUEをコールします。

注意:

この処理は、DBMS_SQL内のアウトバインドを使用してPL/SQLブロックを実行した後にコールする必要があるDBMS_SQL.VARIABLE_VALUEに類似しています。

i) 単一行の挿入

      CREATE OR REPLACE PROCEDURE single_Row_insert
           (c1 NUMBER, c2 NUMBER, r OUT NUMBER) is
      c NUMBER;
      n NUMBER;
      begin
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
     DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r); -- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /
 

ii) 単一行の更新

      CREATE OR REPLACE PROCEDURE single_Row_update
           (c1 NUMBER, c2 NUMBER, r out NUMBER) IS
      c NUMBER;
      n NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1, c2 = :bnd2 ' ||
                          'WHERE rownum < 2 ' || 
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

iii) 単一行の削除

      CREATE OR REPLACE PROCEDURE single_Row_Delete
           (c1 NUMBER, r OUT NUMBER) is
      c NUMBER;
      n number;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE ROWNUM = :bnd1 ' ||
                      'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /
 

iv) 複数行の挿入

      CREATE OR REPLACE PROCEDURE multi_Row_insert
           (c1 DBMS_SQL.NUMBER_TABLE, c2 DBMS_SQL.NUMBER_TABLE, 
            r OUT DBMS_SQL.NUMBER_TABLE) is
      c NUMBER;
      n NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'insert into tab VALUES (:bnd1, :bnd2) ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1);
        DBMS_SQL.BIND_ARRAY(c, 'bnd2', c2);
        DBMS_SQL.BIND_ARRAY(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

v) 複数行の更新。

      CREATE OR REPLACE PROCEDURE multi_Row_update
           (c1 NUMBER, c2 NUMBER, r OUT DBMS_SQL.NUMBER_TABLE) IS
      c NUMBER;
      n NUMBER;
     BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1 WHERE c2 = :bnd2 ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_ARRAY(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

注意:

bnd1とbnd2は配列に指定できます。更新されたすべての行に対する式の値は、bnd3に入れられます。bnd1とbnd2の各値に更新された行を判別する方法はありません。

vi) 複数行の削除

      CREATE OR REPLACE PROCEDURE multi_row_delete
           (c1 DBMS_SQL.NUMBER_TABLE,
            r OUT DBMS_SQL.NUMBER_TABLE) is
      c NUMBER;
      n NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE c1 = :bnd1' ||
                          'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1);
        DBMS_SQL.BIND_ARRAY(c, 'bnd2', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

vii) バルクPL/SQL内のアウトバインド

      CREATE OR REPLACE PROCEDURE foo (n NUMBER, square OUT NUMBER) IS
      BEGIN square := n * n; END;/
 
      CREATE OR REPLACE PROCEDURE bulk_plsql 
         (n DBMS_SQL.NUMBER_TABLE, square OUT DBMS_SQL.NUMBER_TABLE) IS
      c NUMBER;
      r NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'BEGIN foo(:bnd1, :bnd2); END;', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_ARRAY(c, 'bnd1', n);
        DBMS_SQL.BIND_ARRAY(c, 'bnd2', square);
        r := DBMS_SQL.EXECUTE(c);
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', square);
     END;
     /

注意:

number_TableDBMS_SQL.BIND_ARRAYは、数値を内部的にバインドします。文を実行する回数は、インバインド配列内の要素数によって決まります。

例5: DBMS_SQLでのユーザー定義タイプのバインドおよび定義

CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/
 
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var)
/
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'))
/
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'))
/
INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'))
/
 
CREATE OR REPLACE PROCEDURE update_depts(new_dnames dnames_var, region VARCHAR2) IS
   some_dnames dnames_var;
   c      NUMBER;
   r      NUMBER;
   sql_stmt VARCHAR2(32767) :=
    'UPDATE depts SET dept_names = :b1 WHERE region = :b2 RETURNING dept_names INTO :b3';
 
BEGIN
 
   c := DBMS_SQL.OPEN_CURSOR;
 
   DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
   DBMS_SQL.BIND_VARIABLE(c, 'b1', new_dnames);
   DBMS_SQL.BIND_VARIABLE(c, 'b2', region);
   DBMS_SQL.BIND_VARIABLE(c, 'b3', some_dnames);
 
   r := DBMS_SQL.EXECUTE(c);
 
   -- Get value of outbind variable
   DBMS_SQL.VARIABLE_VALUE(c, 'b3', some_dnames);
 
   DBMS_SQL.CLOSE_CURSOR(c);
 
   -- select dept_names
   sql_stmt := 'SELECT dept_names FROM depts WHERE region = :b1';
 
   c := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
   DBMS_SQL.DEFINE_COLUMN(c, 1, some_dnames);
   DBMS_SQL.BIND_VARIABLE(c, 'b1', region);
 
   r := DBMS_SQL.EXECUTE_AND_FETCH(c);
 
   DBMS_SQL.COLUMN_VALUE(c, 1, some_dnames);
 
   DBMS_SQL.CLOSE_CURSOR(c);
 
    -- loop through some_dnames collections
    FOR i IN some_dnames.FIRST .. some_dnames.LAST  LOOP
        DBMS_OUTPUT.PUT_LINE('Dept. Name = ' || some_dnames(i) || ' Updated!');
    END LOOP;
END;
/
 
DECLARE
  new_dnames dnames_var;
BEGIN
  new_dnames := dnames_var('Benefits', 'Advertising', 'Contracting', 
                           'Executive', 'Marketing');
  update_depts(new_dnames, 'Asia');
END;
/

152.8 DBMS_SQLのデータ構造

DBMS_SQLパッケージは、RECORDタイプおよびTABLEタイプのデータ構造を定義します。

152.8.1 DBMS_SQL DESC_RECレコード・タイプ

このレコード・タイプは、動的問合せ内に単一列の記述情報を保持します。

注意:

このタイプは非推奨となっているため、DESC_REC2レコード・タイプを使用することをお薦めします。

これは、DESC_TAB表タイプおよびDESCRIBE_COLUMNSプロシージャの要素タイプです。

構文

TYPE desc_rec IS RECORD (
      col_type            BINARY_INTEGER := 0,
      col_max_len         BINARY_INTEGER := 0,
      col_name            VARCHAR2(32)   := '',
      col_name_len        BINARY_INTEGER := 0,
      col_schema_name     VARCHAR2(32)   := '',
      col_schema_name_len BINARY_INTEGER := 0,
      col_precision       BINARY_INTEGER := 0,
      col_scale           BINARY_INTEGER := 0,
      col_charsetid       BINARY_INTEGER := 0,
      col_charsetform     BINARY_INTEGER := 0,
      col_null_ok         BOOLEAN        := TRUE);
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

フィールド

表152-2 DESC_RECのフィールド

フィールド 説明

col_type

列のタイプ。

col_max_len

列の最大長。

col_name

列の名前。

col_name_len

列名の長さ。

col_schema_name

列のスキーマ名。

col_schema_name_len

列のスキーマ名の長さ。

col_precision

列の精度。

col_scale

列のスケール。

col_charsetid

列のキャラクタ・セットID。

col_charsetform

列のキャラクタ・セット形式。

col_null_ok

NULL列フラグ。NULLが設定可能な場合はTRUE

152.8.2 DBMS_SQL DESC_REC2レコード・タイプ

DESC_REC2は、DESC_TAB2表タイプおよびDESCRIBE_COLUMNS2プロシージャの要素タイプです。

このレコード・タイプはDESC_RECと同じですが、col_nameフィールドがVARCHAR2の設定可能な最大サイズに拡張されている点は異なります。したがって、32文字を超えて列名の値を設定できるため、DESC_RECよりも適切です。この結果、DESC_RECは非推奨となります。

構文

TYPE desc_rec2 IS RECORD (
   col_type            binary_integer := 0,
   col_max_len         binary_integer := 0,
   col_name            varchar2(32767) := '',
   col_name_len        binary_integer := 0,
   col_schema_name     varchar2(32)   := '',
   col_schema_name_len binary_integer := 0,
   col_precision       binary_integer := 0,
   col_scale           binary_integer := 0,
   col_charsetid       binary_integer := 0,
   col_charsetform     binary_integer := 0,
   col_null_ok         boolean        := TRUE);

フィールド

表152-3 DESC_REC2フィールド

フィールド 説明

col_type

列のタイプ。

col_max_len

列の最大長。

col_name

列の名前。

col_name_len

列名の長さ。

col_schema_name

列のスキーマ名。

col_schema_name_len

列のスキーマ名の長さ。

col_precision

列の精度。

col_scale

列のスケール。

col_charsetid

列のキャラクタ・セットID。

col_charsetform

列のキャラクタ・セット形式。

col_null_ok

NULL列フラグ。NULLが設定可能な場合はTRUE

152.8.3 DBMS_SQL DESC_REC3レコード・タイプ

DESC_REC3は、DESC_TAB3表タイプおよびDESCRIBE_COLUMNS3プロシージャの要素タイプです。

DESC_REC3DESC_REC2と同じですが、動的問合せ内に列のタイプ名(type_name)およびタイプ名の長さ(type_name_len)を保持するための2つのフィールドが追加されている点は異なります。列がユーザー定義タイプ(コレクション・タイプまたはオブジェクト・タイプ)の場合は、これらの2つのフィールドにタイプ名およびタイプ名の長さが保持されます。col_typeフィールドの値が109 (ユーザー定義タイプに対するOracleタイプ番号)の場合にのみ、col_type_nameおよびcol_type_name_lenフィールドにデータが移入されます。

構文

TYPE desc_rec3 IS RECORD (
   col_type               binary_integer := 0,
   col_max_len            binary_integer := 0,
   col_name               varchar2(32767) := '',
   col_name_len           binary_integer := 0,
   col_schema_name        varchar2(32) := '',
   col_schema_name_len    binary_integer := 0,
   col_precision          binary_integer := 0,
   col_scale              binary_integer := 0,
   col_charsetid          binary_integer := 0,
   col_charsetform        binary_integer := 0,
   col_null_ok            boolean := TRUE,
   col_type_name          varchar2(32767)   := '',
   col_type_name_len      binary_integer := 0);

フィールド

表152-4 DESC_REC3のフィールド

フィールド 説明

col_type

列のタイプ。

col_max_len

列の最大長。

col_name

列の名前。

col_name_len

列名の長さ。

col_schema_name

列のスキーマ名。

col_schema_name_len

列のスキーマ名の長さ。

col_precision

列の精度。

col_scale

列のスケール。

col_charsetid

列のキャラクタ・セットID。

col_charsetform

列のキャラクタ・セット形式。

col_null_ok

NULL列フラグ。NULLが設定可能な場合はTRUE

col_type_name

ユーザー定義タイプの列タイプ名。このフィールドは、col_typeが109の場合に有効です。

col_type_name_len

ユーザー定義タイプの列タイプ名の長さ。このフィールドは、col_typeが109の場合に有効です。

152.8.4 DBMS_SQL DESC_REC4レコード・タイプ

DESC_REC4は、DESC_TAB4表タイプおよびDESCRIBE_COLUMNS3プロシージャの要素タイプです。

DESC_REC4DESC_REC3と同じですが、動的問合せ内の列のスキーマ名(col_schema_name)およびタイプ名(col_type_name)を保持するフィールドで、より長い識別子がサポートされる点は異なります。

構文

TYPE desc_rec4 IS RECORD (
   col_type               binary_integer := 0,
   col_max_len            binary_integer := 0,
   col_name               varchar2(32767) := '',
   col_name_len           binary_integer := 0,
   col_schema_name        DBMS_ID := '',
   col_schema_name_len    binary_integer := 0,
   col_precision          binary_integer := 0,
   col_scale              binary_integer := 0,
   col_charsetid          binary_integer := 0,
   col_charsetform        binary_integer := 0,
   col_null_ok            boolean := TRUE,
   col_type_name          DBMS_ID   := '',
   col_type_name_len      binary_integer := 0);

参照:

事前定義済サブタイプDBMS_IDの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

フィールド

表152-5 DESC_REC4のフィールド

フィールド 説明

col_type

列のタイプ。

col_max_len

列の最大長。

col_name

列の名前。

col_name_len

列名の長さ。

col_schema_name

列のスキーマ名。

col_schema_name_len

列のスキーマ名の長さ。

col_precision

列の精度。

col_scale

列のスケール。

col_charsetid

列のキャラクタ・セットID。

col_charsetform

列のキャラクタ・セット形式。

col_null_ok

NULL列フラグ。NULLが設定可能な場合はTRUE

col_type_name

ユーザー定義タイプの列タイプ名。このフィールドは、col_typeが109の場合に有効です。

col_type_name_len

ユーザー定義タイプの列タイプ名の長さ。このフィールドは、col_typeが109の場合に有効です。

152.8.5 DBMS_SQL BFILE_TABLE表タイプ

これは、BFILEの表です。

構文

TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;

152.8.6 DBMS_SQL BINARY_DOUBLE_TABLE表タイプ

これは、BINARY_DOUBLEの表です。

構文

TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;

152.8.7 DBMS_SQL BINARY_FLOAT_TABLE表タイプ

これは、BINARY_FLOATの表です。

構文

TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;

152.8.8 DBMS_SQL BLOB_TABLE表タイプ

これは、BLOBの表です。

構文

TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;

152.8.9 DBMS_SQL CLOB_TABLE表タイプ

これは、CLOBの表です。

構文

TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;

152.8.10 DBMS_SQL DATE_TABLE表タイプ

これは、DATEの表です。

構文

type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

152.8.11 DBMS_SQL DESC_TAB表タイプ

これは、DESC_RECレコード・タイプの表です。

構文

TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

152.8.12 DBMS_SQL DESC_TAB2表タイプ

これは、DESC_REC2レコード・タイプの表です。

構文

TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;

152.8.13 DBMS_SQL DESC_TAB3表タイプ

これは、DESC_REC3レコード・タイプの表です。

構文

TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER; 

152.8.14 DBMS_SQL DESC_TAB4表タイプ

これは、DBMS_SQL DESC_REC4レコード・タイプの表です。

構文

TYPE DESC_TAB4 IS TABLE OF DESC_REC4 INDEX BY BINARY_INTEGER; 

152.8.15 DBMS_SQL INTERVAL_DAY_TO_SECOND_TABLE表タイプ

これは、DSINTERVAL_UNCONSTRAINEDの表です。

構文

 TYPE interval_day_to_second_Table IS TABLE OF
    DSINTERVAL_UNCONSTRAINED INDEX BY binary_integer;

152.8.16 DBMS_SQL INTERVAL_YEAR_TO_MONTH_TABLE表タイプ

これは、YMINTERVAL_UNCONSTRAINEDの表です。

構文

TYPE interval_year_to_month_table IS TABLE OF YMINTERVAL_UNCONSTRAINED 
   INDEX BY BINARY_INTEGER;

152.8.17 DBMS_SQL NUMBER_TABLE表タイプ

これは、NUMBERの表です。

構文

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

152.8.18 DBMS_SQL TIME_TABLE表タイプ

これは、TIME_UNCONSTRAINEDの表です。

構文

TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;

152.8.19 DBMS_SQL TIME_WITH_TIME_ZONE_TABLE表タイプ

これは、TIME_TZ_UNCONSTRAINEDの表です。

構文

TYPE time_with_time_zone_table IS TABLE OF TIME_TZ_UNCONSTRAINED 
   INDEX BY BINARY_INTEGER;;

152.8.20 DBMS_SQL TIMESTAMP_TABLE表タイプ

これは、TIMESTAMP_UNCONSTRAINEDの表です。

構文

TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;

152.8.21 DBMS_SQL TIMESTAMP_WITH_LTZ_TABLE表タイプ

これは、TIMESTAMP_LTZ_UNCONSTRAINEDの表です。

構文

TYPE timestamp_with_ltz_table IS TABLE OF
    TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer;

152.8.22 DBMS_SQL TIMESTAMP_WITH_TIME_ZONE_TABLE表タイプ

これは、TIMESTAMP_TZ_UNCONSTRAINEDの表です。

構文

TYPE timestamp_with_time_zone_Table IS TABLE OF 
    TIMESTAMP_TZ_UNCONSTRAINED INDEX BY binary_integer;

152.8.23 DBMS_SQL UROWID_TABLE表タイプ

これは、UROWIDの表です。

構文

TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;

152.8.24 DBMS_SQL VARCHAR2_TABLE表タイプ

これは、VARCHAR2(2000)の表です。

構文

TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

152.8.25 DBMS_SQL VARCHAR2A表タイプ

これは、VARCHAR2(32767)の表です。

構文

TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

152.8.26 DBMS_SQL VARCHAR2S表タイプ

これは、VARCHAR2(256)の表です。

注意:

このタイプは、VARCHAR2A表タイプによって置き換えられています。このタイプは、レガシー・コードの下位互換性を維持する目的で現在保持されていますが、非推奨となる予定であり、将来のリリースではサポートされなくなります。

構文

TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

152.9 DBMS_SQLサブプログラムの要約

この表は、DBMS_SQLサブプログラムを示し、簡単に説明しています。

表152-6 DBMS_SQLパッケージのサブプログラム

サブプログラム 説明

BIND_ARRAYプロシージャ

指定の値を指定のコレクションにバインドします。

BIND_VARIABLEプロシージャ

指定の値を指定の変数にバインドします。

BIND_VARIABLE_PKGプロシージャ

指定の値を指定のパッケージ変数にバインドします。

CLOSE_CURSORプロシージャ

指定したカーソルをクローズして、メモリーを解放します。

COLUMN_VALUEプロシージャ

カーソル内の指定位置にあるカーソル要素の値を戻します。

COLUMN_VALUE_LONGプロシージャ

DEFINE_COLUMN_LONGで定義したLONG列の選択された部分を戻します。

DEFINE_ARRAYプロシージャ

指定したカーソルから選択するコレクションを定義します。SELECT文のみで使用されます。

DEFINE_COLUMNプロシージャ

指定したカーソルから選択する列を定義します。SELECT文のみで使用されます。

DEFINE_COLUMN_CHARプロシージャ

指定したカーソルから選択するタイプCHARの列を定義します。SELECT文のみで使用されます。

DEFINE_COLUMN_LONGプロシージャ

指定したカーソルから選択するLONG列を定義します。SELECT文のみで使用されます。

DEFINE_COLUMN_RAWプロシージャ

指定したカーソルから選択するタイプRAWの列を定義します。SELECT文のみで使用されます。

DEFINE_COLUMN_ROWIDプロシージャ

指定したカーソルから選択するタイプROWIDの列を定義します。SELECT文のみで使用されます。

DESCRIBE_COLUMNSプロシージャ

DBMS_SQLによってオープンされ解析されたカーソルの列の情報を表示します。

DESCRIBE_COLUMNS2プロシージャ

指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。

DESCRIBE_COLUMNS3プロシージャ

指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。

EXECUTEファンクション

指定のカーソルを実行します。

EXECUTE_AND_FETCHファンクション

指定のカーソルを実行して、行をフェッチします。

FETCH_ROWSファンクション

指定のカーソルから行をフェッチします。

GET_NEXT_RESULTプロシージャ

再帰文のコール元に戻される次の結果の文を取得するか、このコール元が自らを再帰文のクライアントとして設定している場合、クライアントとしてこのコール元に戻される次の結果を取得します。

IS_OPENファンクション

指定のカーソルがオープンの場合にTRUEを戻します。

LAST_ERROR_POSITIONファンクション

エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。

LAST_ROW_COUNTファンクション

フェッチされた累積行数を戻します。

LAST_ROW_IDファンクション

最後に処理された行のROWIDを戻します。

LAST_SQL_FUNCTION_CODEファンクション

文のSQL機能コードを戻します。

OPEN_CURSORファンクション

新規カーソルのID番号を戻します。

PARSEプロシージャ

指定した文を解析します。

RETURN_RESULTプロシージャ

実行した文の結果をクライアント・アプリケーションに戻します。

TO_CURSOR_NUMBERファンクション

強くタイプ付けされたREFカーソルまたは弱くタイプ付けされたREFカーソルのうちOPENが実行されたカーソルを使用して、そのカーソルをDBMS_SQLカーソル番号に変換します。

TO_REFCURSORファンクション

OPENPARSEおよびEXECUTEが実行されたカーソルを使用して、そのカーソルをPL/SQLで管理可能なREF CURSOR(弱くタイプ付けされたカーソル)に変換/移行します。変換/移行後のカーソルは、システム固有の動的SQLを使用するように切り替えられているPL/SQLシステム固有の動的SQLで消費できます。

VARIABLE_VALUEプロシージャ

指定のカーソルについて指定の変数の値を戻します。

VARIABLE_VALUE_PKGプロシージャ

指定のカーソルについて指定の変数の値を戻します。これは、宣言されたパッケージに対して、returning句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。変数の型は、パッケージ仕様部で宣言する必要があります。

152.9.1 BIND_ARRAYプロシージャ

このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。

構文

DBMS_SQL.BIND_ARRAY ( 
   c                   IN INTEGER, 
   name                IN VARCHAR2, 
   <table_variable>    IN <datatype> 
 [,index1              IN INTEGER, 
   index2              IN INTEGER)] ); 

<table_variable>とそれに対応する<datatype>は、次のいずれかの組合せになります。

<clob_tab>     Clob_Table
<bflt_tab>     Binary_Float_Table
<bdbl_tab>     Binary_Double_Table
<blob_tab>     Blob_Table
<bfile_tab>    Bfile_Table
<date_tab>     Date_Table
<num_tab>      Number_Table
<urowid_tab>   Urowid_Table
<vchr2_tab>    Varchar2_Table
<tm_tab>       Time_Table
<ttz_tab>      Time_With_Time_Zone_Table
<tms_tab>      Timestamp_Table
<tstz_tab>     Timestamp_With_ltz_Table
<tstz_tab>     Timestamp_With_Time_Zone_Table
<ids_tab>      Interval_Day_To_Second_Table
<iym_tab>      Interval_Year_To_Month_Table

BIND_ARRAYプロシージャは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。

パラメータ

表152-7 BIND_ARRAYプロシージャのパラメータ

パラメータ 説明

c

値をバインドするカーソルのID番号。

name

文内のコレクションの名前。

table_variable

<datatype>として宣言されたローカル変数。

index1

範囲の下限を示す表要素の索引。

index2

範囲の上限を示す表要素の索引。

使用上の注意

範囲をバインドするためには、範囲を指定する要素(タブ(index1)とタブ(index2))が表に含まれている必要がありますが、その範囲は詳細でなくてもかまいません。index1には、index2以下の値を指定してください。タブ(index1)とタブ(index2)の間にあるすべての要素がバインドして使用されます。

バインド・コールで索引を指定しない場合で、かつ文内の2つの異なるバインドが異なる数の要素を含んだ表を指定している場合、実際に使用される要素の数は、すべての表の最小値となります。これは索引を指定する場合にも当てはまり、すなわちすべての表に関する2つの索引の間では最小範囲が選択されます。

問合せ内のすべてのバインド変数が、配列バインドである必要はありません。一部は通常のバインドの場合があり、式の評価などでは、同じ値がコレクションの各要素に使用されます。

バルク配列バインド

バルクSELECT、INSERT、UPDATEおよびDELETEは、多くのコールを1つにまとめることによって、アプリケーションのパフォーマンスが向上します。DBMS_SQLパッケージによって、ユーザーはPL/SQL表タイプを使用しながらデータの収集に対する処理を実行できます。

表項目は、バインドされていない同種のコレクションです。表項目は、持続記憶域では他のリレーショナル表に似ており、組込みの配列を持ちません。ただし、表項目が、(問合せまたは持続データのナビゲーション・アクセスのいずれかによって)作業領域に移されたり、あるいはPL/SQLの変数またはパラメータの値として作成されると、要素の値を取得して設定するために配列形式の構文で使用できる添字が、その表項目の要素に与えられます。

これらの要素の添字は詳細である必要はなく、負数を含むあらゆる数値が使用できます。たとえば、表項目には、-10、2および7の位置のみにある要素を含めることができます。

表項目が一時作業領域から持続記憶域に移されると、添字は格納されません。つまり、表項目は、持続記憶域内では順序が付いていません。

表は、バインド実行時に、PL/SQLバッファからローカルのDBMS_SQLバッファにコピーされ(すべてのスカラー・タイプについて同様)、ローカルのDBMS_SQLバッファから操作されます。したがって、バインド・コール後に表を変更した場合でも、その変更が実行方法に影響を与えることはありません。

スカラー・タイプとLOBタイプ・コレクション

ローカル変数を次のいずれかの表項目タイプとして宣言できます。これらの表項目タイプは、DBMS_SQLではパブリック・タイプとして定義されています。

TYPE binary_double_table 
                    IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;
TYPE binary_float_table 
                    IS TABLE OF BINARY_FLOAT   INDEX BY BINARY_INTEGER;
TYPE bfile_table    IS TABLE OF BFILE          INDEX BY BINARY_INTEGER;
TYPE blob_table     IS TABLE OF BLOB           INDEX BY BINARY_INTEGER;
TYPE clob_table     IS TABLE OF CLOB           INDEX BY BINARY_INTEGER;
TYPE date_table     IS TABLE OF DATE           INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_Table 
                    IS TABLE OF dsinterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE interval_year_to_MONTH_Table 
                    IS TABLE OF yminterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE number_table   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;
TYPE time_table     IS TABLE OF time_unconstrained           
                                               INDEX BY BINARY_INTEGER;
TYPE time_with_time_zone_table 
                    IS TABLE OF time_tz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_table 
                    IS TABLE OF timestamp_unconstrained   
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_with_ltz_Table 
                    IS TABLE OF timestamp_ltz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_with_time_zone_Table 
                    IS TABLE OF timestamp_tz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE urowid_table   IS TABLE OF UROWID         INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

例152-1 バルクDMLの使用例

次の一連の例では、INSERTUPDATEおよびDELETEの各SQL DML文でのバルク配列バインド(表項目)の使用方法を示します。

次に、emp表に新しい従業員7人を追加するバルクINSERT文の例を示します。

DECLARE
  stmt VARCHAR2(200);
  empno_array      DBMS_SQL.NUMBER_TABLE;
  empname_array    DBMS_SQL.VARCHAR2_TABLE;
  jobs_array       DBMS_SQL.VARCHAR2_TABLE;
  mgr_array        DBMS_SQL.NUMBER_TABLE;
  hiredate_array   DBMS_SQL.VARCHAR2_TABLE;
  sal_array        DBMS_SQL.NUMBER_TABLE;
  comm_array       DBMS_SQL.NUMBER_TABLE;
  deptno_array     DBMS_SQL.NUMBER_TABLE;
  c                NUMBER;
  dummy            NUMBER;
BEGIN
  empno_array(1):= 9001;
  empno_array(2):= 9002;
  empno_array(3):= 9003;
  empno_array(4):= 9004;
  empno_array(5):= 9005;
  empno_array(6):= 9006;
  empno_array(7):= 9007;

  empname_array(1) := 'Dopey';
  empname_array(2) := 'Grumpy';
  empname_array(3) := 'Doc';
  empname_array(4) := 'Happy';
  empname_array(5) := 'Bashful';
  empname_array(6) := 'Sneezy';
  empname_array(7) := 'Sleepy';

  jobs_array(1) := 'Miner';
  jobs_array(2) := 'Miner';
  jobs_array(3) := 'Miner';
  jobs_array(4) := 'Miner';
  jobs_array(5) := 'Miner';
  jobs_array(6) := 'Miner';
  jobs_array(7) := 'Miner';

  mgr_array(1) := 9003;
  mgr_array(2) := 9003;
  mgr_array(3) := 9003;
  mgr_array(4) := 9003;
  mgr_array(5) := 9003;
  mgr_array(6) := 9003;
  mgr_array(7) := 9003;

  hiredate_array(1) := '06-DEC-2006';
  hiredate_array(2) := '06-DEC-2006';
  hiredate_array(3) := '06-DEC-2006';
  hiredate_array(4) := '06-DEC-2006';
  hiredate_array(5) := '06-DEC-2006';
  hiredate_array(6) := '06-DEC-2006';
  hiredate_array(7) := '06-DEC-2006';

  sal_array(1):= 1000;
  sal_array(2):= 1000;
  sal_array(3):= 1000;
  sal_array(4):= 1000;
  sal_array(5):= 1000;
  sal_array(6):= 1000;
  sal_array(7):= 1000;

  comm_array(1):= 0;
  comm_array(2):= 0;
  comm_array(3):= 0;
  comm_array(4):= 0;
  comm_array(5):= 0;
  comm_array(6):= 0;
  comm_array(7):= 0;

  deptno_array(1):= 11;
  deptno_array(2):= 11;
  deptno_array(3):= 11;
  deptno_array(4):= 11;
  deptno_array(5):= 11;
  deptno_array(6):= 11;
  deptno_array(7):= 11;

  stmt := 'INSERT INTO emp VALUES(
     :num_array, :name_array, :jobs_array, :mgr_array, :hiredate_array, 
     :sal_array, :comm_array, :deptno_array)';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':num_array', empno_array);
  DBMS_SQL.BIND_ARRAY(c, ':name_array', empname_array);
  DBMS_SQL.BIND_ARRAY(c, ':jobs_array', jobs_array);
  DBMS_SQL.BIND_ARRAY(c, ':mgr_array', mgr_array);
  DBMS_SQL.BIND_ARRAY(c, ':hiredate_array', hiredate_array);
  DBMS_SQL.BIND_ARRAY(c, ':sal_array', sal_array);
  DBMS_SQL.BIND_ARRAY(c, ':comm_array', comm_array);
  DBMS_SQL.BIND_ARRAY(c, ':deptno_array', deptno_array);

  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/
SHOW ERRORS;

次に、emp表内の4人の既存従業員の給与を更新するバルクUPDATE文の例を示します。

DECLARE
  stmt VARCHAR2(200);
  empno_array     DBMS_SQL.NUMBER_TABLE;
  salary_array    DBMS_SQL.NUMBER_TABLE;
  c               NUMBER;
  dummy           NUMBER;
BEGIN
 
  empno_array(1):= 7369;
  empno_array(2):= 7876;
  empno_array(3):= 7900;
  empno_array(4):= 7934;
 
  salary_array(1) := 10000;
  salary_array(2) := 10000;
  salary_array(3) := 10000;
  salary_array(4) := 10000;
 
  stmt := 'update emp set sal = :salary_array
    WHERE empno = :num_array';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':num_array', empno_array);
  DBMS_SQL.BIND_ARRAY(c, ':salary_array', salary_array);
  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
 
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

たとえば、DELETE文では、WHERE句に配列をバインドして、配列内の要素ごとに文を実行させることもできます。

DECLARE
  stmt VARCHAR2(200);
  dept_no_array DBMS_SQL.NUMBER_TABLE;
  c NUMBER;
  dummy NUMBER;
begin
  dept_no_array(1) := 10; dept_no_array(2) := 20;
  dept_no_array(3) := 30; dept_no_array(4) := 40;
  dept_no_array(5) := 30; dept_no_array(6) := 40;
  stmt := 'delete from emp where deptno = :dept_array';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':dept_array', dept_no_array, 1, 4);
  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c); 

  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/ 

前述の例では、1から4までの要素のみが、BIND_ARRAYコールで指定したとおりに使用されます。配列の各要素は、大量の従業員をデータベースから削除する可能性があります。

152.9.2 BIND_VARIABLEプロシージャ

これらのプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。

構文

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN <datatype>);

<datatype>は、次のいずれかのデータ・タイプである必要があります。

ADT (user-defined object types)
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NESTED table
NUMBER
OPAQUE types
REF
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
VARRAY
YMINTERVAL_UNCONSTRAINED

BIND_VARIABLEは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。

BIND_VARIABLEでは、次の構文もサポートされています。大カッコ[]は、BIND_VARIABLEプロシージャのオプション・パラメータを示します。

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN VARCHAR2 CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);

CHARRAWおよびROWIDデータをバインドするために、次のバリエーションを構文で使用できます。

DBMS_SQL.BIND_VARIABLE_CHAR (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN CHAR CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);

DBMS_SQL.BIND_VARIABLE_RAW (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN RAW [,out_value_size IN INTEGER]);

DBMS_SQL.BIND_VARIABLE_ROWID (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN ROWID);

プラグマ

pragma restrict_references(bind_variable,WNDS);

パラメータ

表152-8 BIND_VARIABLEプロシージャのパラメータ

パラメータ 説明

c

値をバインドするカーソルのID番号。

name

文内の変数の名前。

バインド変数名の長さは<=30バイトにする必要があります。

value

カーソル内の変数にバインドする値。

IN変数とIN/OUT変数の場合、この値は、このパラメータで渡される値のタイプと同じタイプです。

out_value_size

VARCHAR2RAWCHAR OUTまたはIN/OUT変数の最大予測OUT値サイズ(バイト単位)。

サイズの指定がない場合は、現行値の長さが使用されます。このパラメータは、valueパラメータが初期化されていない場合、指定する必要があります。

使用上の注意

この変数がIN変数、IN/OUT変数またはINコレクションである場合は、指定したバインド値が、変数タイプまたは配列タイプに対して有効である必要があります。OUT変数のバインド値は無視されます。

SQL文のバインド変数またはコレクションは、名前によって識別されます。バインド変数またはバインド配列に値をバインドする場合は、次の例に示すように、文中でバインド変数を識別する文字列の先頭にコロンを付ける必要があります。

SELECT emp_name FROM emp WHERE SAL > :X;

この例では、対応するバインド・コールは次のようになります。

BIND_VARIABLE(cursor_name, ':X', 3500); 

or

BIND_VARIABLE (cursor_name, 'X', 3500);

152.9.3 BIND_VARIABLE_PKGプロシージャ

このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。変数の型は、パッケージ仕様部で宣言する必要があります。これらのタイプでは、バルク操作はサポートされていません。

構文

DBMS_SQL.BIND_VARIABLE_PKG (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN <datatype>);

<datatype>は、次のいずれかのデータ・タイプにできます。

  • RECORD

  • VARRAY

  • NESTED TABLE

  • INDEX BY PLS_INTEGER TABLE

  • INDEX BY BINARY_INTEGER TABLE

表152-9 BIND_VARIABLE_PKGのパラメータ

パラメータ 説明

c

値を取得するカーソルのID番号。

name

値を取得する文の変数の名前。

value

  • 単一行オプション: 指定した位置の変数の値を戻します。この出力パラメータのタイプが、BIND_VARIABLE_PKGへのコールで定義されている実際の値のタイプと異なる場合は、例外ORA-06562inconsistent_typeが発生します。

  • 配列オプション: <table_type>として宣言されたローカル変数

例152-2 パッケージ変数をバインドするためのDBMS_SQL.BIND_VARIABLE_PKGを使用した動的SQL

変数タイプは、パッケージ仕様部に宣言されます。BIND_VARIABLE_PKGは、カーソルSQL文の変数v1をバインドするために使用されます。

CREATE OR REPLACE PACKAGE ty_pkg AS 
   TYPE rec IS RECORD ( n1 NUMBER, n2 NUMBER); 
   TYPE trec IS TABLE OF REC INDEX BY BINARY_INTEGER; 
   TYPE trect IS TABLE OF NUMBER;
   TYPE trecv IS VARRAY(100) OF NUMBER;
END ty_pkg; 
/
CREATE OR REPLACE PROCEDURE dyn_sql_ibbi AS 
  dummy NUMBER; 
  cur   NUMBER; 
  v1 ty_pkg.trec; 
  str VARCHAR2(3000); 
  n1 NUMBER;
  n2 NUMBER;
BEGIN 
  FOR i in 1..3 LOOP
     v1(i).n1 := i*10; 
     v1(i).n2 := i*20;
  END LOOP; 
  str := 'SELECT * FROM TABLE(:v1)' ; 
  cur := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(cur, str, DBMS_SQL.NATIVE); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v1', v1); 
  dummy := DBMS_SQL.EXECUTE(cur); 
  DBMS_SQL.DEFINE_COLUMN(cur, 1, n1);   
  DBMS_SQL.DEFINE_COLUMN(cur, 2, n2);   

  LOOP 
    IF DBMS_SQL.FETCH_ROWS(cur) > 0 THEN 
      -- get column values of the row 
       DBMS_SQL.COLUMN_VALUE(cur, 1, n1); 
       DBMS_SQL.COLUMN_VALUE(cur, 2, n2); 
       DBMS_OUTPUT.PUT_LINE('n1 = '||n1||' n2 = '||n2); 
    ELSE 
      -- No more rows 
       EXIT; 
    END IF; 
  END LOOP; 
  DBMS_SQL.CLOSE_CURSOR(cur); 
END dyn_sql_ibbi;
/
EXEC dyn_sql_ibbi;

n1 = 10 n2 = 20
n1 = 20 n2 = 40
n1 = 30 n2 = 60

152.9.4 CLOSE_CURSORプロシージャ

このプロシージャは、指定のカーソルをクローズします。

構文

DBMS_SQL.CLOSE_CURSOR (
   c    IN OUT INTEGER);

プラグマ

pragma restrict_references(close_cursor,RNDS,WNDS);

パラメータ

表152-10 CLOSE_CURSORプロシージャのパラメータ

パラメータ モード 説明

c

IN

クローズするカーソルのID番号。

c

OUT

カーソルはNULLに設定されています。

CLOSE_CURSORをコールした後、カーソルに割り当てられたメモリーは解放され、そのカーソルからはフェッチできなくなります。

152.9.5 COLUMN_VALUEプロシージャ

このプロシージャは、指定したカーソル内の指定の位置にあるカーソル要素の値を戻します。このプロシージャは、FETCH_ROWSをコールしてフェッチしたデータへのアクセスに使用されます。

構文

DBMS_SQL.COLUMN_VALUE (
   c                 IN  INTEGER,
   position          IN  INTEGER,
   value             OUT <datatype> 
 [,column_error      OUT NUMBER] 
 [,actual_length     OUT INTEGER]);

大カッコ[ ]はオプション・パラメータを示します。<datatype>は次のいずれかのデータ・タイプである必要があります。

BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NUMBER
TIME_TZ_UNCONSTRAINED
TIME_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types

CHARRAWおよびROWIDデータを含んだ変数では、次のバリエーションを構文で使用できます。

DBMS_SQL.COLUMN_VALUE_CHAR (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT CHAR CHARACTER SET ANY_CS
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);
 
DBMS_SQL.COLUMN_VALUE_RAW (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT RAW
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);

DBMS_SQL.COLUMN_VALUE_ROWID (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT ROWID
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);

次の構文を使用すると、COLUMN_VALUEプロシージャでバルク操作に対応できます。

DBMS_SQL.COLUMN_VALUE( 
   c                 IN             INTEGER, 
   position          IN             INTEGER, 
   <param_name>      IN OUT NOCOPY  <table_type>);  

<param_name>とそれに対応する<table_type>は、次のいずれかの組合せになります。

bdbl_tab     Binary_Double_Table
bflt_tab     Binary_Float_Table
bf_tab       Bfile_Table
bl_tab       Blob_Table
cl_tab       Clob_Table
d_tab        Date_Table
ids_tab      Interval_Day_To_Second_Table
iym_tab      Interval_Year_To_Month_Table
n_tab        Number_Table
tm_tab       Time_Table
ttz_tab      Time_With_Time_Zone_Table
tms_tab      Timestamp_Table
tstz_tab     Timestamp_With_ltz_Table
tstz_tab     Timestamp_With_Time_Zone_Table
ur_tab       Urowid_Table
c_tab        Varchar2_Table

プラグマ

pragma restrict_references(column_value,RNDS,WNDS);

パラメータ

表152-11 COLUMN_VALUEプロシージャのパラメータ(単一行)

パラメータ 説明

c

値をフェッチするカーソルのID番号。

position

カーソル内の列の相対位置。

文の最初の列は位置1です。

value

指定した列の値を戻します。

この出力パラメータのタイプが、DEFINE_COLUMNへのコールで定義されている値の実際のタイプと異なる場合、例外エラーORA-06562inconsistent_typeが発生します。

column_error

指定した列値のエラー・コードを戻します。

actual_length

指定した列内の値の(切捨て前の)実際の長さ。

表152-12 COLUMN_VALUEプロシージャのパラメータ(バルク)

パラメータ 説明

c

値をフェッチするカーソルのID番号。

position

カーソル内の列の相対位置。

文の最初の列は位置1です。

<param_name>

<table_type>として宣言されたローカル変数。<param_name>は、バルク操作でのIN OUT NOCOPYパラメータです。

バルク操作では、サブプログラムによって、(暗黙的に保持される)適切な索引に新しい要素が追加されます。たとえば、DEFINE_ARRAYプロシージャの使用時にバッチ・サイズ(cntパラメータ)として10行、開始索引(lower_bound)として1を指定した場合、FETCH_ROWSファンクションをコールした後に、このサブプログラムを最初にコールすると索引1から10、その次にコールすると索引11から20に要素が移入され、以降同様の処理が行われます。

例外

指定したOUTパラメータのvalueが、実際の値のタイプと異なる場合は、INCONSISTENT_TYPE(ORA-06562)が発生します。このタイプは、DEFINE_COLUMNプロシージャをコールして列を定義したときに指定したタイプです。

152.9.6 COLUMN_VALUE_LONGプロシージャ

このプロシージャは、LONG列の値の一部を取得します。

構文

DBMS_SQL.COLUMN_VALUE_LONG (
   c            IN  INTEGER, 
   position     IN  INTEGER, 
   length       IN  INTEGER, 
   offset       IN  INTEGER,
   value        OUT VARCHAR2,
   value_length OUT INTEGER);

プラグマ

pragma restrict_references(column_value_long,RNDS,WNDS);

パラメータ

表152-13 COLUMN_VALUE_LONGプロシージャのパラメータ

パラメータ 説明

c

値を取得するカーソルのカーソルID番号。

position

値を取得する列の位置。

length

フェッチするLONG値のバイト数。

offset

フェッチを開始するためのLONGフィールドへのオフセット。

value

VARCHAR2の列の値。

value_length

値に実際に戻されるバイト数。

152.9.7 DEFINE_ARRAYプロシージャ

このプロシージャは、(FETCH_ROWSコールで)行をフェッチする列に対してコレクションを定義します。このプロシージャによって、ユーザーは単一のSELECT文から、行を一括してフェッチできます。1回のフェッチ・コールで、PL/SQLの集計オブジェクトに多数の行をフェッチできます。

行をフェッチすると、それらの行はCOLUMN_VALUEコールを実行するまでDBMS_SQLバッファにコピーされ、COLUMN_VALUEコールの実行時点で、このコールに引数として渡された表にコピーされます。

スカラー・タイプとLOBタイプ・コレクション

ローカル変数を、次のいずれかの表項目タイプとして宣言し、DBMS_SQLを使用して、任意の行数をその中にフェッチできます。(これらは、BIND_ARRAYプロシージャに指定できるタイプと同じです。)

TYPE binary_double_table 
                    IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;
TYPE binary_float_table 
                    IS TABLE OF BINARY_FLOAT   INDEX BY BINARY_INTEGER;
TYPE bfile_table    IS TABLE OF BFILE          INDEX BY BINARY_INTEGER;
TYPE blob_table     IS TABLE OF BLOB           INDEX BY BINARY_INTEGER;
TYPE clob_table     IS TABLE OF CLOB           INDEX BY BINARY_INTEGER;
TYPE date_table     IS TABLE OF DATE           INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_Table 
                    IS TABLE OF dsinterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE interval_year_to_MONTH_Table 
                    IS TABLE OF yminterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE number_table   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;
TYPE time_table     IS TABLE OF time_unconstrained           
                                               INDEX BY BINARY_INTEGER;
TYPE time_with_time_zone_table 
                    IS TABLE OF time_tz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_table 
                    IS TABLE OF timestamp_unconstrained   
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_with_ltz_Table 
                    IS TABLE OF timestamp_ltz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_with_time_zone_Table 
                    IS TABLE OF timestamp_tz_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE urowid_table   IS TABLE OF UROWID         INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

構文

DBMS_SQL.DEFINE_ARRAY (
   c           IN INTEGER, 
   position    IN INTEGER,
   <table_variable>    IN <datatype> 
   cnt         IN INTEGER, 
   lower_bnd   IN INTEGER);

<table_variable>とそれに対応する<datatype>は、次のいずれかの組合せになります。DEFINE_ARRAYは、異なるデータ・タイプを受け入れるためにオーバーロードされています。

<clob_tab>     Clob_Table
<bflt_tab>     Binary_Float_Table
<bdbl_tab>     Binary_Double_Table
<blob_tab>     Blob_Table
<bfile_tab>    Bfile_Table
<date_tab>     Date_Table
<num_tab>      Number_Table
<urowid_tab>   Urowid_Table
<vchr2_tab>    Varchar2_Table
<tm_tab>       Time_Table
<ttz_tab>      Time_With_Time_Zone_Table
<tms_tab>      Timestamp_Table
<tstz_tab>     Timestamp_With_ltz_Table
<tstz_tab>     Timestamp_With_Time_Zone_Table
<ids_tab>      Interval_Day_To_Second_Table
<iym_tab>      Interval_Year_To_Month_Table

プラグマ

pragma restrict_references(define_array,RNDS,WNDS);

後続のFETCH_ROWSコールがcnt行をフェッチします。COLUMN_VALUEがコールされると、これらの行はlower_bndlower_bnd+1、lower_bnd+2のように配置されます。行が送られてくる間、ユーザーはFETCH_ROWSコールまたはCOLUMN_VALUEコールを継続して発行します。行は、COLUMN_VALUEコールに引数として指定した表内に蓄積されます。

パラメータ

表152-14 DEFINE_ARRAYプロシージャのパラメータ

パラメータ 説明

c

配列をバインドするカーソルのID番号。 

position

定義している配列内にある列の相対位置。

文の最初の列は位置1です。

table_variable

<datatype>として宣言されたローカル変数。

cnt

フェッチする行数。

lower_bnd

結果のコレクションへのコピーは、この下限の索引から開始されます。

使用上の注意

行数(cnt)にはゼロより大きい整数を指定する必要があり、それ以外の場合には例外が発生します。lower_bndは、正の数、負の数またはゼロでもかまいません。DEFINE_ARRAYコールが発行された問合せに、配列バインドを含めることはできません。

PROCEDURE BULK_PLSQL(deptid NUMBER)
    TYPE namelist IS TABLE OF employees.last_name%TYPE;
    TYPE sallist IS TABLE OF employees.salary%TYPE;
    names    namelist;
    sals     sallist;
    c        NUMBER;
    r        NUMBER;
    sql_stmt VARCHAR2(32767) :=
        'SELECT last_name, salary FROM employees WHERE department_id = :b1';
 
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
    DBMS_SQL.BIND_VARIABLE(c, 'b1', deptid);
 
    DBMS_SQL.DEFINE_ARRAY(c, 1, names, 5);
    DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5);
 
    r := DBMS_SQL.EXECUTE(c);
 
    LOOP
      r := DBMS_SQL.FETCH_ROWS(c);
      DBMS_SQL.COLUMN_VALUE(c, 1, names);
      DBMS_SQL.COLUMN_VALUE(c, 2, sals);
      EXIT WHEN r != 5;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(c);
 
    -- loop through the names and sals collections
    FOR i IN names.FIRST .. names.LAST  LOOP
      DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
    END LOOP;
END;
/

例152-3 例: 配列の定義

次の例では、DEFINE_ARRAYプロシージャの使用方法を示します。

declare
  c       NUMBER;
  d       NUMBER;
  n_tab   DBMS_SQL.NUMBER_TABLE;
  indx    NUMBER := -10;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  dBMS_SQL.PARSE(c, 'select n from t order by 1', DBMS_SQL.NATIVE);

  DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);

  d := DBMS_SQL.EXECUTE(c);
  loop
    d := DBMS_SQL.FETCH_ROWS(c);

    DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);

    EXIT WHEN d != 10;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(c);

  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

前述の例では、FETCH_ROWSファンクションをコールするたびに、DBMS_SQLバッファに保持されている10行がフェッチされます。COLUMN_VALUEプロシージャがコールされると、それらの行は、DEFINE文の指定に従って、-10から-1の位置で指定されたPL/SQL表(この場合はn_tab)に移動します。次に、2番目のバッチがループ内でフェッチされ、行が0(ゼロ)から9の位置に移動し、その後同様に続きます。

各配列への現行の索引は、自動的にメンテナンスされます。この索引は、EXECUTE時に「indx」に初期化され、COLUMN_VALUEがコールされるたびに更新されます。任意の時点で再実行する場合は、各DEFINEの現行の索引が「indx」に再初期化されます。

このようにして、問合せのすべての結果が表内にフェッチされます。FETCH_ROWSで10行をフェッチできない場合は、実際にフェッチされた行数を戻して(1行もフェッチできなかった場合は0(ゼロ)を戻します)、ループを終了します。

DEFINE_ARRAYプロシージャの別の使用例を次に示します。

次のように定義されたMULTI_TAB表を想定します。

CREATE TABLE multi_tab (num NUMBER, 
                        dat1 DATE, 
                        var VARCHAR2(24), 
                        dat2 DATE) 

この表からすべてを選択して4つのPL/SQL表に移動するには、次の簡単なプログラムを使用できます。

DECLARE
  c       NUMBER;
  d       NUMBER;
  n_tab  DBMS_SQL.NUMBER_TABLE;
  d_tab1 DBMS_SQL.DATE_TABLE;
  v_tab  DBMS_SQL.VARCHAR2_TABLE;
  d_tab2 DBMS_SQL.DATE_TABLE;
  indx NUMBER := 10;
BEGIN

  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, 'select * from multi_tab order by 1', DBMS_SQL.NATIVE);

  DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab,  5, indx);
  DBMS_SQL.DEFINE_ARRAY(c, 2, d_tab1, 5, indx);
  DBMS_SQL.DEFINE_ARRAY(c, 3, v_tab,  5, indx);
  DBMS_SQL.DEFINE_ARRAY(c, 4, d_tab2, 5, indx);

  d := DBMS_SQL.EXECUTE(c);

  LOOP
    d := DBMS_SQL.FETCH_ROWS(c);

    DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
    DBMS_SQL.COLUMN_VALUE(c, 2, d_tab1);
    DBMS_SQL.COLUMN_VALUE(c, 3, v_tab);
    DBMS_SQL.COLUMN_VALUE(c, 4, d_tab2);
  
    EXIT WHEN d != 5;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(c);

/* 

これで、4つの表はあらゆる用途に使用できます。使用方法の1つとして、'INSERT into SOME_T values (:a, :b, :c, :d)'などの文を使用して、行を別の表に移動するためにBIND_ARRAYを使用できます。

*/

EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

152.9.8 DEFINE_COLUMNプロシージャ

このプロシージャは、指定のカーソルから選択する列を定義します。このプロシージャが使用できるのは、SELECTカーソルのみです。

定義されている列は、指定のカーソル内にある文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。

「DEFINE_COLUMN_CHARプロシージャ」「DEFINE_COLUMN_LONGプロシージャ」「DEFINE_COLUMN_RAWプロシージャ」および「DEFINE_COLUMN_ROWIDプロシージャ」も参照してください。

構文

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN <datatype>);

<datatype>は、次のいずれかのデータ・タイプである必要があります。

BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NUMBER
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types

DEFINE_COLUMNは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。

DEFINE_COLUMNプロシージャでは、次の構文もサポートされます。

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN VARCHAR2 CHARACTER SET ANY_CS,
   column_size    IN INTEGER);

プラグマ

pragma restrict_references(define_column,RNDS,WNDS);

パラメータ

表152-15 DEFINE_COLUMNプロシージャのパラメータ

パラメータ 説明

c

選択対象に定義されている行のカーソルのID番号。

position

定義している行内にある列の相対位置。文の最初の列は位置1です。

column

定義している列の値。この値のタイプによって、定義している列のタイプが決まります。

column_size

タイプVARCHAR2の列に対する列値の最大予測サイズ(バイト単位)。

使用上の注意

文字長セマンティクスを使用するとき、タイプVARCHAR2の列値に戻される最大バイト数は、column_size *現在の文字セットの最大文字バイト数で求められます。たとえば、column_sizeを10に指定すると、UTF8文字セットで文字長セマンティクスを使用するとき、これが表す文字数に関係なく、最大で30(10*3)バイトが戻されます。

152.9.9 DEFINE_COLUMN_CHARプロシージャ

このプロシージャは、指定のカーソルから選択するCHARデータのある列を定義します。このプロシージャが使用できるのは、SELECTカーソルのみです。

定義されている列は、指定のカーソル内にある文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。

「DEFINE_COLUMNプロシージャ」「DEFINE_COLUMN_LONGプロシージャ」「DEFINE_COLUMN_RAWプロシージャ」および「DEFINE_COLUMN_ROWIDプロシージャ」も参照してください。

構文

DBMS_SQL.DEFINE_COLUMN_CHAR (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN CHAR CHARACTER SET ANY_CS,
   column_size    IN INTEGER);

プラグマ

pragma restrict_references(define_column,RNDS,WNDS);

パラメータ

表152-16 DEFINE_COLUMN_CHARプロシージャのパラメータ

パラメータ 説明

c

選択対象に定義されている行のカーソルのID番号。

position

定義している行内にある列の相対位置。文の最初の列は位置1です。

column

定義している列の値。この値のタイプによって、定義している列のタイプが決まります。

column_size

タイプCHARの列に対する列値の最大予測サイズ(文字数)。

152.9.10 DEFINE_COLUMN_LONGプロシージャ

このプロシージャは、SELECTカーソルに対してLONG列を定義します。定義されている列は、指定のカーソルの文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。

構文

DBMS_SQL.DEFINE_COLUMN_LONG (
   c              IN INTEGER,
   position       IN INTEGER); 

パラメータ

表152-17 DEFINE_COLUMN_LONGプロシージャのパラメータ

パラメータ 説明

c

選択対象に定義されている行のカーソルのID番号。

position

定義している行内にある列の相対位置。

文の最初の列は位置1です。

152.9.11 DEFINE_COLUMN_RAWプロシージャ

このプロシージャは、指定のカーソルから選択するタイプRAWの列を定義します。

このプロシージャが使用できるのは、SELECTカーソルのみです。

定義されている列は、指定のカーソル内にある文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。

「DEFINE_COLUMNプロシージャ」「DEFINE_COLUMN_CHARプロシージャ」「DEFINE_COLUMN_LONGプロシージャ」および「DEFINE_COLUMN_ROWIDプロシージャ」も参照してください。

構文

DBMS_SQL.DEFINE_COLUMN_RAW (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN RAW,
   column_size    IN INTEGER);

プラグマ

pragma restrict_references(define_column,RNDS,WNDS);

パラメータ

表152-18 DEFINE_COLUMN_RAWプロシージャのパラメータ

パラメータ 説明

c

選択対象に定義されている行のカーソルのID番号。

position

定義している行内にある列の相対位置。文の最初の列は位置1です。

column

定義している列の値。この値のタイプによって、定義している列のタイプが決まります。

column_size

RAWタイプの列に対する列値の最大予測サイズ(バイト単位)。

152.9.12 DEFINE_COLUMN_ROWIDプロシージャ

このプロシージャは、指定のカーソルから選択するタイプROWIDの列を定義します。このプロシージャが使用できるのは、SELECTカーソルのみです。

定義されている列は、指定のカーソル内にある文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。

「DEFINE_COLUMNプロシージャ」「DEFINE_COLUMN_CHARプロシージャ」「DEFINE_COLUMN_LONGプロシージャ」および「DEFINE_COLUMN_RAWプロシージャ」も参照してください。

構文

DBMS_SQL.DEFINE_COLUMN_ROWID (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN ROWID); 

プラグマ

pragma restrict_references(define_column,RNDS,WNDS);

パラメータ

表152-19 DEFINE_COLUMN_ROWIDプロシージャのパラメータ

パラメータ 説明

c

選択対象に定義されている行のカーソルのID番号。

position

定義している行内にある列の相対位置。文の最初の列は位置1です。

column

定義している列の値。この値のタイプによって、定義している列のタイプが決まります。

152.9.13 DESCRIBE_COLUMNSプロシージャ

このプロシージャは、DBMS_SQLによってオープンされ解析されたカーソルの列の情報を表示します。

構文

DBMS_SQL.DESCRIBE_COLUMNS ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB);

パラメータ

表152-20 DESCRIBE_COLUMNSプロシージャのパラメータ

パラメータ 説明

c

表示される列のカーソルのID番号。

col_cnt

問合せの選択リストにある列数。

desc_t

問合せの各列の情報を表示する表。

例152-4 列の定義の表示

このコードは、説明が必要な表のSELECT *問合せを使用することで、SQL*PlusのDESCRIBEコールのかわりとして使用できます。

DECLARE
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num    NUMBER;
  PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
  BEGIN
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('col_type            =    ' || rec.col_type);
    DBMS_OUTPUT.PUT_LINE('col_maxlen          =    ' || rec.col_max_len);
    DBMS_OUTPUT.PUT_LINE('col_name            =    ' || rec.col_name);
    DBMS_OUTPUT.PUT_LINE('col_name_len        =    ' || rec.col_name_len);
    DBMS_OUTPUT.PUT_LINE('col_schema_name     =    ' || rec.col_schema_name);
    DBMS_OUTPUT.PUT_LINE('col_schema_name_len =    ' || rec.col_schema_name_len);
    DBMS_OUTPUT.PUT_LINE('col_precision       =    ' || rec.col_precision);
    DBMS_OUTPUT.PUT_LINE('col_scale           =    ' || rec.col_scale);
    DBMS_OUTPUT.PUT('col_null_ok         =    ');
    IF (rec.col_null_ok) THEN
      DBMS_OUTPUT.PUT_LINE('true');
    ELSE
      DBMS_OUTPUT.PUT_LINE('false');
    END IF;
  END;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(c, 'SELECT * FROM scott.bonus', DBMS_SQL.NATIVE);
 
  d := DBMS_SQL.EXECUTE(c);
 
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

/*
 * Following loop could simply be for j in 1..col_cnt loop.
 * Here we are simply illustrating some of the PL/SQL table
 * features.
 */
  col_num := rec_tab.first;
  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      EXIT WHEN (col_num IS NULL);
    END LOOP;
  END IF;
 
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

152.9.14 DESCRIBE_COLUMNS2プロシージャ

このプロシージャは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。

構文

DBMS_SQL.DESCRIBE_COLUMNS2 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB2);

プラグマ

PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);

パラメータ

表152-21 DESCRIBE_COLUMNS2プロシージャのパラメータ

パラメータ 説明

c

表示される列のカーソルのID番号。

col_cnt

問合せの選択リストにある列数。

desc_t

問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。

152.9.15 DESCRIBE_COLUMNS3プロシージャ

このプロシージャは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。

構文

DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB3);

BMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DESC_TAB4);

プラグマ

PRAGMA RESTRICT_REFERENCES(describe_columns3,WNDS);

パラメータ

表152-22 DESCRIBE_COLUMNS3プロシージャのパラメータ

パラメータ 説明

c

表示される列のカーソルのID番号。

col_cnt

問合せの選択リストにある列数。

desc_t

問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。

使用上の注意

カーソルIDに基づいて渡されるカーソルには、OPENおよびPARSEを実行しておく必要があります。実行しておかない場合、カーソルIDが無効であることを示すエラーが発生します。

CREATE TYPE PROJECT_T AS OBJECT
      ( projname          VARCHAR2(20),
        mgr               VARCHAR2(20))
/
 
CREATE TABLE projecttab(deptno NUMBER, project HR.PROJECT_T)
/
 
DECLARE
  curid      NUMBER;
  desctab    DBMS_SQL.DESC_TAB3;
  colcnt     NUMBER;
  sql_stmt   VARCHAR2(200) := 'select * from projecttab';
BEGIN
 
    curid := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
 
    DBMS_SQL.DESCRIBE_COLUMNS3(curid, colcnt, desctab);
 
    FOR i IN 1 .. colcnt LOOP
      IF desctab(i).col_type = 109 THEN
        DBMS_OUTPUT.PUT(desctab(i).col_name || ' is user-defined type: ');
        DBMS_OUTPUT.PUT_LINE(desctab(i).col_schema_name || '.' ||
                             desctab(i).col_type_name);
      END IF;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
 
Output:
 
PROJECT is user-defined type: HR.PROJECT_T

152.9.16 EXECUTEファンクション

このファンクションは、指定のカーソルを実行します。このファンクションはカーソルのID番号を受け入れて、処理された行数を戻します。

戻り値は、INSERT文、UPDATE文およびDELETE文に対してのみ有効で、DDL文を含めた他のタイプの文に対しては、戻り値は定義されてなく、無視する必要があります。

構文

DBMS_SQL.EXECUTE (
   c   IN INTEGER)
  RETURN INTEGER;

パラメータ

表152-23 EXECUTEファンクションのパラメータ

パラメータ 説明

c

実行するカーソルのカーソルID番号。

戻り値

処理された行数を戻します。

使用上の注意

TO_CURSOR_NUMBERファンクションにより戻されるDBMS_SQLカーソルは、すでに実行されているDBMS_SQLカーソルと同じ方法で実行されます。したがって、このカーソルにEXECUTEをコールするとエラーが発生します。

152.9.17 EXECUTE_AND_FETCHファンクション

このファンクションは、指定のカーソルを実行して行をフェッチします。

このファンクションは、EXECUTEをコールしてからFETCH_ROWSをコールするのと同じ機能を提供します。ただし、リモート・データベースに対して使用する場合は、EXECUTE_AND_FETCHをコールした方がネットワークのラウンドトリップ数を低減できます。

EXECUTE_AND_FETCHファンクションは、実際にフェッチされた行数を戻します。

構文

DBMS_SQL.EXECUTE_AND_FETCH (
   c              IN INTEGER,
   exact          IN BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

プラグマ

pragma restrict_references(execute_and_fetch,WNDS);

パラメータ

表152-24 EXECUTE_AND_FETCHファンクションのパラメータ

パラメータ 説明

c

実行してフェッチするカーソルのカーソルID番号。

exact

問合せで実際に一致する行数が1以外の場合は、TRUEに設定すると、例外が発生します。

注意: LONG列に対して、exactパラメータをTRUEに設定するオプションはサポートされていません。

例外が発生しても、行はフェッチされ、使用可能です。

戻り値

指定された行を戻します。

152.9.18 FETCH_ROWSファンクション

このファンクションは、指定のカーソルから行をフェッチします。

FETCH_ROWSは、フェッチする行が残っているかぎり、繰り返しコールできます。これらの行はバッファに取り出し、FETCH_ROWSへの各コール後に、COLUMN_VALUEをコールして列ごとに読み込む必要があります。

FETCH_ROWSファンクションは、フェッチするカーソルのID番号を受け入れて、実際にフェッチされた行数を戻します。

構文

DBMS_SQL.FETCH_ROWS (
   c              IN INTEGER)
  RETURN INTEGER;

プラグマ

pragma restrict_references(fetch_rows,WNDS);

パラメータ

表152-25 FETCH_ROWSファンクションのパラメータ

パラメータ 説明

c

ID番号。

戻り値

指定のカーソルから行を戻します。

152.9.19 GET_NEXT_RESULTプロシージャ

このプロシージャは、再帰文のコール元に戻される次の結果の文を取得するか、このコール元が自らを再帰文のクライアントとして設定している場合、クライアントとしてこのコール元に戻される次の結果を取得します。

文が戻される順序は、RETURN_RESULTプロシージャによって戻される順序と同じです。

構文

DBMS_SQL.GET_NEXT_RESULT(
   c            IN          INTEGER,
   rc           OUT         SYS_REFCURSOR);

DBMS_SQL.GET_NEXT_RESULT(
   c            IN          INTEGER,
   rc           OUT         INTEGER);

パラメータ

表152-26 GET_NEXT_RESULTプロシージャのパラメータ

パラメータ 説明

c

再帰文カーソル

rc

次に戻される結果の文のカーソルまたはREFカーソル

例外

ORA-01403 no_data_found: これは、戻される文の結果がない場合に発生します。

使用上の注意

  • 文の結果のカーソルを取得した後、これが不要になったら、コール元はカーソルを正しくクローズする必要があります。

  • 取得されていないすべての戻された文のカーソルは、再帰文のカーソルをクローズした後にクローズされます。

DECLARE
  c  INTEGER;
  rc SYS_REFCURSOR;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR(treat_as_client_for_results => TRUE);
  DBMS_SQL.PARSE(c                  => c,
                 statement          => 'begin proc; end;');
  DBMS_SQL.EXECUTE(c);
  LOOP
    BEGIN
      DBMS_SQL.GET_NEXT_RESULT(c, rc);
    EXCEPTIONS
      WHEN no_data_found THEN
        EXIT;
    END;
    LOOP
      FETCH rc INTO ...
      ...
    END LOOP;
  END LOOP;
END;

152.9.20 IS_OPENファンクション

このファンクションは、指定のカーソルが現在オープンしているかどうかをチェックします。

構文

DBMS_SQL.IS_OPEN (
   c              IN INTEGER)
  RETURN BOOLEAN;

プラグマ

pragma restrict_references(is_open,RNDS,WNDS);

パラメータ

表152-27 IS_OPENファンクションのパラメータ

パラメータ 説明

c

チェックするカーソルのカーソルID番号。

戻り値

オープンされ、クローズされていないカーソル番号に対してはTRUENULLのカーソル番号に対してはFALSEを戻します。CLOSE_CURSORプロシージャは、渡されたカーソル変数をNULLにすることに注意してください。

例外

ORA-29471 DBMS_SQLアクセスが拒否されました: これは、無効なカーソルID番号が検出された場合に発生します。セッションでこのエラーが発生し、レポートされると、その後は同じセッションでDBMS_SQLをコールするたびにこのエラーが発生しますが、これは、このセッションではDBMS_SQLを実行できなくなることを意味します。

152.9.21 LAST_ERROR_POSITIONファンクション

このファンクションは、エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。SQL文内の最初の文字は、位置0(ゼロ)にあります。

構文

DBMS_SQL.LAST_ERROR_POSITION 
   RETURN INTEGER;

プラグマ

pragma restrict_references(last_error_position,RNDS,WNDS);

戻り値

エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。

使用上の注意

このファンクションは、別のDBMS_SQLプロシージャまたはファンクションのコール前、かつPARSEのコール後にコールしてください。

152.9.22 LAST_ROW_COUNTファンクション

このファンクションは、フェッチされた累積行数を戻します。

構文

DBMS_SQL.LAST_ROW_COUNT 
   RETURN INTEGER;

プラグマ

pragma restrict_references(last_row_count,RNDS,WNDS);

戻り値

フェッチされた累積行数を戻します。

使用上の注意

このファンクションは、FETCH_ROWSコールまたはEXECUTE_AND_FETCHコール後にコールしてください。EXECUTEコール後にコールすると、戻される値は0(ゼロ)です。

152.9.23 LAST_ROW_IDファンクション

このファンクションは、処理された最後の行のROWIDを戻します。

構文

DBMS_SQL.LAST_ROW_ID 
   RETURN ROWID;

プラグマ

pragma restrict_references(last_row_id,RNDS,WNDS);

戻り値

最後に処理された行のROWIDを戻します。

使用上の注意

このファンクションは、FETCH_ROWSコールまたはEXECUTE_AND_FETCHコール後にコールしてください。

152.9.24 LAST_SQL_FUNCTION_CODEファンクション

このファンクションは、文のSQL機能コードを戻します。

これらのコードについては、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。

構文

DBMS_SQL.LAST_SQL_FUNCTION_CODE 
   RETURN INTEGER;

プラグマ

pragma restrict_references(last_sql_function_code,RNDS,WNDS);

戻り値

文のSQL機能コードを戻します。

使用上の注意

このファンクションは、SQL文の実行直後にコールする必要があります。それ以外の場合は、戻り値が定義されません。

152.9.25 OPEN_CURSORファンクション

このファンクションは、新規のカーソルをオープンします。

security_levelパラメータでは、オープンされたカーソルのセキュリティへのファイングレイン・コントロールの適用が許可されます。

構文

DBMS_SQL.OPEN_CURSOR (
   treat_as_client_for_results    IN     BOOLEAN    DEFAULT FALSE) 
  RETURN INTEGER;

DBMS_SQL.OPEN_CURSOR (
   security_level                 IN     INTEGER,
   treat_as_client_for_results    IN     BOOLEAN    DEFAULT FALSE) 
  RETURN INTEGER;

パラメータ

表152-28 OPEN_CURSORファンクションのパラメータ

パラメータ 説明

security_level

オープンされたカーソルに適用するセキュリティ保護のレベルを指定します。有効なセキュリティ・レベル値は、01および2です。security_levelパラメータを指定せずにopen_cursorのオーバーロードを使用してカーソルをオープンした場合にこのオーバーロードに引数値としてNULLを指定すると、オープンされたカーソルに対して、セキュリティ・レベルのデフォルト値1が適用されます。

  • Level 0: カーソルに対して、セキュリティ・チェックなしですべてのDBMS_SQL操作が可能です。カーソルは、フェッチされることがあり、カーソルは、(カーソルの解析時に有効だったユーザーIDおよびロールとは異なる)有効なユーザーIDおよびロールで実行されるコードによってフェッチでき、さらに、このコードによって再バインドおよび再実行することもできます。デフォルトでは、セキュリティのこのレベルはオフになっています。

  • Level 1: カーソルに対してバインド操作および実行操作を行う場合のDBMS_SQLのコール元の参照コンテナ、有効なユーザーID、およびロールは、そのカーソルに対して最後に解析操作を行ったコール元の有効なユーザーIDおよびロールと同じである必要があります。

  • Level 2: カーソルに対してバインド、実行、定義、記述、フェッチのすべての操作を行う場合のDBMS_SQLのコール元の参照コンテナ、有効なユーザーID、およびロールは、そのカーソルに対して最後に解析操作を行ったコール元の有効なユーザーIDおよびロールと同じである必要があります。

treat_as_client_for results

再帰文のコール元が自らをクライアントに設定し、再帰文からクライアントに戻される文の結果を受信することを許可します。戻される文の結果は、GET_NEXT_RESULTプロシージャによって取得できます。

プラグマ

pragma restrict_references(open_cursor,RNDS,WNDS);

戻り値

新規カーソルのID番号を戻します。

使用上の注意

  • このカーソルが不要になった場合は、CLOSE_CURSORプロシージャをコールして、明示的にクローズする必要があります。

  • カーソルを使用すると、同じSQL文を繰り返し実行したり、新規のSQL文を実行することができます。カーソルを再使用した場合、新規のSQL文が解析されるときに、対応するカーソル・データ領域の内容がリセットされます。カーソルを再使用しないかぎり、クローズして再オープンする必要はありません。

152.9.26 PARSEプロシージャ

このプロシージャは、指定したカーソル内の指定した文を解析します。すべての文が即時に解析されます。さらに、DDL文は、解析時にただちに実行されます。

PARSEプロシージャには、複数のバージョンがあります。

  • 引数としてVARCHAR2文を使用します。

  • セグメント化された文字列を使用します。1つはVARCHAR2A (TABLE OF VARCHAR2(32767))、もう1つはVARCHAR2S (TABLE OF VARCHAR2(256))を引数として使用します。これらのオーバーロードは、PL/SQL表の文の要素を連結して、連結結果の文字列を解析します。これらのプロシージャを使用すると、文を分割することによって、単一のVARCHAR2変数についての制限を超えた長い文を解析できます。

  • 引数としてCLOBを使用します。この解析プロシージャのCLOBオーバーロード・バージョンを使用すると、32KBを超えるSQL文を解析できます。

構文

各バージョンには複数のオーバーロードがあります。

DBMS_SQL.PARSE (
   c                           IN   INTEGER,
   statement                   IN   VARCHAR2,
   language_flag               IN   INTEGER[
 [,edition                     IN   VARCHAR2 DEFAULT NULL],
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE]
 [,schema                      IN   VARCHAR2 DEFAULT NULL]
 [,container                   IN   VARCHAR2)];
DBMS_SQL.PARSE (
   c                           IN   INTEGER,
   statement                   IN   CLOB,
   language_flag               IN   INTEGER[
 [,edition                     IN   VARCHAR2 DEFAULT NULL],
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE]
 [,schema                      IN   VARCHAR2 DEFAULT NULL]
 [,container                   IN   VARCHAR2)];  
DBMS_SQL.PARSE (
   c                           IN   INTEGER, 
   statement                   IN   VARCHAR2A,
   lb                          IN   INTEGER, 
   ub                          IN   INTEGER,
   lfflg                       IN   BOOLEAN, 
   language_flag               IN   INTEGER[
 [,edition                     IN   VARCHAR2 DEFAULT NULL],
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE]
 [,schema                      IN   VARCHAR2 DEFAULT NULL]
 [,container                   IN   VARCHAR2)];
 
DBMS_SQL.PARSE (
   c                           IN   INTEGER, 
   statement                   IN   VARCHAR2s,
   lb                          IN   INTEGER, 
   ub                          IN   INTEGER,
   lfflg                       IN   BOOLEAN, 
   language_flag               IN   INTEGER[
 [,edition                     IN   VARCHAR2 DEFAULT NULL],
   apply_crossedition_trigger  IN   VARCHAR2 DEFAULT NULL,
   fire_apply_trigger          IN   BOOLEAN DEFAULT TRUE]
 [,schema                      IN   VARCHAR2 DEFAULT NULL]
 [,container                   IN   VARCHAR2)];

パラメータ

表152-29 PARSEプロシージャのパラメータ

パラメータ 説明

c

文を解析するカーソルのID番号。

statement

解析するSQL文。32Kを超えてCLOBに格納できるSQL文。

PL/SQL文と異なり、SQL文の終わりにはセミコロンを含めないでください。次に例を示します。

DBMS_SQL.PARSE(cursor1, 'BEGIN proc; END;', 2);

DBMS_SQL.PARSE(cursor1, 'INSERT INTO tab VALUES(1)', 2);

lb

文内の要素の下限。

ub

文内の要素の上限。

lfflg

TRUEの場合、連結している各要素の後に改行を挿入します。

language_flag

SQL文の動作を指定します。設定可能な値およびそれに対応する動作の詳細は、「DBMS_SQLの定数」を参照してください。

edition

次に示す条件のもとで、文を実行するエディションを指定します。

  • NULLであり、containerNULLの場合、文は現行エディションで実行されます。

  • 有効なコンテナが指定されている場合は、NULLを渡すと、文はターゲット・コンテナのデフォルト・エディションで実行されます。

  • 文を実行するユーザーとエディションを指定した場合、ユーザーにはそのエディションに対するUSE権限が必要です。

次の一般的な条件が適用されます。文字列の内容はSQL識別子として処理され、実際のエディション名に特殊文字または小文字が含まれている場合は、残りの文字列を二重引用符で囲む必要があります(二重引用符を使用しない場合、内容は大文字になります)。

apply_crossedition_trigger

指定したSQLに適用する転送crosseditionトリガーの未修飾の名前を指定します。この名前は、文が実行されるエディションおよびcurrent_schema設定を使用して解決されます。トリガーは、文を実行するユーザーによって所有されている必要があります。NULL以外の値を指定した場合は、指定したcrosseditionトリガーが実行されます(この場合に前提となるのは、fire_apply_triggerTRUEであること、トリガーが有効になっていること、トリガーが文のターゲットの表で定義されていること、文のタイプがトリガーのdml_event_clauseと一致していること、有効なWHENおよびUPDATE OFの制限事項が満たされていることなどです)。他の転送crosseditionトリガーは、指定したトリガーが文のターゲットである表に対して追加のDMLを実行している場合と同様に適用されるcrosseditionトリガーDMLルールを使用して、実行および選択することもできます。crossedition以外のトリガーおよびreverse crosseditionトリガーは実行されません。文字列の内容はSQL識別子として処理され、実際のトリガー名に特殊文字または小文字が含まれている場合は、残りの文字列を二重引用符で囲む必要があります(二重引用符を使用しない場合、内容は大文字になります)。

fire_apply_trigger

指定したapply_crossedition_trigger自体が実行されるか、他のトリガーを選択する際に使用されるガイドのみの指定が必要なことを示します。これは文がapply_crossedition_trigger自体によって実行されるアクションの代替である場合に、通常はFALSEに設定されます。FALSEの場合、指定したトリガーは実行されませんが、他のトリガーは、指定したトリガーが文のターゲットの表に対してDMLを実行している場合と同様に起動対象として選択されます。文がDMLでない場合、apply_crossedition_triggerおよびfire_apply_triggerパラメータは無視されます。

schema

未修飾のオブジェクト名を解決するスキーマを指定します。NULLの場合、現行スキーマは有効なユーザーのスキーマです。

container

カーソルを実行するターゲット・コンテナの名前。NULLまたは指定しない場合、ターゲット・コンテナの名前はコール元コンテナと同じで、コンテナの切替えは実行されません。有効なコンテナ名を指定する場合、現行ユーザーはターゲット・コンテナに切り替えるためのSET CONTAINER権限を持つ共通ユーザーであることが必要です。コンテナの切替えが完了すると、有効なユーザーにはデフォルトのロールが設定されます。

使用上の注意

  • DBMS_SQLを使用してDDL文を動的に実行すると、プログラムが応答しなくなる可能性があります。たとえば、パッケージ内のプロシージャをコールすると、実行がユーザー側に戻るまでそのパッケージがロックされます。最初のロックを解放する前に、動的にパッケージを削除するなど、ロックの競合を引き起こす操作を行うとプログラムの実行が停止します。

  • クライアント側コードは、リモート・パッケージの変数または定数を参照できないため、定数の値を明示的に使用する必要があります。

    たとえば、次のコードは、クライアント側でコンパイルしません。

    DBMS_SQL.PARSE(cur_hdl, stmt_str, DBMS_SQL.NATIVE); -- 定数DBMS_SQL.NATIVEを使用

    次のコードは、引数が明示的に指定されているので、クライアント側で有効です。

    DBMS_SQL.PARSE(cur_hdl, stmt_str, 1); -- クライアント上でコンパイル

  • VARCHAR2Sタイプは、レガシー・コードの下位互換性を維持する目的で現在サポートされています。ただし、VARCHAR2Aを使用することをお薦めします(こちらの機能の方が優れており、VARCHAR2Sは将来のリリースでは非推奨となるため)。

  • 32KBを超えるSQL文を解析する場合、VARCHAR2Awオーバーロードのかわりに、新しいCLOBオーバーロード・バージョンのPARSEプロシージャを使用できます。

  • containerパラメータの値がコール側コンテナと同じである場合、コンテナの切替えは行われません。ただし、現行ユーザーのデフォルト・ロールは有効になります。

例外

コンパイルに関する警告を伴ったDBMS_SQLを使用して、タイプ、プロシージャ、ファンクション、またはパッケージを作成する場合、ORA-24344例外が発生しますが、PL/SQLユニットはそのまま作成されます。

152.9.27 RETURN_RESULTプロシージャ

このプロシージャは、実行した文の結果をクライアント・アプリケーションに戻します。

結果は、クライアントが後で取得できます。または、この文の結果が戻される再帰文を実行する直接のコール元に文の結果を戻し、このコール元が後で結果を取得できます。

コール元は次のいずれかにできます。

  • DBMS_SQLを使用して再帰文を実行するPL/SQLストアド・プロシージャ

  • JDBCを使用するJavaストアド・プロシージャ

  • ADO.NETを使用する.NETストアド・プロシージャ

  • Oracle Call Interface(OCI)を使用する外部プロシージャ

構文

DBMS_SQL.RETURN_RESULT(
   rc           IN OUT      SYS_REFCURSOR, 
   to_client    IN          BOOLEAN           DEFAULT TRUE);

DBMS_SQL.RETURN_RESULT(
   rc           IN OUT      INTEGER, 
   to_client    IN          BOOLEAN           DEFAULT TRUE);

パラメータ

表152-30 RETURN_RESULTプロシージャのパラメータ

パラメータ 説明

rc

文カーソルまたはREFカーソル

to_client

文の結果をクライアントに戻します(または戻しません)。戻さない場合、直接のコール元に戻されます。

使用上の注意

  • 現在、SQL問合せのみを戻すことができます。リモート・プロシージャ・コールを介して文の結果を戻すことはサポートされていません。

  • 文が戻されると、この文が戻されたクライアントまたは直接のコール元を除いてアクセスできなくなります。

  • クライアントによって実行された文または中間の再帰文がSQL問合せで、エラーが発生している場合、文の結果を戻すことができません。

  • 戻されるREFカーソルでは、強いタイプまたは弱いタイプを指定できます。

  • 戻される問合せは部分的にフェッチできます。

  • EXECUTE IMMEDIATE文はその再帰文から戻される文の結果を取得するインタフェースを提供しないため、EXECUTE IMMEDIATE文のコール元に戻される文の結果のカーソルは、文が完了するとクローズされます。PL/SQLで再帰文から戻される文の結果を取得するには、DBMS_SQLを使用して再帰文を実行します。

CREATE PROCEDURE proc AS
  rc1 sys_refcursor;
  rc2 sys_refcursor;
BEGIN
  OPEN rc1 FOR SELECT * FROM t1;
  DBMS_SQL.RETURN_RESULT(rc1);
  OPEN rc2 FOR SELECT * FROM t2;
  DBMS_SQL.RETURN_RESULT(rc2);
END;
/ 

152.9.28 TO_CURSOR_NUMBERファンクション

このファンクションは、強くタイプ付けされたREFカーソルまたは弱くタイプ付けされたREFカーソルのうちOPENが実行されたカーソルを使用して、そのカーソルをDBMS_SQLカーソル番号に変換します。

構文

DBMS_SQL.TO_CURSOR_NUMBER(
   rc IN OUT SYS_REFCURSOR)
  RETURN INTEGER;

パラメータ

表152-31 TO_CURSOR_NUMBERファンクションのパラメータ

パラメータ 説明

rc

カーソル番号に変換されるREF CURSOR

戻り値

REF CURSORから変換された、DBMS_SQLで管理可能なカーソル番号を戻します。

使用上の注意

  • 渡されるREF CURSORには、OPENを実行しておく必要があります。実行しておかない場合、エラーが発生します。

  • REF CURSORDBMS_SQLカーソル番号に変換されると、REF CURSORはシステム固有の動的SQL操作でアクセスできなくなります。

  • このサブプログラムにより戻されるDBMS_SQLカーソルは、すでに実行されているDBMS_SQLカーソルと同じ方法で実行されます。

CREATE OR REPLACE PROCEDURE DO_QUERY(sql_stmt VARCHAR2) IS
  TYPE CurType IS REF CURSOR;
  src_cur         CurType;
  curid           NUMBER;
  desctab         DBMS_SQL.DESC_TAB;
  colcnt          NUMBER;
  namevar         VARCHAR2(50);
  numvar          NUMBER;
  datevar         DATE;
  empno           NUMBER := 100;
BEGIN
 
 
    -- sql_stmt := 'select ...... from employees where employee_id = :b1';
    OPEN src_cur FOR sql_stmt USING empno;
 
    -- Switch from native dynamic SQL to DBMS_SQL
    curid := DBMS_SQL.TO_CURSOR_NUMBER (src_cur);
 
    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
 
    -- Define columns
    FOR i IN 1 .. colcnt LOOP
        IF desctab(i).col_type = 2 THEN
           DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
.......
         ELSE
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
         END IF;
    END LOOP;
 
  -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
          IF (desctab(i).col_type = 1) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
         ELSIF (desctab(i).col_type = 2) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
          ELSIF (desctab(i).col_type = 12) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
....
          END IF;
        END LOOP;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
/ 

152.9.29 TO_REFCURSORファンクション

このファンクションは、OPENPARSEおよびEXECUTEが実行されたカーソルを使用して、そのカーソルをPL/SQLで管理可能なREF CURSOR(弱くタイプ付けされたカーソル)に変換/移行します(変換/移行後のカーソルは、システム固有の動的SQLを使用するように切り替えられているPL/SQLシステム固有の動的SQLで消費できます)。

このサブプログラムが使用できるのは、SELECTカーソルのみです。

構文

DBMS_SQL.TO_REFCURSOR(
   cursor_number IN OUT INTEGER)
  RETURN SYS_REFCURSOR;

パラメータ

表152-32 TO_REFCURSORファンクションのパラメータ

パラメータ 説明

cursor_number

REF CURSORに変換されるカーソルのカーソル番号。

戻り値

DBMS_SQLカーソル番号から変換されたPL/SQL REF CURSORを戻します。

使用上の注意

  • cursor_numberによって渡されるカーソルには、OPENPARSEおよびEXECUTEを実行しておく必要があります。実行しておかない場合、エラーが発生します。

  • cursor_numberがREF CURSORに変換されると、cursor_numberDBMS_SQL操作でアクセスできなくなります。

  • cursor_numberがREF CURSORに変換された後に、DBMS_SQL.IS_OPENを使用してcursor_numberがオープンしているかどうかを確認しようとすると、エラーが発生します。

  • カーソル番号が最後に有効なコンテナ・パラメータを使用して解析された場合、そのカーソル番号をREF CURSORに変換することはできません。

CREATE OR REPLACE PROCEDURE DO_QUERY(mgr_id NUMBER) IS
  TYPE CurType IS REF CURSOR;
  src_cur         CurType;
  curid           NUMBER;
  sql_stmt        VARCHAR2(200);
  ret             INTEGER;
  empnos          DBMS_SQL.Number_Table;
  depts           DBMS_SQL.Number_Table;
BEGIN

  -- DBMS_SQL.OPEN_CURSOR
  curid := DBMS_SQL.OPEN_CURSOR;
 
  sql_stmt :=    'SELECT EMPLOYEE_ID, DEPARTMENT_ID from employees where MANAGER_ID = :b1';

  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(curid, 'b1', mgr_id);
  ret := DBMS_SQL.EXECUTE(curid);

  -- Switch from DBMS_SQL to native dynamic SQL
  src_cur := DBMS_SQL.TO_REFCURSOR(curid);

  -- Fetch with native dynamic SQL
  FETCH src_cur BULK COLLECT INTO empnos, depts;

  IF empnos.COUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('EMPNO DEPTNO');
    DBMS_OUTPUT.PUT_LINE('----- ------');
    -- Loop through the empnos and depts collections
    FOR i IN 1 .. empnos.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(empnos(i) || '   ' || depts(i));
    END LOOP;
  END IF;
   -- Close cursor
  CLOSE src_cur;
END;
/ 

152.9.30 VARIABLE_VALUEプロシージャ

このプロシージャは、指定のカーソルについて指定の変数の値を戻します。これは、returning句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。

構文

DBMS_SQL.VARIABLE_VALUE (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT NOCOPY <datatype>);
 

<datatype>は、次のいずれかのデータ・タイプである必要があります。

ADT (user-defined object types)
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NESTED table
NUMBER
OPAQUE types
REF
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
VARRAY
YMINTERVAL_UNCONSTRAINED

CHARRAWおよびROWIDデータを含んだ変数では、次のバリエーションを構文で使用できます。

DBMS_SQL.VARIABLE_VALUE_CHAR (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT CHAR CHARACTER SET ANY_CS);

DBMS_SQL.VARIABLE_VALUE_RAW (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT RAW);

DBMS_SQL.VARIABLE_VALUE_ROWID (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT ROWID);

次の構文を使用すると、VARIABLE_VALUEプロシージャでバルク操作に対応できます。

DBMS_SQL.VARIABLE_VALUE ( 
   c                 IN   INTEGER, 
   name              IN   VARCHAR2,
   value             OUT NOCOPY <table_type>); 

バルク操作の場合、<table_type>はサポートされているDBMS_SQL事前定義済表タイプである必要があります。

「DBMS_SQLのデータ構造」を参照してください。

プラグマ

pragma restrict_references(variable_value,RNDS,WNDS);

パラメータ

表152-33 VARIABLE_VALUEプロシージャのパラメータ

パラメータ 説明

c

値を取得するカーソルのID番号。

name

取得した値を代入する変数名。

value

  • 単一行オプション: 指定した位置の変数の値を戻します。この出力パラメータのタイプが、BIND_VARIABLEへのコールで定義されている値の実際のタイプと異なる場合、例外エラーORA-06562inconsistent_typeが発生します。

  • 配列オプション: <table_type>として宣言されたローカル変数。バルク操作では、valueOUT NOCOPYパラメータとなります。

152.9.31 VARIABLE_VALUE_PKGプロシージャ

このプロシージャは、指定のカーソルについて指定の変数の値を戻します。

これは、宣言されたパッケージに対して、returning句を使用してPL/SQLブロックまたはDML文内のコレクションまたはレコード・タイプのバインド変数の値を戻すために使用されます。変数の型は、パッケージ仕様部で宣言する必要があります。これらのタイプでは、バルク操作はサポートされていません。

構文

DBMS_SQL.VARIABLE_VALUE_PKG ( 
   c                 IN   INTEGER, 
   name              IN   VARCHAR2,
   value             OUT NOCOPY <table_type>); 

<datatype>は、次のいずれかのデータ・タイプにできます。

  • RECORD

  • VARRAY

  • NESTED TABLE

  • INDEX BY PLS_INTEGER TABLE

  • INDEX BY BINARY_INTEGER TABLE

パラメータ

表152-34 VARIABLE_VALUE_PKGのパラメータ

パラメータ 説明

c

値を取得するカーソルのID番号。

name

取得した値を代入する変数名。

value

  • 単一行オプション: 指定した位置の変数の値を戻します。この出力パラメータのタイプが、BIND_VARIABLE_PKGへのコールで定義されている実際の値のタイプと異なる場合は、例外ORA-06562inconsistent_typeが発生します。

  • 配列オプション: <table_type>として宣言されたローカル変数。

例152-5 動的SQLでDBMS_SQL.VARIABLE_VALUE_PKGを使用してバインド変数の値を取得

データ・タイプは、パッケージ仕様部に宣言されます。VARIABLE_VALUE_PKGは、カーソルSQL文のバインド変数v2の値を取得するために使用されます。

CREATE OR REPLACE PACKAGE ty_pkg AS 
TYPE rec IS RECORD 
   ( n1 NUMBER, 
    n2 NUMBER); 
TYPE trect IS TABLE OF NUMBER; 
END ty_pkg; 
/
CREATE OR REPLACE PROCEDURE dyn_sql_nt  AS 
  dummy NUMBER; 
  cur   NUMBER; 
  v1 ty_pkg.trect; 
  v2 ty_pkg.trect; 
  str VARCHAR2(3000); 
BEGIN 
  v1 := ty_pkg.trect(1000); 
  str := 'declare v1 ty_pkg.trect;  begin v1:=:v1;  v1(1) := 2000; :v2 := v1; end;' ; 
  cur := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.PARSE(cur, str, DBMS_SQL.NATIVE); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v1', v1); 
  DBMS_SQL.BIND_VARIABLE_PKG(cur, ':v2', v2); 
  dummy := DBMS_SQL.EXECUTE(cur); 
  DBMS_SQL.VARIABLE_VALUE_PKG(cur, ':v2', v2); 
  DBMS_OUTPUT.PUT_LINE('n =  ' 
  || V2(1)); 
  DBMS_SQL.CLOSE_CURSOR(cur); 
END dyn_sql_nt;
/
EXEC dyn_sql_nt;

n =  2000