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)
TYPE refCur IS REF CURSOR;
OPEN emp_cv FOR
'SELECT * ' ||
'FROM emp_' || postfix ||
' ORDER BY sal DESC';
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
emp_rec.empno || ': $' ||
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');
SQL> exec show_emp_any('20');
SQL> exec show_emp_any('30');
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60578/，如需转载，请注明出处，否则将追究法律责任。