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
