DBMS_SQLパッケージ

7.3 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サブプログラムはリモートで起動できます。

7.3.1 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の場合、プロシージャは問合せ結果をサブプログラムの直接のコール元に戻します。

7.3.2 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)です。

例7-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を使用してその結果をフェッチします。

例7-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: 011.44.1644.429265
Titles:
- Sales Manager (01-JAN-07 - 31-DEC-07)
- Sales Representative (24-MAR-06 - 31-DEC-06)
 
PL/SQL procedure successfully completed.

7.3.3 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カーソル番号がまだオープンしているかどうかを確認すると、エラーが発生します。

例7-13では、DBMS_SQL.TO_REFCURSORファンクションを使用して、DBMS_SQLパッケージからシステム固有の動的SQLに切り替えています。

例7-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;
/

7.3.4 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操作では、この値にアクセスできなくなります。

例7-14では、DBMS_SQL.TO_CURSOR_NUMBERファンクションを使用して、システム固有の動的SQLからDBMS_SQLパッケージに切り替えています。

例7-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;
/