Dynamic SQL queries that return multirow result sets[akadia]

The OPEN FOR clause allows to build dynamic SQL with variable table-, or column names that return multirow result sets. Let's take a closer look at OPEN FOR and multirow queries with a concrete example. Create the following tables:

SQL> create table emp_10 as select * from emp where deptno = 10;
SQL> create table emp_20 as select * from emp where deptno = 20;
SQL> create table emp_30 as select * from emp where deptno = 30;

Now look at the following code

CREATE OR REPLACE PROCEDURE show_emp_any (
postfix IN VARCHAR2)
IS
TYPE refCur IS REF CURSOR;
emp_cv refCur;
emp_rec emp%ROWTYPE;
BEGIN
OPEN emp_cv FOR
'SELECT * ' ||
'FROM emp_' || postfix ||
' ORDER BY sal DESC';
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.put_line (
emp_rec.empno || ': $' ||
TO_CHAR (emp_rec.sal)
);
END LOOP;
CLOSE emp_cv;
END;
/

The first step in a dynamic multirow query is to define the cursor variable that will point to the result set in the System Global Area. We declare the cursor variable as an instance of a REF CURSOR type, so we must consider the question of whether to make it strong or weak. A REF CURSOR is strong if it is strongly typed, which means the TYPE statement includes a RETURN clause defining what structure is allowable (number of items and datatypes) for data fetched through a cursor variable based on this TYPE.

With dynamic SQL, however, you are not allowed to use strong REF CURSORs. At compile time, the query associated with the REF CURSOR is just a string, so the PL/SQL engine cannot validate that the query's SELECT list is consistent with the record structure in the REF CURSOR's RETURN clause.

Once that is done, we declare a record that we can use to retrieve individual rows from any of the three tables (all three tables have the same structure, so we can %ROWTYPE the record against any of the tables.

We must use dynamic SQL, since we are constructing the name of the table each time we run the program. But dynamic SQL doesn't require any more in the way of special code than what we have just described. We can use the familiar explicit-cursor syntax unchanged for my cursor-variable-based code, as the following lines demonstrate:

SQL> exec show_emp_any('10');

7839: $5000
7782: $2450
7934: $1300

SQL> exec show_emp_any('20');

7788: $3000
7902: $3000
7566: $2975
7876: $1100
7369: $800

SQL> exec show_emp_any('30');
7698: $2850
7499: $1600
7844: $1500
7521: $1250
7654: $1250
7900: $950

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60578/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60578/