163 DBMS_SQL
DBMS_SQL
パッケージでは、動的SQLを使用するためのインタフェースを提供し、PL/SQLを使用したデータ操作言語(DML)文やデータ定義言語(DDL)文の解析を可能にします。
たとえば、DBMS_SQL
パッケージが提供するPARSEプロシージャ
を使用することによって、ストアド・プロシージャ内からDROP TABLE
文を入力できます。
この章のトピックは、次のとおりです:
参照:
ネイティブ動的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
163.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
パッケージの間には、いくつか相違点があります。相違点は、次のとおりです。
163.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
エラーが発生します。
チェックが実行されるタイミングを定義するためのメカニズムは、許容値がNULL
、1
および2
の仮パラメータsecurity_level
を取るOPEN_CURSOR
サブプログラムの新しいオーバーロードです。
-
security_level
=1
(またはNULL
)の場合、チェックはバインド時および実行時にのみ行われます。 -
security_level
=2
の場合、チェックは常に行われます。
アップグレードに関する考慮事項
このセキュリティ体制は、以前のリリースよりも厳しくなっています。したがって、DBMS_SQL
を使用すると、アップグレード時にランタイム・エラーが発生する可能性があります。
163.3 DBMS_SQLの定数
DBMS_SQL Constants
パッケージは、PARSEプロシージャ
のlanguage_flag
パラメータとともに使用される定数を提供します。
これらの定数を、次の表に示します。
表163-1 DBMS_SQLの定数
名前 | タイプ | 値 | 説明 |
---|---|---|---|
|
|
|
Oracle Databaseバージョン6の動作を指定します。 |
|
|
|
プログラムの接続先のデータベースの通常の動作を指定します。 |
|
|
2 |
Oracle Databaseバージョン7の動作を指定します。 |
|
|
4294967295 |
Oracle以外のデータベースの構文および動作を指定します。解析するSQL文は、まず、データベース・セッションで設定されたSQL翻訳プロファイルを使用して翻訳する必要があります。SQL翻訳プロファイルは、SQL文をOracleに翻訳する方法を指示するデータベース・スキーマ・オブジェクトです。プロファイルが設定されていない場合、エラーが発生します。 |
関連項目
163.4 DBMS_SQLの操作上のノート
次の操作上のノートは、問合せの処理、更新の処理、使用と削除、およびエラーの特定について説明しています。
問合せの処理
動的SQLを使用して問合せを処理する場合は、次のステップを実行する必要があります。
-
DEFINE_COLUMNプロシージャ
、DEFINE_COLUMN_LONGプロシージャまたはDEFINE_ARRAYプロシージャをコールして、SELECT文が戻す値を受け入れる変数を指定します。 -
EXECUTEファンクションをコールして、
SELECT
文を実行します。 -
FETCH_ROWSファンクション(または
EXECUTE_AND_FETCH
)をコールして、問合せに一致した行を取得します。 -
COLUMN_VALUEプロシージャまたはCOLUMN_VALUE_LONGプロシージャをコールして、問合せに関してFETCH_ROWSファンクションが取得した列の値を判別します。PL/SQLプロシージャへのコールを含んだ無名ブロックを使用した場合は、VARIABLE_VALUEプロシージャをコールして、これらのプロシージャの出力変数に割り当てられた値を取得します。
更新、挿入および削除の処理
動的SQLを使用してINSERT
、UPDATE
またはDELETE
を処理する場合は、次のステップを実行する必要があります。
-
EXECUTEファンクションをコールして、
INSERT
文、UPDATE
文またはDELETE
文を実行します。 -
文に
returning
句がある場合は、VARIABLE_VALUEプロシージャをコールして出力変数に割り当てられた値を取得します。
エラーの位置
DBMS_SQL
パッケージには、セッションで最後に参照されたカーソルの情報を取得するための追加ファンクションがあります。これらのファンクションが戻す値は、SQL文の実行直後にのみ有効です。また、エラーを検出するファンクションは、特定のDBMS_SQL
コール後にのみ意味を持ちます。たとえば、PARSEプロシージャの1つを呼び出した直後には、LAST_ERROR_POSITIONファンクションをコールします。
163.5 DBMS_SQL実行フロー
これらのファンクションは、DBMS_SQL実行フローを構成します。
163.5.1 OPEN_CURSOR
SQL文を処理するためには、オープン・カーソルが必要です。OPEN_CURSORファンクションをコールすると、ユーザーはOracleが保持している有効なカーソルを示すデータ構造のカーソルID
番号を受け取ります。
これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQL
パッケージでのみ使用されます。
関連項目
163.5.2 PARSE
SQL文はすべて、PARSEプロシージャをコールして解析する必要があります。文を解析することによって、その文の構文がチェックされ、プログラム内のカーソルに関連付けられます。
DML文またはDDL文はすべて解析できます。DDL文は解析時に実行され、暗黙のコミットを実行します。
図163-1に、DBMS_SQL
の実行フローを示します。
関連項目
163.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専用のデータ・タイプであってはなりません。
163.5.4 DEFINE_COLUMN、DEFINE_COLUMN_LONG、またはDEFINE_ARRAY
DEFINE_COLUMN
、DEFINE_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
をコールする必要があります。
163.5.6 FETCH_ROWSまたはEXECUTE_AND_FETCH
FETCH_ROWSファンクションで問合せを満たす行を取得します。フェッチで行を取得できなくなるまで、フェッチを連続実行して別の行を取得します。単一の実行に対してEXECUTEをコールしている場合は、EXECUTEファンクション
をコールして次にFETCH_ROWSをコールするかわりに、EXECUTE_AND_FETCHファンクション
をコールするほうが効率的な場合があります。
163.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プロシージャを使用します。列値へのオフセット(バイト単位)とフェッチするバイト数を指定できます。
163.6 DBMS_SQLの例外
この例外は、指定したOUT
パラメータ(要求した値を設定するパラメータ)のタイプがその値のタイプと異なる場合、COLUMN_VALUEプロシージャ
またはVARIABLE_VALUEプロシージャ
で発生します。
inconsistent_type EXCEPTION; pragma exception_init(inconsistent_type, -6562);
163.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句
この句を使用すると、INSERT
、UPDATE
および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_Table
のDBMS_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; /
163.8 DBMS_SQLのデータ構造
DBMS_SQL
パッケージは、RECORD
タイプおよびTABLE
タイプのデータ構造を定義します。
レコード・タイプ
DESCRIBE_COLUMNSプロシージャの表タイプ
スカラーおよびLOBのコレクションの表タイプ
DBMS_SQLバルク操作は、これらの事前定義済DBMS_SQL表タイプでのみサポートされています。
163.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;
フィールド
表163-2 DESC_RECのフィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列の文字セットID。 |
|
列の文字セット形式。 |
|
|
163.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);
フィールド
表163-3 DESC_REC2のフィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列の文字セットID。 |
|
列の文字セット形式。 |
|
|
163.8.3 DBMS_SQL DESC_REC3レコード・タイプ
DESC_REC3
は、DESC_TAB3
表タイプおよびDESCRIBE_COLUMNS3プロシージャ
の要素タイプです。
DESC_REC3
はDESC_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);
フィールド
表163-4 DESC_REC3のフィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列の文字セットID。 |
|
列の文字セット形式。 |
|
|
|
ユーザー定義タイプの列タイプ名。このフィールドは、 |
|
ユーザー定義タイプの列タイプ名の長さ。このフィールドは、 |
163.8.4 DBMS_SQL DESC_REC4レコード・タイプ
DESC_REC4
は、DESC_TAB4
表タイプおよびDESCRIBE_COLUMNS3プロシージャの要素タイプです。
DESC_REC4
はDESC_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言語リファレンス』を参照してください。フィールド
表163-5 DESC_REC4のフィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列の文字セットID。 |
|
列の文字セット形式。 |
|
|
|
ユーザー定義タイプの列タイプ名。このフィールドは、 |
|
ユーザー定義タイプの列タイプ名の長さ。このフィールドは、 |
163.8.5 DBMS_SQL BFILE_TABLE表タイプ
これは、BFILE
の表です。
構文
TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;
163.8.6 DBMS_SQL BINARY_DOUBLE_TABLE表タイプ
これは、BINARY_DOUBLE
の表です。
構文
TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
163.8.7 DBMS_SQL BINARY_FLOAT_TABLE表タイプ
これは、BINARY_FLOAT
の表です。
構文
TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
163.8.8 DBMS_SQL BLOB_TABLE表タイプ
これは、BLOB
の表です。
構文
TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
163.8.9 DBMS_SQL CLOB_TABLE表タイプ
これは、CLOB
の表です。
構文
TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
163.8.10 DBMS_SQL DATE_TABLE表タイプ
これは、DATE
の表です。
構文
type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
163.8.11 DBMS_SQL DESC_TAB表タイプ
これは、DESC_RECレコード・タイプ
の表です。
構文
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;
163.8.12 DBMS_SQL DESC_TAB2表タイプ
これは、DESC_REC2レコード・タイプ
の表です。
構文
TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;
163.8.13 DBMS_SQL DESC_TAB3表タイプ
これは、DESC_REC3
レコード・タイプの表です。
構文
TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER;
163.8.14 DBMS_SQL DESC_TAB4表タイプ
これは、DBMS_SQL DESC_REC4レコード・タイプの表です。
構文
TYPE DESC_TAB4 IS TABLE OF DESC_REC4 INDEX BY BINARY_INTEGER;
163.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;
163.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;
163.8.17 DBMS_SQL NUMBER_TABLE表タイプ
これは、NUMBER
の表です。
構文
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
163.8.18 DBMS_SQL TIME_TABLE表タイプ
これは、TIME_UNCONSTRAINED
の表です。
構文
TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;
163.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;;
163.8.20 DBMS_SQL TIMESTAMP_TABLE表タイプ
これは、TIMESTAMP_UNCONSTRAINED
の表です。
構文
TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;
163.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;
163.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;
163.8.23 DBMS_SQL UROWID_TABLE表タイプ
これは、UROWID
の表です。
構文
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
163.8.24 DBMS_SQL VARCHAR2_TABLE表タイプ
これは、VARCHAR2(2000)
の表です。
構文
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
163.8.25 DBMS_SQL VARCHAR2A表タイプ
これは、VARCHAR2(32767)
の表です。
構文
TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
163.8.26 DBMS_SQL VARCHAR2S表タイプ
これは、VARCHAR2(256)
の表です。
ノート:
このタイプは、VARCHAR2A表タイプによって置き換えられています。このタイプは、レガシー・コードの下位互換性を維持する目的で現在保持されていますが、非推奨となる予定であり、将来のリリースではサポートされなくなります。
構文
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
163.9 DBMS_SQLサブプログラムの要約
この表は、DBMS_SQL
サブプログラムを示し、簡単に説明しています。
表163-6 DBMS_SQLパッケージのサブプログラム
サブプログラム | 説明 |
---|---|
指定の値を指定のコレクションにバインドします。 |
|
指定の値を指定の変数にバインドします。 |
|
指定の値を指定のパッケージ変数にバインドします。 |
|
指定したカーソルをクローズして、メモリーを解放します。 |
|
カーソル内の指定位置にあるカーソル要素の値を戻します。 |
|
|
|
指定したカーソルから選択するコレクションを定義します。 |
|
指定したカーソルから選択する列を定義します。 |
|
指定したカーソルから選択するタイプ |
|
指定したカーソルから選択する |
|
指定したカーソルから選択するタイプ |
|
指定したカーソルから選択するタイプ |
|
|
|
指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。 |
|
指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。 |
|
指定のカーソルを実行します。 |
|
指定のカーソルを実行して、行をフェッチします。 |
|
指定のカーソルから行をフェッチします。 |
|
再帰文のコール元に戻される次の結果の文を取得するか、このコール元が自らを再帰文のクライアントとして設定している場合、クライアントとしてこのコール元に戻される次の結果を取得します。 |
|
指定のカーソルがオープンの場合に |
|
エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。 |
|
フェッチされた累積行数を戻します。 |
|
最後に処理された行の |
|
文のSQL機能コードを戻します。 |
|
新規カーソルのID番号を戻します。 |
|
指定した文を解析します。 |
|
実行した文の結果をクライアント・アプリケーションに戻します。 |
|
強くタイプ付けされたREFカーソルまたは弱くタイプ付けされたREFカーソルのうち |
|
|
|
指定のカーソルについて指定の変数の値を戻します。 |
|
指定のカーソルについて指定の変数の値を戻します。これは、宣言されたパッケージに対して、returning句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。変数の型は、パッケージ仕様部で宣言する必要があります。 |
163.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
プロシージャは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表163-7 BIND_ARRAYプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値をバインドするカーソルのID番号。 |
|
文内のコレクションの名前。 |
|
< |
|
範囲の下限を示す表要素の索引。 |
|
範囲の上限を示す表要素の索引。 |
使用上のノート
範囲をバインドするためには、範囲を指定する要素(タブ(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;
例163-1 バルクDMLの使用例
次の一連の例では、INSERT
、UPDATE
および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
コールで指定したとおりに使用されます。配列の各要素は、大量の従業員をデータベースから削除する可能性があります。
163.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]);
CHAR
、RAW
および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);
パラメータ
表163-8 BIND_VARIABLEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値をバインドするカーソルのID番号。 |
|
文内の変数の名前。 バインド変数名の長さは<=30バイトにする必要があります。 |
|
カーソル内の変数にバインドする値。
|
|
サイズの指定がない場合は、現行値の長さが使用されます。このパラメータは、 |
使用上のノート
この変数が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);
163.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
表163-9 BIND_VARIABLE_PKGのパラメータ
パラメータ | 説明 |
---|---|
|
値を取得するカーソルのID番号。 |
|
値を取得する文の変数の名前。 |
|
|
例163-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
163.9.4 CLOSE_CURSORプロシージャ
このプロシージャは、指定のカーソルをクローズします。
構文
DBMS_SQL.CLOSE_CURSOR ( c IN OUT INTEGER);
プラグマ
pragma restrict_references(close_cursor,RNDS,WNDS);
パラメータ
表163-10 CLOSE_CURSORプロシージャのパラメータ
パラメータ | モード | 説明 |
---|---|---|
|
|
クローズするカーソルのID番号。 |
|
|
カーソルはNULLに設定されています。
|
163.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
CHAR
、RAW
および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);
パラメータ
表163-11 COLUMN_VALUEプロシージャのパラメータ(単一行)
パラメータ | 説明 |
---|---|
|
値をフェッチするカーソルのID番号。 |
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
指定した列の値を戻します。 この出力パラメータのタイプが、 |
|
指定した列値のエラー・コードを戻します。 |
|
指定した列内の値の(切捨て前の)実際の長さ。 |
表163-12 COLUMN_VALUEプロシージャのパラメータ(バルク)
パラメータ | 説明 |
---|---|
|
値をフェッチするカーソルのID番号。 |
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
< バルク操作では、サブプログラムによって、(暗黙的に保持される)適切な索引に新しい要素が追加されます。たとえば、DEFINE_ARRAYプロシージャの使用時にバッチ・サイズ( |
例外
指定したOUT
パラメータのvalue
が、実際の値のタイプと異なる場合は、INCONSISTENT_TYPE
(ORA
-06562
)が発生します。このタイプは、DEFINE_COLUMN
プロシージャをコールして列を定義したときに指定したタイプです。
163.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);
パラメータ
表163-13 COLUMN_VALUE_LONGプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値を取得するカーソルのカーソルID番号。 |
|
値を取得する列の位置。 |
|
フェッチするLONG値のバイト数。 |
|
フェッチを開始するためのLONGフィールドへのオフセット。 |
|
|
|
値に実際に戻されるバイト数。 |
163.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_bnd
、lower_bnd
+1、lower_bnd
+2のように配置されます。行が送られてくる間、ユーザーはFETCH_ROWS
コールまたはCOLUMN_VALUE
コールを継続して発行します。行は、COLUMN_VALUE
コールに引数として指定した表内に蓄積されます。
パラメータ
表163-14 DEFINE_ARRAYプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
配列をバインドするカーソルのID番号。 |
|
定義している配列内にある列の相対位置。 文の最初の列は位置1です。 |
|
< |
|
フェッチする行数。 |
|
結果のコレクションへのコピーは、この下限の索引から開始されます。 |
使用上のノート
行数(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; /
例163-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; /
163.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);
パラメータ
表163-15 DEFINE_COLUMNプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。文の最初の列は位置1です。 |
|
定義している列の値。この値のタイプによって、定義している列のタイプが決まります。 |
|
タイプ |
使用上のノート
文字長セマンティクスを使用するとき、タイプVARCHAR2
の列値に戻される最大バイト数は、column_size
*
現在の文字セットの最大文字バイト数で求められます。たとえば、column_size
を10に指定すると、UTF8文字セットで文字長セマンティクスを使用するとき、これが表す文字数に関係なく、最大で30(10*3)バイトが戻されます。
163.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);
パラメータ
表163-16 DEFINE_COLUMN_CHARプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。文の最初の列は位置1です。 |
|
定義している列の値。この値のタイプによって、定義している列のタイプが決まります。 |
|
タイプ |
163.9.10 DEFINE_COLUMN_LONGプロシージャ
このプロシージャは、SELECT
カーソルに対してLONG
列を定義します。定義されている列は、指定のカーソルの文のSELECT
リスト内での相対位置によって識別されます。COLUMN
値のタイプによって、定義されている列のタイプが決まります。
「DEFINE_COLUMNプロシージャ」、「DEFINE_COLUMN_CHARプロシージャ」、「DEFINE_COLUMN_RAWプロシージャ」および「DEFINE_COLUMN_ROWIDプロシージャ」も参照してください。
構文
DBMS_SQL.DEFINE_COLUMN_LONG ( c IN INTEGER, position IN INTEGER);
パラメータ
表163-17 DEFINE_COLUMN_LONGプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。 文の最初の列は位置1です。 |
163.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);
パラメータ
表163-18 DEFINE_COLUMN_RAWプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。文の最初の列は位置1です。 |
|
定義している列の値。この値のタイプによって、定義している列のタイプが決まります。 |
|
|
163.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);
パラメータ
表163-19 DEFINE_COLUMN_ROWIDプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。文の最初の列は位置1です。 |
|
定義している列の値。この値のタイプによって、定義している列のタイプが決まります。 |
163.9.13 DESCRIBE_COLUMNSプロシージャ
このプロシージャは、DBMS_SQL
によってオープンされ解析されたカーソルの列の情報を表示します。
構文
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB);
パラメータ
表163-20 DESCRIBE_COLUMNSプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。 |
例163-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;
/
163.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);
パラメータ
表163-21 DESCRIBE_COLUMNS2プロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。 |
163.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);
パラメータ
表163-22 DESCRIBE_COLUMNS3プロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。この表は、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
163.9.16 EXECUTEファンクション
このファンクションは、指定のカーソルを実行します。このファンクションはカーソルのID
番号を受け入れて、処理された行数を戻します。
戻り値は、INSERT
文、UPDATE
文およびDELETE
文に対してのみ有効で、DDL文を含めた他のタイプの文に対しては、戻り値は定義されてなく、無視する必要があります。
構文
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;
パラメータ
表163-23 EXECUTEファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
実行するカーソルのカーソルID番号。 |
戻り値
処理された行数を戻します。
使用上のノート
TO_CURSOR_NUMBERファンクション
により戻されるDBMS_SQLカーソルは、すでに実行されているDBMS_SQL
カーソルと同じ方法で実行されます。したがって、このカーソルにEXECUTE
をコールするとエラーが発生します。
163.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);
パラメータ
表163-24 EXECUTE_AND_FETCHファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
実行してフェッチするカーソルのカーソルID番号。 |
|
問合せで実際に一致する行数が1以外の場合は、 ノート: LONG列に対して、exactパラメータをTRUEに設定するオプションはサポートされていません。 例外が発生しても、行はフェッチされ、使用可能です。 |
戻り値
指定された行を戻します。
163.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);
パラメータ
表163-25 FETCH_ROWSファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
ID番号。 |
戻り値
指定のカーソルから行を戻します。
163.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);
パラメータ
表163-26 GET_NEXT_RESULTプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
再帰文カーソル |
|
次に戻される結果の文のカーソルまたは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;
163.9.20 IS_OPENファンクション
このファンクションは、指定のカーソルが現在オープンしているかどうかをチェックします。
構文
DBMS_SQL.IS_OPEN ( c IN INTEGER) RETURN BOOLEAN;
プラグマ
pragma restrict_references(is_open,RNDS,WNDS);
パラメータ
表163-27 IS_OPENファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
チェックするカーソルのカーソルID番号。 |
戻り値
オープンされ、クローズされていないカーソル番号に対してはTRUE
、NULL
のカーソル番号に対してはFALSE
を戻します。CLOSE_CURSORプロシージャは、渡されたカーソル変数をNULL
にすることに注意してください。
例外
ORA-29471 DBMS_SQLアクセスが拒否されました:
これは、無効なカーソルID番号が検出された場合に発生します。セッションでこのエラーが発生し、レポートされると、その後は同じセッションでDBMS_SQLをコールするたびにこのエラーが発生しますが、これは、このセッションではDBMS_SQLを実行できなくなることを意味します。
163.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
のコール後にコールしてください。
163.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(ゼロ)です。
163.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
コール後にコールしてください。
163.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文の実行直後にコールする必要があります。それ以外の場合は、戻り値が定義されません。
163.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;
パラメータ
表163-28 OPEN_CURSORファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
オープンされたカーソルに適用するセキュリティ保護のレベルを指定します。有効なセキュリティ・レベル値は、
|
|
再帰文のコール元が自らをクライアントに設定し、再帰文からクライアントに戻される文の結果を受信することを許可します。戻される文の結果は、GET_NEXT_RESULTプロシージャによって取得できます。 |
プラグマ
pragma restrict_references(open_cursor,RNDS,WNDS);
戻り値
新規カーソルのID番号を戻します。
使用上のノート
-
このカーソルが不要になった場合は、CLOSE_CURSORプロシージャをコールして、明示的にクローズする必要があります。
-
カーソルを使用すると、同じSQL文を繰り返し実行したり、新規のSQL文を実行することができます。カーソルを再使用した場合、新規のSQL文が解析されるときに、対応するカーソル・データ領域の内容がリセットされます。カーソルを再使用しないかぎり、クローズして再オープンする必要はありません。
163.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)];
パラメータ
表163-29 PARSEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
文を解析するカーソルのID番号。 |
|
解析するSQL文。32Kを超えて PL/SQL文と異なり、SQL文の終わりにはセミコロンを含めないでください。たとえば:
|
|
文内の要素の下限。 |
|
文内の要素の上限。 |
|
|
|
SQL文の動作を指定します。設定可能な値およびそれに対応する動作の詳細は、「DBMS_SQLの定数」を参照してください。 |
|
次に示す条件のもとで、文を実行するエディションを指定します。
次の一般的な条件が適用されます。文字列の内容はSQL識別子として処理され、実際のエディション名に特殊文字または小文字が含まれている場合は、残りの文字列を二重引用符で囲む必要があります(二重引用符を使用しない場合、内容は大文字になります)。 |
|
指定したSQLに適用する転送crosseditionトリガーの未修飾の名前を指定します。この名前は、文が実行されるエディションおよび |
|
指定した |
|
未修飾のオブジェクト名を解決するスキーマを指定します。 |
|
カーソルを実行するターゲット・コンテナの名前。 |
使用上のノート
-
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文を解析する場合、
VARCHAR2A
wオーバーロードのかわりに、新しいCLOB
オーバーロード・バージョンのPARSEプロシージャを使用できます。 -
container
パラメータの値がコール側コンテナと同じである場合、コンテナの切替えは行われません。ただし、現行ユーザーのデフォルト・ロールは有効になります。
例外
コンパイルに関する警告を伴ったDBMS_SQL
を使用して、タイプ、プロシージャ、ファンクション、またはパッケージを作成する場合、ORA-24344
例外が発生しますが、PL/SQLユニットはそのまま作成されます。
163.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);
パラメータ
表163-30 RETURN_RESULTプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
文カーソルまたはREFカーソル |
|
文の結果をクライアントに戻します(または戻しません)。戻さない場合、直接のコール元に戻されます。 |
使用上のノート
-
現在、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; /
163.9.28 TO_CURSOR_NUMBERファンクション
このファンクションは、強くタイプ付けされたREFカーソルまたは弱くタイプ付けされたREFカーソルのうちOPEN
が実行されたカーソルを使用して、そのカーソルをDBMS_SQL
カーソル番号に変換します。
構文
DBMS_SQL.TO_CURSOR_NUMBER( rc IN OUT SYS_REFCURSOR) RETURN INTEGER;
パラメータ
表163-31 TO_CURSOR_NUMBERファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
カーソル番号に変換される |
戻り値
REF
CURSOR
から変換された、DBMS_SQLで管理可能なカーソル番号を戻します。
使用上のノート
-
渡される
REF
CURSOR
には、OPEN
を実行しておく必要があります。実行しておかない場合、エラーが発生します。 -
REF
CURSOR
がDBMS_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; /
163.9.29 TO_REFCURSORファンクション
このファンクションは、OPEN
、PARSE
およびEXECUTE
が実行されたカーソルを使用して、そのカーソルをPL/SQLで管理可能なREF CURSOR
(弱くタイプ付けされたカーソル)に変換/移行します(変換/移行後のカーソルは、システム固有の動的SQLを使用するように切り替えられているPL/SQLシステム固有の動的SQLで消費できます)。
このサブプログラムが使用できるのは、SELECT
カーソルのみです。
構文
DBMS_SQL.TO_REFCURSOR( cursor_number IN OUT INTEGER) RETURN SYS_REFCURSOR;
パラメータ
表163-32 TO_REFCURSORファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
|
戻り値
DBMS_SQL
カーソル番号から変換されたPL/SQL REF
CURSOR
を戻します。
使用上のノート
-
cursor_number
によって渡されるカーソルには、OPEN
、PARSE
およびEXECUTE
を実行しておく必要があります。実行しておかない場合、エラーが発生します。 -
cursor_numberが
REF
CURSOR
に変換されると、cursor_number
はDBMS_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; /
163.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
CHAR
、RAW
および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事前定義済表タイプである必要があります。
プラグマ
pragma restrict_references(variable_value,RNDS,WNDS);
パラメータ
表163-33 VARIABLE_VALUEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値を取得するカーソルのID番号。 |
|
取得した値を代入する変数名。 |
|
|
163.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
パラメータ
表163-34 VARIABLE_VALUE_PKGのパラメータ
パラメータ | 説明 |
---|---|
|
値を取得するカーソルのID番号。 |
|
取得した値を代入する変数名。 |
|
|
例163-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