Cursors in DBMS - Cursors in SQL

When we want to retrieve certain records, we use SELECT statements. They retrieve all the matching records for the filter conditions in WHERE clause. But when this SELECT statement used in the PL/SQL block is restricted to return only one row. This is not always welcome in a program. There will be situations where we need to select multiple rows by using SELECT query. In order do this; we use cursors where we write a SELECT query to retrieve multiple rows. These cursors are the named SELECT query and the result of which will be traversed one by one in the program using loops.

Table of Contents

There are two types of cursors – Implicit and Explicit Cursors

Implicit Cursors

These are the cursors which are automatically created and used by the DBMS. These cursors are created when we use DML statements like INSERT, DELETE or UPDATE or when we use SELECT query. Even for single row SELECT statement DBMS creates implicit cursors. It allocates some space in the memory to hold the data. Even when we fire DML statements, the implicit cursor is fired to select the matching records and the space is reserved for those records in the memory.

Even though these implicit cursors are internal and users/developers cannot control their execution, developers can view the details of these cursors using its attributes. We can access this implicit cursor using ‘SQL’ which refers to the cursor SQL. Its attributes are described below:

%ISOPEN – It returns TRUE or FALSE depending on the cursor is still open for execution or not. It returns always FALSE for implicit cursor because DBMS would have executed and closed the cursor automatically.

%FOUND – It returns TRUE if the DML statement affects any of the rows or the SELECT statement retrieves one or more row. Otherwise it returns FALSE. If the statements are not executed then it returns NULL.

%NOTFOUND – It is opposite of %FOUND. It returns TRUE if the DML statement does not affect any of the rows or the SELECT statement does not retrieve any row. Otherwise it returns FALSE.

%ROWCOUNT – It returns the number of rows affected by the DML statements or the number of rows retrieved by the SELECT statement.

These implicit cursor attributes are called as below:

SQL%ISOPEN
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND

One of the examples of implicit cursor

DECLARE
	n_count NUMBER;
BEGIN
	UPDATE EMPLOYEE
       	      SET SALARY = SALARY + (SALARY*0.1) – 10% increment in salary for DEPT_ID = 10 employees
	WHERE DEPT_ID = 10;
IF SQL%FOUND THEN – checks if any row is updated
	n_count: = SQL%ROWCOUNT; -- Number of rows updated are counted
 	DBMS_OUTPUT.PUT_LINE (‘Total Number of employees who got increment: ‘|| n_count);
ELSE
	DBMS_OUTPUT.PUT_LINE (‘None of the employees got increment’);
END IF;
END;

Explicit Cursors

These cursors are created by the developer in his program. In this type of cursors, developer will have the control on it. Explicit cursors have to be declared in the DECLARE section and can be used as any other variable, but it will hold one or more rows of data.

Any explicit cursor created in the PL/SQL block will have four basic steps:

DECLARE the Cursor – This step declares the cursor with its name and defines the SELECT statement for the cursor. The general syntax for declaring explicit cursor is as below:

CURSOR cursor_name IS
	           SELECT STATEMENT;
E.g.: 
	CURSOR cursor_Dept10Emp IS
	           SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

OPEN the Cursor – Cursor is opened for reading the records from it. When we open the cursor it gets executed and memory space is allocated for the records.

OPEN cursor_name

OPEN cursor_Dept10Emp;

FETCH the records – This step will start traversing the records in the cursor. Rows will be fetched one at a time from this cursor. We can perform any calculation or manipulation on these fetched records.

FETCH cursor_Dept10Emp INTO n_empID, v_empName;

CLOSE the Cursor – Once the cursor is used and no more required in the program, close the cursor. This will release the allocated memory for this cursor.

CLOSE cursor_Dept10Emp;

Simple program with above 4 steps would be like below

DECLARE
	CURSOR cursor_Dept10Emp IS   -- Declare the cursor
	           SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

	n_empID EMPLOYEE.EMP_ID%TYPE;
	v_empName EMPLOYEE.EMP_NAME%TYPE;
BEGIN
	OPEN cursor_Dept10Emp; -- Open the cursor

	LOOP
		FETCH cursor_Dept10Emp INTO n_empID, v_empName; -- Fetch cursor records

		EXIT WHEN cursor_Dept10Em%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘|| n_empID|| ‘Employee Name:’|| v_empName);
END LOOP;

CLOSE cursor_Dept10Emp; -- Close the Cursor
END;

Cursor For Loop

In the above four steps, FETCH step will retrieve only one record at a time and it will assign individual column values into respective variables. Also a separate loop has to be created to iterate through all the records. If we use For loop for cursors, then we can iterate and traverse the whole records (all the columns) of cursor query using the for loop variable. This is useful when all the records of a table need to be used in the program. The cursor will automatically be opened when it is called in the FOR loop and closed when for loop ends. No need to explicitly OPEN or CLOSE the cursor. Below example shows how cursor for loop is different from above four steps.

DECLARE
	CURSOR cursor_Dept10Emp IS   -- Declare the cursor
	           SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;

	rec_emp EMPLOYEE%ROWTYPE
BEGIN
	FOR rec_emp IN cursor_Dept10Emp -- implicitly opens cursor and assigns cursor variable rec_emp
	LOOP 
	DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME);
END LOOP;
END;

Cursor Parameter

We can even pass parameters to the cursors like we do in procedures and functions. These parameters are used in the cursor query to retrieve the data. The scope of the cursor parameter is localized to cursor alone.

DECLARE
	CURSOR cursor_Dept10Emp (deptID EMPLOYEE.DEPT_ID%TYPE) IS   	          
		 SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = deptID; -- deptID is the cursor parameter
	rec_emp EMPLOYEE%ROWTYPE

BEGIN
	FOR rec_emp IN cursor_Dept10Emp (10) – Pass the cursor parameter as 10
	LOOP 
	DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME);
END LOOP;
END;

Ref Cursors

These are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.

We can see the difference between cursor and refcursor in below program.

DECLARE
           TYPE rc_cursor is ref cursor; 
	CURSOR c_course IS
		SELECT * FROM COURSE;
           l_cursor rc_cursor;
         n_ID NUMBER;
BEGIN
	IF n_ID = 10 THEN
	-- Dynamically opens the cursor for student ids less than 10
		Open l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID<= 10’;
	ELSE	
	-- Dynamically opens the cursor for student ids greater than 10
		OPEN l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID > 10’;
	 END IF;
	-- Opens static cursor c_course
	 OPEN c_course;
END;