Cursors In Oracle

This article introduces 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
  1. CURSOR cursor_name  
  2. IS  
  3. SELECT_statement; 
Syntax of cursor with parameter
  1. CURSOR cursor_name (parameter_list)  
  2. IS  
  3. SELECT_statement; 
Example
  1. CURSOR cur (order_id_in IN varchar2)  
  2. IS  
  3. SELECT order_no  
  4. FROM orders_table1  
  5. 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 

Oracle 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:
  1. SET SERVEROUTPUT ON  
  2. BEGIN  
  3. UPDATE Students  
  4. SET Stud_name = 'Varun'  
  5. WHERE Cust_name LIKE 'Varun%';  
  6. DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);  
  7. 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
  1. BEGIN  
  2. UPDATE employee_info SET Designation='Project Manager'  
  3. Where Emp_Name = 'Reet';  
  4. If SQL%FOUND THEN  
  5. DBMS_OUTPUT_LINE('IF FOUND THEN UPDATED');  
  6. ENDIF;   
  7. If SQL%NOTFOUND THEN  
  8. DBMS_OUTPUT_LINE('NOT UPDATED IF NOT FOUND');  
  9. ENDIF;   
  10. If SQL%ROWCOUNT >0 THEN  
  11. DBMS_OUTPUT_LINE('SQL%ROWCOUNT || 'ROWS UPDATED');   
  12. ELSE  
  13. DBMS_OUTPUT_LINE('NO ROWS UPDATED FOUND');  
  14. 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.
    1. CURSOR cursor_name   
    2. IS  
    3. SELECT_Statement
    Example:
    1. CURSOR Customer_cur  
    2. IS  
    3. SELECT * from customers;
  2. OPEN

    Opening the previously declared cursor for a query and memory is allotted.
    1. OPEN Cursor_name;
    Example
    1. OPEN Customer_cur
  3. FETCH

    Fetches the result from previously declared and opened cursor.
    1. FETCH Cursor_name INTO Variable_list;
    Example
    1. FETCH Customer_cur INTO Customer_rec;
  4. CLOSE

    Close the previously opened cursor to release the memory reserved.
    1. CLOSE Cursor_name;
    Example
    1. CLOSE Customer_cur;
Explicit 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
  1. DECLARE  
  2. b_stud_id students.stud_id%TYPE;  
  3. CURSOR s_student IS  
  4. SELECT stud_id  
  5. FROM STUDENTS  
  6. WHERE stud_id < 1800;  
  7. BEGIN  
  8. OPEN s_student;  
  9. LOOP  
  10. FETCH s_student INTO d_stud_id;  
  11. EXIT WHEN s_student%NOTFOUND;  
  12. DBMS_OUTPUT.PUT_LINE ('STUDENT Num: 11b_stud_id);  
  13. END LOOP;  
  14. CLOSE s_student;  
  15. EXCEPTION  
  16. WHEN OTHERS  
  17. THEN  
  18. IF s_student%ISOPEN  
  19. THEN  
  20. CLOSE s_student;  
  21. ENDIF;  
  22. END;