Systems Engineering and RDBMS

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:

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;

DECLARE
v_object_name test.object_name%type;

BEGIN

IF SQL%FOUND IS NULL AND SQL%NOTFOUND IS NULL THEN
dbms_output.put_line(‘Attribute values are NULL’);
END IF;

—–SELECT
SELECT object_name
INTO v_object_name
FROM test
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);
END IF;

—— UPDATE STATEMENT
UPDATE TEST
SET STATUS = ‘INVALID’
WHERE object_name =’ HELLO';

IF SQL%NOTFOUND THEN
dbms_output.put_line(‘UPDATE RowCount = ‘ || SQL%ROWCOUNT);
END IF;

END;
/

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/

Explicit cursors:

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;
DECLARE
v_object_name test.object_name%type;
v_status test.status%type;
v_opn_stat VARCHAR2(5);

CURSOR c1 IS
SELECT object_name,status
FROM test
WHERE object_Type = ‘VIEW';
BEGIN

OPEN c1;
IF C1%ISOPEN = TRUE THEN
v_opn_stat := ‘TRUE';
ELSE
v_opn_stat := ‘FALSE';
END IF;

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’);
END IF;

LOOP
FETCH c1 INTO v_object_name,v_status;

EXIT WHEN C1%NOTFOUND;

dbms_output.put_line(‘Fetch RowCount = ‘ || C1%ROWCOUNT);

END LOOP;

CLOSE C1;

END;
/

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.

Resources:

  • Articles: – here and here.
  • Oracle documentation – here.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: