Cursors in Oracle

Cursor

• Oracle creates an area of memory called a Context Area to process SQL statements.
• The context area includes:
  
  
  1. Number of rows processed by the statement.
  2. A Cursor is a pointer to a Context Area.
     
     
• A SQL query result is called an Active set of records.
• This temporary work area is used to store the data retrieved from the database and manipulate this data.
• Once the complete result set is fetched, the context area is flushed out to release back the memory.

Features

• A Cursor allows fetching and processing rows returned by a select statement and process one row at a time.

Types

There are the following two types of Cursors:

1. Implicit Cursors
2. Explicit Cursors

Implicit Cursors

• An Implicit Cursor that is automatically declared by an Oracle Server statement is executed
• User/Programmer cannot control the information in this cursor

Cursor Attributes

Attributes	Working
% IS OPEN	Returns BOOLEAN value Evaluate to TRUE IF the cursor is open else evaluates to FALSE
%FOUND	Returns BOOLEAN value Evaluates to TRUE IF the most resent fetch returns a row else evaluates to FALSE
%NOTFOUND	Returns BOOLEAN value Evaluates to TRUE IF the most resent fetch does not return a row else evaluates to FALSE
%ROWCOUNT	Returns a number Evaluates the total number of row returned so far by the cursor.

Create Table

CREATE TABLE Employee_Detail
(
   EMP_ID NUMBER, EMP_NAME VARCHAR(50), Designation VARCHAR(50), SALARY NUMBER
)

Insert Records

insert into Employee_Detail values(100, 'Rupesh Kahane', 'CEO', 30000)
insert into Employee_Detail values(101, 'Vaibhav Taware', 'Developer', 14000)
insert into Employee_Detail values(102, 'Ajit Katte', 'Sales', 18000)
insert into Employee_Detail values(103, 'Vaibhav Taware', 'B Developer', 17000)
insert into Employee_Detail values(104, 'Mitesh M', 'Tester', 9000)
insert into Employee_Detail values(105, 'Abhijit', 'Team Lead', 20000)
insert into Employee_Detail values(106, 'Nikhil G', 'Developer', 20000)

Declare

v_Designation Employee_detail.Designation%Type := 'P LEAD';
BEGIN
Update Employee_detail Set Designation ='Developer'
where Designation =v_Designation;
DBMS_Output.PUT_LINE(SQL%ROWCOUNT || ' Rows are Updated');
IF SQL%NOTFOUND THEN
DBMS_Output.PUT_LINE('Data not found...Process Terminated');
END IF;
END;

Output


Explicit Cursor 

• An Explicit Cursor is defined within the program for a query that returns more than one row of data.
• This cursor allows sequential processing of each row of the returned data from the database.
• An Explicit Cursor is declared using Name with an association of a SELECT statement in the DECLARE section of the PL/SQL block.

Advantages

• Provides programmatic control for programmers.
• Easy to trap errors.
• Designed to work with SELECT statements that return more than one record at a time.
• Require additional steps to operate than an Implicit Cursor.

There are a few steps that you need to follow:

1. Cursor Declaration
   
   A Cursor is declared in the declaration block and provided with a Name and a SELECT statement.
   
   
2. Cursor Opening
   
   To process any cursor for the business logic, it is mandatory that it should be opened. This enables creation of a Context Area.
   
   
3. Cursor Fetching
   
   Fetching retrieves records from the Context Area into a variable such that the variable can be used for the business logic.
   A Fetch command operates on the current record only and processes through the result set one record at a time.
   
   
4. Cursor Closing
   
   The cursor should be closed else it may lead to a memory leak. Until the cursor is closed, the memory is not released.

Syntax

DECLARE
      Cursor Declaration
BEGIN
      Opening of Cursor
      Fetching of Cursor
      Close Cursor
End;

Cursor with “Simple loop”

DECLARE      v_EMP_ID Employee_detail.EMP_ID%TYPE;
CURSOR employee_cursor
IS
SELECT EMP_ID
FROM Employee_detail;
BEGIN OPEN employee_cursor;
FETCH employee_cursor INTO v_EMP_ID;
      DBMS_Output.PUT_LINE('EMPLOYEE ID ' || v_EMP_ID);
FETCH employee_cursor INTO v_EMP_ID;
      DBMS_Output.PUT_LINE('EMPLOYEE ID ' || v_EMP_ID);
FETCH employee_cursor INTO v_EMP_ID;
      DBMS_Output.PUT_LINE('EMPLOYEE ID ' || v_EMP_ID);
CLOSE employee_cursor;
END;

Output



Cursor with “loop”

DECLARE
CURSOR employee_cursor
IS
SELECT * FROM Employee_detail;
v_Emp_Data employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_Emp_Data;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_Output.PUT_LINE(' Emp Id: ' || v_Emp_Data.Emp_ID);
DBMS_Output.PUT_LINE(' Name: ' ||v_Emp_Data.Emp_Name);
DBMS_Output.PUT_LINE('');
END LOOP;
CLOSE employee_cursor;
END;

Output

 In the above example within the loop each record in the Active Set is retrieved and used.

• An EXIT WHEN statement is mandatory to be the part of the loop.
• A loop is terminated with a proper cursor attribute operating on %NOTFOUND status.
• A loop is terminated when the condition is TRUE.

Program To print employee details

DECLARE
CURSOR employee_cursor
IS
SELECT * FROM Employee_detail;
v_Emp_Data employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
DBMS_Output.PUT_LINE(' Employee Details are as follows');
DBMS_Output.PUT_LINE('--------------------------------------------------------------');
DBMS_Output.PUT_LINE('Emp Id '||' Name '||' Organization '||'Salary ');
DBMS_Output.PUT_LINE('--------------------------------------------------------------');
LOOP
FETCH employee_cursor INTO v_Emp_Data;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_Output.PUT_LINE(RPAD(v_Emp_Data.Emp_ID,15)|| RPAD(v_Emp_Data.Emp_Name,15)|| LPAD(v_Emp_Data.Designation,15)|| LPAD(v_Emp_Data.Salary,15));
DBMS_Output.PUT_LINE('');
END LOOP;
CLOSE employee_cursor;
END;

Output



Cursor with “While Loop”

DECLARE
CURSOR employee_cursor
IS
SELECT *
FROM Employee_detail;
v_EMP_DATA employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
FETCH employee_cursor INTO v_EMP_DATA;
WHILE employee_cursor%FOUND
LOOP
DBMS_Output.PUT_LINE('Employee Name is : ' || v_EMP_DATA.EMP_NAME);
FETCH employee_cursor INTO v_EMP_DATA;
END LOOP;
CLOSE employee_cursor;
END;

Output



Cursor with “FOR Loop”

• The loop works on a range oriented operational logic.
• The loop is very useful for traveling through all the data in a database table.
• This is more dynamic in operation than a simple loop.
• The cursor does not require an explicit OPEN, FETCH and CLOSE.

 

DECLARE
CURSOR employee_cursor
IS
SELECT *
FROM Employee_detail;
v_EMP_DATA employee_cursor%ROWTYPE;
BEGIN
FOR v_EMP_DATA IN employee_cursor
LOOP
DBMS_Output.PUT_LINE('Employee Id is :' || v_EMP_DATA.EMP_Id);
DBMS_Output.PUT_LINE('Employee Name is :' || v_EMP_DATA.EMP_NAME );
DBMS_Output.PUT_LINE('Designation is :' || v_EMP_DATA.Designation );
DBMS_Output.PUT_LINE('Employee Salary is :' || v_EMP_DATA.Salary);
DBMS_Output.PUT_LINE('' );
END LOOP;
END;

Output



Conclusion

I hope that this article will be very useful for beginners to understand cursors. I welcome all your suggestions and comments.