Implicit Cursor in Oracle plsql

Implicit Cursor

Implicit Cursor Oracle plsql:

Types of Cursor?

a. Explicit - If user is declared Cursor, then it is explicit cursor.
b. Implicit - If Oracle is declared Cursor, then it is implicit cursor.

We will have look about Implicit cursor.

When Oracle declare this cursor?

After execution of DML(Select, Insert, Update, Delete) command, Oracle will declare this implicit cursor. It can be also called as SQL.

Importance of Implicit cursor is to provide the status of last execution of DML command whether the last statement is successful or not.

Below 3 attributes are being used for Implicit cursor:

%found      - - > TRUE/FALSE
                       < If DML is successful, returns TRUE, If not then returns FALSE>

%notfound - - > TRUE/FALSE
                        < If DML is not successful, returns TRUE, If DML is successful then returns FALSE>

%rowcount - - > It returns how many rows get affected by last successful DML operation.
                        < For Ex: 15 rows inserted in the xxxxx table
                                         25 rows deleted from the xxxxx table >

How to use this 3 attributes:

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

Please go through the below scenario and understand why we are using implicit cursor.

We have a EMPLOYEE table with the employee no. 1001, 1002,1003, 1005 and if we try to delete the employee no. 1001 & 1004 then the result will be successful even if the employee no. 1004 is not there in the employee table. So in this case implicit cursor is required to know really the record is deleted or not.

DECLARE
         IMPCUR EMPLOYEE.empno%type;
BEGIN
        IMPCUR := &amp;empno;
        DELETE FROM EMPLOYEE WHERE empno = IMPCUR;
        if SQL%found then
             dbms_output.put_line('record deleted');
       else
            dbms_output.put_line('no such employee')
       end if;
       commit;
END;

After executing the above query, it will provide exact result whether the row is deleted or not.

Where COMMIT - > Once the row is deleted from table, we cant retrieve the row in to table.
      ROLLBACK - >  Even if the row is deleted from table, we can retrieve the row in to table.

                      Thanks for visiting our blog !!! Follow us for Oracle plsql training Chennai