PolarDB:例

関数からREF CURSORを返す

次の例では、特定のジョブを持つ従業員を選択するクエリでカーソル変数を開きます。 カーソル変数は関数のこのRETURNステートメントで指定されているため、関数の呼び出し元が結果セットを使用できるようになります。

CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
リターンSYS_REFCURSOR
IS
    emp_refcur SYS_REFCURSOR;
開始
    OPEN emp_refcur FOR SELECT empno、ename FROM emp WHERE job = p_job;
    RETURN emp_refcur;
エンド; 

この関数は、匿名ブロックの宣言トピックで宣言されているカーソル変数に関数の戻り値を割り当てることにより、次の匿名ブロックで呼び出されます。 結果セットは、このカーソル変数を使用してフェッチされ、閉じられます。

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job emp.job % TYPE := 'SALESMAN';
    v_emp_refcur SYS_REFCURSOR;
開始
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB '| | v_job);
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    v_emp_refcur := emp_by_job(v_job);
    LOOP
        FETCH v_emp_refcurにv_empno、v_ename;
        v_emp_refcur % NOTFOUNDのときに終了します。
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    クローズv_emp_refcur;
エンド; 

匿名ブロックが実行されると、次の出力が生成されます。

従業員と仕事のセールスマン
EMPNO ENAME
----- -------
7499アレン
7521ワード
7654マーティン
7844ターナー 

カーソル操作のモジュール化

次の例は、カーソル変数に対するさまざまな操作を別々のプログラムにモジュール化する方法を示しています。

次の手順では、指定されたカーソル変数を、すべての行を取得するSELECTステートメントで開きます。

CREATE OR REPLACE PROCEDURE open_all_emp ()
    p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
開始
    SELECT empnoの場合はp_emp_refcurを開く、empからename;
エンド; 

このバリエーションは、指定された部門以外のすべての行を取得するSELECTステートメントで指定されたカーソル変数を開きます。

CREATE OR REPLACE PROCEDURE open_emp_by_dept ()
    p_emp_refcur IN OUT SYS_REFCURSOR、
    p_deptno emp.de ptno % TYPE
)
IS
開始
    SELECT empno、ename FROM empのための開くp_emp_refcur
        どこdeptno = p_deptno;
エンド; 

この3番目のバリエーションでは、指定されたカーソル変数をSELECTステートメントで開きます。 また、関数の戻り値は、開かれたカーソル変数であることに注意してください。

関数の作成または置き換えopen_dept (
    p_dept_refcur IN OUT SYS_REFCURSOR
) リターンSYS_REFCURSOR
IS
    v_dept_refcur SYS_REFCURSOR;
開始
    v_dept_refcur := p_dept_refcur;
    オープンv_dept_refcur FOR SELECT deptno, dname FROM dept;
    RETURN v_dept_refcur;
エンド; 

このプロシージャは、従業員番号と名前で構成されるカーソル変数結果セットを取得して表示します。

CREATE OR REPLACE PROCEDURE fetch_emp ()
    p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
    v_empno         emp.empno%TYPE;
    v_ename emp.ename % TYPE;
開始
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH p_emp_refcurにインv_empno、v_ename;
        出口p_emp_refcur % NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    エンドループ;
エンド; 

このプロシージャは、部門番号と名前で構成されるカーソル変数の結果セットを取得して表示します。

CREATE OR REPLACE PROCEDURE fetch_dept ()
    SYS_REFCURSORのp_dept_refcur
)
IS
    v_deptno dept.de ptno % TYPE;
    v_dname dept.dname % TYPE;
開始
    DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
    DBMS_OUTPUT.PUT_LINE('----------');
    LOOP
        FETCH p_dept_refcurにv_deptno、v_dname;
        終了時p_dept_refcur % NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_deptno | | '| | v_dname);
    エンドループ;
エンド; 

このプロシージャは、指定されたカーソル変数を閉じます。

CREATE OR REPLACE PROCEDURE close_refcur ()
    p_refcur IN OUT SYS_REFCURSOR
)
IS
開始
    閉じるp_refcur;
エンド; 

次の匿名ブロックは、前述のすべてのプログラムを実行します。

DECLARE
    gen_refcur SYS_REFCURSOR;
開始
    DBMS_OUTPUT.PUT_LINE('すべての従業員');
    open_all_emp(gen_refcur);
    fetch_emp(gen_refcur);
    DBMS_OUTPUT.PUT_LINE('***************');

    DBMS_OUTPUT.PUT_LINE ('DEPT #10の従業員 ');
    open_emp_by_dept(gen_refcur、10);
    fetch_emp(gen_refcur);
    DBMS_OUTPUT.PUT_LINE('***************');

    DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
    fetch_dept(open_dept(gen_refcur));
    DBMS_OUTPUT.PUT_LINE('*****************');

    close_refcur(gen_refcur);
エンド; 

匿名ブロックから次の出力が生成されます。

すべての従業員
EMPNO ENAME
----- -------
7369スミス
7499アレン
7521ワード
7566ジョーンズ
7654マーティン
7698 BLAKE
7782 CLARK
7788スコット
7839キング
7844ターナー
7876アダムス
7900ジェームズ
7902フォード
7934ミラー
****************
DEPT #10の従業員
EMPNO ENAME
----- -------
7782 CLARK
7839キング
7934ミラー
****************
出国
DEPT DNAME
---- ---------
10会計
20研究
30販売
40の操作
*****************