5.4 Using Cursor AttributesWhenever you work with explicit and implicit cursors (including cursor variables), PL/SQL provides a set of cursor attributes that return information about the cursor. PL/SQL 8.1 adds another, composite attribute, SQL%BULK_ROWCOUNT, for use with or after the FORALL statement. All of the current attributes are summarized in Table 5.1 .
In these attributes, cur is the name of an explicit cursor, a cursor variable, or the string "SQL" for implicit cursors (UPDATE, DELETE, and INSERT statements, since none of the attributes can be applied to an implicit query). The %BULK_ROWCOUNT structure has the same semantics as an index-by table. The n th row in this pseudo index-by table stores the number of rows processed by the n th execution of the DML operation in the FORALL statement. Let's examine the behavior of these cursor attributes in FORALL and BULK COLLECT statements by running the script found in the showattr.sql file on the disk. I start out by creating a utility function and general show_attributes procedure: /* Filename on companion disk: showattr.sql */ CREATE OR REPLACE FUNCTION boolstg (bool IN BOOLEAN) RETURN VARCHAR2 IS BEGIN IF bool THEN RETURN 'TRUE '; ELSIF NOT bool THEN RETURN 'FALSE'; ELSE RETURN 'NULL '; END IF; END; / CREATE OR REPLACE PROCEDURE show_attributes ( depts IN number_varray) IS BEGIN FORALL indx IN depts.FIRST .. depts.LAST UPDATE emp SET sal = sal + depts(indx) WHERE deptno = depts(indx); DBMS_OUTPUT.PUT_LINE ( 'FOUND-' || boolstg(SQL%FOUND) || ' ' || 'NOTFOUND-' || boolstg(SQL%NOTFOUND) || ' ' || 'ISOPEN-' || boolstg(SQL%ISOPEN) || ' ' || 'ROWCOUNT-' || NVL (TO_CHAR (SQL%ROWCOUNT), 'NULL')); FOR indx IN depts.FIRST .. depts.LAST LOOP DBMS_OUTPUT.PUT_LINE ( depts(indx) || '-' || SQL%BULK_ROWCOUNT(indx)); END LOOP; ROLLBACK; END; / Then I run a query to show some data and show the attributes for two different lists of department numbers, followed by a use of BULK COLLECT: SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; DECLARE /* No employees in departments 98 and 99 */ depts1 number_varray := number_varray (10, 20, 98); depts2 number_varray := number_varray (99, 98); BEGIN show_attributes (depts1); show_attributes (depts2); END; / DECLARE CURSOR allsals IS SELECT sal FROM emp; salaries number_varray; BEGIN OPEN allsals; FETCH allsals BULK COLLECT INTO salaries; DBMS_OUTPUT.PUT_LINE ( 'FOUND-' || boolstg(SQL%FOUND) || ' ' || 'NOTFOUND-' || boolstg(SQL%NOTFOUND) || ' ' || 'ISOPEN-' || boolstg(SQL%ISOPEN) || ' ' || 'ROWCOUNT-' || NVL (TO_CHAR (SQL%ROWCOUNT), 'NULL')); END; / Here is the output from this script: DEPTNO COUNT(*) ------ --------- 10 3 20 5 30 6 FOUND-TRUE NOTFOUND-FALSE ISOPEN-FALSE ROWCOUNT-8 10-3 98-0 20-5 FOUND-FALSE NOTFOUND-TRUE ISOPEN-FALSE ROWCOUNT-0 99-0 98-0 FOUND-NULL NOTFOUND-NULL ISOPEN-FALSE ROWCOUNT-NULL From this output, we can conclude the following:
The %BULK_ROWCOUNT attribute is a handy device, but it is also quite limited. Keep the following in mind:
If I try to execute code like either of these statements: DBMS_OUTPUT.PUT_LINE (SQL%BULK_ROWCOUNT.COUNT); IF SQL%BULK_ROWCOUNT.FIRST IS NOT NULL I get this error: PLS-00332: "%BULK_ROWCOUNT" is not a valid prefix for a qualified name All you can really do with %BULK_ROWCOUNT is reference individual rows in this special structure. Copyright (c) 2000 O'Reilly & Associates. All rights reserved. |
|