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