ヘッダーをスキップ
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス
10g リリース2(10.2)
B19245-02
  目次
目次
索引
索引

戻る
戻る
 
次へ
次へ
 

100 DBMS_SQL

DBMS_SQLパッケージでは、動的SQLを使用するためのインタフェースを提供し、PL/SQLを使用したデータ操作言語(DML)文やデータ定義言語(DDL)文の解析を可能にします。たとえば、DBMS_SQLパッケージが提供するPARSEプロシージャを使用することによって、ストアド・プロシージャ内からDROP TABLE文を入力できます。


関連項目:

  • システム固有の動的SQLの詳細は、『Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス』を参照してください。
  • DBMS_SQLとシステム固有の動的SQLの比較は、『Oracle Databaseアプリケーション開発者ガイド-基礎編』を参照してください。


この章では、次の項目について説明します。


DBMS_SQLの使用方法


概要

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

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

また、一部のタスクはDBMS_SQLでのみ実行できます。

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


関連項目:

『Oracle Call Interfaceプログラマーズ・ガイド』

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

DBMS_SQLパッケージの使用例は、次のとおりです。このコードは、Oracle Call Interfaceのユーザーにとってはかなり簡潔です。


セキュリティ・モデル

DBMS_SQLは、AUTHID CURRENT_USERでコンパイルされます。

無名PL/SQLブロックからコールされたすべてのDBMS_SQLサブプログラムは、現行のユーザーの権限を使用して実行されます。


関連項目:

実行者権限または定義者権限を使用してサブプログラムを起動する方法の詳細は、『Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス』を参照してください。


定数

v6 constant INTEGER     := 0;
native constant INTEGER := 1;
v7 constant INTEGER     := 2;

タイプ

一般のタイプ

バルクSQLタイプ

BFILE_TABLE

TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;

BINARY_DOUBLE_TABLE

TYPE binary_double_table IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;

BINARY_FLOAT_TABLE

TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;

BLOB_TABLE

TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;

CLOB_TABLE

TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;

DATE_TABLE

type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;

INTERVAL_DAY_TO_SECOND_TABLE

 TYPE interval_day_to_second_Table IS TABLE OF
    dsinterval_unconstrained INDEX BY binary_integer;

INTERVAL_YEAR_TO_MONTH_TABLE

TYPE interval_year_to_month_table IS TABLE OF yminterval_unconstrained INDEX BY BINARY_INTEGER;

DESC_REC、DESC_TAB

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;

NUMBER_TABLE

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TIME_TABLE

TYPE time_table IS TABLE OF time_unconstrained INDEX BY BINARY_INTEGER;

TIME_WITH_TIME_ZONE_TABLE

TYPE time_with_time_zone_table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX
BY BINARY_INTEGER;

TIMESTAMP_TABLE

TYPE timestamp_table IS TABLE OF timestamp_unconstrained INDEX BY BINARY_INTEGER;

TIMESTAMP_WITH_LTZ_TABLE

TYPE timestamp_with_ltz_table IS TABLE OF
    TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer;

UROWID_TABLE

TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;

VARCHAR2_TABLE

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

VARCHAR2A、DESC_REC2

TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  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);
  TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;

VARCHAR2S

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

例外

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

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


使用上の注意

実行フロー

  1. OPEN_CURSOR

  2. PARSE

  3. BIND_VARIABLEまたはBIND_ARRAY

  4. DEFINE_COLUMN、DEFINE_COLUMN_LONGまたはDEFINE_ARRAY

  5. EXECUTE

  6. FETCH_ROWSまたはEXECUTE_AND_FETCH

  7. VARIABLE_VALUE、COLUMN_VALUEまたはCOLUMN_VALUE_LONG

  8. CLOSE_CURSOR

OPEN_CURSOR

SQL文を処理するためには、オープン・カーソルが必要です。OPEN_CURSORファンクションをコールすると、ユーザーはOracleが保持している有効なカーソルを示すデータ構造のカーソルID番号を受け取ります。これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQLパッケージでのみ使用されます。


PARSE

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

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


注意:

パッケージやプロシージャを削除するためにDDL文を解析するときに、パッケージ内のプロシージャが使用中の場合は、デッドロックが起こる可能性があります。プロシージャへのコール後は、実行がユーザー側に戻されるまで、そのプロシージャは使用中であるとみなされます。このようなデッドロックは、5分後にタイムアウトします。

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

図100-1 DBMS_SQL実行フロー

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


BIND_VARIABLEまたはBIND_ARRAY

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

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

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


DEFINE_COLUMN、DEFINE_COLUMN_LONGまたはDEFINE_ARRAY

SELECT文内で選択されている行の列は、選択リスト内での相対位置(左から右)によって識別されます。問合せの場合は、定義プロシージャの1つ(DEFINE_COLUMNDEFINE_COLUMN_LONGまたはDEFINE_ARRAY)をコールして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をコールする必要があります。 


EXECUTE

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


FETCH_ROWSまたはEXECUTE_AND_FETCH

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


VARIABLE_VALUE、COLUMN_VALUEまたはCOLUMN_VALUE_LONG

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

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


CLOSE_CURSOR

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

カーソルをクローズしないと、カーソルが不要になっても、そのカーソルが使用しているメモリーは割り当てられたままになります。


問合せの処理

動的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直後にLAST_ERROR_POSITIONファンクションをコールします。


この項には、DBMS_SQLパッケージを使用するプロシージャの例が記述されています。

例1

この文のテキストはコンパイル時に判明しているため、この例では、動的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;

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

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

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

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

exec@hq.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;
/

例3、4および5: バルクDML

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

たとえば、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コールで指定したとおりに使用されます。配列の各要素は、大量の従業員をデータベースから削除する可能性があります。

次に、バルクINSERT文の例を示します。

DECLARE
  stmt VARCHAR2(200);
  empno_array DBMS_SQL.NUMBER_TABLE;
  empname_array DBMS_SQL.VARCHAR2_TABLE;
  c NUMBER;
  dummy NUMBER;
BEGIN
  FOR i in 0..9 LOOP
    empno_array(i) := 1000 + i;
    empname_array(I) := get_name(i);
  END LOOP;
  stmt := 'INSERT INTO emp VALUES(:num_array, :name_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);
  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;
/

実行が開始されると、10人の従業員はすべて表に挿入されます。 

最後に、バルクUPDATE文の例を示します。

Declare
  stmt VARCHAR2(200);
  emp_no_array DBMS_SQL.NUMBER_TABLE;
  emp_addr_array DBMS_SQL.VARCHAR2_TABLE;
  c NUMBER;
  dummy NUMBER;
BEGIN
  for i in 0..9 loop
    emp_no_array(i) := 1000 + i;
    emp_addr_array(I) := get_new_addr(i);
  END LOOP;
  stmt := 'update emp set ename = :name_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, ':name_array', empname_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;
/

EXECUTEファンクションがコールされると、全従業員のアドレスが一度に更新されます。2つのコレクションの処理は、いつも同時に進行します。WHERE句で複数の行が戻される場合、全従業員は、その時点でaddr_arrayが参照するアドレスを取得します。

例6および7: 配列の定義

次の例では、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プロシージャ・コールが実行されると、それらの行は指定したPL/SQL表(この場合はn_tab)のDEFINE文で指定した-10から-1の位置に移動します。次に、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;
/

例8: 列の定義の表示

この例は、定義を表示する表に対して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;
/

例9: RETURNING句

RETURNING句が、従来のOracleデータベース・リリースのDML文に追加されました。この句を使用すると、INSERTUPDATEおよびDELETE文は式の値を戻すことができます。この値は、バインド変数に戻されます。

単一行を挿入、更新または削除する場合は、DBMS_SQL.BIND_VARIABLEを使用して、これらのアウトバインドをバインドします。複数行を挿入、更新または削除する場合は、DBMS_SQL.BIND_ARRAYを使用します。これらのバインド変数の値を取得するには、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, c2 NUMBER, r OUT NUMBER) is
      c NUMBER;
      n number;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'delete from tab ' ||
                          '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;
      /

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は、数値を内部的にバインドします。文を実行する回数は、インバインド配列内の要素数によって決まります。


DBMS_SQLサブプログラムの要約

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

サブプログラム 説明

BIND_ARRAYプロシージャ


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

BIND_VARIABLEプロシージャ


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

CLOSE_CURSORプロシージャ


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

COLUMN_VALUEプロシージャ


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

COLUMN_VALUE_LONGプロシージャ


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

DEFINE_ARRAYプロシージャ


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

DEFINE_COLUMNプロシージャ


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

DEFINE_COLUMN_LONGプロシージャ


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

DESCRIBE_COLUMNSプロシージャ


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

DESCRIBE_COLUMNS2プロシージャ


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

EXECUTEファンクション


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

EXECUTE_AND_FETCHファンクション


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

FETCH_ROWSファンクション


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

IS_OPENファンクション


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

LAST_ERROR_POSITIONファンクション


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

LAST_ROW_COUNTファンクション


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

LAST_ROW_IDファンクション


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

LAST_SQL_FUNCTION_CODEファンクション


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

OPEN_CURSORファンクション


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

PARSEプロシージャ


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

VARIABLE_VALUEプロシージャ


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



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

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

パラメータ

表100-2 BIND_ARRAYプロシージャのパラメータ

パラメータ 説明

c

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

name

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

table_variable

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

index1

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

index2

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


使用上の注意

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

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

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

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


関連項目:

コレクションのバインド方法の例は、「例3、4および5: バルクDML」を参照してください。

バルク配列バインド

バルク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;

<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_VARIABLEプロシージャ

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

構文

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          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
VARCHAR2 CHARACTER SET ANY_CS
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);

関連項目:

『Oracle Databaseアプリケーション開発者ガイド-ラージ・オブジェクト』

プラグマ

pragma restrict_references(bind_variable,WNDS);

パラメータ

表100-3 BIND_VARIABLEプロシージャのパラメータ

パラメータ 説明

c

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

name

文内の変数の名前。

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);

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

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

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

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


関連項目:

コレクションのバインド方法の例は、「例3、4および5: バルクDML」を参照してください。

バルク配列バインド

バルク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;

<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

CLOSE_CURSORプロシージャ

このファンクションは、指定のカーソルをクローズします。

構文

DBMS_SQL.CLOSE_CURSOR (
   c    IN OUT INTEGER);

プラグマ

pragma restrict_references(close_cursor,RNDS,WNDS);

パラメータ

表100-4 CLOSE_CURSORプロシージャのパラメータ

パラメータ モード 説明

c

IN

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

c

OUT

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

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



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

<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

注意:

大カッコ[ ]は、オプション・パラメータを示します。


関連項目:

『Oracle Databaseアプリケーション開発者ガイド-ラージ・オブジェクト』

プラグマ

pragma restrict_references(column_value,RNDS,WNDS);

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

DBMS_SQL.COLUMN_VALUE(
   c                 IN  INTEGER,
   position          IN  INTEGER,
   <table_variable>  IN  <datatype>);

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

<bdbl_tab>     Binary_Double_Table
<bflt_tab>     Binary_Float_Table
<bfile_tab>    Bfile_Table
<blob_tab>     Blob_Table
<clob_tab>     Clob_Table
<date_tab>     Date_Table
<ids_tab>      Interval_Day_To_Second_Table
<iym_tab>      Interval_Year_To_Month_Table
<num_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
<urowid_tab>   Urowid_Table
<vchr2_tab>    Varchar2_Table

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]);

パラメータ

表100-5 COLUMN_VALUEプロシージャのパラメータ

パラメータ 説明

c

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

position

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

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

value

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

指定した行番号がフェッチされた行数の合計より大きい場合は、エラー・メッセージが発生します。

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

table_variable

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

column_error

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

actual_length

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


例外

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


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);

パラメータ

表100-6 COLUMN_VALUE_LONGプロシージャのパラメータ

パラメータ 説明

c

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

position

値を取得する列の位置。

length

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

offset

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

value

VARCHAR2の列の値。

value_length

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



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;

<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

構文

DBMS_SQL.DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
   <table_variable>    IN <datatype>
   cnt         IN INTEGER,
   lower_bnd   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

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

プラグマ

pragma restrict_references(define_array,RNDS,WNDS);

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

パラメータ

表100-7 DEFINE_ARRAYプロシージャのパラメータ

パラメータ 説明

c

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

position

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

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

table_variable

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

cnt

フェッチする行数。

lower_bnd

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


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


関連項目:

コレクションの定義方法の例は、「例6および7: 配列の定義」を参照してください。


DEFINE_COLUMNプロシージャ

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

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

構文

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
VARCHAR2 CHARACTER SET ANY_CS
YMINTERVAL_UNCONSTRAINED

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


関連項目:

『Oracle Databaseアプリケーション開発者ガイド-ラージ・オブジェクト』

プラグマ

pragma restrict_references(define_column,RNDS,WNDS);

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

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

CHARRAWおよびROWIDデータを持つ列の定義には、プロシージャ構文で次のバリエーションを使用できます。

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

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

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

パラメータ

表100-8 DEFINE_COLUMNプロシージャのパラメータ

パラメータ 説明

c

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

position

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

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

column

定義している列の値。

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

column_size

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



DEFINE_COLUMN_LONGプロシージャ

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

構文

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

パラメータ

表100-9 DEFINE_COLUMN_LONGプロシージャのパラメータ

パラメータ 説明

c

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

position

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

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



DESCRIBE_COLUMNSプロシージャ

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

構文

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

パラメータ

表100-10 DESCRIBE_COLUMNSプロシージャのパラメータ

パラメータ 説明

c

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

col_cnt

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

desc_t

DESC_RECの表。各DESC_RECに問合せの列の情報が表示されます。



関連項目:

DESCRIBE_COLUMNSの使用方法は、「例8: 列の定義の表示」を参照してください。


DESCRIBE_COLUMNS2プロシージャ

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

構文

DBMS_SQL.DESCRIBE_COLUMNS2 (
   c              IN  INTEGER,
   col_cnt        OUT INTEGER,
   desc_tab2      OUT DESC_TAB);

プラグマ

PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);

パラメータ

表100-11 DESCRIBE_COLUMNS2プロシージャのパラメータ

パラメータ 説明

c

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

col_cnt

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

desc_tab2

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



EXECUTEファンクション

このファンクションは、指定のカーソルを実行します。このファンクションはカーソルのID番号を受け入れて、処理された行数を戻します。戻り値は、INSERT文、UPDATE文およびDELETE文に対してのみ有効で、DDL文を含めた他のタイプの文に対しては、戻り値は定義されず無視されます。

構文

DBMS_SQL.EXECUTE (
   c   IN INTEGER)
  RETURN INTEGER;

パラメータ

表100-12 EXECUTEファンクションのパラメータ

パラメータ 説明

c

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



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);

パラメータ

表100-13 EXECUTE_AND_FETCHファンクションのパラメータ

パラメータ 説明

c

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

exact

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

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

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



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);

パラメータ

表100-14 FETCH_ROWSファンクションのパラメータ

パラメータ 説明

c

ID番号。



IS_OPENファンクション

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

構文

DBMS_SQL.IS_OPEN (
   c              IN INTEGER)
  RETURN BOOLEAN;

プラグマ

pragma restrict_references(is_open,RNDS,WNDS);

パラメータ

表100-15 IS_OPENファンクションのパラメータ

パラメータ 説明

c

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


戻り値

表100-16 IS_OPENファンクションの戻り値

戻り値 説明

TRUE

指定のカーソルは、現在オープンしています。

FALSE

指定のカーソルは、現在オープンしていません。



LAST_ERROR_POSITIONファンクション

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

構文

DBMS_SQL.LAST_ERROR_POSITION
   RETURN INTEGER;

プラグマ

pragma restrict_references(last_error_position,RNDS,WNDS);

使用上の注意

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


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(ゼロ)です。


LAST_ROW_IDファンクション

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

構文

DBMS_SQL.LAST_ROW_ID
   RETURN ROWID;

プラグマ

pragma restrict_references(last_row_id,RNDS,WNDS);

使用上の注意

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


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文の実行直後にコールする必要があります。それ以外の場合にコールすると、戻り値が定義されません。


OPEN_CURSORファンクション

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

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

構文

DBMS_SQL.OPEN_CURSOR
   RETURN INTEGER;

プラグマ

pragma restrict_references(open_cursor,RNDS,WNDS);

戻り値

このファンクションは、新規カーソルのカーソルID番号を戻します。


PARSEプロシージャ

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

PARSEプロシージャには、2つのバージョンがあります。1つは引数としてVARCHAR2文を使用するバージョン、もう1つは引数としてVARCHAR2SVARCHAR2の表)を使用するバージョンです。

構文

DBMS_SQL.PARSE (
   c                  IN   INTEGER,
   statement          IN   VARCHAR2,
   language_flag      IN   INTEGER);

DBMS_SQL.PARSE (
   c                  IN   INTEGER,
   statement          IN   VARCHAR2A,
   lb                 IN   INTEGER,
   ub                 IN   INTEGER,
   lfflg              IN   BOOLEAN,
   language_flag      IN   INTEGER);

PARSEプロシージャは、大規模なSQL文について次の構文もサポートしています。

DBMS_SQL.PARSE (
   c                  IN   INTEGER,
   statement          IN   VARCHAR2S,
   lb                 IN   INTEGER,
   ub                 IN   INTEGER,
   lfflg              IN   BOOLEAN,
   language_flag      IN   INTEGER);

注意:

このプロシージャは、PL/SQL表の文の要素を連結し、その結果の文字列を解析します。このプロシージャを使用すると、文を分割することによって、単一のVARCHAR2変数についての制限を超えた長い文を解析できます。

パラメータ

表100-17 PARSEプロシージャのパラメータ

パラメータ 説明

c

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

statement

解析する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

OracleでSQL文を処理する方法を決定します。次のオプションが認識されます。

  • V6(または0)は、バージョン6の動作を指定します。

  • NATIVE(または1)は、プログラムの接続先のデータベースに関する通常の動作を指定します。

  • V7(または2)は、Oracleデータベース・バージョン7の動作を指定します。


使用上の注意


注意:

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


前述の構文を持つSQL文を解析するためのサイズは、32KBに制限されています。


注意:

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

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

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

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

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


32KBを超えるSQL文を解析するために、DBMS_SQLパッケージは、PL/SQL表を使用して文字列の表をPARSEプロシージャに渡します。これらの文字列は連結された後、Oracleサーバーに渡されます。

ローカル変数をVARCHAR2S表項目型として宣言し、次に、PARSEプロシージャを使用すると、大規模なSQL文をVARCHAR2Sとして解析できます。

VARCHAR2Sデータ型の定義は、次のとおりです。

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

例外

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


VARIABLE_VALUEプロシージャ

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

構文

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

<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

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

DBMS_SQL.VARIABLE_VALUE (
   c                 IN  INTEGER,
   name              IN  VARCHAR2,
   <table_variable>  IN  <datatype>);

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

<bdbl_tab>     Binary_Double_Table
<bflt_tab>     Binary_Float_Table
<bfile_tab>    Bfile_Table
<blob_tab>     Blob_Table
<clob_tab>     Clob_Table
<date_tab>     Date_Table
<ids_tab>      Interval_Day_To_Second_Table
<iym_tab>      Interval_Year_To_Month_Table
<num_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
<urowid_tab>   Urowid_Table
<vchr2_tab>    Varchar2_Table

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);

プラグマ

pragma restrict_references(variable_value,RNDS,WNDS);

パラメータ

表100-18 VARIABLE_VALUEプロシージャのパラメータ

パラメータ 説明

c

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

name

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

value

指定した位置の変数の値を戻します。

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

position

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

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