PolarDB for PostgreSQL(Compatible with Oracle) also supports dynamic queries by using the OPEN FOR USING statement. A string literal or string variable is supplied in the OPEN FOR USING statement to the SELECT statement.
OPEN name FOR dynamic_string
[ USING bind_arg [, bind_arg_2 ] ...] ;
name is the identifier of a previously declared cursor variable. dynamic_string is a string literal or string variable containing a SELECT statement (without the terminating semi-colon (;)). bind_arg, bind_arg_2... are bind arguments that are used to pass variables to corresponding placeholders in the SELECT statement when the cursor variable is opened. The placeholders are identifiers prefixed by a colon character.
The following code is an example of 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 output is generated when the procedure is executed:
EXEC dept_query;
EMPNO ENAME
----- -------
7499 ALLEN
7698 BLAKE
7844 TURNER
In the next example, the previous query is modified to use 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 output is generated:
EXEC dept_query(30, 1500);
EMPNO ENAME
----- -------
7499 ALLEN
7698 BLAKE
7844 TURNER
Finally, a string variable is used to pass SELECT. This provides 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