| Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11g リリース1（11.1） E05686-02 |
|
 戻る |
 次へ |
DBMS_SQLパッケージでは、動的SQLを使用するためのインタフェースを提供し、PL/SQLを使用したデータ操作言語（DML）文やデータ定義言語（DDL）文の解析を可能にします。 たとえば、DBMS_SQLパッケージが提供するPARSEプロシージャを使用することによって、ストアド・プロシージャ内からDROP TABLE文を入力できます。
|
関連項目: ネイティブ動的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
この章では、次の項目について説明します。
概要
セキュリティ・モデル
定数
例外
使用上の注意
例
レコード・タイプ
表タイプ
Oracleによって、ユーザーは動的SQLを使用するストアド・プロシージャと無名PL/SQLブロックを記述できます。動的SQL文は、ユーザーのソース・プログラムに埋め込まれていません。実行時にプログラムに入力されるか、またはプログラムによって作成されるように、文字列で格納されています。これによって、ユーザーは用途の広いプロシージャを作成できます。たとえば、この動的SQLによって、実行時まで名前がわからない表で動作するプロシージャを作成できます。
ネイティブ動的SQLはDBMS_SQLに代わるものであり、動的SQL文をPL/SQLブロックに直接設定できます。 ほとんどの場合、ネイティブ動的SQLは、DBMS_SQLと比べると簡単に使用でき、より高パフォーマンスです。ただし、ネイティブ動的SQL自体に、次の制限があります。
メソッド4（不明数の入力または出力を含む動的SQL文）はサポートされていません。
また、一部のタスクはDBMS_SQLでのみ実行できます。
ストアド・プロシージャ内から動的SQLを使用する機能は一般的に、Oracle Call Interface（OCI）の手順に従っています。
|
関連項目: 『Oracle Call Interfaceプログラマーズ・ガイド』 |
PL/SQLは、Cなどの他の一般的なプログラム言語とは、多少異なります。たとえば、ユーザーはアドレス（ポインタとも呼ばれます）をPL/SQLで参照できません。 そのため、Oracle Call InterfaceとDBMS_SQLパッケージの間には、いくつか相違点があります。相違点は、次のとおりです。
OCIはアドレスによるバインドを使用するのに対して、DBMS_SQLパッケージは値によるバインドを使用します。
DBMS_SQLでは、無名ブロックのOUTパラメータの値を取得するためにVARIABLE_VALUEをコールする必要があります。また、行をフェッチして、行内の列の値をプログラムに実際に取得した後で、COLUMN_VALUEをコールする必要があります。
現行のリリースのDBMS_SQLパッケージは、CANCELカーソル・プロシージャを提供していません。
NULLはPL/SQL変数の値として完全にサポートされているため、インジケータ変数は不要です。
DBMS_SQLパッケージの使用例は、次のとおりです。このコードは、Oracle Call Interfaceのユーザーにとってはかなり簡潔です。
DBMS_SQLは、SYS所有のパッケージであり、AUTHID CURRENT_USERでコンパイルされます。 無名PL/SQLブロックからコールされたすべてのDBMS_SQLサブプログラムは、現行のユーザーの権限を使用して実行されます。
|
関連項目: 実行者権限または定義者権限の使用方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
Oracle Database 11gリリース1（11.1）では、パッケージのセキュリティを強化するために多くの機能拡張がDBMS_SQLに導入されています。
オープン・カーソル番号による悪意のあるアクセスまたは誤ったアクセスの防止
オープン・カーソルを示していないカーソル番号を使用してDBMS_SQLサブプログラムをコールすると、エラーORA-29471が発生します。 このエラーが発生すると、アラート・ログにアラートが発行され、セッションが存続している間DBMS_SQLは操作不能になります。
IS_OPENファンクションに対するコール内のカーソル番号の実際の値が、セッション内で現在オープンされているカーソルを示している場合、戻り値はTRUEになります。実際の値がNULLの場合、戻り値はFALSEになります。 これら以外の場合は、ORA-29471エラーが発生します。
OPEN_CURSORファンクションは、カーソル番号の仮パラメータを持たない唯一のDBMS_SQLサブプログラムであり、カーソル番号を戻します。したがって、これらのルールの範囲外です。
カーソルの不適切な使用の防止
カーソルは、既存のカーソルを破壊するセキュリティ侵害から保護されています。
バインド時および実行時にチェックが行われます。 チェックは、必要に応じてDBMS_SQLサブプログラムをコールするたびに実行することもできます。次のことがチェックされます。
current_userが、サブプログラムをコールした時点と最新の解析をコールした時点で同じであること。
サブプログラムをコールした時点で使用可能なロールが、最新の解析をコールした時点で使用可能なロールのスーパーセットと同じであること。
定義者の権限サブプログラムを使用した場合との整合性を維持するために、ロールは適用されません。
いずれかのチェックが正常に実行されなかった場合は、ORA-29470エラーが発生します。
チェックが実行されるタイミングを定義するためのメカニズムは、許容値がNULL、1および2の仮パラメータsecurity_levelを取るOPEN_CURSORサブプログラムの新しいオーバーロードです。
security_level = 1（またはNULL）の場合、チェックはバインド時および実行時にのみ行われます。
security_level = 2の場合、チェックは常に行われます。
アップグレードに関する注意事項
前述のセキュリティ体制は、Oracle Database 10gリリース2（10.2）以前のリリースと比較すると厳しくなっています。 したがって、DBMS_SQLを使用している場合、アップグレード時にランタイム・エラーが発生する可能性があります。前述のセキュリティ拡張機能によってアプリケーションのセキュリティが向上されているため、Oracle Database 11gリリース1（11.1）に移行する際にセキュリティ・チェックを一時的に緩和する必要がある場合があります。この場合は、前述のセキュリティ制限を緩和する手順について、Oracleサポート・サービスに問い合せてください。
表122-1に示す定数は、PARSEプロシージャのlanguage_flagパラメータとともに使用します。
表122-1 DBMS_SQLの定数
| 名前 | 型 | 値 | 説明 |
|---|---|---|---|
|
|
|
|
Oracle Databaseバージョン6の動作を指定します。 |
|
NATIVE |
|
|
プログラムの接続先のデータベースの通常の動作を指定します。 |
|
|
|
2 |
Oracle Databaseバージョン7の動作を指定します。 |
inconsistent_type EXCEPTION; pragma exception_init(inconsistent_type, -6562);
この例外は、指定したOUTパラメータ（要求した値を設定するパラメータ）のタイプがその値のタイプと異なる場合、COLUMN_VALUEプロシージャまたはVARIABLE_VALUEプロシージャで発生します。
SQL文を処理するためには、オープン・カーソルが必要です。 OPEN_CURSORファンクションをコールすると、ユーザーはOracleが保持している有効なカーソルを示すデータ構造のカーソルID番号を受け取ります。 これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQLパッケージでのみ使用されます。
SQL文はすべて、PARSEプロシージャをコールして解析する必要があります。文を解析することによって、その文の構文がチェックされ、プログラム内のカーソルに関連付けられます。
DML文またはDDL文はすべて解析できます。DDL文は解析時に実行され、暗黙のコミットを実行します。
|
注意: パッケージやプロシージャを削除するためにDDL文を解析するときに、パッケージ内のプロシージャが使用中の場合は、デッドロックが起こる可能性があります。プロシージャへのコール後は、実行がユーザー側に戻されるまで、そのプロシージャは使用中であるとみなされます。このようなデッドロックは、5分後にタイムアウトします。 |
図122-1に、DBMS_SQL実行フローを示します。
多くのDML文では、プログラム内のデータをOracleに入力することが必要です。実行時に提供する入力データを含んでいるSQL文を定義する場合は、SQL文内のプレースホルダを使用して、データの提供場所にマークを付ける必要があります。
SQL文内の各プレースホルダに対してバインド・プロシージャ（BIND_ARRAYプロシージャまたはBIND_VARIABLEプロシージャ）の1つをコールして、プログラム内の変数の値（または配列の値）をプレースホルダに提供する必要があります。SQL文が引き続き実行されると、Oracleは、ユーザーのプログラムが入力変数と出力変数、またはバインド変数に設定したデータを使用します。
DBMS_SQLは、その都度異なるバインド変数を使用してDML文を繰り返し実行できます。 BIND_ARRAYプロシージャを使用すると、スカラーのコレクションをバインドでき、それぞれの値はEXECUTEごとに1回だけ入力変数として使用されます。これは、OCIがサポートする配列インタフェースに類似しています。
SELECT文内で選択されている行の列は、選択リスト内での相対位置（左から右）によって識別されます。 問合せの場合は、定義プロシージャの1つ（DEFINE_COLUMN、DEFINE_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ファンクションをコールして、SQL文を実行します。
FETCH_ROWSファンクションは、問合せを満たす行を取得します。フェッチで行を取得できなくなるまで、フェッチを連続実行して別の行を取得します。 1回のみの実行に対してEXECUTEをコールしている場合は、EXECUTEの次にFETCH_ROWSをコールするより、EXECUTE_AND_FETCHをコールする方が効率的です。
問合せの場合は、COLUMN_VALUEをコールして、FETCH_ROWSコールによって取得する列の値を判別します。 returning句によるPL/SQLプロシージャまたはDML文へのコールを含んだ無名ブロックの場合は、VARIABLE_VALUEをコールして、文の実行時に出力変数に割り当てられた値を取得します。
LONGデータベース列（サイズは最大2GBまで可能）の一部のみをフェッチするには、COLUMN_VALUE_LONGプロシージャを使用します。列値へのオフセット（バイト単位）とフェッチするバイト数を指定できます。
セッションでカーソルが不要な場合は、CLOSE_CURSORをコールしてカーソルをクローズします。Oracle Open Gatewayを使用している場合は、これ以外のときにもカーソルのクローズが必要になる場合があります。追加情報は、Oracle Open Gatewayの関連文書を参照してください。
カーソルをクローズしないと、カーソルが不要になっても、そのカーソルが使用しているメモリーは割り当てられたままになります。
動的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直後に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;
このようなプロシージャを作成すると、次の操作を実行できます。
コール元のプログラムによって、実行時にSQL文を動的に生成できます。
SQL文は、DDL文またはバインドなしのDMLでかまいません。
たとえば、このプロシージャの作成後に、次のコールを行うことができます。
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
次の一連の例では、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コールで指定したとおりに使用されます。配列の各要素は、大量の従業員をデータベースから削除する可能性があります。
例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 DatabaseリリースのDML文に追加されました。この句を使用すると、INSERT、UPDATEおよび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_TableのDBMS_SQL.BIND_ARRAYは、数値を内部的にバインドします。文を実行する回数は、インバインド配列内の要素数によって決まります。 |
例10: 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;
/
DBMS_SQLパッケージは、次のRECORDタイプおよびTABLEタイプを定義します。
レコード・タイプ
表タイプ
このレコード・タイプは、動的問合せ内に単一列の記述情報を保持します。 このレコード・タイプは、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;
フィールド
表122-2 DESC_RECのフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ。 |
|
|
列の最大長。 |
|
col_name |
列の名前。 |
|
col_name_len |
列名の長さ。 |
|
|
列のスキーマ名。 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度。 |
|
|
列のスケール。 |
|
|
列のキャラクタ・セットID。 |
|
col_charsetform |
列のキャラクタ・セット形式。 |
|
|
|
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);
フィールド
表122-3 DESC_REC2のフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ。 |
|
|
列の最大長。 |
|
col_name |
列の名前。 |
|
col_name_len |
列名の長さ。 |
|
|
列のスキーマ名。 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度。 |
|
|
列のスケール。 |
|
|
列のキャラクタ・セットID。 |
|
col_charsetform |
列のキャラクタ・セット形式。 |
|
|
|
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);
フィールド
表122-4 DESC_REC3のフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ。 |
|
|
列の最大長。 |
|
|
列の名前。 |
|
col_name_len |
列名の長さ。 |
|
|
列のスキーマ名。 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度。 |
|
|
列のスケール。 |
|
|
列のキャラクタ・セットID。 |
|
col_charsetform |
列のキャラクタ・セット形式。 |
|
|
|
|
|
ユーザー定義タイプの列タイプ名。このフィールドは、 |
|
|
ユーザー定義タイプの列タイプ名の長さ。このフィールドは、 |
これは、BFILEの表です。
構文
TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;
これは、BINARY_DOUBLEの表です。
構文
TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
これは、BINARY_FLOATの表です。
構文
TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
これは、BLOBの表です。
構文
TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
これは、CLOBの表です。
構文
TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
これは、DATEの表です。
構文
type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
これは、DESC_RECレコード・タイプの表です。
構文
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;
これは、DESC_REC2レコード・タイプの表です。
構文
TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;
これは、DESC_REC3レコード・タイプの表です。
構文
TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER;
これは、DSINTERVAL_UNCONSTRAINEDの表です。
構文
TYPE interval_day_to_second_Table IS TABLE OF
DSINTERVAL_UNCONSTRAINED INDEX BY binary_integer;
これは、YMINTERVAL_UNCONSTRAINEDの表です。
構文
TYPE interval_year_to_month_table IS TABLE OF YMINTERVAL_UNCONSTRAINED INDEX BY BINARY_INTEGER;
これは、NUMBERの表です。
構文
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
これは、TIME_UNCONSTRAINEDの表です。
構文
TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;
これは、TIME_TZ_UNCONSTRAINEDの表です。
構文
TYPE time_with_time_zone_table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY BINARY_INTEGER;;
これは、TIMESTAMP_UNCONSTRAINEDの表です。
構文
TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;
これは、TIMESTAMP_LTZ_UNCONSTRAINEDの表です。
構文
TYPE timestamp_with_ltz_table IS TABLE OF
TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer;
これは、TIMESTAMP_TZ_UNCONSTRAINEDの表です。
構文
TYPE timestamp_with_time_zone_Table IS TABLE OF
TIMESTAMP_TZ_UNCONSTRAINED INDEX BY binary_integer;
これは、UROWIDの表です。
構文
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
これは、VARCHAR2(2000)の表です。
構文
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
これは、VARCHAR2(32767)の表です。
構文
TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
これは、VARCHAR2(256)の表です。
|
注意: このタイプは、VARCHAR2A表タイプによって置き換えられています。このタイプは、レガシー・コードの下位互換性を維持する目的で現在保持されていますが、非推奨となる予定であり、将来のリリースではサポートされなくなります。 |
構文
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
表122-5 DBMS_SQLパッケージのサブプログラム
| サブプログラム | 説明 |
|---|---|
|
|
指定の値を指定のコレクションにバインドします。 |
|
|
指定の値を指定の変数にバインドします。 |
|
|
指定したカーソルをクローズして、メモリーを解放します。 |
|
|
カーソル内の指定位置にあるカーソル要素の値を戻します。 |
|
|
|
|
|
指定したカーソルから選択するコレクションを定義します。 |
|
|
指定したカーソルから選択する列を定義します。 |
|
|
指定したカーソルから選択する |
|
|
|
|
|
指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。 |
|
|
指定した列の情報を表示します。DESCRIBE_COLUMNSプロシージャの代替オプションです。 |
|
|
指定のカーソルを実行します。 |
|
|
指定のカーソルを実行して、行をフェッチします。 |
|
|
指定のカーソルから行をフェッチします。 |
|
|
指定のカーソルがオープンの場合に |
|
|
エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。 |
|
|
フェッチされた累積行数を戻します。 |
|
|
最後に処理された行の |
|
|
文のSQL機能コードを戻します。 |
|
|
新規カーソルのID番号を戻します。 |
|
|
指定した文を解析します。 |
|
|
強く型付けされたREFカーソルまたは弱く型付けされたされたREFカーソルのうち |
|
|
|
|
|
指定のカーソルについて指定の変数の値を戻します。 |
このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。
構文
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プロシージャは、異なるデータ型を受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表122-6 BIND_ARRAYプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値をバインドするカーソルのID番号。 |
|
|
文内のコレクションの名前。 |
|
|
< |
|
|
範囲の下限を示す表要素の索引。 |
|
|
範囲の上限を示す表要素の索引。 |
使用上の注意
バインド変数名の長さは、<=30バイトである必要があります。
範囲をバインドするためには、範囲を指定する要素（タブ（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;
<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.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 user-defined object types collections (VARRAYs and nested tables) REFs Opaque types
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);
|
関連項目: 『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』 |
プラグマ
pragma restrict_references(bind_variable,WNDS);
パラメータ
表122-7 BIND_VARIABLEプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値をバインドするカーソルのID番号。 |
|
|
文内の変数の名前。 |
|
|
カーソル内の変数にバインドする値。
|
|
|
サイズの指定がない場合は、現行値の長さが使用されます。このパラメータは、 |
使用上の注意
この変数が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バイトである必要があります。
このプロシージャは、指定のカーソルをクローズします。
構文
DBMS_SQL.CLOSE_CURSOR ( c IN OUT INTEGER);
プラグマ
pragma restrict_references(close_cursor,RNDS,WNDS);
パラメータ
表122-8 CLOSE_CURSORプロシージャのパラメータ
| パラメータ | モード | 説明 |
|---|---|---|
|
|
|
クローズするカーソルのID番号。 |
|
|
|
カーソルはNULLに設定されています。
|
このプロシージャは、指定したカーソル内の指定の位置にあるカーソル要素の値を戻します。 このプロシージャは、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);
パラメータ
表122-9 COLUMN_VALUEプロシージャのパラメータ（単一行）
| パラメータ | 説明 |
|---|---|
|
|
値をフェッチするカーソルのID番号。 |
|
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
|
指定した列の値を戻します。 この出力パラメータのタイプが、 |
|
|
指定した列値のエラー・コードを戻します。 |
|
|
指定した列内の値の（切捨て前の）実際の長さ。 |
表122-10 COLUMN_VALUEプロシージャのパラメータ（バルク）
| パラメータ | 説明 |
|---|---|
|
|
値をフェッチするカーソルのID番号。 |
|
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
|
< バルク操作では、サブプログラムによって、（暗黙的に保持される）適切な索引に新しい要素が追加されます。 たとえば、DEFINE_ARRAYプロシージャの使用時にバッチ・サイズ（ |
例外
指定したOUTパラメータのvalueが、実際の値のタイプと異なる場合は、INCONSISTENT_TYPE（ORA-06562）が発生します。 このタイプは、DEFINE_COLUMNプロシージャをコールして列を定義したときに指定したタイプです。
|
関連項目: 『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』 |
このプロシージャは、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);
パラメータ
表122-11 COLUMN_VALUE_LONGプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値を取得するカーソルのカーソルID番号。 |
|
|
値を取得する列の位置。 |
|
|
フェッチするLONG値のバイト数。 |
|
|
フェッチを開始するためのLONGフィールドへのオフセット。 |
|
|
|
|
|
値に実際に戻されるバイト数。 |
このプロシージャは、（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コールに引数として指定した表内に蓄積されます。
パラメータ
表122-12 DEFINE_ARRAYプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
配列をバインドするカーソルのID番号。 |
|
|
定義している配列内にある列の相対位置。 文の最初の列は位置1です。 |
|
|
< |
|
|
フェッチする行数。 |
|
|
結果のコレクションへのコピーは、この下限の索引から開始されます。 |
使用上の注意
行数（cnt）には0（ゼロ）より大きい整数を指定する必要があります。それ以外の値が指定されると、例外が発生します。lower_boundは、正の数、負の数または0（ゼロ）でもかまいません。 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;
/
このプロシージャは、指定のカーソルから選択する列を定義します。このプロシージャが使用できるのは、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 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), urowid IN INTEGER;
CHAR、RAWおよび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);
|
関連項目: 『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』 |
プラグマ
pragma restrict_references(define_column,RNDS,WNDS);
パラメータ
表122-13 DEFINE_COLUMNプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
選択対象に定義されている行のカーソルのID番号。 |
|
|
定義している行内にある列の相対位置。 文の最初の列は位置1です。 |
|
|
定義している列の値。 この値のタイプによって、定義している列のタイプが決まります。 |
|
|
|
このプロシージャは、SELECTカーソルに対してLONG列を定義します。定義されている列は、指定のカーソルの文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。
構文
DBMS_SQL.DEFINE_COLUMN_LONG ( c IN INTEGER, position IN INTEGER);
パラメータ
表122-14 DEFINE_COLUMN_LONGプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
選択対象に定義されている行のカーソルのID番号。 |
|
|
定義している行内にある列の相対位置。 文の最初の列は位置1です。 |
このプロシージャは、DBMS_SQLによってオープンされ解析されたカーソルの列の情報を表示します。
構文
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB);
パラメータ
表122-15 DESCRIBE_COLUMNSプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
表示される列のカーソルのID番号。 |
|
|
問合せの選択リストにある列数。 |
|
|
問合せの各列の情報を表示する表。 |
このプロシージャは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。
構文
DBMS_SQL.DESCRIBE_COLUMNS2 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB2);
プラグマ
PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);
パラメータ
表122-16 DESCRIBE_COLUMNS2プロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
表示される列のカーソルのID番号。 |
|
|
問合せの選択リストにある列数。 |
|
|
問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。 |
このプロシージャは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。
構文
DBMS_SQL.DESCRIBE_COLUMNS3 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB3);
プラグマ
PRAGMA RESTRICT_REFERENCES(describe_columns3,WNDS);
パラメータ
表122-17 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
このファンクションは、指定のカーソルを実行します。このファンクションはカーソルのID番号を受け入れて、処理された行数を戻します。戻り値は、INSERT文、UPDATE文およびDELETE文に対してのみ有効で、DDL文を含めた他のタイプの文に対しては、戻り値は定義されず無視されます。
構文
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;
パラメータ
このファンクションは、指定のカーソルを実行して行をフェッチします。 このファンクションは、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);
パラメータ
表122-19 EXECUTE_AND_FETCHファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
実行してフェッチするカーソルのカーソルID番号。 |
|
|
問合せで実際に一致する行数が1以外の場合は、 注意: LONG列に対して、exactパラメータをTRUEに設定するオプションはサポートされていません。 例外が発生しても、行はフェッチされ、使用可能です。 |
このファンクションは、指定のカーソルから行をフェッチします。 FETCH_ROWSは、フェッチする行が残っているかぎり、繰り返しコールできます。 これらの行はバッファに取り出し、FETCH_ROWSへの各コール後に、COLUMN_VALUEをコールして各列ごとに読み込む必要があります。
FETCH_ROWSファンクションは、フェッチするカーソルのID番号を受け入れて、実際にフェッチされた行数を戻します。
構文
DBMS_SQL.FETCH_ROWS ( c IN INTEGER) RETURN INTEGER;
プラグマ
pragma restrict_references(fetch_rows,WNDS);
パラメータ
このファンクションは、指定のカーソルが現在オープンしているかどうかをチェックします。
構文
DBMS_SQL.IS_OPEN ( c IN INTEGER) RETURN BOOLEAN;
プラグマ
pragma restrict_references(is_open,RNDS,WNDS);
パラメータ
戻り値
オープンされ、クローズされていないカーソル番号に対してはTRUE、NULLのカーソル番号に対してはFALSEを戻します。 CLOSE_CURSORプロシージャは、渡されたカーソル変数をNULLにすることに注意してください。
例外
ORA-29471 DBMS_SQLアクセスが拒否されました: このエラーは、無効なカーソルID番号が検出された場合に発生します。 セッションでこのエラーが発生し、レポートされると、その後は同じセッションでDBMS_SQLをコールするたびにこのエラーが発生します。つまり、このセッションではDBMS_SQLを実行できなくなります。
このファンクションは、エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。SQL文内の最初の文字は、位置0（ゼロ）にあります。
構文
DBMS_SQL.LAST_ERROR_POSITION RETURN INTEGER;
プラグマ
pragma restrict_references(last_error_position,RNDS,WNDS);
使用上の注意
このファンクションは、別のDBMS_SQLプロシージャまたはファンクションのコール前、かつPARSEのコール後にコールしてください。
このファンクションは、フェッチされた累積行数を戻します。
構文
DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;
プラグマ
pragma restrict_references(last_row_count,RNDS,WNDS);
使用上の注意
このファンクションは、FETCH_ROWSコールまたはEXECUTE_AND_FETCHコール後にコールしてください。EXECUTEコール後にコールすると、戻される値は0（ゼロ）です。
このファンクションは、処理された最後の行のROWIDを戻します。
構文
DBMS_SQL.LAST_ROW_ID RETURN ROWID;
プラグマ
pragma restrict_references(last_row_id,RNDS,WNDS);
使用上の注意
このファンクションは、FETCH_ROWSコールまたはEXECUTE_AND_FETCHコール後にコールしてください。
このファンクションは、文のSQL機能コードを戻します。 これらのコードについては、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
構文
DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;
プラグマ
pragma restrict_references(last_sql_function_code,RNDS,WNDS);
使用上の注意
このファンクションは、SQL文の実行直後にコールする必要があります。それ以外の場合にコールすると、戻り値が定義されません。
このファンクションは、新規のカーソルをオープンします。 2番目のオーバーロードは、security_levelパラメータを使用して、オープンされたカーソルのセキュリティにファイングレイン・コントロールを適用します。
このカーソルが不要になった場合は、CLOSE_CURSORプロシージャをコールして、明示的にクローズする必要があります。
構文
DBMS_SQL.OPEN_CURSOR RETURN INTEGER; DBMS_SQL.OPEN_CURSOR ( security_level IN INTEGER) RETURN INTEGER;
パラメータ
表122-22 OPEN_CURSORファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
オープンされたカーソルに適用するセキュリティ保護のレベルを指定します。有効なセキュリティ・レベル値は、
|
プラグマ
pragma restrict_references(open_cursor,RNDS,WNDS);
戻り値
このファンクションは、新規カーソルのカーソルID番号を戻します。
使用上の注意
カーソルを使用すると、同じSQL文を繰り返し実行したり、新規のSQL文を実行することができます。カーソルを再使用した場合、新規のSQL文が解析されるときに、対応するカーソル・データ領域の内容がリセットされます。カーソルを再使用しないかぎり、クローズして再オープンする必要はありません。
このプロシージャは、指定したカーソル内の指定した文を解析します。すべての文が即時に解析されます。さらに、DDL文は、解析時にただちに実行されます。
PARSEプロシージャには、次の3つのバージョンがあります。
引数としてVARCHAR2文を使用します。
引数としてVARCHAR2(32767)の表VARCHAR2aを使用します。このプロシージャのVARCHAR2Aオーバーロード・バージョンは、PL/SQL表の文の要素を連結し、その結果の文字列を解析します。このプロシージャを使用すると、文を分割することによって、単一のVARCHAR2変数についての制限を超えた長い文を解析できます。
引数としてCLOBを使用します。この解析プロシージャのCLOBオーバーロード・バージョンを使用すると、32KBを超えるSQL文を解析できます。
構文
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER);
DBMS_SQL.PARSE ( c IN INTEGER, statement IN CLOB, 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);
パラメータ
表122-23 PARSEプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
文を解析するカーソルのID番号。 |
|
|
解析するSQL文。32Kを超えて PL/SQL文と異なり、SQL文の終わりにはセミコロンを含めないでください。次に例を示します。
|
|
|
文内の要素の下限。 |
|
|
文内の要素の上限。 |
|
|
|
|
|
OracleでSQL文を処理する方法を決定します。次のオプションが認識されます。
|
使用上の注意
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を使用することをお薦めします。VARCHAR2Aの機能の方が優れており、VARCHAR2Sは将来のリリースでは非推奨となるためです。
32KBを超えるSQL文を解析する場合、VARCHAR2Aオーバーロードのかわりに、新しいCLOBオーバーロード・バージョンのPARSEプロシージャを使用できます。
例外
コンパイルに関する警告を伴ったDBMS_SQLを使用して、タイプ、プロシージャ、ファンクションまたはパッケージを作成する場合、ORA-24344例外が発生しますが、プロシージャはそのまま作成されます。
このファンクションは、強く型付けされたREFカーソルまたは弱く型付けされたされたREFカーソルのうちOPENが実行されたカーソルを使用して、そのカーソルをDBMS_SQLカーソル番号に変換します。
構文
DBMS_SQL.TO_CURSOR_NUMBER( rc IN OUT SYS_REFCURSOR) RETURN INTEGER;
パラメータ
戻り値
REF CURSORから変換された、DBMS_SQLで管理可能なカーソル番号を戻します。
使用上の注意
渡されるREF CURSORには、OPENを実行しておく必要があります。実行しておかない場合、エラーが発生します。REF CURSORがDBMS_SQLカーソル番号に変換されると、REF CURSORはシステム固有の動的SQL操作でアクセスできなくなります。フェッチを開始した後にREF CURSORと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_CURSORID(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);
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;
/
このファンクションは、OPEN、PARSEおよびEXECUTEが実行されたカーソルを使用して、そのカーソルをPL/SQLで管理可能なREF CURSOR（弱く型付けされたカーソル）に変換/移行します。変換/移行後のカーソルは、システム固有の動的SQLを使用するように切り替えられているPL/SQLシステム固有の動的SQLで消費できます。このサブプログラムが使用できるのは、SELECTカーソルのみです。
構文
DBMS_SQL.TO_REFCURSOR( cursor_number IN INTEGER) RETURN SYS_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とDBMS_SQLカーソル番号を切り替えることはできません。切り替えようとすると、エラーが発生します。
例
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;
/
このプロシージャは、指定のカーソルについて指定の変数の値を戻します。このプロシージャは、returning句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。
構文
DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY <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 user-defined object types collections (VARRAYs and nested tables) REFs Opaque types
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>は次のようになります。
Binary_Double_Table Binary_Float_Table Bfile_Table Blob_Table Clob_Table Date_Table Interval_Day_To_Second_Table Interval_Year_To_Month_Table Number_Table Time_Table Time_With_Time_Zone_Table Timestamp_Table Timestamp_With_ltz_Table; Timestamp_With_Time_Zone_Table Urowid_Table Varchar2_Table
プラグマ
pragma restrict_references(variable_value,RNDS,WNDS);
パラメータ
