Using dynamic queries with REF CURSOR v17
EDB Postgres Advanced Server supports dynamic queries by way of the OPEN FOR USING
statement. A string literal or string variable is supplied in the OPEN FOR USING
statement to the SELECT
command:
OPEN <name> FOR <dynamic_string> [ USING <bind_arg> [, <bind_arg_2> ] ...];
Where:
name
is the identifier of a previously declared cursor variable.
dynamic_string
is a string literal or string variable containing a SELECT
command without the terminating semi-colon.
bind_arg, bind_arg_2...
are bind arguments that pass variables to corresponding placeholders in the SELECT
command when the cursor variable is opened. The placeholders are identifiers prefixed by a colon character.
This example shows a dynamic query using a string literal:
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;
The following is the output from this procedure:
EXEC dept_query;
EMPNO ENAME ----- ------- 7499 ALLEN 7698 BLAKE 7844 TURNER
This example query uses bind arguments to pass the query parameters:
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;
The following is the resulting output:
EXEC dept_query(30, 1500);
EMPNO ENAME ----- ------- 7499 ALLEN 7698 BLAKE 7844 TURNER
Finally, a string variable is used to pass the SELECT
, providing the most flexibility:
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; EXEC dept_query(20, 1500);
EMPNO ENAME ----- ------- 7566 JONES 7788 SCOTT 7902 FORD