REFCURSORバインド変数の使用方法

SQL*PlusのREFCURSORバインド変数を使用すると、PL/SQLブロックに含まれるSELECT文の結果をSQL*Plusからフェッチし、その書式を設定できます。

REFCURSORバインド変数は、ストアド・プロシージャ内のPL/SQLカーソル変数の参照にも使用できます。この機能を使用して、データベース内にSELECT文を格納し、それらの文をSQL*Plusから参照できます。

REFCURSORバインド変数は、ストアド・ファンクションから戻すこともできます。

PL/SQL procedure successfully completed.

この時点で、SELECT文の結果をSQL*PlusでPRINTコマンドを使用して表示できます。

PRINT employee_info

EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

PRINT文は、カーソルもクローズします。結果を再出力するには、PRINTを使用する前にPL/SQLブロックを再実行する必要があります。

Package created.

次に、OPEN... FOR SELECT文が含まれたストアド・プロシージャを作成します。

CREATE OR REPLACE PACKAGE BODY EmpPack AS
  PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS
  BEGIN
    OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
    FROM EMP_DETAILS_VIEW
    WHERE JOB_ID='SA_MAN' ;
  END;
END;
 /

Procedure created.

SQL*Plusバインド変数をパラメータとして、プロシージャを実行します。

VARIABLE cv REFCURSOR
EXECUTE EmpPack.EmpInfoRpt(:cv)

PL/SQL procedure successfully completed.

この時点でバインド変数を出力します。

PRINT cv

EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

このプロシージャは、同じまたは別のREFCURSORバインド変数を使用して何度でも実行できます。

VARIABLE pcv REFCURSOR
EXECUTE EmpInfo_rpt(:pcv)

PL/SQL procedure successfully completed.

PRINT pcv

EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

Function created.

このファンクションを実行します。

VARIABLE rc REFCURSOR
EXECUTE :rc := EmpInfo_fn

PL/SQL procedure successfully completed.

この時点でバインド変数を出力します。

PRINT rc

EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

このファンクションは、同じまたは別のREFCURSORバインド変数を使用して何度でも実行できます。

EXECUTE :rc := EmpInfo_fn

PL/SQL procedure successfully completed.

例5-14 REFCURSORバインド変数の作成、参照および表示

REFCURSORバインド変数を作成、参照および表示するには、最初にREFCURSORデータ型のローカル・バインド変数を宣言します。

create procedure p4 as
  c1 sys_refcursor;
  c2 sys_refcursor;
begin
  open c1 for SELECT * FROM DEPT;
  dbms_sql.return_result(c1);
  open c2 for SELECT * FROM EMP;
  dbms_sql.return_result(c2);
end;
/

次に、このバインド変数を使用するPL/SQLブロックをOPEN... FOR SELECT文に入力します。この文は、カーソル変数をオープンし、問合せを実行します。OPENコマンドおよびカーソル変数については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

この例では、SQL*Plusのemployee_infoバインド変数をカーソル変数にバインドします。

BEGIN
OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY
FROM 	EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ;
END;
 /

例5-15 ストアド・プロシージャでのREFCURSOR変数の使用方法

REFCURSORバインド変数は、パラメータとしてプロシージャに渡されます。パラメータは、REF CURSOR型です。最初に、型を定義します。

CREATE OR REPLACE PACKAGE EmpPack AS
  TYPE EmpInfoTyp IS REF CURSOR;
  PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp);
END EmpPack;
/

例5-16 ストアド・ファンクションでのREFCURSOR変数の使用方法

次の形式で、OPEN... FOR SELECT文を含むストアド・ファンクションを作成します。

CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN -
cv_types.EmpInfo IS
resultset cv_types.EmpInfoTyp;
BEGIN
OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
RETURN(resultset);
END;
/