カーソル変数による動的照会 (PL/SQL)

データ・サーバーでは、PL/SQL コンテキストにおける、OPEN FOR ステートメントによる動的照会がサポートされています。

構文

構文図を読む構文図をスキップするOPEN cursor-variable-nameFORdynamic-string USING,bind-arg

説明

OPEN cursor-variable-name
PL/SQL コンテキスト内で既に宣言されているカーソル変数の ID を指定します。
FOR dynamic-string
SELECT ステートメントが含まれるストリング・リテラルまたはストリング変数を指定します (終了のセミコロンは付けません)。 ステートメントには、名前付きパラメーター (例えば、:param1 など) を含めることができます。
USING bind-arg
1 つ以上のバインド引数を指定します。dynamic-string 内のプレースホルダーは、カーソルのオープン時に、バインド引数の値に置換されます。

以下の例は、ストリング・リテラルを使用する動的照会を示しています。
CREATE OR REPLACE PROCEDURE dept_query
IS
    emp_refcur      SYS_REFCURSOR;
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
BEGIN
    OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||
        ' AND sal >= 1500';
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO v_empno, v_ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_refcur;
END;
以下に、DEPT_QUERY プロシージャーによって生成される出力例を示します。
CALL dept_query;

EMPNO    ENAME
-----    -------
7499     ALLEN
7698     BLAKE
7844     TURNER
前述の例に示す照会を、バインド引数を加えて変更し、照会パラメーターを渡すようにすることができます。
CREATE OR REPLACE PROCEDURE dept_query (
    p_deptno        emp.deptno%TYPE,
    p_sal           emp.sal%TYPE
)
IS
    emp_refcur      SYS_REFCURSOR;
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
BEGIN
    OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = :dept'
        || ' AND sal >= :sal' USING p_deptno, p_sal;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO v_empno, v_ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_refcur;
END;
以下の CALL ステートメントにより、前述の例で生成された出力と同じものが生成されます。
CALL dept_query(30, 1500);
以下のようにストリング変数を使用して SELECT ステートメントを渡すと、柔軟性が最も高くなります。
CREATE OR REPLACE PROCEDURE dept_query (
    p_deptno        emp.deptno%TYPE,
    p_sal           emp.sal%TYPE
)
IS
    emp_refcur      SYS_REFCURSOR;
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    p_query_string  VARCHAR2(100);
BEGIN
    p_query_string := 'SELECT empno, ename FROM emp WHERE ' ||
        'deptno = :dept AND sal >= :sal';
    OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO v_empno, v_ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_refcur;
END;
以下に、この形式の DEPT_QUERY プロシージャーによって生成される出力例を示します。
CALL dept_query(20, 1500);

EMPNO    ENAME
-----    -------
7566     JONES
7788     SCOTT
7902     FORD