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

Attributes	Description
%ISOPEN	Returns TRUE if cursor is open, else FALSE.
%FOUND	Returns TRUE if record was fetch successfully, else FALSE.
%NOTFOUND	Returns TRUE if records was not fetched successfully, else FALSE.
%ROWCOUNT	Returns 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'

Id	Name	Designation	Salary
1	Albert	Programmer	50000
2	Anna	HR	25000
3	Mark	Analyst	55000
4	Jason	Content writer	21000
5	Andrew	Programmer	90000

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.

Id	Name	Designation	Salary
1	Albert	Programmer	51000
2	Anna	HR	26000
3	Mark	Analyst	56000
4	Jason	Content writer	21000
5	Andrew	Programmer	90000

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'.

Id	Name	Designation	Salary
1	Albert	Programmer	51000
2	Anna	HR	26000
3	Mark	Analyst	56000
4	Jason	Content writer	21000
5	Andrew	Programmer	90000

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;
/