DBMS_SQL
パッケージでは、動的SQLを使用するためのインタフェースを提供し、PL/SQLを使用したデータ操作言語(DML)文やデータ定義言語(DDL)文の解析を可能にします。たとえば、DBMS_SQL
パッケージが提供するPARSE
プロシージャを使用することによって、ストアド・プロシージャ内からDROP TABLE
文を入力できます。
この章の内容は次のとおりです。
概要
セキュリティ・モデル
定数
使用上の注意
例外
例
レコード・タイプ
表タイプ
注意: ネイティブ動的SQLの詳細は、Oracle TimesTen In-Memory DatabaseのPL/SQL開発者ガイドのPL/SQLにおける動的SQL(EXECUTE IMMEDIATE文)に関する説明または『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
TimesTenを使用すると、動的SQLを使用するストアド・プロシージャおよび無名PL/SQLブロックを記述できます。動的SQL文は、ユーザーのソース・プログラムに埋め込まれておらず、実行時にプログラムに入力されるか、またはプログラムによって作成されるように、文字列で格納されています。この機能によって、より汎用的なプロシージャを作成できます。たとえば、この動的SQLによって、実行時まで名前がわからない表で動作するプロシージャを作成できます。
ネイティブ動的SQLはDBMS_SQL
に代わるものであり、動的SQL文をPL/SQLブロックに直接設定できます。ほとんどの場合、ネイティブ動的SQLは、DBMS_SQL
と比べると簡単に使用でき、より高パフォーマンスです。ただし、ネイティブ動的SQL自体に、メソッド4(不明数の入力または出力を含む動的SQL文)はサポートされないなどの制限があります。また、一部のタスクはDBMS_SQL
でのみ実行できます。
ストアド・プロシージャ内から動的SQLを使用する機能は一般的に、Oracle Call Interface(OCI)の手順に従っています。OCIについては、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
PL/SQLは、Cなどの他の一般的なプログラム言語とは、多少異なります。たとえば、ユーザーはアドレス(ポインタとも呼ばれます)をPL/SQLで参照できません。そのため、OCIとDBMS_SQL
パッケージの間には、次のようないくつかの相違点があります。
OCIはアドレスでバインドし、DBMS_SQL
パッケージは値でバインドします。
DBMS_SQL
では、VARIABLE_VALUE
をコールして無名ブロックに対するOUT
パラメータの値を取り出す必要があり、行内の列の値を実際にプログラムに取り出すには、行のフェッチ後にCOLUMN_VALUE
をコールする必要があります。
現行のリリースのDBMS_SQL
パッケージは、CANCEL
カーソル・プロシージャを提供していません。
NULL
はPL/SQL変数の値として完全にサポートされているため、インジケータ変数は不要です。
DBMS_SQL
はSYS
によって所有されており、AUTHID CURRENT_USER
でコンパイルされています。無名PL/SQLブロックからコールされるDBMS_SQL
サブプログラムは、現行ユーザーの権限を使用して実行されます。
AUTHID
句については、Oracle TimesTen In-Memory DatabaseのPL/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
の場合、チェックは常に行われます。
表6-1に示す定数は、PARSEプロシージャのlanguage_flag
パラメータとともに使用します。TimesTenの場合は、NATIVE
を使用します。
表6-1 DBMS_SQLの定数
名前 | 型 | 値 | 説明 |
---|---|---|---|
|
|
|
Oracle Databaseバージョン6の動作を指定します(TimesTenには適用されません)。 |
|
|
|
プログラムの接続先のデータベースに関する通常の動作を指定します。 |
|
|
2 |
Oracle Databaseバージョン7の動作を指定します(TimesTenには適用されません)。 |
SQL文を処理するためには、オープン・カーソルが必要です。OPEN_CURSORファンクションをコールすると、ユーザーはTimesTenが保持している有効なカーソルを示すデータ構造のカーソルID番号を受け取ります。これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQL
パッケージでのみ使用されます。
SQL文はすべて、PARSEプロシージャをコールして解析する必要があります。文を解析することによって、その文の構文がチェックされ、プログラム内のカーソルに関連付けられます。
DML文またはDDL文はすべて解析できます。DDL文は解析時に実行され、暗黙のコミットを実行します。
注意: DDL文を解析してプロシージャまたはパッケージを削除する場合、問題のプロシージャまたは問題のパッケージ内のプロシージャをまだ使用していると、タイムアウトが発生する可能性があります。プロシージャをコールすると、実行がユーザー側に戻るまで、そのプロシージャは使用中であるとみなされます。このようなタイムアウトは、即座に発生します。 |
図6-1に、DBMS_SQL
の実行フローを示します。
多くのDML文では、ユーザーのプログラム内のデータをTimesTenに入力する必要があります。実行時に提供する入力データを含んでいるSQL文を定義する場合は、SQL文内のプレースホルダを使用して、データの提供場所にマークを付ける必要があります。
SQL文内の各プレースホルダに対してバインド・プロシージャ(BIND_ARRAYプロシージャまたはBIND_VARIABLEプロシージャ)の1つをコールして、プログラム内の変数の値(または配列の値)をプレースホルダに提供する必要があります。SQL文が引き続き実行されると、TimesTenは、ユーザーのプログラムが入力変数と出力変数、またはバインド変数に設定したデータを使用します。
DBMS_SQL
は、その都度異なるバインド変数を使用してDML文を繰り返し実行できます。BIND_ARRAY
プロシージャを使用すると、スカラーのコレクションをバインドでき、それぞれの値はEXECUTE
ごとに1回だけ入力変数として使用されます。これは、OCIがサポートする配列インタフェースに類似しています。
SELECT
文内で選択されている行の列は、選択リスト内での相対位置(左から右)によって識別されます。問合せの場合は、定義プロシージャ(DEFINE_COLUMN
またはDEFINE_ARRAY
)をコールしてSELECT
値を受け入れる変数を指定する必要があり、その方法はINTO
句が静的問合せに対して行う場合とほとんど同じです。
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
をコールして、文の実行時に出力変数に割り当てられた値を取得します。
セッションでカーソルが不要な場合は、CLOSE_CURSOR
をコールしてカーソルをクローズします。
カーソルをクローズしないと、カーソルが不要になっても、そのカーソルが使用しているメモリーは割り当てられたままになります。
動的SQLを使用して問合せを処理する場合は、次のステップを実行する必要があります。
DEFINE_COLUMNプロシージャまたはDEFINE_ARRAYプロシージャをコールして、SELECT
文が戻す値を受け入れる変数を指定します。
EXECUTEファンクションをコールして、SELECT
文を実行します。
FETCH_ROWSファンクション(またはEXECUTE_AND_FETCH
)をコールして、問合せに一致した行を取得します。
COLUMN_VALUEプロシージャをコールして、問合せに関して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ファンクションをコールします。
inconsistent_type EXCEPTION; pragma exception_init(inconsistent_type, -6562);
この例外は、指定したOUT
パラメータ(要求した値を設定するパラメータ)のタイプがその値のタイプと異なる場合、COLUMN_VALUEプロシージャまたはVARIABLE_VALUEプロシージャで発生します。
この項には、DBMS_SQL
パッケージを使用するプロシージャの例が記述されています。
例1
この文のテキストはコンパイル時に判明しているため、この例では、動的SQLを使用する必要はありませんが、パッケージの基礎となる基本概念を示しています。
demo
プロシージャでは、(HR
スキーマのemployees
表から作成した)表myemployees
から、給与が指定の値を超えているすべての従業員が削除されます。
CREATE OR REPLACE PROCEDURE demo(p_salary IN NUMBER) AS cursor_name INTEGER; rows_processed INTEGER; BEGIN cursor_name := dbms_sql.open_cursor; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM myemployees WHERE salary > :x', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', p_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; /
myemployees
表を作成し、給与が$15,000以上の従業員の数を確認します。
Command> create table myemployees as select * from employees; 107 rows inserted. Command> select * from myemployees where salary>=15000; < 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 > < 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 1989-09-21 00:00:00, AD_VP, 17000, <NULL>, 100, 90 > < 102, Lex, De Haan, LDEHAAN, 515.123.4569, 1993-01-13 00:00:00, AD_VP, 17000, <NULL>, 100, 90 > 3 rows found.
demo
を実行して給与が$14,999を超える従業員をすべて削除し、結果を確認します。
Command> begin > demo(14999); > end; > / PL/SQL procedure successfully completed. Command> select * from myemployees where salary>=15000; 0 rows found.
例2
次のプロシージャの例は、コピー元表とコピー先表の名前が渡され、コピー元表からコピー先表に行をコピーします。このプロシージャの例は、コピー元表とコピー先表にはいずれも次の列があることを前提としています。
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 (in TimesTen commit closes cursors automatically): COMMIT; 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: Bulk DML
次の一連の例では、INSERT
、UPDATE
およびDELETE
の各SQL DML文でのバルク配列バインド(表項目)の使用方法を示します。
次に、HR
スキーマのdepartments
表に新しい従業員3人を追加するバルクINSERT
文の例を示します。
DECLARE stmt VARCHAR2(200); departid_array DBMS_SQL.NUMBER_TABLE; deptname_array DBMS_SQL.VARCHAR2_TABLE; mgrid_array DBMS_SQL.NUMBER_TABLE; locid_array DBMS_SQL.NUMBER_TABLE; c NUMBER; dummy NUMBER; BEGIN departid_array(1):= 280; departid_array(2):= 290; departid_array(3):= 300; deptname_array(1) := 'Community Outreach'; deptname_array(2) := 'Product Management'; deptname_array(3) := 'Acquisitions'; mgrid_array(1) := 121; mgrid_array(2) := 120; mgrid_array(3) := 70; locid_array(1):= 1500; locid_array(2):= 1700; locid_array(3):= 2700; stmt := 'INSERT INTO departments VALUES( :departid_array, :deptname_array, :mgrid_array, :locid_array)'; c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, ':departid_array', departid_array); DBMS_SQL.BIND_ARRAY(c, ':deptname_array', deptname_array); DBMS_SQL.BIND_ARRAY(c, ':mgrid_array', mgrid_array); DBMS_SQL.BIND_ARRAY(c, ':locid_array', locid_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; /
次に、新しい行が表示されている、SELECT
文からの出力を示します。
Command> select * from departments; < 10, Administration, 200, 1700 > ... < 280, Community Outreach, 121, 1500 > < 290, Product Management, 120, 1700 > < 300, Acquisitions, 70, 2700 > 30 rows found.
次に、HR
スキーマのemployees
表内の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):= 203; empno_array(2):= 204; empno_array(3):= 205; empno_array(4):= 206; salary_array(1) := 7000; salary_array(2) := 11000; salary_array(3) := 13000; salary_array(4) := 9000; stmt := 'update employees set salary = :salary_array WHERE employee_id = :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; /
例を実行する前の指定の従業員のエントリは次のように仮定され、給与は$6500、$10000、$12000および$8300であると表示されています。
Command> select * from employees where employee_id>=203 and employee_id<=206; < 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP, 6500, <NULL>, 101, 40 > < 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, <NULL>, 101, 70 > < 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR, 12000, <NULL>, 101, 110 > < 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT, 8300, <NULL>, 205, 110 > 4 rows found.
次に、例を実行した後の新しい給与が表示されています。
Command> select * from employees where employee_id>=203 and employee_id<=206; < 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP, 7000, <NULL>, 101, 40 > < 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 11000, <NULL>, 101, 70 > < 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR, 13000, <NULL>, 101, 110 > < 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT, 9000, <NULL>, 205, 110 > 4 rows found.
たとえば、DELETE
文では、次のようにWHERE
句に配列をバインドし、配列内の要素ごとに文を実行できます。
DECLARE stmt VARCHAR2(200); dept_no_array DBMS_SQL.NUMBER_TABLE; c NUMBER; dummy NUMBER; BEGIN dept_no_array(1) := 60; dept_no_array(2) := 70; stmt := 'delete from employees where department_id = :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, 1); 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に設定される)BIND_ARRAY
コールによって配列の最初の要素のみが指定され、部門60の従業員のみが削除されます。
例を実行する前、部門60と70には、それぞれ5人と1人の従業員がいます(部門番号は各行の最後のエントリです)。
Command> select * from employees where department_id>=60 and department_id<=70; < 103, Alexander, Hunold, AHUNOLD, 590.423.4567, 1990-01-03 00:00:00, IT_PROG, 9000, <NULL>, 102, 60 > < 104, Bruce, Ernst, BERNST, 590.423.4568, 1991-05-21 00:00:00, IT_PROG, 6000, <NULL>, 103, 60 > < 105, David, Austin, DAUSTIN, 590.423.4569, 1997-06-25 00:00:00, IT_PROG, 4800, <NULL>, 103, 60 > < 106, Valli, Pataballa, VPATABAL, 590.423.4560, 1998-02-05 00:00:00, IT_PROG, 4800, <NULL>, 103, 60 > < 107, Diana, Lorentz, DLORENTZ, 590.423.5567, 1999-02-07 00:00:00, IT_PROG, 4200, <NULL>, 103, 60 > < 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, <NULL>, 101, 70 > 6 rows found.
例を実行すると、部門70の従業員のみが残ります。
Command> select * from employees where department_id>=60 and department_id<=70; < 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, <NULL>, 101, 70 > 1 row found.
例6: 配列の定義
CREATE OR REPLACE PROCEDURE BULK_PLSQL(deptid NUMBER) IS names DBMS_SQL.VARCHAR2_TABLE; sals DBMS_SQL.NUMBER_TABLE; 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, 1); DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5, 1); 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; /
たとえば、employees
表の部門20の場合は、次の出力が生成されます。
Command> begin > bulk_plsql(20); > end; > / Name = Hartstein, salary = 13000 Name = Fay, salary = 6000 PL/SQL procedure successfully completed.
例7: 列の定義の表示
これは、定義が記述されている表に対してSELECT *
問合せを使用することで、ttIsql
DESCRIBE
コマンドの代替として使用できます。この例では、employees
表の列を説明しています。
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 employees', 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 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; /
次に、employees
表の列の定義を示す簡略化した簡単な出力サンプルを示しますが、HR
スキーマから実行されたことを前提にしています。ここでは、最初の2列からの情報のみが示されています。
col_type = 2 col_maxlen = 7 col_name = EMPLOYEE_ID col_name_len = 11 col_schema_name = HR col_schema_name_len = 8 col_precision = 6 col_scale = 0 col_null_ok = false col_type = 1 col_maxlen = 20 col_name = FIRST_NAME col_name_len = 10 col_schema_name = HR col_schema_name_len = 8 col_precision = 0 col_scale = 0 col_null_ok = true ...
例8: RETURNING句
この句を使用すると、INSERT
、UPDATE
およびDELETE
文は式の値を戻すことができます。この値は、バインド変数に戻されます。
単一行を挿入、更新または削除する場合は、DBMS_SQL.BIND_VARIABLE
を使用して、これらのアウトバインドをバインドします。複数行を挿入、更新または削除する場合は、DBMS_SQL.BIND_ARRAY
を使用します。これらのバインド変数の値を取得するには、DBMS_SQL.VARIABLE_VALUE
をコールする必要があります。
注意: これは、DBMS_SQL 内でアウトバインドを使用したPL/SQLブロックを実行した後で、DBMS_SQL.VARIABLE_VALUE をコールする必要があることと同様です。 |
次の例は、表tab
が作成されていることを前提にしています。
Command> create table tab (c1 number, c2 number);
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; /
次は、この例を実行し、その結果を示しています。最初、表は空でした。
Command> declare r NUMBER; > begin > single_Row_insert(100,200,r); > dbms_output.put_line('Product = ' || r); > end; > / Product = 20000 PL/SQL procedure successfully completed. Command> select * from tab; < 100, 200 > 1 row found.
ii) 単一行の更新。rownum
は、行番号の内部変数であることに注意してください。
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 = 1 ' || '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; /
次は、この例を実行し、前の例で挿入された行を更新した結果を示しています。
Command> declare r NUMBER; > begin > single_Row_update(200,300,r); > dbms_output.put_line('Product = ' || r); > end; > / Product = 60000 PL/SQL procedure successfully completed. Command> select * from tab; < 200, 300 > 1 row found.
iii) 複数行の挿入。
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; /
次のスクリプトでは、この例を実行します。
declare c1_array dbms_sql.number_table; c2_array dbms_sql.number_table; r_array dbms_sql.number_table; begin c1_array(1) := 10; c1_array(2) := 20; c1_array(3) := 30; c2_array(1) := 15; c2_array(2) := 25; c2_array(3) := 35; multi_Row_insert(c1_array,c2_array,r_array); dbms_output.put_line('Product for row1 = ' || r_array(1)); dbms_output.put_line('Product for row2 = ' || r_array(2)); dbms_output.put_line('Product for row3 = ' || r_array(3)); end; /
結果を次に示します。最初、表は空でした。
Product for row1 = 150 Product for row2 = 500 Product for row3 = 1050 PL/SQL procedure successfully completed. Command> select * from tab; < 10, 15 > < 20, 25 > < 30, 35 > 3 rows found.
iv) 複数行の更新。
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_OUTPUT.PUT_LINE(n || ' rows updated'); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /
注意: bnd1 とbnd2 は、同様に配列にできることに注意してください。更新されたすべての行に対する式の値は、bnd3 に入れられます。bnd1 とbnd2 の各値について、どの行が更新されたかを区別する方法はありません。 |
次のスクリプトでは、例を実行します。
declare c1 NUMBER; c2 NUMBER; r_array dbms_sql.number_table; begin c1 := 100; c2 := 0; multi_Row_update(c1, c2, r_array); dbms_output.put_line('Product for row1 = ' || r_array(1)); dbms_output.put_line('Product for row2 = ' || r_array(2)); dbms_output.put_line('Product for row3 = ' || r_array(3)); end; /
次に、前の例に挿入された行を更新した結果を示します。(更新された行数のレポートは、例そのものからの値です。テスト・スクリプトによって、製品がレポートされます。)
3 rows updated Product for row1 = 1500 Product for row2 = 2500 Product for row3 = 3500 PL/SQL procedure successfully completed. Command> select * from tab; < 100, 15 > < 100, 25 > < 100, 35 > 3 rows found. Command>
v) 複数行の削除。
CREATE OR REPLACE PROCEDURE multi_Row_delete (c1_test NUMBER, 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_VARIABLE(c, 'bnd1', c1_test); DBMS_SQL.BIND_ARRAY(c, 'bnd2', r); n := DBMS_SQL.EXECUTE(c); DBMS_OUTPUT.PUT_LINE(n || ' rows deleted'); DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /
次のスクリプトでは、例を実行します。
declare c1_test NUMBER; r_array dbms_sql.number_table; begin c1_test := 100; multi_Row_delete(c1_test, r_array); dbms_output.put_line('Product for row1 = ' || r_array(1)); dbms_output.put_line('Product for row2 = ' || r_array(2)); dbms_output.put_line('Product for row3 = ' || r_array(3)); end; /
次に、前の例で更新された行を削除した結果を示します。(削除された行数のレポートは、例そのものからの値です。テスト・スクリプトによって、製品がレポートされます。)
3 rows deleted Product for row1 = 1500 Product for row2 = 2500 Product for row3 = 3500 PL/SQL procedure successfully completed. Command> select * from tab; 0 rows found.
注意: Number_Table のDBMS_SQL.BIND_ARRAY は、数値を内部的にバインドします。文を実行する回数は、インバインド配列内の要素数によって決まります。 |
DBMS_SQL
パッケージは、次のレコード・タイプと表タイプを定義します。
注意:
|
レコード・タイプ
表タイプ
このレコード・タイプは、動的問合せ内に単一列の記述情報を保持します。これは、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;
フィールド
表6-2 DESC_RECフィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列のキャラクタ・セットID。 |
|
列のキャラクタ・セット・フォーム。 |
|
Null列フラグ。 |
DESC_REC2
は、DESC_TAB2
表タイプおよびDESCRIBE_COLUMNS2プロシージャの要素タイプです。
このレコード・タイプはDESC_REC
と同じですが、col_name
フィールドがVARCHAR2
の設定可能な最大サイズに拡張されている点は異なります。したがって、32文字を超えて列名の値を設定できるため、非推奨となっている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);
フィールド
表6-3 DESC_REC2フィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列のキャラクタ・セットID。 |
|
列のキャラクタ・セット・フォーム。 |
|
Null列フラグ。 |
DESC_REC3
は、DESC_TAB3
表タイプおよびDESCRIBE_COLUMNS3プロシージャの要素タイプです。
DESC_REC3
はDESC_REC2
と同じですが、動的問合せ内に列のタイプ名(type_name
)およびタイプ名の長さ(type_name_len
)を保持するための2つのフィールドが追加されている点は異なります。col_type
フィールドの値が現在使用されていない109(ユーザー定義タイプに対するOracle Databaseタイプ番号)の場合にのみ、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);
フィールド
表6-4 DESC_REC3フィールド
フィールド | 説明 |
---|---|
|
列のタイプ。 |
|
列の最大長。 |
|
列の名前。 |
|
列名の長さ。 |
|
列のスキーマ名。 |
|
列のスキーマ名の長さ。 |
|
列の精度。 |
|
列のスケール。 |
|
列のキャラクタ・セットID。 |
|
列のキャラクタ・セット・フォーム。 |
|
Null列フラグ。 |
|
今後の使用のため予約済です。 |
|
今後の使用のため予約済です。 |
これは、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;
これは、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;
これは、TIMESTAMP_UNCONSTRAINED
の表です。
構文
TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED 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)
の表です。
構文
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
表6-5 DBMS_SQLパッケージのサブプログラム
サブプログラム | 説明 |
---|---|
|
指定の値を指定のコレクションにバインドします。 |
|
指定の値を指定の変数にバインドします。 |
|
指定したカーソルをクローズして、メモリーを解放します。 |
|
カーソル内の指定位置にあるカーソル要素の値を戻します。 |
重要: TimesTenでは したがって、このマニュアルでは、 |
|
|
指定したカーソルから選択するコレクションを定義します。 |
|
指定したカーソルから選択する列を定義します。 |
指定したカーソルから選択する 重要: TimesTenでは したがって、このマニュアルでは、 |
|
|
|
|
DESCRIBE_COLUMNSプロシージャの代替オプションとして、指定した列の情報を表示します。 |
|
DESCRIBE_COLUMNSプロシージャの代替オプションとして、指定した列の情報を表示します。 |
|
指定のカーソルを実行します。 |
|
指定のカーソルを実行して、行をフェッチします。 |
|
指定のカーソルから行をフェッチします。 |
|
指定のカーソルがオープンしている場合は |
|
エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。 |
|
フェッチされた累積行数を戻します。 |
|
最後に処理された行のROWIDを戻します。 TimesTenでは、DML文で最後に処理された行のROWIDはサポートされていません。このファンクションは、 |
|
文のSQL機能コードを戻します。 |
|
新規カーソルのID番号を戻します。 |
|
指定の文を解析します。 |
|
強くまたは弱くタイプ付けされたオープンされているREF CURSORを使用して、それを |
|
オープン、解析および実行されたカーソルを使用し、PL/SQLシステム固有の動的SQLで消費できるPL/SQLで管理可能なREF CURSOR(弱くタイプ付けされたカーソル)に変換または移行します。 |
|
指定のカーソルについて指定の変数の値を戻します。 |
このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。
構文
DBMS_SQL.BIND_ARRAY ( c IN INTEGER, name IN VARCHAR2, <table_variable> IN <datatype> [,index1 IN INTEGER, index2 IN INTEGER)] );
table_variable
とそれに対応するdatatype
は、次のいずれかの組合せになります。
<bflt_tab> dbms_sql.Binary_Float_Table <bdbl_tab> dbms_sql.Binary_Double_Table <c_tab> dbms_sql.Varchar2_Table <d_tab> dbms_sql.Date_Table <ids_tab> dbms_sql.Interval_Day_to_Second_Table <iym_tab> dbms_sql.Interval_Year_to_Month_Table <n_tab> dbms_sql.Number_Table <tm_tab> dbms_sql.Time_Table <tms_tab> dbms_sql.Timestamp_Table
BIND_ARRAY
プロシージャは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表6-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
バッファから操作されます。したがって、バインド・コール後に表を変更した場合でも、その変更が実行方法に影響を与えることはありません。
ローカル変数を次のいずれかの表項目タイプとして宣言でき、これらの表項目タイプは、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 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 timestamp_table IS TABLE OF timestamp_unconstrained INDEX BY BINARY_INTEGER; TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; <tm_tab> Time_Table <tms_tab> Timestamp_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 DATE INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED) NUMBER TIME(9) (TIME_UNCONSTRAINED) TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED) VARCHAR2 CHARACTER SET ANY_CS INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED) VARRAY Nested table
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);
パラメータ
表6-7 BIND_VARIABLEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値がバインドされるカーソルのID番号。 |
|
文内の変数の名前。 |
|
カーソル内の変数にバインドする値。
|
|
サイズの指定がない場合は、現行値の長さが使用されます。このパラメータは、 |
使用上の注意
この変数がIN
変数、IN OUT
変数またはIN
コレクションである場合は、指定したバインド値が、変数タイプまたは配列タイプに対して有効である必要があります。OUT
変数のバインド値は無視されます。
SQL文のバインド変数またはコレクションは、名前によって識別されます。バインド変数またはバインド配列に値をバインドする場合は、次の例に示すように、文中でバインド変数を識別する文字列の先頭にコロンを付ける必要があります。
SELECT last_name FROM employees WHERE salary > :X;
この例では、対応するバインド・コールは次のようになります。
BIND_VARIABLE(cursor_name, ':X', 3500);
または
BIND_VARIABLE (cursor_name, 'X', 3500);
バインド変数名の長さは、30バイト以下にする必要があります。
例
「例」を参照してください。
このプロシージャは、指定のカーソルをクローズします。
構文
DBMS_SQL.CLOSE_CURSOR (
c IN OUT INTEGER);
パラメータ
表6-8 CLOSE_CURSORプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
|
このプロシージャは、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 DATE INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED) NUMBER TIME(9) (TIME_UNCONSTRAINED) TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED) VARCHAR2 CHARACTER SET ANY_CS INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED) VARRAY Nested table
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> dbms_sql.Binary_Double_Table <bflt_tab> dbms_sql.Binary_Float_Table <c_tab> dbms_sql.Varchar2_Table <d_tab> dbms_sql.Date_Table <ids_tab> dbms_sql.Interval_Day_To_Second_Table <iym_tab> dbms_sql.Interval_Year_To_Month_Table <n_tab> dbms_sql.Number_Table <tm_tab> dbms_sql.Time_Table <tms_tab> dbms_sql.Timestamp_Table
パラメータ
表6-9 COLUMN_VALUEプロシージャのパラメータ(単一行)
パラメータ | 説明 |
---|---|
|
値をフェッチするカーソルのID番号。 |
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
指定した列の値を戻します。 この出力パラメータのタイプが、 |
|
指定した列値のエラー・コードを戻します。 |
|
指定した列内の値の(切捨て前の)実際の長さ。 |
表6-10 COLUMN_VALUEプロシージャのパラメータ(バルク)
パラメータ | 説明 |
---|---|
|
値をフェッチするカーソルのID番号。 |
|
カーソル内の列の相対位置。 文の最初の列は位置1です。 |
|
バルク操作では、サブプログラムによって、(暗黙的に保持される)適切な索引に新しい要素が追加されます。たとえば、DEFINE_ARRAYプロシージャの使用時にバッチ・サイズ( |
例外
ORA-06562: Type of out argument must match type of column or bind variable
この例外は、指定したOUT
パラメータのvalue
のタイプが、実際の値のタイプと異なると発生します。このタイプは、DEFINE_COLUMN
をコールして列を定義したときに指定したタイプです。
例
「例」を参照してください。
このプロシージャは、指定の列について、FETCH_ROWSファンクション・コールで行の値がフェッチされるコレクションを定義します。このプロシージャによって、ユーザーは単一のSELECT
文から、行を一括してフェッチできます。単一のフェッチで、PL/SQLの集計オブジェクトに複数の行をフェッチできます。
コレクションのスカラー・タイプ
ローカル変数を、次のいずれかの表項目タイプとして宣言し、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 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 timestamp_table IS TABLE OF timestamp_unconstrained 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
は、次のいずれかの組合せになります。
<bflt_tab> dbms_sql.Binary_Float_Table <bdbl_tab> dbms_sql.Binary_Double_Table <c_tab> dbms_sql.Varchar2_Table <d_tab> dbms_sql.Date_Table <n_tab> dbms_sql.Number_Table <tm_tab> dbms_sql.Time_Table <tms_tab> dbms_sql.Timestamp_Table <ids_tab> dbms_sql.Interval_Day_To_Second_Table <iym_tab> dbms_sql.Interval_Year_To_Month_Table
DEFINE_ARRAY
は、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表6-11 DEFINE_ARRAYプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
配列がバインドされるカーソルのID番号。 |
|
定義している配列内にある列の相対位置。 文の最初の列は位置1です。 |
|
|
|
フェッチする行数。 |
|
結果のコレクションへのコピーは、この下限の索引から開始されます。 |
使用上の注意
行数(cnt
)には0(ゼロ)より大きい整数を指定する必要があります。lower_bnd
は、正の数、負の数または0(ゼロ)でもかまいません。DEFINE_ARRAY
コールが発行された問合せに、配列バインドを含めることはできません。
例外
ORA-29253: Invalid count argument passed to procedure dbms_sql.define_array
この例外は、行数(cnt
)が0(ゼロ)以下であると発生します。
例
「例」を参照してください。
このプロシージャは、指定のカーソルから選択する列を定義します。このプロシージャが使用できるのは、SELECT
カーソルのみです。
定義されている列は、指定のカーソル内にある文のSELECT
リスト内での相対位置によって識別されます。COLUMN
値のタイプによって、定義されている列のタイプが決まります。
構文
DBMS_SQL.DEFINE_COLUMN ( c IN INTEGER, position IN INTEGER, column IN <datatype>);
datatype
は、次のいずれかのタイプである必要があります。
BINARY_DOUBLE BINARY_FLOAT DATE INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED) NUMBER TIME(9) (TIME_UNCONSTRAINED) TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED) INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED) VARRAY Nested table
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);
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);
パラメータ
表6-12 DEFINE_COLUMNプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
選択対象に定義されている行のカーソルのID番号。 |
|
定義している行内にある列の相対位置。 文の最初の列は位置1です。 |
|
定義している列の値。 この値のタイプによって、定義している列のタイプが決まります。 |
|
タイプ |
例
「例」を参照してください。
このプロシージャは、DBMS_SQL
によってオープンされ解析されたカーソルの列の情報を表示します。
構文
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_TAB);
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_REC);
パラメータ
表6-13 DESCRIBE_COLUMNSプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。 |
例
「例」を参照してください。
このファンクションは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。
構文
DBMS_SQL.DESCRIBE_COLUMNS2 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_TAB2);
DBMS_SQL.DESCRIBE_COLUMNS2 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_REC2);
パラメータ
表6-14 DESCRIBE_COLUMNS2プロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。 |
このファンクションは、指定した列の情報を表示します。これは、DESCRIBE_COLUMNSプロシージャの代替オプションです。
構文
DBMS_SQL.DESCRIBE_COLUMNS3 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_TAB3);
DBMS_SQL.DESCRIBE_COLUMNS3 ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DBMS_SQL.DESC_REC3);
パラメータ
表6-15 DESCRIBE_COLUMNS3プロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
表示される列のカーソルのID番号。 |
|
問合せの選択リストにある列数。 |
|
問合せの各列の情報を表示する表。この表は、1から順に、問合せの選択リストの要素数まで索引付けされます。 |
使用上の注意
カーソルIDに基づいて渡されるカーソルには、OPENおよびPARSEを実行しておく必要があり、そうしないと、カーソルIDが無効であるとしてエラーが発生します。
このファンクションは、指定のカーソルを実行します。このファンクションはカーソルのID番号を受け入れて、処理された行数を戻します。戻り値は、INSERT
文、UPDATE
文およびDELETE
文に対してのみ有効です。DDLを含めた他のタイプの文に対しては、戻り値は定義されず無視されます。
構文
DBMS_SQL.EXECUTE (
c IN INTEGER)
RETURN INTEGER;
パラメータ
戻り値
処理された行数を示す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;
パラメータ
表6-17 EXECUTE_AND_FETCHファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
実行してフェッチするカーソルのカーソルID番号。 |
|
問合せで実際に一致する行数が1以外の場合は、 例外が発生しても、行はフェッチされ、使用可能です。 |
戻り値
フェッチされた行数を示すINTEGER
値。
例外
ORA-01422: Exact fetch returns more than requested number of rows
この例外は、問合せで一致する行数が1でないと発生します。
このファンクションは、指定のカーソルから行をフェッチします。DEFINE_ARRAYプロシージャ・コールは、行の値がフェッチされるコレクションを定義します。
FETCH_ROWS
コールは、DEFINE_ARRAY
コールのcnt
パラメータに基づいて、指定された行数をフェッチします。フェッチされた行は、列ごとにCOLUMN_VALUEプロシージャ・コールを実行するまで、引数としてCOLUMN_VALUE
に渡された表に行がコピーされるときに、DBMS_SQL
バッファにコピーされます。行が配置される場所は、DEFINE_ARRAY
コール内のlower_bnd
設定に従って、lower_bnd
、lower_bnd
+1、lower_bnd
+2のようになります。行がまだ入ってきていても、ユーザーは引き続きFETCH_ROWS
およびCOLUMN_VALUE
コールを発行します。フェッチされずに残っている行があるかぎり、FETCH_ROWS
を繰り返しコールできます。
FETCH_ROWS
ファンクションは、フェッチするカーソルのID番号を受け入れて、実際にフェッチされた行数を戻します。
構文
DBMS_SQL.FETCH_ROWS (
c IN INTEGER)
RETURN INTEGER;
パラメータ
戻り値
フェッチされた行数を示すINTEGER
値。
例
「例」を参照してください。
このファンクションは、指定のカーソルが現在オープンしているかどうかをチェックします。
構文
DBMS_SQL.IS_OPEN (
c IN INTEGER)
RETURN BOOLEAN;
パラメータ
戻り値
オープンされ、クローズされていないカーソル番号に対してはTRUE
、NULL
のカーソル番号に対してはFALSE
を戻します。CLOSE_CURSORプロシージャは、渡されたカーソル変数にNULLを代入することに注意してください。
例外
ORA-29471 DBMS_SQL access denied
これは、無効なカーソルID番号が検出された場合に発生します。セッションでこのエラーが発生し、レポートされると、その後は同じセッションでDBMS_SQL
をコールするたびにこのエラーが発生しますが、これは、このセッションではDBMS_SQL
を実行できなくなることを意味します。
このファンクションは、エラーが発生したSQL文テキストのバイト・オフセットを返します。SQL文の最初の文字は、0の位置に配置されます。
構文
DBMS_SQL.LAST_ERROR_POSITION RETURN INTEGER;
戻り値
エラーが発生したSQL文テキスト内のバイト・オフセットを示すINTEGER
値。
使用上の注意
このファンクションは、別のDBMS_SQL
プロシージャまたはファンクションのコール前、かつPARSE
のコール後にコールしてください。
このファンクションは、フェッチされた累積行数を戻します。
構文
DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;
戻り値
フェッチされた累積行数を示すINTEGER
値。
使用上の注意
このファンクションは、FETCH_ROWS
コールまたはEXECUTE_AND_FETCH
コール後にコールしてください。EXECUTE
コール後にコールすると、戻される値は0(ゼロ)です。
このファンクションは、最後に処理された行のROWIDを戻します。
TimesTenはDML文で最後に処理された行のROWIDをサポートしないため、このファンクションはNULL
を戻します。
構文
DBMS_SQL.LAST_ROW_ID RETURN ROWID;
戻り値
TimesTenの場合はNULL
を戻します。
このファンクションは、文のSQL機能コードを戻します。これらのコードについては、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。
構文
DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;
戻り値
文のSQL機能コードを示すINTEGER
値。
使用上の注意
このファンクションは、SQL文の実行直後にコールしてください。それ以外の場合、戻り値が定義されません。
このプロシージャは、新規のカーソルをオープンします。2番目のオーバーロードは、security_level
パラメータを使用して、オープンされたカーソルのセキュリティにファイングレイン・コントロールを適用します。
このカーソルが不要になった場合は、CLOSE_CURSORプロシージャをコールして、明示的にクローズする必要があります。
構文
DBMS_SQL.OPEN_CURSOR
RETURN INTEGER;
DBMS_SQL.OPEN_CURSOR (
security_level IN INTEGER)
RETURN INTEGER;
パラメータ
表6-20 OPEN_CURSORファンクションのパラメータ
パラメータ | 説明 |
---|---|
|
オープンされたカーソルに適用するセキュリティ保護のレベルを指定します。有効なセキュリティ・レベル値は、0、1および2です。
|
戻り値
新しいカーソルのカーソルID。
使用上の注意
カーソルを使用すると、同じSQL文を繰り返し実行したり、新規のSQL文を実行することができます。カーソルを再使用した場合、新規のSQL文が解析されるときに、対応するカーソル・データ領域の内容がリセットされます。カーソルを再使用しないかぎり、クローズして再オープンする必要はありません。
このプロシージャは、指定したカーソル内の指定した文を解析します。すべての文が即時に解析されます。さらに、DDL文は、解析時にただちに実行されます。
PARSE
プロシージャには、複数のバージョンがあります。
引数としてVARCHAR2
文を使用します。
引数としてVARCHAR2(32767)
の表VARCHAR2A
を使用します。このプロシージャのVARCHAR2A
オーバーロード・バージョンは、PL/SQL表の文の要素を連結し、その結果の文字列を解析します。このプロシージャを使用すると、文を分割することによって、単一のVARCHAR2
変数についての制限を超えた長い文を解析できます。
構文
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER);
DBMS_SQL.PARSE ( c IN INTEGER, statement IN DBMS_SQL.VARCHAR2A, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER);
DBMS_SQL.PARSE ( c IN INTEGER, statement IN DBMS_SQL.VARCHAR2S, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER);
パラメータ
表6-21 PARSEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
文を解析するカーソルのID番号。 |
|
解析するSQL文。 PL/SQL文と異なり、SQL文の終わりにはセミコロンを含めないでください。次に例を示します。 DBMS_SQL.PARSE(cursor1, 'BEGIN proc; END;', 2); DBMS_SQL.PARSE(cursor1, 'INSERT INTO tab VALUES(1)', 2); |
|
文内の要素の下限。 |
|
文内の要素の上限。 |
|
|
|
OracleでSQL文を処理する方法を決定します。TimesTenの場合は、 |
使用上の注意
クライアント側コードは、リモート・パッケージの変数または定数を参照できないため、定数の値を明示的に使用する必要があります。
たとえば、次のコードは、クライアント側でコンパイルしません。
DBMS_SQL.PARSE(cur_hdl, stmt_str, DBMS_SQL.NATIVE); -- uses constant DBMS_SQL.NATIVE
次のコードは、引数が明示的に指定されているので、クライアント側で有効です。
DBMS_SQL.PARSE(cur_hdl, stmt_str, 1); -- compiles on the client
VARCHAR2S
タイプは、下位互換性を維持する目的のみでサポートされています。かわりに、VARCHAR2A
を使用することをお薦めします。
例外
ORA-24344: Success with compilation error
コンパイルに関する警告を伴ったタイプ、プロシージャ、ファンクションまたはパッケージを作成する場合、この例外が発生しますが、オブジェクトはそのまま作成されます。
例
「例」を参照してください。
このファンクションは、強くまたは弱くタイプ付けされたオープンされているREF CURSORを使用して、それをDBMS_SQL
カーソル番号に変換します。
構文
DBMS_SQL.TO_CURSOR_NUMBER(
rc IN OUT SYS_REFCURSOR)
RETURN INTEGER;
パラメータ
戻り値
REF CURSORから変換された、DBMS_SQL
で管理可能なカーソル番号を戻します。
使用上の注意
渡されるREF CURSORはオープンされている必要があります(OPEN_CURSOR
)。
REF CURSORがDBMS_SQL
カーソル番号に変換されると、REF CURSORはシステム固有の動的SQL操作でアクセスできなくなります。
フェッチが開始された後でREF CURSORとDBMS_SQL
カーソル番号を切り替えることはできません。
CREATE OR REPLACE PROCEDURE DO_QUERY1(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; BEGIN -- sql_stmt := 'select * from employees'; OPEN src_cur FOR sql_stmt; -- 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; /
このプロシージャは、次のように実行できます。
Command> begin > do_query1('select * from employees'); > end; > / PL/SQL procedure successfully completed.
このファンクションは、オープン(OPEN_CURSOR
)、解析(PARSE
)および実行(EXECUTE
)されたカーソルを使用して、それをPL/SQLで管理可能なREF CURSOR(弱くタイプ付けされたカーソル)に変換または移行しますが、変換または移行後のカーソルは、PL/SQLシステム固有の動的SQLで消費できます。このサブプログラムが使用できるのは、SELECT
カーソルのみです。
構文
DBMS_SQL.TO_REFCURSOR(
cursor_number IN OUT INTEGER)
RETURN SYS_REFCURSOR;
パラメータ
戻り値
DBMS_SQL
カーソル番号から変換されたPL/SQL REF CURSOR。
使用上の注意
cursor_number
によって渡されるカーソルは、オープン、解析および実行されている必要があります。そうしないと、エラーが発生します。
cursor_number
がREF CURSORに変換されると、これはDBMS_SQL
操作でアクセスできなくなります。
cursor_number
がREF CURSORに変換された後に、DBMS_SQL.IS_OPEN
を使用すると、エラーが発生します。
フェッチが開始された後でREF CURSORとDBMS_SQL
カーソル番号を切り替えることはできません。エラーが発生します。
CREATE OR REPLACE PROCEDURE DO_QUERY2(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; /
次の例では、103の管理IDに対してこのプロシージャを実行します。
Command> begin > do_query2(103); > end; > / EMPNO DEPTNO ----- ------ 104 60 105 60 106 60 107 60 PL/SQL procedure successfully completed.
このプロシージャは、指定のカーソルについて指定の変数の値を戻します。これは、RETURNING
句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。
構文
DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY <datatype>);
datatype
は、次のいずれかのタイプである必要があります。
BINARY_DOUBLE BINARY_FLOAT DATE INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED) NUMBER TIME(9) (TIME_UNCONSTRAINED) TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED) VARCHAR2 CHARACTER SET ANY_CS INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED) VARRAY Nested table
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.Binary_Double_Table dbms_sql.Binary_Float_Table dbms_sql.Date_Table dbms_sql.Interval_Day_To_Second_Table dbms_sql.Interval_Year_To_Month_Table dbms_sql.Number_Table dbms_sql.Time_Table dbms_sql.Timestamp_Table dbms_sql.Varchar2_Table
パラメータ
表6-24 VARIABLE_VALUEプロシージャのパラメータ
パラメータ | 説明 |
---|---|
|
値を取得するカーソルのID番号。 |
|
取得した値を代入する変数名。 |
|
単一行オプションの場合、これは指定した位置の変数の値です。 配列オプションの場合、これは |
例外
ORA-06562: Type of out argument must match type of column or bind variable
これは、出力パラメータのタイプがBIND_VARIABLE
コールで定義された値のタイプと違う場合に発生します。
例
「例」を参照してください。