What is Cursor in SQL: Types, Examples, and Benefits Explained

Cursor:

Cursor is a private area created by a PL/SQL block. When we fetch data using the cursor select statement, that data is stored in the cursor area.

Example:

DECLARE
CURSOR C1 IS SELECT * FROM EMP WHERE SAL>20000;
SAL_2 EMP.SAL%TYPE;
BEGIN
OPEN C1
LOOP
FETCH C1 IN SAL_2;
UPDATE EMP SET SAL=SAL+(SAL_2*20/100);
EXIT WHEN C1% NOT FOUND;
END LOOP;
CLOSE C1;
END;

Types of Cursors:

cursors are three types

• Implicit cursor
• Explicit cursor
• Ref cursor

What is an Implicit Cursor in PL/SQL?

An implicit cursor in PL/SQL is automatically created by the PL/SQL engine whenever a DML operation (such as INSERT, UPDATE, DELETE) or a SELECT statement is executed in the PL/SQL block. The implicit cursor provides the status of these DML operations or the SELECT query.

The predefined name for the implicit cursor is ‘SQL,’ and it comes with three key attributes:

1. %FOUND – Indicates whether a row was affected or fetched.
2. %NOTFOUND – Indicates if no rows were affected or fetched.
3. %ROWCOUNT – Returns the total number of rows affected by the DML operation.

Example: Updating the salaries of all employees in the “HR” department by a 10% increase using an implicit cursor.

BEGIN
   UPDATE emp
   SET sal = sal * 1.10
   WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'HR');
   
   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated successfully in the HR department.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('No records were updated.');
   END IF;
END;

What is an Explicit Cursor in PL/SQL?

An explicit cursor in PL/SQL is used when there is a need to fetch multiple records in a PL/SQL block. While the SELECT INTO clause allows fetching only one record, using an explicit cursor enables retrieving multiple rows from the SQL query within the PL/SQL block.

When a PL/SQL code is executed, the PL/SQL engine processes it. If any SQL statements are encountered within the PL/SQL block, they are immediately sent to the SQL engine for execution. The fetched data from the SQL engine is stored in a cursor area created by the PL/SQL engine through context switches. This cursor area acts as a private storage space, where the data retrieved by the SQL engine is stored.

An explicit cursor comes with four important attributes:

1. %FOUND – Indicates whether the cursor has fetched any row.
2. %NOTFOUND – Indicates if no rows were fetched.
3. %ROWCOUNT – Returns the number of rows fetched so far.
4. %ISOPEN – Indicates whether the cursor is currently open.

Example: Fetching details of employees from the emp table who belong to the “SALES” department.

DECLARE
   CURSOR emp_cursor IS
      SELECT empno, ename, sal 
      FROM emp 
      WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');
   
   v_empno   emp.empno%TYPE;
   v_ename   emp.ename%TYPE;
   v_sal     emp.sal%TYPE;
BEGIN
   OPEN emp_cursor;
   
   LOOP
      FETCH emp_cursor INTO v_empno, v_ename, v_sal;
      
      EXIT WHEN emp_cursor%NOTFOUND;
      
      DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename || ', Salary: ' || v_sal);
   END LOOP;
   
   CLOSE emp_cursor;
END;

Explicit cursor has three model

• Basic cursor
• Cursor for loop
• Cursor for loop with select statement

Basic cursor:

DECLARE  
  CURSOR C1 IS SELECT EMPNO,ENAME,DNAME FROM EMP E, DEPT D  
WHERE E.DEPTNO=D.DEPTNO; 
  LN_EMPNO EMP.EMPNO%TYPE; 
  LV_ENAME VARCHAR2(30); 
  LV_DNAME DEPT.DNAME%TYPE; 
  BEGIN 
       OPEN C1; 
        LOOP 
       FETCH C1 INTO LN_EMPNO,LV_ENAME,LV_DNAME; 
       EXIT WHEN C1% NOTFOUND; 
     DBMS_OUTPUT.PUT_LINE(LN_EMPNO||', '||LV_ENAME||', '||LV_DNAME); 
          END LOOP; 
         CLOSE C1; 
  END;

Cursor For loop :

DECLARE 
CURSOR C1 IS SELECT * FROM EMP; 
BEGIN 
FOR I IN C1 LOOP 
DBMS_OUTPUT.PUT_LINE(I.EMPNO||', '||I.ENAME); 
END LOOP; 
END;

Cursor For Loop with Select Statement :

BEGIN 
FOR I IN (SELECT * FROM DEPT) LOOP 
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||', '||I.DNAME); 
END LOOP; 
END;

Even declaration section also not necessary for cursor for loop with select statement. We
can directly write select statement in loop;

Ref cursor :

A REF CURSOR in PL/SQL is a highly flexible pointer that allows developers to handle result sets fetched by SQL queries dynamically. Unlike standard explicit or implicit cursors, the REF CURSOR can execute dynamic SQL queries and can be passed as a parameter between different PL/SQL programs, functions, or procedures. This capability makes it an essential tool for handling complex and varying result sets, especially when working with dynamic query requirements in PL/SQL.

SYS_REFCURSOR; is pre defined data type. If you declare any variable for sys refcursor that variable you can open for any select statement.

By using a REF CURSOR, you can fetch data from multiple tables or queries at runtime, making it suitable for real-time data retrieval in applications. There are two main types of REF CURSORs in PL/SQL:

• Strong REF CURSORs (with a fixed return structure)
• Weak REF CURSORs (without a predefined structure).

Strong REF CURSOR:

The return type is predefined with a specific structure or data type.

Example : Fetch details of employees, including their ID, name, salary, and department name, for a specific department. The structure of the result set is predefined and strictly typed.

DECLARE

   TYPE strong_emp_refcur_type IS REF CURSOR RETURN emp%ROWTYPE;
   emp_ref_cursor strong_emp_refcur_type; -- Declare the REF CURSOR variable
   v_emp_record emp%ROWTYPE; -- Variable to hold each row fetched
   v_deptno     dept.deptno%TYPE := 30; -- Example department number
BEGIN
   OPEN emp_ref_cursor FOR
      SELECT * FROM emp
      WHERE deptno = v_deptno;
   -- Loop through the result set fetched by the strong REF CURSOR
   LOOP
      FETCH emp_ref_cursor INTO v_emp_record;
      EXIT WHEN emp_ref_cursor%NOTFOUND; -- Exit the loop when all rows are fetched
      
      DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_emp_record.empno || 
                           ', Name: ' || v_emp_record.ename || 
                           ', Salary: ' || v_emp_record.sal ||
                           ', Job: ' || v_emp_record.job);
   END LOOP;
   CLOSE emp_ref_cursor;
END;

• In this example, a Strong REF CURSOR named emp_ref_cursor is declared to fetch employees from a specific department (deptno = 30).
• The strong_emp_refcur_type is defined with the RETURN emp%ROWTYPE clause, making it “strongly typed” since it must match the exact structure of the emp table.
• The program loops through each record fetched by the REF CURSOR and displays details such as employee number (empno), name (ename), salary (sal), and job (job).

Weak Ref cursor:

By using only one weak ref cursor we can assign to no. of select statements. We can able to join different tables in select statement.Explicit cursor always associated with same result set but ref cursors can assign to
different result sets. Means one ref cursor we can open for emp table for once and next time we can open same ref cursor for dept table data and again we can open same ref cursor for salgrade table data but explicit cursor is always open with same result set.

Example : From the below example you can observe that variable TYP is opened for emp table and next for dept table also. For strong refcursor we can use only for particular table or structure only.

DECLARE 
TYP SYS_REFCURSOR; 
VEC EMP%ROWTYPE; 
VED DEPT%ROWTYPE; 
 
       BEGIN 
    OPEN TYP FOR SELECT * FROM EMP; 
          LOOP 
          FETCH TYP INTO VEC; 
          EXIT WHEN TYP%NOTFOUND; 
          DBMS_OUTPUT.PUT_LINE(VEC.ENAME||' '||VEC.SAL); 
          END LOOP; 
     CLOSE TYP; 
   
    OPEN TYP FOR SELECT * FROM DEPT; 
       LOOP 
       FETCH TYP INTO VED; 
       EXIT WHEN TYP%NOTFOUND; 
       DBMS_OUTPUT.PUT_LINE(VED.DEPTNO||' '||VED.DNAME); 
         END LOOP; 
      END; 

• Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance
• Mastering Oracle Database Architecture: A Comprehensive Overview
• Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
• Understanding Oracle Triggers – A Comprehensive Guide with Examples
• SQL Query Design Patterns: Writing Efficient Queries