커서 변수에 의한 동적 쿼리(PL/SQL)

Db2® 데이터 서버는 PL/SQL 컨텍스트의 OPEN문을 통해 동적 쿼리를 지원합니다.

구문

설명

OPEN cursor-variable-name: PL/SQL 컨텍스트에서 이전에 선언된 커서 변수의 ID를 지정합니다.
FOR 동적 문자열: SELECT문을 포함하는 문자열 리터럴 또는 문자열 변수를 지정합니다(종료 세미콜론 없음). 명령문에는 이름 지정된 매개변수(예: :param1)가 포함될 수 있습니다.
USING 바인드-arg: 하나 이상의 바인드 인수를 지정하며, 커서가 열릴 때 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