Database Reference
In-Depth Information
Figure 24.11
Using EXECUTE
IMMEDIATE to
Execute SQL DML
PL/SQL Blocks.
24.6.1
Building Cursors Dynamically
Cursors can also be executed dynamically. The following example uses a
REF cursor. There are other methods of coding dynamic cursors in PL/
SQL, but a simple example will suffice here. The result of the following
code is shown in Figure 24.12.
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE GETROWS
(VCOUNTRY IN ARTIST.COUNTRY%TYPE) AS
TYPE TARTIST IS REF CURSOR;
CARTIST TARTIST;
RARTIST ARTIST%ROWTYPE;
BEGIN
OPEN CARTIST FOR 'SELECT * FROM ARTIST
WHERE COUNTRY = '''||VCOUNTRY||'''';
LOOP
FETCH CARTIST INTO RARTIST;
EXIT WHEN CARTIST%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RARTIST.NAME
||' comes from '||RARTIST.COUNTRY);
END LOOP;
CLOSE CARTIST;
END;
/
EXEC GETROWS('CAN');
