7 DBMS_SQL
DBMS_SQLパッケージでは、動的SQLを使用してデータ操作言語(DML)またはデータ定義言語(DDL)文を実行したり、PL/SQL無名ブロックを実行したり、PL/SQLストアド・プロシージャおよびファンクションをコールするためのインタフェースを提供します。
たとえば、DBMS_SQLパッケージが提供するPARSEプロシージャを使用することによって、ストアド・プロシージャ内からDROP TABLE文を入力できます。
この章の内容は次のとおりです。
-
-
概要
-
セキュリティ・モデル
-
定数
-
操作上のノート
-
例外
-
例
-
-
-
レコード・タイプ
-
表タイプ
-
ノート:
ネイティブ動的SQLの詳細は、Oracle TimesTen In-Memory Database PL/SQL開発者ガイドのPL/SQLでの動的SQL (EXECUTE IMMEDIATE文)を参照してください。Oracle Database PL/SQL言語リファレンスのEXECUTE IMMEDIATE文も参照してください。
DBMS_SQLの使用
概要
TimesTen PL/SQLは動的SQLをサポートしています。動的SQL文は、ユーザーのソース・プログラムに埋め込まれておらず、実行時にプログラムに入力されるか、またはプログラムによって作成されるように、文字列で格納されています。
この機能によって、より汎用的なプロシージャを作成できます。たとえば、この動的SQLによって、実行時まで名前がわからない表で動作するプロシージャを作成できます。
ネイティブ動的SQL(EXECUTE IMMEDIATE)はDBMS_SQLに代わるものであり、動的SQL文、PL/SQLブロックおよびPL/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サブプログラムは、現行ユーザーの権限を使用して実行されます。
Oracle TimesTen In-Memory Databaseセキュリティ・ガイドの定義者権限および実行者権限(AUTHID句)を参照してください。
定数
表7-1に示す定数は、PARSEプロシージャのlanguage_flagパラメータとともに使用します。TimesTenの場合は、NATIVEを使用します。
表7-1 DBMS_SQLの定数
| 名前 | タイプ | 値 | 説明 |
|---|---|---|---|
|
|
|
|
Oracle Databaseバージョン6の動作を指定します(TimesTenには適用されません)。 |
|
|
|
|
プログラムの接続先のデータベースに関する一般的な動作を指定します。 |
|
|
|
2 |
Oracle Databaseバージョン7の動作を指定します(TimesTenには適用されません)。 |
操作上のノート
実行フロー
OPEN_CURSOR
SQL文を処理するためには、オープン・カーソルが必要です。OPEN_CURSORファンクションをコールすると、ユーザーはTimesTenが保持している有効なカーソルを示すデータ構造のカーソルID番号を受け取ります。これらのカーソルは、プリコンパイラ、OCIまたはPL/SQLレベルで定義されたカーソルとは異なり、DBMS_SQLパッケージでのみ使用されます。
PARSE
SQL文はすべて、PARSEプロシージャをコールして解析する必要があります。文を解析することによって、その文の構文がチェックされ、プログラム内のカーソルに関連付けられます。
DML文またはDDL文はすべて解析できます。DDL文は解析時に実行され、暗黙のコミットを実行します。
ノート:
DDL文を解析してプロシージャまたはパッケージを削除する場合、問題のプロシージャまたは問題のパッケージ内のプロシージャをまだ使用していると、タイムアウトが発生する可能性があります。プロシージャをコールすると、実行がユーザー側に戻るまで、そのプロシージャは使用中であるとみなされます。このようなタイムアウトは、即座に発生します。
図7-1に、DBMS_SQLの実行フローを示します。
BIND_VARIABLEまたはBIND_ARRAY
多くのDML文では、ユーザーのプログラム内のデータをTimesTenに入力する必要があります。実行時に提供する入力データを含んでいるSQL文を定義する場合は、SQL文内のプレースホルダを使用して、データの提供場所にマークを付ける必要があります。
SQL文内の各プレースホルダに対して、バインド・プロシージャ(BIND_ARRAYプロシージャまたはBIND_VARIABLEプロシージャ)のいずれかをコールして、プログラム内の変数の値(または配列の値)をプレースホルダに提供する必要があります。SQL文が引き続き実行されると、TimesTenは、ユーザーのプログラムが入力変数と出力変数、またはバインド変数に設定したデータを使用します。
DBMS_SQLは、その都度異なるバインド変数を使用してDML文を繰り返し実行できます。BIND_ARRAYプロシージャを使用すると、スカラーのコレクションをバインドでき、それぞれの値はEXECUTEごとに1回だけ入力変数として使用されます。これは、OCIがサポートする配列インタフェースに類似しています。
DEFINE_COLUMNまたはDEFINE_ARRAY
SELECT文内で選択されている行の列は、選択リスト内での相対位置(左から右)によって識別されます。問合せの場合は、定義プロシージャ(DEFINE_COLUMNまたはDEFINE_ARRAY)をコールしてSELECT値を受け入れる変数を指定する必要があり、その方法はINTO句が静的問合せに対して行う場合とほとんど同じです。
DEFINE_ARRAYプロシージャを使用して、単一のSELECT文で行がフェッチされるPL/SQLコレクションを定義できます。DEFINE_ARRAYは、1回のフェッチで複数行をフェッチするインタフェースを提供します。行をフェッチするためにCOLUMN_VALUEプロシージャを使用する前に、DEFINE_ARRAYをコールする必要があります。
EXECUTE
EXECUTEファンクションをコールして、SQL文を実行します。
FETCH_ROWSまたはEXECUTE_AND_FETCH
FETCH_ROWSファンクションは、問合せを満たす行を取得します。フェッチで行を取得できなくなるまで、フェッチを連続実行して別の行を取得します。1回のみの実行に対してEXECUTEをコールしている場合は、EXECUTEの次にFETCH_ROWSをコールするより、EXECUTE_AND_FETCHをコールする方が効率的です。
VARIABLE_VALUEまたはCOLUMN_VALUE
問合せの場合は、COLUMN_VALUEをコールして、FETCH_ROWSコールによって取得する列の値を判別します。RETURNING句によるPL/SQLプロシージャまたはDML文へのコールを含んだ無名ブロックの場合は、VARIABLE_VALUEをコールして、文の実行時に出力変数に割り当てられた値を取得します。
CLOSE_CURSOR
セッションでカーソルが不要な場合は、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ファンクションをコールします。
例外
次の表に、DBMS_SQLで発生する例外を示します。
表7-2 DBMS_SQLで発生する例外
| 例外 | エラー・コード | 説明 |
|---|---|---|
|
|
-6562 |
指定した |
例
この項には、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文は式の値を戻すことができます。この値は、バインド変数に戻されます。
単一行を挿入、更新または削除する場合は、BIND_VARIABLEを使用して、これらのアウトバインドをバインドします。複数行を挿入、更新または削除する場合は、BIND_ARRAYを使用します。これらのバインド変数の値を取得するには、VARIABLE_VALUEをコールする必要があります。
ノート:
これは、DBMS_SQL内でアウトバインドを使用したPL/SQLブロックを実行した後で、VARIABLE_VALUEをコールする必要があることと同様です。
次の例は、表tabが作成されていることを前提にしています。
Command> create table tab (c1 number, c2 number);たとえば、単一行の挿入、単一行の更新、複数行の挿入、複数行の更新、複数行の削除などがあります。
単一行の挿入
これは単一行の挿入を示しています。
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.単一行の更新
これは単一行の更新を示しています。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.複数行の挿入
これは複数行の挿入を示しています。
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;次のスクリプトを使用すると、ttIsqlでこの例を実行できます。
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.複数行の更新
これは複数行の更新を示しています。
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の各値について、どの行が更新されたかを区別する方法はありません。
次のスクリプトを使用すると、ttIsqlでこの例を実行できます。
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;次のスクリプトを使用すると、ttIsqlでこの例を実行できます。
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のBIND_ARRAYは数値を内部的にバインドします。文を実行する回数は、インバインド配列内の要素数によって決まります。
例9: 動的SQLでのPL/SQLブロック
DBMS_SQLまたはEXECUTE IMMEDIATEのいずれかを使用して、動的SQLでPL/SQLブロックを実行できます。この例では、DBMS_SQLを使用しブロックを実行します。
次のプロシージャがあるとします。
Command> create or replace procedure foo is
begin
dbms_output.put_line('test');
end;
/
Procedure created.DBMS_SQLを使用しPL/SQLブロックでこのプロシージャを実行します。
Command> declare
c number;
r number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'begin foo; end;', dbms_sql.native);
r := dbms_sql.execute(c);
end;
/
test
PL/SQL procedure successfully completed.データ構造
DBMS_SQLパッケージは、次のレコード・タイプと表タイプを定義します。
ノート:
-
PLS_INTEGERデータ・タイプとBINARY_INTEGERデータ・タイプは同じです。このドキュメントでは、リファレンス情報でデータ・タイプ(表タイプ、レコード・タイプ、サブプログラム・パラメータ、サブプログラム戻り値など)を示す場合にBINARY_INTEGERを使用しますが、説明および例ではいずれも使用される場合があります。 -
INTEGERデータ・タイプとNUMBER(38)データ・タイプも同じです。このドキュメントでは、全体をとおしてINTEGERを使用します。
表タイプ
DESC_RECレコード・タイプ
ノート:
このタイプは非推奨となっているため、DESC_REC2レコード・タイプを使用することをお薦めします。
このレコード・タイプは、動的問合せ内に単一列の記述情報を保持します。これは、DESC_TAB表タイプおよびDESCRIBE_COLUMNSプロシージャの要素タイプです。
構文
TYPE desc_rec IS RECORD (
col_type BINARY_INTEGER := 0,
col_max_len BINARY_INTEGER := 0,
col_name VARCHAR2(32) := '',
col_name_len BINARY_INTEGER := 0,
col_schema_name VARCHAR2(32) := '',
col_schema_name_len BINARY_INTEGER := 0,
col_precision BINARY_INTEGER := 0,
col_scale BINARY_INTEGER := 0,
col_charsetid BINARY_INTEGER := 0,
col_charsetform BINARY_INTEGER := 0,
col_null_ok BOOLEAN := TRUE);
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;フィールド
表7-3 DESC_RECのフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ |
|
|
列の最大長 |
|
|
列の名前 |
|
|
列名の長さ。 |
|
|
列のスキーマ名 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度 |
|
|
列のスケール |
|
|
列の文字セットID。 |
|
|
列の文字セット形式。 |
|
|
NULL列フラグ( |
DESC_REC2レコード・タイプ
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);フィールド
表7-4 DESC_REC2のフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ |
|
|
列の最大長 |
|
|
列の名前 |
|
|
列名の長さ。 |
|
|
列のスキーマ名 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度 |
|
|
列のスケール |
|
|
列の文字セットID。 |
|
|
列の文字セット形式。 |
|
|
NULL列フラグ( |
DESC_REC3レコード・タイプ
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);フィールド
表7-5 DESC_REC3のフィールド
| フィールド | 説明 |
|---|---|
|
|
列のタイプ |
|
|
列の最大長 |
|
|
列の名前 |
|
|
列名の長さ。 |
|
|
列のスキーマ名 |
|
|
列のスキーマ名の長さ。 |
|
|
列の精度 |
|
|
列のスケール |
|
|
列の文字セットID。 |
|
|
列の文字セット形式。 |
|
|
NULL列フラグ( |
|
|
予約済 |
|
|
予約済 |
BINARY_DOUBLE_TABLE表タイプ
これは、BINARY_DOUBLEの表です。
構文
TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;BINARY_FLOAT_TABLE表タイプ
これは、BINARY_FLOATの表です。
構文
TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;DESC_TAB表タイプ
これは、DESC_RECレコード・タイプの表です。
構文
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;DESC_TAB2表タイプ
これは、DESC_REC2レコード・タイプの表です。
構文
TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;DESC_TAB3表タイプ
これは、DESC_REC3レコード・タイプの表です。
構文
TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER; INTERVAL_DAY_TO_SECOND_TABLE表タイプ
これは、DSINTERVAL_UNCONSTRAINEDの表です。
構文
TYPE interval_day_to_second_Table IS TABLE OF
DSINTERVAL_UNCONSTRAINED INDEX BY binary_integer;INTERVAL_YEAR_TO_MONTH_TABLE表タイプ
これは、YMINTERVAL_UNCONSTRAINEDの表です。
構文
TYPE interval_year_to_month_table IS TABLE OF YMINTERVAL_UNCONSTRAINED
INDEX BY BINARY_INTEGER;TIME_TABLE表タイプ
これは、TIME_UNCONSTRAINEDの表です。
構文
TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;TIMESTAMP_TABLE表タイプ
これは、TIMESTAMP_UNCONSTRAINEDの表です。
構文
TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;VARCHAR2_TABLE表タイプ
これは、VARCHAR2(2000)の表です。
構文
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;VARCHAR2A表タイプ
これは、VARCHAR2(32767)の表です。
構文
TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;VARCHAR2S表タイプ
これは、VARCHAR2(256)の表です。
ノート:
このタイプは、VARCHAR2A表タイプによって置き換えられています。これは、下位互換性を維持する目的のみでサポートされています。
構文
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;DBMS_SQLのサブプログラム
表7-6に、DBMS_SQLのサブプログラムの概要と各サブプログラムの詳細な説明を示します。
表7-6 DBMS_SQLパッケージのサブプログラム
| サブプログラム | 説明 |
|---|---|
|
指定の値を指定のコレクションにバインドします。 |
|
|
指定の値を指定の変数にバインドします。 |
|
|
指定したカーソルをクローズして、メモリーを解放します。 |
|
|
カーソル内の指定位置にあるカーソル要素の値を戻します。 |
|
|
|
重要: TimesTenでは したがって、このマニュアルでは、 |
|
指定したカーソルから選択するコレクションを定義します。 |
|
|
指定したカーソルから選択する列を定義します。 |
|
|
|
指定したカーソルから選択する 重要: TimesTenでは したがって、このマニュアルでは、 |
|
|
|
|
DESCRIBE_COLUMNSプロシージャの代替オプションとして、指定した列の情報を表示します。 |
|
|
DESCRIBE_COLUMNSプロシージャの代替オプションとして、指定した列の情報を表示します。 |
|
|
指定のカーソルを実行します。 |
|
|
指定のカーソルを実行して、行をフェッチします。 |
|
|
指定のカーソルから行をフェッチします。 |
|
|
指定のカーソルがオープンしている場合は |
|
|
エラーが発生したSQL文テキスト内のバイト・オフセットを戻します。 |
|
|
フェッチされた累積行数を戻します。 |
|
|
最後に処理された行のrowidを戻します(TimesTenでは TimesTenは、この機能をサポートしていません。 |
|
|
文のSQLファンクション・コードを戻します。 |
|
|
新規カーソルのID番号を戻します。 |
|
|
指定の文を解析します。 |
|
|
強くまたは弱くタイプ付けされたオープンされている |
|
|
オープン、解析および実行されたカーソルを使用し、PL/SQLシステム固有の動的SQLで消費できるPL/SQLで管理可能な |
|
|
指定のカーソルについて指定の変数の値を戻します。 |
BIND_ARRAYプロシージャ
このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。
構文
DBMS_SQL.BIND_ARRAY (
c IN INTEGER,
name IN VARCHAR2,
<table_variable> IN <datatype>
[,index1 IN INTEGER,
index2 IN INTEGER)] ); table_variableとそれに対応するdatatypeは、次のいずれかの組合せになります。
<bflt_tab> dbms_sql.Binary_Float_Table
<bdbl_tab> dbms_sql.Binary_Double_Table
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_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_TableBIND_ARRAYプロシージャは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表7-7 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 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 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例
例を参照してください。
BIND_VARIABLEプロシージャ
このプロシージャは、文内の変数の名前に基づいて、カーソル内の指定の変数に指定の値または値のセットをバインドします。
構文
DBMS_SQL.BIND_VARIABLE (
c IN INTEGER,
name IN VARCHAR2,
value IN <datatype>);datatypeは、次のいずれかのタイプである必要があります。
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
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 tableBIND_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);パラメータ
表7-8 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バイト以下にする必要があります。
例
例を参照してください。
CLOSE_CURSORプロシージャ
このプロシージャは、指定のカーソルをクローズします。カーソルに割り当てられたメモリーは解放され、そのカーソルからはフェッチできなくなります。
構文
DBMS_SQL.CLOSE_CURSOR (
c IN OUT INTEGER);パラメータ
表7-9 CLOSE_CURSORプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
(
|
COLUMN_VALUEプロシージャ
このプロシージャは、FETCH_ROWSファンクションをコールしてフェッチしたデータへのアクセスに使用されます。これは、指定のカーソルの指定の位置に対してカーソル要素の値を戻します。
構文
DBMS_SQL.COLUMN_VALUE (
c IN INTEGER,
position IN INTEGER,
value OUT <datatype>
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);大カッコ[]はオプション・パラメータを示し、datatypeは次のいずれかのデータ・タイプである必要があります。
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
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 tableCHAR、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
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_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パラメータ
表7-10 COLUMN_VALUEプロシージャのパラメータ(単一行)
| パラメータ | 説明 |
|---|---|
|
|
値をフェッチするカーソルのID番号 |
|
|
カーソルにある列の相対位置(文の最初の列は位置1です) |
|
|
指定した列から戻された値 |
|
|
該当する場合、列値のエラー・コード |
|
|
指定した列内の値の(切捨て前の)実際の長さ |
表7-11 COLUMN_VALUEプロシージャのパラメータ(バルク)
| パラメータ | 説明 |
|---|---|
|
|
値をフェッチするカーソルのID番号 |
|
|
カーソルにある列の相対位置(文の最初の列は位置1です) |
|
|
バルク操作では、サブプログラムによって、(暗黙的に保持される)適切な索引に新しい要素が追加されます。たとえば、DEFINE_ARRAYプロシージャの使用時にバッチ・サイズ( |
例外
ORA-06562: Type of out argument must match type of column or bind variableこの例外は、指定したOUTパラメータのvalueのタイプが、実際の値のタイプと異なると発生します。このタイプは、DEFINE_COLUMNをコールして列を定義したときに指定したタイプです。
例
例を参照してください。
DEFINE_ARRAYプロシージャ
このプロシージャは、指定の列について、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 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 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
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_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_TableDEFINE_ARRAYは、異なるデータ・タイプを受け入れるためにオーバーロードされていることに注意してください。
パラメータ
表7-12 DEFINE_ARRAYプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
配列がバインドされるカーソルのID番号 |
|
|
定義している配列内にある列の相対位置(文の最初の列は位置1です) |
|
|
|
|
|
フェッチする行数 |
|
|
開始索引(コレクションへ結果をコピーする開始位置) |
使用上のノート
行数(cnt)にはゼロより大きい整数を指定する必要があります。lower_bndは、正の数、負の数またはゼロでもかまいません。DEFINE_ARRAYコールが発行された問合せに、配列バインドを含めることはできません。
例外
ORA-29253: Invalid count argument passed to procedure dbms_sql.define_arrayこの例外は、行数(cnt)がゼロ以下であると発生します。
例
例を参照してください。
DEFINE_COLUMNプロシージャ
このプロシージャは、指定のカーソルから選択する列を定義します。このプロシージャが使用できるのは、SELECTカーソルのみです。
定義されている列は、指定のカーソル内にある文のSELECTリスト内での相対位置によって識別されます。COLUMN値のタイプによって、定義されている列のタイプが決まります。
構文
DBMS_SQL.DEFINE_COLUMN (
c IN INTEGER,
position IN INTEGER,
column IN <datatype>);
datatypeは、次のいずれかのタイプである必要があります。
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
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 tableDEFINE_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); パラメータ
表7-13 DEFINE_COLUMNプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
選択対象に定義されている行のカーソルのID番号。 |
|
|
定義している行内にある列の相対位置(文の最初の列は位置1です) |
|
|
定義している列の値 この値のタイプによって、定義している列のタイプが決まります。 |
|
|
タイプ |
例
例を参照してください。
DESCRIBE_COLUMNSプロシージャ
このプロシージャは、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);パラメータ
表7-14 DESCRIBE_COLUMNSプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
表示される列のカーソルのID番号。 |
|
|
問合せの選択リストにある列数。 |
|
|
問合せの各列の情報を表示する表 |
例
例を参照してください。
DESCRIBE_COLUMNS2プロシージャ
このファンクションは、指定した列の情報を表示します。これは、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);パラメータ
表7-15 DESCRIBE_COLUMNS2プロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
表示される列のカーソルのID番号。 |
|
|
問合せの選択リストにある列数。 |
|
|
1から順に、問合せの選択リストの要素数まで索引付けされている、問合せの各列の説明を入力する表 |
DESCRIBE_COLUMNS3プロシージャ
このファンクションは、指定した列の情報を表示します。これは、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);パラメータ
表7-16 DESCRIBE_COLUMNS3プロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
表示される列のカーソルのID番号。 |
|
|
問合せの選択リストにある列数。 |
|
|
1から順に、問合せの選択リストの要素数まで索引付けされている、問合せの各列の説明を入力する表 |
使用上のノート
カーソルIDに基づいて渡されるカーソルには、OPENおよびPARSEを実行しておく必要があり、そうしないと、カーソルIDが無効であるとしてエラーが発生します。
EXECUTEファンクション
INSERT文、UPDATE文およびDELETE文に対してのみ有効です。 DDLを含めた他のタイプの文に対しては、戻り値は定義されず無視されます。
構文
DBMS_SQL.EXECUTE (
c IN INTEGER)
RETURN INTEGER;パラメータ
表7-17 EXECUTEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
実行するカーソルのカーソルID番号 |
戻り値
処理された行数を示すINTEGER値
EXECUTE_AND_FETCHファンクション
このファンクションは、指定のカーソルを実行して行をフェッチします。これは、EXECUTEをコールしてからFETCH_ROWSをコールするのと同じ機能を提供しますが、リモート・データベースに対して使用する場合は、EXECUTE_AND_FETCHをコールした方がネットワークのラウンドトリップ数を低減できます。
EXECUTE_AND_FETCHファンクションは、実際にフェッチされた行数を戻します。
構文
DBMS_SQL.EXECUTE_AND_FETCH (
c IN INTEGER,
exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;パラメータ
表7-18 EXECUTE_AND_FETCHファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
実行してフェッチするカーソルのカーソルID番号 |
|
|
問合せで実際に一致する行数が1以外の場合、例外を発生させるには、 例外が発生しても、行はフェッチされ、使用可能です。 |
戻り値
フェッチされた行数を示すINTEGER値
例外
ORA-01422: Exact fetch returns more than requested number of rowsこの例外は、問合せで一致する行数が1でないと発生します。
FETCH_ROWSファンクション
このファンクションは、指定のカーソルから行をフェッチします。
DEFINE_ARRAYプロシージャ・コールは、行の値がフェッチされるコレクションを定義します。
FETCH_ROWSコールは、DEFINE_ARRAYコールのcntパラメータに基づいて、指定された行数をフェッチします。フェッチされた行は、列ごとにCOLUMN_VALUEプロシージャ・コールを実行するまでDBMS_SQLバッファにコピーされます。その時点で、行は、引数としてCOLUMN_VALUEに渡された表にコピーされます。行が配置される場所は、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;パラメータ
表7-19 FETCH_ROWSファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
フェッチするカーソルのID番号 |
戻り値
フェッチされた行数を示すINTEGER値
例
例を参照してください。
IS_OPENファンクション
このファンクションは、指定のカーソルが現在オープンしているかどうかをチェックします。
構文
DBMS_SQL.IS_OPEN (
c IN INTEGER)
RETURN BOOLEAN;パラメータ
表7-20 IS_OPENファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
チェックするカーソルのカーソルID番号 |
戻り値
オープンされ、クローズされていないカーソル番号に対してはTRUE、NULLのカーソル番号に対してはFALSE
CLOSE_CURSORプロシージャは、渡されたカーソル変数にnullを代入することに注意してください。
例外
ORA-29471 DBMS_SQL access deniedこれは、無効なカーソルID番号が検出された場合に発生します。セッションでこのエラーが発生し、レポートされると、その後は同じセッションでDBMS_SQLをコールするたびにこのエラーが発生しますが、これは、このセッションではDBMS_SQLを実行できなくなることを意味します。
LAST_ERROR_POSITIONファンクション
このファンクションは、エラーが発生したSQL文テキストのバイト・オフセットを返します。SQL文の最初の文字は、0の位置に配置されます。
構文
DBMS_SQL.LAST_ERROR_POSITION
RETURN INTEGER;戻り値
エラーが発生したSQL文テキスト内のバイト・オフセットを示すINTEGER値
使用上のノート
このファンクションは、別のDBMS_SQLプロシージャまたはファンクションのコール前、かつPARSEのコール後にコールしてください。
LAST_ROW_COUNTファンクション
このファンクションは、フェッチされた累積行数を戻します。
構文
DBMS_SQL.LAST_ROW_COUNT
RETURN INTEGER;戻り値
フェッチされた累積行数を示すINTEGER値
使用上のノート
このファンクションは、FETCH_ROWSコールまたはEXECUTE_AND_FETCHコール後にコールしてください。EXECUTEコール後にコールすると、戻される値は0(ゼロ)です。
LAST_ROW_IDファンクション
このファンクションは最後に処理された行のrowidを戻します(TimesTenではNULL)。
TimesTenは、この機能をサポートしていません。
構文
DBMS_SQL.LAST_ROW_ID
RETURN ROWID;戻り値
TimesTenの場合はNULL
LAST_SQL_FUNCTION_CODEファンクション
このファンクションは、文のSQLファンクション・コードを戻します。
これらのコードについては、Oracle Call Interfaceプログラマーズ・ガイドのOCI関数コードの表を参照してください。
構文
DBMS_SQL.LAST_SQL_FUNCTION_CODE
RETURN INTEGER;戻り値
文のSQLファンクション・コードを示すINTEGER値
使用上のノート
このファンクションは、SQL文の実行直後にコールしてください。それ以外の場合、戻り値が定義されません。
OPEN_CURSORファンクション
このプロシージャは、新規のカーソルをオープンします。
2番目のオーバーロードは、security_levelパラメータを使用して、オープンされたカーソルのセキュリティにファイングレイン・コントロールを適用します。ただし、TimesTenではセキュリティは施行されません(security_level=0)。
このカーソルが不要になった場合は、CLOSE_CURSORプロシージャをコールして、明示的にクローズする必要があります。
構文
DBMS_SQL.OPEN_CURSOR
RETURN INTEGER;
DBMS_SQL.OPEN_CURSOR (
security_level IN INTEGER)
RETURN INTEGER;パラメータ
表7-21 OPEN_CURSORファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
オープンされたカーソルに適用するセキュリティ保護のレベルを指定します。TimesTenではセキュリティ・レベル0のみが有効です(レベル1および2はサポートされていません)。
|
戻り値
新しいカーソルのカーソルID
使用上のノート
カーソルを使用すると、同じSQL文を繰り返し実行したり、新規のSQL文を実行することができます。カーソルを再使用した場合、新規のSQL文が解析されるときに、対応するカーソル・データ領域の内容がリセットされます。カーソルを再使用しないかぎり、クローズして再オープンする必要はありません。
PARSEプロシージャ
このプロシージャは、指定したカーソル内の指定した文を解析します。すべての文が即時に解析されます。さらに、DDL文は、解析時にただちに実行されます。
PARSEプロシージャには、複数のバージョンがあります。
-
引数として
VARCHAR2文を使用します。 -
引数として
VARCHAR2(32767)の表VARCHAR2Aを使用します。 -
引数として
VARCHAR2(32767)の表VARCHAR2Sを使用します。 -
引数として
CLOB文を使用します。この解析プロシージャの
CLOBオーバーロード・バージョンを使用すると、32 KBを超えるSQL文を解析できます。
このプロシージャの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);
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER);パラメータ
表7-22 PARSEプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
文を解析するカーソルのID番号 |
|
|
解析するSQL文 CLOBには、32 KBを超えるSQL文を格納できます。 PL/SQL文と異なり、SQL文の終わりにはセミコロンを含めないでください。たとえば: |
|
|
文内の要素の下限。 |
|
|
文内の要素の上限。 |
|
|
連結している各要素の後に改行を挿入する場合は |
|
|
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コンパイルに関する警告を伴ったタイプ、プロシージャ、ファンクションまたはパッケージを作成する場合、この例外が発生しますが、オブジェクトはそのまま作成されます。
例
例を参照してください。
TO_CURSOR_NUMBERファンクション
このファンクションは、強くまたは弱くタイプ付けされたオープンされているREF CURSORを使用して、それをDBMS_SQLカーソル番号に変換します。
構文
DBMS_SQL.TO_CURSOR_NUMBER(
rc IN OUT SYS_REFCURSOR)
RETURN INTEGER;パラメータ
表7-23 TO_CURSOR_NUMBERファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
カーソル番号に変換されるREF CURSOR |
戻り値
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.TO_REFCURSORファンクション
このファンクションは、オープン(OPEN_CURSOR)、解析(PARSE)および実行(EXECUTE)されたカーソルを使用して、それをPL/SQLで管理可能なREF CURSOR(弱くタイプ付けされたカーソル)に変換または移行しますが、変換または移行後のカーソルは、PL/SQLシステム固有の動的SQLで消費できます。
このサブプログラムが使用できるのは、SELECTカーソルのみです。
構文
DBMS_SQL.TO_REFCURSOR(
cursor_number IN OUT INTEGER)
RETURN SYS_REFCURSOR;パラメータ
表7-24 TO_REFCURSORファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
REF CURSORに変換されるカーソルのカーソル番号 |
戻り値
DBMS_SQLカーソル番号から変換されたPL/SQL REF CURSOR
使用上のノート
-
cursor_numberによって渡されるカーソルは、オープン、解析および実行されている必要があります。そうしないと、エラーが発生します。 -
cursor_numberがREF CURSORに変換されると、これはDBMS_SQL操作でアクセスできなくなります。 -
cursor_numberがREF CURSORに変換された後に、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.VARIABLE_VALUEプロシージャ
このプロシージャは、指定のカーソルについて指定の変数の値を戻します。これは、RETURNING句を使用してPL/SQLブロックまたはDML文内のバインド変数の値を戻すために使用されます。
構文
DBMS_SQL.VARIABLE_VALUE (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <datatype>); datatypeは、次のいずれかのタイプである必要があります。
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
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 tableCHAR、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.Blob_Table
dbms_sql.Clob_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パラメータ
表7-25 VARIABLE_VALUEプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
値を取得するカーソルのID番号 |
|
|
取得した値を代入する変数名 |
|
|
単一行オプションの場合、指定した位置の変数の値 配列オプションの場合、 ノート: バルク操作の場合、 |
例外
ORA-06562: Type of out argument must match type of column or bind variableこれは、出力パラメータのタイプがBIND_VARIABLEコールで定義された値のタイプと違う場合に発生します。
例
例を参照してください。