Cursors in Oracle

Oracle Cursors 

This article inroduces the term "Cursors" in Oracle.

Cursors

In Oracle, Cursors are the temporary private working area where queries are processed. It is used to access the result set present in memory. A cursor contains the information on a select statement and the rows of the data accessed by it.

Syntax

CURSOR cursor_name

IS
SELECT_statement; 

Syntax of cursor with parameter
 

CURSOR cursor_name (parameter_list)

IS
SELECT_statement; 

Example

CURSOR cur (order_id_in IN varchar2)

IS
SELECT order_no
FROM orders_table1
WHERE order_id = order_id_in; 

Note

The result of this cursor is the order_no whose order_id matches the order_id passed to the cursor via the parameter.

Features of Cursors 

Cursors consist of the following two features:

• It allows us to fetch and process rows returned by the select statement.
• A cursor is named so that it can be referenced.

Normally, cursors are divided into the two parts:

1. Implicit Cursors
2. Explicit Cursors 

Implicit Cursor

An Implicit Cursor is also known as a predefined cursor. Every time when an Oracle query is executed an implicit cursor is automatically declared and used by Oracle. Implicit cursors are managed by the Oracle Engine itself. In this process the user is not at all aware of the implicit cursor since it cannot tell us how many rows were affected by an update, the numbers of rows updated are returned by SQL%ROWCOUNT. It is used to process INSERT, UPDATE, DELETE and SELECT INTO statements where the operations like DECLARE, OPEN, FETCH, and CLOSE are automatically performed by Oracle. Implicit cursors are used in the statement that returns only one row and if more than one row is returned an error will occur.

SQL%ROWCOUNT can be used as follows:

SET SERVEROUTPUT ON
BEGIN
UPDATE Students
SET Stud_name = 'Varun'
WHERE Cust_name LIKE 'Varun%';
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;

Implicit Cursor Attributes

Attributes	Working
% IS OPEN	Cursor opens automatically via Oracle Engine and returns the value = true in an open condition otherwise return false.
%FOUND	If one or more than one row is processed and affects the INSERT, UPDATE, DELETE and SELECT statement operation, then the cursor returns the value = true and otherwise false.
%NOTFOUND	If no row is processed and did not affect the INSERT, UPDATE, DELETE and SELECT statement operation, then the cursor returns the value = true and otherwise false.
%ROWCOUNT	Returns the number of rows processed or affected by the INSERT, UPDATE, DELETE and SELECT statement.

 

Drawbacks of Implicit Cursor

1. Provide less programmatic control
2. More vulnerable to data errors
3. Less efficient than explicit cursor

Example using attributes for the following employee_Info  table:
 

Emp_id	Emp_name	Designation	Salary
1001	Rahul	Project Manager	80K
1002	Karan	Developer	40K
1003	Seema	Sr. Developer	50K
1004	Shreya	Developer	38k
1005	Reet	Tech Lead	55K
1006	Gaurav	Tech Lead	62K

BEGIN
UPDATE employee_info SET Designation='Project Manager'
Where Emp_Name = 'Reet';
If SQL%FOUND THEN
DBMS_OUTPUT_LINE('IF FOUND THEN UPDATED');
ENDIF; 
If SQL%NOTFOUND THEN
DBMS_OUTPUT_LINE('NOT UPDATED IF NOT FOUND');
ENDIF; 
If SQL%ROWCOUNT >0 THEN
DBMS_OUTPUT_LINE('SQL%ROWCOUNT || 'ROWS UPDATED'); 
ELSE
DBMS_OUTPUT_LINE('NO ROWS UPDATED FOUND');
END; 

Explicit Cursor

An Explicit Cursor is also called a user-defined cursor. To do the operation on a set of multiple rows, the user-defined cursor is created by the users/ programmers. In this, each row is processed individually. Explicit cursors are created during the execution of a SELECT statement, also declared and named by the user itself. In an explicit cursor the operations such as FETCH, OPEN, CLOSE and DECLARE are performed by the users.

Working Process of Explicit Cursor

Here we are using Customer_cur as a cursor name:

1. DECLARE
   
   Cursor initialized in to temporary private memory area.
   
   syntax
   
   CURSORcursor_name
   IS
   SELECT_Statement
   
   Example:
   
   CURSORCustomer_cur
   IS
   SELECT*
   FROMCustomers;
    

2. OPEN
   
   Opening the previously declared cursor for a query and memory is allotted.
   
   syntax
   
   OPENCursor_name;
   
   Example
   
   OPENCustomer_cur
   

3. FETCH
   
   Fetches the result from previously declared and opened cursor.
   
   syntax
   
   FETCHCursor_name INTOVariable_list;
   
   Example
   
   FETCHCustomer_cur INTOCustomer_rec;
   

4. CLOSE
   
   Close the previously opened cursor to release the memory reserved.
   
   syntax
   
   CLOSECursor_name;
   
   Example
   
   CLOSE Customer_cur;

Implicit Cursor Attributes
 

Attributes	Syntax	Working
% IS OPEN	RahulCursor_name ISOPEN	A Boolean attribute that returns the value = TRUE if cursor is open and returns FALSE if cursor is closed
%FOUND	Cursor_name%FOUND	A Boolean attribute that returns the value = TRUE if the previous fetch returns a row , otherwise return FALSE.
%NOTFOUND	Cursor_name% NOTFOUND	A Boolean attribute that returns the value = TRUE if the previous fetch did not returns a row, otherwise return FALSE.
%ROWCOUNT	Cursor_name% ROWCOUNT	Returns the number of records fetched from a cursor at that point in time.

Here is an example of an Explicit Cursor showing the complete cycle of processes: DECLARE, OPEN, FETCH and the closing of a Cursor with the use of the preceding attributes:

Example

DECLARE
b_stud_id students.stud_id%TYPE;
CURSOR s_student IS
SELECT stud_id
FROM STUDENTS
WHERE stud_id < 1800;
BEGIN
OPEN s_student;
LOOP
FETCH s_student INTO d_stud_id;
EXIT WHEN s_student%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('STUDENT Num: 11b_stud_id);
END LOOP;
CLOSE s_student;
EXCEPTION
WHEN OTHERS
THEN
IF s_student%ISOPEN
THEN
CLOSE s_student;
ENDIF;
END;