Cursor in PL/SQL

  • Oracle Engine uses a work area for its internal processing to execute SQL statements.
  • This work area is private to SQL's operations and is called a cursor.
  • The data stored in the cursor is called the active data set.
  • Cursor contains information on a SELECT statement and the rows of data accessed by it.
  • It can hold more than one row, but can process only one row at a time.
  • Cursor is used to access the result-set present in the memory.
  • This result set contains the records returned on execution of a query.

Attributes of Cursors

AttributesDescription
%ISOPENReturns TRUE if cursor is open, else FALSE.
%FOUNDReturns TRUE if record was fetch successfully, else FALSE.
%NOTFOUNDReturns TRUE if records was not fetched successfully, else FALSE.
%ROWCOUNTReturns number of records processed from the cursor.

Types of Cursors

The two types of cursors are:

1. Implicit cursors

  • Implicit cursors are automatically generated by the Oracle engine.
  • If the Oracle Engine opens a cursor for its internal processing, it is known as Implicit cursor.
  • Implicit cursors are created by default to process the statements when DML statements(INSERT, UPDATE, DELETE) are executed.
Example:
Update the information of employees using implicit cursor.

Consider the following table titled 'Employee'

IdNameDesignationSalary
1AlbertProgrammer50000
2AnnaHR25000
3MarkAnalyst55000
4JasonContent writer21000
5AndrewProgrammer90000

Write a procedure to update the salary of employees using implicit cursor.

DECLARE   
   total_rows number(2);  
BEGIN  
   UPDATE Employee  
   SET salary = salary + 1000;  
   IF sql%notfound THEN  
      dbms_output.put_line('no Employee updated');  
   ELSIF sql%found THEN  
      total_rows := sql%rowcount;  
      dbms_output.put_line( total_rows | |  ' Employee updated ');  
   END IF;   
END;  
/


The result is shown in the following table.

IdNameDesignationSalary
1AlbertProgrammer51000
2AnnaHR26000
3MarkAnalyst56000
4JasonContent writer21000
5AndrewProgrammer90000

2. Explicit Cursor

  • If a cursor is opened for processing data through a PL/SQL block as per requirement like user defined cursor, is known as an Explicit cursor.
  • Explicit cursor is created while executing a SELECT statement that returns more than one row.
  • These cursor should be defined in the declaration section of the PL/SQL block and created on a SELECT statement which returns more than one row.
Syntax:
Cursor cursor_name IS select_statement;

Following are the steps to work with an explicit cursor:

1. Declare

Syntax:
CURSOR Name IS
SELECT statement;

2.Open

Syntax:
OPEN Cursor_name;
        

3. Fetch

This statement is used to access one row at a time.

Syntax:
FETCH cursor_name INTO variable_list;  
       

4. Close

Syntax:
Close Cursor_name;

Example:
Write a PL/SQL code to retrieve the employee name and designation using explicit cursor.  
Consider the following table titled 'Employee'.

IdNameDesignationSalary
1AlbertProgrammer51000
2AnnaHR26000
3MarkAnalyst56000
4JasonContent writer21000
5AndrewProgrammer90000

Write a Pl/SQL code to retrieve the employee name and designation using explicit cursor.

DECLARE  
c_id employee.id%type;
c_name employee.name%type;  
c_addr employee.designation%type;  
CURSOR c_employee is  
SELECT id, name, designation FROM employee;  
BEGIN  
OPEN c_employee;  
LOOP  
FETCH c_employee into c_id, c_name, c_addr;  
EXIT WHEN c_employee%notfound;
dbms_output.put_line(c_id | |  ' ' | |  c_name | |  ' ' | |  c_addr);  
END LOOP;  
CLOSE c_employee;  
END;
/


Output:
1 Albert Programmer     
2 Anna HR
3 Mark Analyst
4 Jason Content writer
5 Andrew Programmer  
▲