When I use an implicit cursor, Oracle opens, parses, executes and eventually closes that cursor for me – implicitly. So how is it possible that I can get information about that closed cursor through SQL%ROWCOUNT?
Answer:
On the one hand, I could offer a somewhat flip answer: you can find out the number of rows queried or modified by your implicit query using SQL%ROWCOUNT because Oracle makes that information available to you.
'Nuff said? Not really. In fact, there are many nuances to the way that Oracle keeps track of cursor attributes and offers that information to us. Among other things, it is important to recognize that the implicit, SQL% cursor attributes reflect the status of the most recently executed SQL statement (specifically, SQL% attributes report on the outcome from the most recent implicit cursor in your current recursive call, which is demonstrated by the code in the section "Script showing implicit cursor attributes" found at the end of this answer) – and which statement that is may not be entirely clear at all times. I will come back to that point after reviewing some basics about cursor attributes.
Oracle supports the following cursor attributes for both implicit (SQL%) and explicit cursors:
%IsOpen – returns TRUE if the cursor is open
%Found and %NotFound – was a row found in that last fetch, or not?
%RowCount – returns the number of rows fetched or modified
These two next implicit cursor attributes are set by Oracle only after execution of a FORALL statement:
SQL%Bulk_RowCount – a "pseudo-collection" that tells you about the number of rows modified by the statement. That is, SQL%BULK_ROWCOUNT(i) contains the number of rows processed by the ith execution of the FORALL's DML statement.
SQL%Bulk_Exceptions – a "pseudo-collection" that tells you which rows in the collection referenced by a FORALL statement caused an exception to be raised
You can access an explicit cursor attribute for an explicit cursor or for a cursor variable based on a ref cursor. Note that if the explicit cursor is not open, then accessing any but the %IsOpen attribute will raise the "ORA-01001: invalid cursor" exception. You can see this behavior by running the code found in the section "Script showing explicit cursor attributes" found at the end of this answer.
Life with implicit cursors is a bit different. First of all, the term "implicit cursor" has two distinct meanings:
(1) (informal) any cursor that PL/SQL looks after for you without you having to declare it. This would include the cursor that supports an implicit cursor for loop.
(2) (formal) a cursor that you don't declare and whose attributes are accessible via the Sql% notation. This excludes the cursor that supports an implicit cursor for loop.
The implicit cursor for loop has no effect on the current values of the Sql% attributes. They stand unchanged even inside the loop, even though the informal, implicit cursor of that loop is open. Thus, all references to the implicit cursor attributes in this answer below have to do with formal implicit cursors as just defined.
Here are some things to keep in mind about regarding implicit cursor attributes:
• You can always access SQL%attribute, without fear of raising ORA-01001.
• SQL%IsOpen always returns false, since Oracle always opens, processes, and closes an implicit cursor, ahem, implicitly. But the other implicit cursor attributes retain their settings, which explains why you can access SQL%ROWCOUNT even though the cursor is closed!
• Implicit cursors come into play with any of the following: (a) static SQL: select into, an implicit cursor for loop, and all insert, update, or delete (incl forall); (b) execute immediate (incl forall)
• Implicit cursors are never used with DBMS_Sql
• An implicit cursor for loop doesn't affect the values of the Sql% attributes. The code in "Script showing implicit cursor attributes" demonstrates this behavior.
You can read lots more about this topic in the Oracle documentation located at:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1275
as well as Chapters 14 and 15 of Oracle PL/SQL Programming, 4th edition (http://www.amazon.com/gp/product/0596009771/sr=1-1/qid=1137633135/ref=pd_bbs_1/104-9087433-0361506?%5Fencoding=UTF8 or http://www.oreilly.com/catalog/oraclep4/) also cover this material in some detail.
Let's take a look at an example. Suppose, for example, that I create and populate this table:
DROP TABLE otn_books
/
CREATE TABLE otn_books (
title VARCHAR2(1000),
author VARCHAR2(1000),
page_count INTEGER
)
/
BEGIN
INSERT INTO otn_books
VALUES (
'Fun with PL/SQL'
, 'Christopher Racicot', 100 );
INSERT INTO otn_books
VALUES (
'Do it Right with PL/SQL'
, 'Bryn Llewellyn', 200 );
INSERT INTO otn_books
VALUES (
'Seemingly Endless Thoughts about PL/SQL'
, 'Steven Feuerstein', 1000 );
END;
/
I then create a procedure that displays the highest page count of all PL/SQL books:
CREATE OR REPLACE PROCEDURE show_max_count
IS
l_total_pages PLS_INTEGER;
BEGIN
SELECT MAX ( page_count )
INTO l_total_pages
FROM otn_books
WHERE title LIKE '%PL/SQL%';
DBMS_OUTPUT.put_line ( 'Biggest PL/SQL book has '
|| l_total_pages
|| ' pages.'
);
END show_max_count;
/
Finally, I write a anonymous block of code that reduces the page count of all books shows the highest page count of all PL/SQL books and then displays the value of SQL%ROWCOUNT:
BEGIN
UPDATE otn_books
SET page_count = page_count / 2
WHERE title LIKE '%PL/SQL%';
show_max_count;
DBMS_OUTPUT.put_line ( 'Number of rows modified...or is it? '
|| SQL%ROWCOUNT
);
END;
/
And when I run this script I see the following output:
Biggest PL/SQL book has 500 pages. Number of rows modified...or is it? 1
Notice that SQL%ROWCOUNT does not return 3, which was the number of rows modified by the UPDATE statement in my block. Instead, that implicit cursor attribute shows me the number of rows returned by the query hidden away (from the view of the anonymous block) in the show_max_count procedure.
With that understood, we can now finish up with a best practice recommendation:
If you are need to retrieve information from the SQL% cursor attributes, you should do so immediately after execution of that SQL statement, so you can be sure that the value returned by this attribute reflects the correct statement.
For more discussion of related topics, you might also want to check out these previously published Q&As on the Best Practice PL/SQL OTN page: "What's going on inside that cursor FOR loop?" "SQL%ROWCOUNT in cursor FOR loop? Sure, for all the good it will do you..."
Script showing implicit cursor attributes -- Provided by Bryn Llewellyn, PL/SQL Product Manager
create or replace procedure Show_Implicit_Cursor_Attrs(t in varchar2) is
c number;
begin
DBMS_Output.Put_Line(Chr(10)||t);
c := Sql%RowCount;
if c is null then
DBMS_Output.Put_Line('Sql%RowCount: null');
else
DBMS_Output.Put_Line('Sql%RowCount: '||c);
end if;
if Sql%Found then
DBMS_Output.Put_Line('Sql%Found: true');
else
DBMS_Output.Put_Line('Sql%Found: false');
end if;
if Sql%IsOpen then
DBMS_Output.Put_Line('Sql%IsOpen: true');
else
DBMS_Output.Put_Line('Sql%IsOpen: false');
end if;
end Show_Implicit_Cursor_Attrs;
/
create or replace procedure p is
begin
Show_Implicit_Cursor_Attrs('In dynamic call to proc before implicit sql');
-- deletes 6 rows
delete from Employees where Manager_Id = 146;
Show_Implicit_Cursor_Attrs('In dynamic call to proc after implicit sql');
end p;
/
create or replace procedure t is
v Employees.Last_Name%type;
begin
Show_Implicit_Cursor_Attrs('At start');
select Last_Name into v from Employees where Employee_Id = 100;
Show_Implicit_Cursor_Attrs('After static select into');
declare n pls_integer := 0;
begin
for j in (select Last_Name from Employees where Employee_Id > 203) loop
n := n + 1;
Show_Implicit_Cursor_Attrs('During implicit cursor for loop');
end loop;
DBMS_Output.Put_Line(n||' rows found');
Show_Implicit_Cursor_Attrs('After implicit cursor for loop');
end;
select Last_Name into v from Employees where Employee_Id = 100;
Show_Implicit_Cursor_Attrs('After another static select into');
-- deletes 8 rows
delete from Employees where Manager_Id = 122;
Show_Implicit_Cursor_Attrs('After static delete');
-- deletes 5 rows
execute immediate 'delete from Employees where Manager_Id = 108';
Show_Implicit_Cursor_Attrs('After ex im delete');
declare Dummy number; c number := DBMS_Sql.Open_Cursor();
begin
DBMS_Sql.Parse(c, 'begin p(); end;', Dbms_Sql.Native);
Dummy := DBMS_Sql.Execute(c);
DBMS_Sql.CLose_Cursor(c);
end;
Show_Implicit_Cursor_Attrs('After dynamic call to proc that does implicit sql');
rollback;
end t;
/
begin Show_Implicit_Cursor_Attrs(''); end;
/
begin p(); end;
/
begin Show_Implicit_Cursor_Attrs(''); end;
/
rollback
/
begin
p();
Show_Implicit_Cursor_Attrs('');
rollback;
end;
/
begin t(); end;
/
Script showing explicit cursor attributes
-- Provided by Bryn Llewellyn, PL/SQL Product Manager
declare
v Employees.Last_Name%type;
cursor Cur(Id in Employees.Employee_Id%type) is
select Last_Name into v from Employees where Employee_Id > Id;
procedure Show_Cur_Attrs is
Invalid_Cursor exception;
pragma Exception_Init(Invalid_Cursor, -01001);
begin
DBMS_Output.Put_Line('');
if Cur%IsOpen then
DBMS_Output.Put_Line('Cur%IsOpen: true');
else
DBMS_Output.Put_Line('Cur%IsOpen: false');
end if;
begin
if Cur%Found then
DBMS_Output.Put_Line('Cur%Found: true');
else
DBMS_Output.Put_Line('Cur%Found: false');
end if;
exception when Invalid_Cursor then
DBMS_Output.Put_Line('Invalid_Cursor caught for %Found');
end;
begin
DBMS_Output.Put_Line('Cur%Rowcount: '||Cur%Rowcount);
exception when Invalid_Cursor then
DBMS_Output.Put_Line('Invalid_Cursor caught for %Rowcount');
end;
end Show_Cur_Attrs;
begin
open Cur(203);
loop
fetch Cur into v;
Show_Cur_Attrs();
exit when Cur%NotFound;
end loop;
close Cur;
Show_Cur_Attrs();
rollback;
end;
/
No comments:
Post a Comment