Cursors – Explicit Cursors

What are Cursors?

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT statement or a DML statement (Insert, Update or Delete). In layman terms, a Cursor is used to fetch (SELECT) a particular dataset from a much larger dataset which can then be used for data manipulation using other DML Operations like Insert, Update or Delete.

There are two types of Cursors: Implicit Cursors and Explicit Cursors.

In this post, we will discuss mainly about Explicit Cursors.

Explicit Cursors: Unlike Implicit cursors, application developers have control over the life cycle of the explicit cursors by manually performing the OPEN and FETCH operations and then CLOSE the Cursor.

A Cursor is defined in the declarative part of the PL/SQL Block and the following are three stages are part of the Cursor life cycle.

OPEN
- Open: Database allocates a private work area in the user’s session memory for cursor processing.
- Parse SQL: Validates the SQL query for syntax and privileges.
- Bind SQL: Provides an input value to the bind variables that are part of the query.
- Executes the SQL Statement (that has the required conditions in the WHERE condition).
FETCH
- Fetches the data from the parsed SQL statement. Fetches the data into a block variable and increments the record pointer.
CLOSE
- Closes the cursor and releases the memory back to System Global Area (SGA).

Consider the following dataset to demonstrate an example of an Explicit cursor. Create a sample table and insert data into the table.

CREATE TABLE xx_test_cursor
(
    emp_name         VARCHAR2 (200),
    emp_id           NUMBER,
    emp_join_date    DATE,
    manager_name     VARCHAR2 (200)
);

INSERT INTO xx_test_cursor
     VALUES ('John',1001,SYSDATE - 30, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Graeme', 1002, SYSDATE - 40, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Shawn', 1003, SYSDATE - 50, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Jonty', 1004, SYSDATE - 60, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Mike', 1007, SYSDATE - 60, 'Frank');
     
INSERT INTO xx_test_cursor
     VALUES ('Peter', 1008, SYSDATE - 60, 'Justin');

INSERT INTO xx_test_cursor
     VALUES ('Jason', 1009, SYSDATE - 60, 'Justin');

INSERT INTO xx_test_cursor
     VALUES ('Glenn', 1010, SYSDATE - 60, 'Justin');

INSERT INTO xx_test_cursor
     VALUES ('Adam', 1011, SYSDATE - 60, 'Justin');
     
INSERT INTO xx_test_cursor
     VALUES ('Chris', 1012, SYSDATE - 60, 'Justin');

COMMIT;

SELECT * FROM xx_test_cursor
order by emp_id;

EMP_NAME	EMP_ID	EMP_JOIN_DATE	        MANAGER_NAME
John	         1,001	08/27/2018 19:01:10	Frank
Graeme	         1,002	08/17/2018 19:01:11	Frank
Shawn	         1,003	08/07/2018 19:01:12	Frank
Jonty	         1,004	07/28/2018 19:01:13	Frank
DARELL	         1,005	07/28/2018 19:23:58	Frank
Mike	         1,007	07/31/2018 01:50:23	Justin
Peter	         1,008	07/31/2018 01:50:24	Justin
Jason	         1,009	07/31/2018 01:50:26	Justin
Glenn	         1,010	07/31/2018 01:50:27	Justin
Adam	         1,011	07/31/2018 01:50:28	Justin

The following code will display the data from the table in the required format.

Cursor – FETCH

Code from Line Number 2 to 5 is where the code is being defined in the declare section.
Code in line number 13 opens the cursor.
Code in line number 14 fetches the data from the SQL cursor into a record variable that has a cursor type datatype.
Once all the data is fetched, code in line number 15 closes the Cursor.
Code from Line 17 to 29, checks if there is any data in the record variable, then loops through the record using an index, and displays the data in the required format, and then closes the loop.

DECLARE
    CURSOR c_data 
    IS
        SELECT emp_name, emp_join_date, manager_name 
        FROM xx_test_cursor;

    TYPE rec_data_type IS TABLE OF c_data%ROWTYPE
         INDEX BY BINARY_INTEGER;
         
    l_data rec_data_type;
    
BEGIN
    OPEN c_data;
    FETCH c_data BULK COLLECT INTO l_data;
    CLOSE c_data;

    IF l_data.COUNT > 0
    THEN
        FOR i IN l_data.FIRST .. l_data.LAST
        LOOP
            DBMS_OUTPUT.put_line (
                   'Employee: '
                || l_data (i).emp_name
                || ' Join date is: '
                || l_data (i).emp_join_date
                || ' and Manager is: '
                || l_data (i).manager_name);
        END LOOP;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception when processing data: ' || SQLERRM);
END;

Corresponding Dbms_output is: 

Employee: Mike Join date is: 31-JUL-18 and Manager is: Justin
Employee: Peter Join date is: 31-JUL-18 and Manager is: Justin
Employee: Jason Join date is: 31-JUL-18 and Manager is: Justin
Employee: Glenn Join date is: 31-JUL-18 and Manager is: Justin
Employee: Adam Join date is: 31-JUL-18 and Manager is: Justin
Employee: John Join date is: 27-AUG-18 and Manager is: Frank
Employee: Graeme Join date is: 17-AUG-18 and Manager is: Frank
Employee: Shawn Join date is: 07-AUG-18 and Manager is: Frank
Employee: Jonty Join date is: 28-JUL-18 and Manager is: Frank
Employee: DARELL Join date is: 28-JUL-18 and Manager is: Frank

FOR LOOP:

DECLARE
    CURSOR c_data 
    IS 
    SELECT emp_name, emp_join_date, manager_name
    FROM xx_test_cursor;
BEGIN
    FOR rec_data IN c_data
    LOOP
        DBMS_OUTPUT.put_line (
               'Employee: '
            || rec_data.emp_name
            || ' Join date is: '
            || rec_data.emp_join_date
            || ' and Manager is: '
            || rec_data.manager_name);
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception when processing data: ' || SQLERRM);
END;

Corresponding dbms_output is: 

Employee: Mike Join date is: 31-JUL-18 and Manager is: Justin
Employee: Peter Join date is: 31-JUL-18 and Manager is: Justin
Employee: Jason Join date is: 31-JUL-18 and Manager is: Justin
Employee: Glenn Join date is: 31-JUL-18 and Manager is: Justin
Employee: Adam Join date is: 31-JUL-18 and Manager is: Justin
Employee: John Join date is: 27-AUG-18 and Manager is: Frank
Employee: Graeme Join date is: 17-AUG-18 and Manager is: Frank
Employee: Shawn Join date is: 07-AUG-18 and Manager is: Frank
Employee: Jonty Join date is: 28-JUL-18 and Manager is: Frank
Employee: DARELL Join date is: 28-JUL-18 and Manager is: Frank

Cursors – Explicit Cursors

Phani Adivi

Recent Posts