PL/SQL - Implicit Cursors

Cursor

Oracle creates a memory area, known as the context area for processing an SQL statements, which contain all the informations needed for procesing the SQL statements.

A Cursor is the pointer to the memory area. PL/SQL controls the memory area through the cursor. It holds the rows (one or more) returned by the SQL statement. The set of rows the cursor holds is referred as the Active set.

These cursors can be named, so that you can rerferred them in a program to fetch and process the rows returned by the SQL statement.

There are two types of cursors,

• Implicit cursor
• Explicit cursor

Implicit cursor

Implicit cursors are automatically created by the Oracle whenever a DML statement is executed, when there is no explicit cursor for the statement. These cursors cannot be named, and hence they cannot be controlled or referred from another place of the code.

For INSERT operations, the cursor hold the data needs to be insreted. For UPDATE and DELETE the cursor finds the rows that would be affected.

In PL/SQL, the most recent cursor as the SQL cursor, which have the attributes such as %FOUND, %NOTFOUND, %ISOPEN, AND %ROWCOUNT. And additonal attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

Attributes

• %FOUND
  Returns TRUE if an INSERT, UPDATE, OR DELETE statement affected one or more rows or a SELECT INTO statement returned one or two. Otherwisem it returns FALSE.

• %NOTFOUND
  Opposite of %FOUND. Returns TRUE if an INSERT, UPDATE, OR DELETE statement affected no rows or a SELECT INTO statement returned no rows. Otherwisem it returns FALSE.

• %ISOPEN
  Always return FALSE for implicit cursor, because Oracle close the cursor automaticlly after executing the SQL statement.

• %ROWCOUNT
  Returs the number of row affected by a DML SQL statemet.

sql%attribute_name is the sytax for acessing the SQL cursor attribute.

Example

SELECT * FROM CUSTOMERS;

+----+----------+ 
| ID | NAME     | 
+----+----------+
|  1 | BINO     |  
+----+----------+
|  2 | JINO     |  
+----+----------+

The below will update the table and change the name and use the sql%rowcount attribute to find the number of rows affected.

DECLARE  
   total_rows_affected number(2);  
BEGIN 
   
   UPDATE CUSTOMERS SET NAME = 'BINO VARGHESE' WHERE NAME = 'BINO';
   
   IF sql%notfound THEN 
      dbms_output.put_line('no rows affected.'); 
   ELSIF sql%found THEN 
      total_rows_affected := sql%rowcount;
      dbms_output.put_line( total_rows_affected || ' row(s) affected.'); 
   END IF;  
END; 

The output will be as following

PL/SQL procedure successfully completed.

1 row(s) affected. 

if the DBMS.OUTPUT.PUT_LINE is not printing you may refer this Stack overflow question.