Implicit Cursor in Oracle PL SQL

Implicit cursor in Oracle PL-SQL – Learn how Oracle will perform the open, fetches, and close operations on a implicit cursor while executing DML statements with Examples.

Implicit cursor in Oracle PL-SQL

Implicit Cursors are the oracle sql statements that are created automatically when an sql statement is executed.

This sql statement can be a select into query , insert update and delete commands.

• For Insert operations the cursor holds the data that needs to be inserted.
• For Update operation the cursor identifies how many rows are updated(affected)
• For Delete operation the cursor identifies how many rows are deleted(affected)

The process of an implicit Cursor is as follows.

1. Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
2. A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).
3. All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
4. An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
5.  “SQL%” Cursor is the most recently opened cursor .

NOTES

• The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements.
• Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Here is the table naming with Department_TBL1 .

The below table is for  performing DML operations and to understand the concept of Implicit Cursors.

Example Program to Update The department Head in Department_TBL1.

here is the pl/sql block to update the department head to ”SCOTT’ where departmentID naming as ID is 1.

DECLARE
BEGIN
  -- here Update statement is a implicit cursor.
  UPDATE Department_TBL1
  SET DEPT_HEAD   ='SCOTT'
  WHERE ID        =1;
  IF SQL%ROWCOUNT > 0 THEN
    -- Display number of rows > 0 
    --if rows updated with the above update statement
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Rows Updated');
  ELSE
    -- Display no rows updated ,
    --if no records with the above update statement
    DBMS_OUTPUT.PUT_LINE('No Rows Updated');
  END IF;
END;

Output:

1 Rows Updated

To display data after pl/sql block execution please use the following select query.

SELECT * FROM Department_TBL1

Example program with a SELECT INTO clause to store data into a variable.

In the below example , the DEPT_HEAD of a departement assigned to a scalar variable using an INTO clause.

DECLARE
  DEPT_HEAD_NAME VARCHAR2(50);
BEGIN
  -- here SELECT INTO  statement as
  -- a implicit cursor.
  SELECT DEPT_HEAD
  INTO DEPT_HEAD_NAME
  FROM Department_TBL1
  WHERE ID=1;
   -- we can identify number of rows are processed 
   --using "sql%rowcount"
  dbms_output.put_line('Number of rows processed: 
      '||sql%rowcount);
END;

Output

Number of rows processed: 1

The number of records resulted from above sql query in pl-sql block is limited to one by using the WHERE condition.
Thus, the number of rows processed by this implicit cursor can be found by using the attribute rowcount -sql%rowcount”.

NOTE

Implicit cursors also works same as update statement for delete statement also.

Example Program to Delete record in Department_TBL1 with id is 1

DECLARE
BEGIN
  -- here Update statement is a implicit cursor.
  DELETE
  FROM Department_TBL1
  WHERE ID        =1;
  IF SQL%ROWCOUNT > 0 THEN
    -- Display number of rows > 0
    --if rows DELETED with the DELETE  statement
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Rows Deleted');
  ELSE
    -- Display no rows DELETED ,
    --if no records with the above delete statement
    DBMS_OUTPUT.PUT_LINE('No Rows deleted');
  END IF;
END;