Back to the basics: Explicit & Implicit cursors
Posted by decipherinfosys on July 11, 2009
Today in this blog post, we will re-visit most fundamental construct used in PL/SQL programming called “cursors”. Cursors are nothing but the private SQL area in the memory. Cursors provide mechanism to work on individual records by looping through it when more than one records are fetched from the database.
They are divided in two categories:
• Implicit cursors
• Explicit cursors
Implicit cursors are also known as SQL Cursors. Oracle opens implicit cursors for every DML (Insert/Update/Delete) statement. It also opens implicit cursor for the ‘select’ statement, which returns exactly one row. With implicit cursors we don’t have to worry about opening and closing the cursors and fetching the data from the cursors. Oracle handles it automatically so we don’t have to write code to handle it. But there are certain cursor attributes available through which we can track the information about execution of the implicit cursors. Cursor attributes are
• %FOUND (Returns True/False)
• %NOTFOUND (Returns True/False)
• %ISOPEN (Always returns False in case of implicit cursors as cursor is closed internally immediately after execution)
• %ROWCOUNT (# of records effected by DML or SELECT statement)
Let us start with an example to see how we can track cursor execution information using these attributes. First of all we will create test table to work with.
SQL> CREATE TABLE TEST AS SELECT * FROM USER_OBJECTS;
Now we will write small PL/SQL block that has select statement and one DML statement as well to check the attributes of the implicit cursor.
SET SERVEROUTPUT ON;
IF SQL%FOUND IS NULL AND SQL%NOTFOUND IS NULL THEN
dbms_output.put_line(‘Attribute values are NULL’);
WHERE object_Type = ‘TRIGGER';
dbms_output.put_line(‘SELECT RowCount = ‘ || SQL%ROWCOUNT);
—— DELETE statement
DELETE FROM TEST WHERE OBJECT_TYPE = ‘VIEW';
IF SQL%FOUND THEN
dbms_output.put_line(‘DELETE RowCount = ‘ || SQL%ROWCOUNT);
—— UPDATE STATEMENT
SET STATUS = ‘INVALID’
WHERE object_name =’ HELLO';
IF SQL%NOTFOUND THEN
dbms_output.put_line(‘UPDATE RowCount = ‘ || SQL%ROWCOUNT);
Here is the output of the execution.
Attribute values are NULL
SELECT RowCount = 1
DELETE RowCount = 4
UPDATE RowCount = 0
PL/SQL procedure successfully completed.
Above example, gives the results for all the cursor attributes except %isopen. Notice the first line of output. %found and %notfound attributes has null value before the execution of the statement. If dml statement effects one or more than one rows then %found is true otherwise it is false. Similarly if dml statement doesn’t effect any rows, %notfound is true else false.
Apart from these regular attributes, implicit cursor has one more attribute SQL%BULK_ROWCOUNT when we are using bulk collect to fetch multiple records. We have covered it in our previous blog post http://decipherinfosys.wordpress.com/2007/10/12/bulk-collect-and-rowcount-sqlbulk_rowcount/
When user has to work on specific record for processing, he/she has to define explicit cursor. Cursor is called explicit when user, names the cursor and associate it with query. This is the very first step when dealing with explicit cursors. It is known as defining or declaring a cursor. Next three phases are
• Opening a cursor (This will initialize the cursor and identifies the result set.)
• Fetching data from a cursor (This will retrieve the result set for the executed query)
• Closing a cursor (Once execution is done, disable the cursor)
Explicit cursors also have the same attributes as implicit cursors but when used they are precede with the cursor name. Let us see it with an example now.
SET SERVEROUTPUT ON;
CURSOR c1 IS
WHERE object_Type = ‘VIEW';
IF C1%ISOPEN = TRUE THEN
v_opn_stat := ‘TRUE';
v_opn_stat := ‘FALSE';
dbms_output.put_line(‘Cursor Open Status = ‘ || v_opn_stat);
IF C1%FOUND IS NULL AND C1%NOTFOUND IS NULL THEN
dbms_output.put_line(‘Attribute value is NULL’);
FETCH c1 INTO v_object_name,v_status;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(‘Fetch RowCount = ‘ || C1%ROWCOUNT);
In above PL/SQL block, we have covered the attributes of the explicit cursor. %found and %notfound condition gives an error ‘invalid cursor’ if they are used before opening a cursor. Since Boolean variable can’t be used in dbms_output,put_line procedure, we are assigning the value to the varchar variable based on the condition.
One thing to remember that cursor attributes always correspond to the last SELECT or last DML statement executed. If two or more DML statements are fired back to back and user is checking the cursor attribute, it will retain results only for the last statement. If we need to check the cursor attribute for the first statement or any interim statement later in the code, we need to assign it to temporary variable and later on use the temporary variable for checking.
One can also pass parameter to the cursor and use the parameter values in the query rather than passing static values. We will cover it in more detail in our future blog post.