DBMS_SQLパッケージ
DBMS_SQLパッケージは、SQLカーソル番号と呼ばれるエンティティを定義します。SQLカーソル番号は、PL/SQLの整数であるため、コール境界を越えて渡し、格納することができます。
次のいずれかが該当する場合は、DBMS_SQL
パッケージを使用して、動的SQL文を実行する必要があります。
-
実行時まで
SELECT
リストが不明。 -
実行時まで、
SELECT
またはDML文でバインドする必要があるプレースホルダが不明。 -
ストアド・サブプログラムが問合せ結果を暗黙的に(
OUT
REF
CURSOR
パラメータを使用するのではなく)戻すようにする必要があり、それにはDBMS_SQL
.RETURN_RESULT
プロシージャが必要である。
次の状況では、DBMS_SQL
パッケージではなく、システム固有の動的SQLを使用する必要があります。
-
動的SQL文が行を取り出してレコードに入れる場合。
-
INSERT
文、UPDATE
文、DELETE
文、MERGE
文または単一行のSELECT
文である動的SQL文の発行後に、SQLのカーソル属性%FOUND
、%ISOPEN
、%NOTFOUND
または%ROWCOUNT
を使用する場合。
DBMS_SQL
パッケージとシステム固有の動的SQLの両方が必要な場合は、DBMS_SQL
.TO_REFCURSOR
ファンクションとDBMS_SQL
.TO_CURSOR_NUMBER
ファンクションを使用して、それらを切り替えることができます。
ここでのトピック
ノート:
DBMS_SQL
サブプログラムはリモートで起動できます。
関連項目:
-
システム固有の動的SQLの詳細は、「システム固有の動的SQL」を参照してください
-
入力変数または出力変数の数が不明な動的SQL文の実行方法(「メソッド4」)を含む
DBMS_SQL
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
DBMS_SQL.RETURN_RESULTプロシージャ
DBMS_SQL
.RETURN_RESULT
プロシージャは、ストアド・サブプログラムが問合せ結果をクライアント・プログラム(サブプログラムを間接的に起動する)またはサブプログラムの直接のコール元に暗黙的に戻すことを可能にします。DBMS_SQL
.RETURN_RESULT
によって結果が戻された後、受信者のみがこれにアクセスできます。
DBMS_SQL
.RETURN_RESULT
には次の2つのオーバーロードがあります。
PROCEDURE RETURN_RESULT (rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE); PROCEDURE RETURN_RESULT (rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);
rc
パラメータは、オープンされているカーソル変数(SYS_REFCURSOR
)またはオープン・カーソルのカーソル番号(INTEGER
)です。カーソルをオープンしてのそのカーソル番号を取得するには、DBMS_SQL
.OPEN_CURSOR
ファンクション(『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照)を起動します。
to_client
パラメータがTRUE
の場合(デフォルト)、DBMS_SQL
.RETURN_RESULT
プロシージャは問合せ結果を(サブプログラムを間接的に起動する)クライアント・プログラムに戻します。このパラメータがFALSE
の場合、プロシージャは問合せ結果をサブプログラムの直接のコール元に戻します。
関連項目:
-
DBMS_SQL
.RETURN_RESULT
の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 -
Cおよび.NETでの暗黙的問合せ結果のサポートの詳細は、Oracle Call Interfaceプログラマーズ・ガイドを参照してください。
-
暗黙的問合せ結果に対するSQL*Plusのサポートの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください
例8-11 DBMS_SQL.RETURN_RESULTプロシージャ
この例では、プロシージャp
がオプションのto_client
パラメータ(デフォルトではTRUE
)なしで、DBMS_SQL
.RETURN_RESULT
を起動します。したがって、DBMS_SQL
.RETURN_RESULT
は問合せ結果をサブプログラム・クライアント(p
を起動する無名ブロック)に戻します。p
から無名ブロックに戻された結果には、その無名ブロックのみがアクセスできます。
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS c1 SYS_REFCURSOR; c2 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT first_name, last_name FROM employees WHERE employee_id = 176; DBMS_SQL.RETURN_RESULT (c1); -- Now p cannot access the result. OPEN c2 FOR SELECT city, state_province FROM locations WHERE country_id = 'AU'; DBMS_SQL.RETURN_RESULT (c2); -- Now p cannot access the result. END; / BEGIN p; END; /
結果:
ResultSet #1 FIRST_NAME LAST_NAME -------------------- ------------------------- Jonathon Taylor ResultSet #2 CITY STATE_PROVINCE ------------------------------ ------------------------- Sydney New South Wales
DBMS_SQL.GET_NEXT_RESULTプロシージャ
DBMS_SQL
.GET_NEXT_RESULT
プロシージャは、DBMS_SQL
.RETURN_RESULT
プロシージャにより受信者に戻された次の結果を取得します。2つのプロシージャは結果を同じ順序で戻します。
DBMS_SQL
.GET_NEXT_RESULT
には次の2つのオーバーロードがあります。
PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR); PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER);
c
パラメータは、DBMS_SQL
.RETURN_RESULT
プロシージャを使用して問合せ結果を暗黙的に戻すサブプログラムを直接的または間接的に起動するオープン・カーソルのカーソル番号です。
カーソルをオープンしてのそのカーソル番号を取得するには、DBMS_SQL
.OPEN_CURSOR
ファンクションを起動します。DBMS_SQL
.OPEN_CURSOR
にはオプションのパラメータtreat_as_client_for_results
があります。このパラメータがFALSE
の場合(デフォルト)、このカーソルをオープンする(サブプログラムを起動するために)コール元は、DBMS_SQL
.RETURN_RESULT
を使用するサブプログラムからクライアントに対する問合せ結果を受け取るクライアントとして扱われません。これらの問合せ結果はかわりに上位層のクライアントに戻されます。このパラメータがTRUE
の場合、コール元はクライアントとして扱われます。DBMS_SQL
.OPEN_CURSOR
ファンクションの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
rc
パラメータは、カーソル変数(SYS_REFCURSOR
)またはオープン・カーソルのカーソル番号(INTEGER
)です。
例8-12では、プロシージャget_employee_info
はDBMS_SQL
.RETURN_RESULT
を使用して2つの問合せ結果をクライアント・プログラムに戻し、また無名ブロック<<main>>
により動的に起動されています。<<main>>
はget_employee_info
が戻す2つの問合せ結果を受け取る必要があるため、<<main>>
はパラメータtreat_as_client_for_results
をTRUE
に設定したDBMS_SQL
.OPEN_CURSOR
を使用して、get_employee_info
を起動するためのカーソルをオープンします。したがって、DBMS_SQL
.GET_NEXT_RESULT
は結果を<<main>>
に戻し、<<main>>はカーソルrc
を使用してその結果をフェッチします。
例8-12 DBMS_SQL.GET_NEXT_RESULTプロシージャ
CREATE OR REPLACE PROCEDURE get_employee_info (id IN VARCHAR2) AUTHID DEFINER AS rc SYS_REFCURSOR; BEGIN -- Return employee info OPEN rc FOR SELECT first_name, last_name, email, phone_number FROM employees WHERE employee_id = id; DBMS_SQL.RETURN_RESULT(rc); -- Return employee job history OPEN RC FOR SELECT job_title, start_date, end_date FROM job_history jh, jobs j WHERE jh.employee_id = id AND jh.job_id = j.job_id ORDER BY start_date DESC; DBMS_SQL.RETURN_RESULT(rc); END; / <<main>> DECLARE c INTEGER; rc SYS_REFCURSOR; n NUMBER; first_name VARCHAR2(20); last_name VARCHAR2(25); email VARCHAR2(25); phone_number VARCHAR2(20); job_title VARCHAR2(35); start_date DATE; end_date DATE; BEGIN c := DBMS_SQL.OPEN_CURSOR(true); DBMS_SQL.PARSE(c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, ':id', 176); n := DBMS_SQL.EXECUTE(c); -- Get employee info dbms_sql.get_next_result(c, rc); FETCH rc INTO first_name, last_name, email, phone_number; DBMS_OUTPUT.PUT_LINE('Employee: '||first_name || ' ' || last_name); DBMS_OUTPUT.PUT_LINE('Email: ' ||email); DBMS_OUTPUT.PUT_LINE('Phone: ' ||phone_number); -- Get employee job history DBMS_OUTPUT.PUT_LINE('Titles:'); DBMS_SQL.GET_NEXT_RESULT(c, rc); LOOP FETCH rc INTO job_title, start_date, end_date; EXIT WHEN rc%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('- '||job_title||' ('||start_date||' - ' ||end_date||')'); END LOOP; DBMS_SQL.CLOSE_CURSOR(c); END main; /
結果:
Employee: Jonathon Taylor Email: JTAYLOR Phone: 44.1632.960031 Titles: - Sales Manager (01-JAN-17 - 31-DEC-17) - Sales Representative (24-MAR-16 - 31-DEC-16) PL/SQL procedure successfully completed.
DBMS_SQL.TO_REFCURSORファンクション
DBMS_SQL
.TO_REFCURSOR
ファンクションは、SQLカーソル番号を、システム固有の動的SQL文で使用できる弱いカーソル変数に変換します。
SQLカーソル番号をDBMS_SQL
.TO_REFCURSOR
ファンクションに渡す前に、この番号に対してOPEN
、PARSE
およびEXECUTE
を実行する必要があります(そうしない場合、エラーが発生します)。
SQLカーソル番号をREF
CURSOR
変数に変換した後、DBMS_SQL
の操作では、SQLカーソル番号としてではなく、REF
CURSOR
変数としてのみこの値にアクセスできます。たとえば、DBMS_SQL
.IS_OPEN
ファンクションを使用して、変換されたSQLカーソル番号がまだオープンしているかどうかを確認すると、エラーが発生します。
例8-13では、DBMS_SQL
.TO_REFCURSOR
ファンクションを使用して、DBMS_SQL
パッケージからシステム固有の動的SQLに切り替えています。
例8-13 DBMS_SQLパッケージからシステム固有の動的SQLへの切替え
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200); / CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE do_query_1 ( placeholder vc_array, bindvars vc_array, sql_stmt VARCHAR2 ) AUTHID DEFINER IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; bindnames vc_array; empnos numlist; depts numlist; ret NUMBER; isopen BOOLEAN; BEGIN -- Open SQL cursor number: curid := DBMS_SQL.OPEN_CURSOR; -- Parse SQL cursor number: DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE); bindnames := placeholder; -- Bind variables: FOR i IN 1 .. bindnames.COUNT LOOP DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i)); END LOOP; -- Run SQL cursor number: ret := DBMS_SQL.EXECUTE(curid); -- Switch from DBMS_SQL to native dynamic SQL: src_cur := DBMS_SQL.TO_REFCURSOR(curid); FETCH src_cur BULK COLLECT INTO empnos, depts; -- This would cause an error because curid was converted to a REF CURSOR: -- isopen := DBMS_SQL.IS_OPEN(curid); CLOSE src_cur; END; /
DBMS_SQL.TO_CURSOR_NUMBERファンクション
DBMS_SQL
.TO_CURSOR_NUMBER
ファンクションは、(強いか弱いかに関係なく)REF
CURSOR
変数を、DBMS_SQL
サブプログラムに渡すことができるSQLカーソル番号に変換します。
REF
CURSOR
変数をDBMS_SQL
.TO_CURSOR_NUMBER
ファンクションに渡す前に、この変数に対してOPEN
を実行する必要があります。
REF
CURSOR
変数をSQLカーソル番号に変換した後、システム固有の動的SQL操作では、この値にアクセスできなくなります。
例8-14では、DBMS_SQL
.TO_CURSOR_NUMBER
ファンクションを使用して、システム固有の動的SQLからDBMS_SQL
パッケージに切り替えています。
例8-14 システム固有の動的SQLからDBMS_SQLパッケージへの切替え
CREATE OR REPLACE PROCEDURE do_query_2 ( sql_stmt VARCHAR2 ) AUTHID DEFINER IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; desctab DBMS_SQL.DESC_TAB; colcnt NUMBER; namevar VARCHAR2(50); numvar NUMBER; datevar DATE; empno NUMBER := 100; BEGIN -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1'; -- Open REF CURSOR variable: OPEN src_cur FOR sql_stmt USING empno; -- Switch from native dynamic SQL to DBMS_SQL package: 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); -- statements ELSE DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50); END IF; END LOOP; -- Fetch rows with DBMS_SQL package: 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); -- statements END IF; END LOOP; END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END; /