Cursors in PL/SQL

What is a Cursor?

A cursor in PL/SQL is a mechanism that allows you to retrieve and process multiple rows from a database query one by one. It acts as a pointer to the rows returned by an SQL SELECT statement.

When you execute a SQL query, Oracle stores the result set in memory. A cursor is used to iterate over this result set and process each row individually.

---

Types of Cursors in PL/SQL

PL/SQL supports two types of cursors:

1. Implicit Cursors – Automatically created by Oracle for SELECT, INSERT, UPDATE, and DELETE statements.
2. Explicit Cursors – Defined by the user for handling queries that return multiple rows.

---

1. Implicit Cursors

Implicit cursors are automatically handled by PL/SQL whenever a SELECT, INSERT, UPDATE, or DELETE operation is performed. You do not need to explicitly declare or open them.

Example of Implicit Cursor

DECLARE
    total_students NUMBER;
BEGIN
    SELECT COUNT(*) INTO total_students FROM Students;
    DBMS_OUTPUT.PUT_LINE('Total Students: ' || total_students);
END;

📌 Key Points:

• The SELECT statement automatically creates an implicit cursor.
• The INTO clause stores the result in a variable.
• Oracle closes the cursor automatically after execution.

Cursor Attributes for Implicit Cursors

Attribute	Description
%FOUND	Returns TRUE if the last operation affected at least one row, otherwise FALSE.
%NOTFOUND	Returns TRUE if no rows were affected.
%ROWCOUNT	Returns the number of rows affected.
%ISOPEN	Always returns FALSE for implicit cursors (since they are closed automatically).

Example Using Cursor Attributes

DECLARE
    student_name VARCHAR2(50);
BEGIN
    SELECT Name INTO student_name FROM Students WHERE StudentID = 101;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student Found: ' || student_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No student found.');
    END IF;
END;

---

2. Explicit Cursors

Explicit cursors are defined by the user when a SELECT statement returns multiple rows. These cursors give more control over opening, fetching, and closing the result set.

Steps to Use an Explicit Cursor

1. Declare the cursor with a SELECT query.
2. Open the cursor to execute the query.
3. Fetch data row-by-row.
4. Close the cursor after processing.

Syntax of Explicit Cursor

DECLARE
    CURSOR cursor_name IS select_statement;
BEGIN
    OPEN cursor_name;
    FETCH cursor_name INTO variable_list;
    CLOSE cursor_name;
END;

Example of Explicit Cursor

DECLARE
    CURSOR student_cursor IS SELECT StudentID, Name FROM Students;
    v_id Students.StudentID%TYPE;
    v_name Students.Name%TYPE;
BEGIN
    OPEN student_cursor;
    
    LOOP
        FETCH student_cursor INTO v_id, v_name;
        EXIT WHEN student_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ' Name: ' || v_name);
    END LOOP;
    
    CLOSE student_cursor;
END;

📌 Explanation:

• The cursor student_cursor is declared with a SELECT query.
• It is opened using OPEN student_cursor;.
• The FETCH statement retrieves one row at a time into variables.
• The loop continues until all rows are processed (EXIT WHEN student_cursor%NOTFOUND).
• Finally, the cursor is closed using CLOSE student_cursor;.

Cursor Attributes for Explicit Cursors

Attribute	Description
%FOUND	Returns TRUE if the last FETCH returned a row.
%NOTFOUND	Returns TRUE if no more rows are left to fetch.
%ROWCOUNT	Returns the number of rows fetched so far.
%ISOPEN	Returns TRUE if the cursor is currently open.

Example Using Cursor Attributes

DECLARE
    CURSOR student_cursor IS SELECT Name FROM Students;
    v_name Students.Name%TYPE;
BEGIN
    OPEN student_cursor;
    
    FETCH student_cursor INTO v_name;
    
    IF student_cursor%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('First Student: ' || v_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No data found.');
    END IF;

    CLOSE student_cursor;
END;

---

3. Cursor FOR Loop (Simplified Cursor Handling)

PL/SQL provides a FOR loop structure that automatically handles opening, fetching, and closing of cursors.

Syntax:

FOR record_variable IN cursor_name LOOP
    -- Use record_variable.column_name
END LOOP;

Example Using Cursor FOR Loop

DECLARE
    CURSOR student_cursor IS SELECT StudentID, Name FROM Students;
BEGIN
    FOR student_rec IN student_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || student_rec.StudentID || ' Name: ' || student_rec.Name);
    END LOOP;
END;

📌 Advantages of FOR Loop with Cursors:
✅ No need to explicitly OPEN, FETCH, or CLOSE the cursor.
✅ Each row is automatically stored in a record variable (student_rec).
✅ Efficient and reduces errors in cursor handling.

---

4. Parameterized Cursors

A parameterized cursor accepts input values, making it more flexible.

Example of Parameterized Cursor

DECLARE
    CURSOR student_cursor(p_course VARCHAR2) IS
        SELECT StudentID, Name FROM Students WHERE Course = p_course;
BEGIN
    FOR student_rec IN student_cursor('Computer Science') LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || student_rec.StudentID || ' Name: ' || student_rec.Name);
    END LOOP;
END;

📌 Key Benefits:

• The cursor accepts a parameter (p_course).
• It retrieves students only for the specified course ('Computer Science').

---

5. REF Cursors (Dynamic Cursors)

A REF CURSOR (also called a dynamic cursor) allows passing cursor results dynamically between procedures/functions.

Example Using REF CURSOR

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    student_cursor ref_cursor_type;
    v_id Students.StudentID%TYPE;
    v_name Students.Name%TYPE;
BEGIN
    OPEN student_cursor FOR SELECT StudentID, Name FROM Students;
    
    LOOP
        FETCH student_cursor INTO v_id, v_name;
        EXIT WHEN student_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ' Name: ' || v_name);
    END LOOP;
    
    CLOSE student_cursor;
END;

📌 Key Points:
✅ REF CURSOR allows dynamic SQL queries at runtime.
✅ Useful when working with multiple result sets in stored procedures.

---

Summary of Cursors in PL/SQL

Type of Cursor	Description
Implicit Cursor	Automatically created by Oracle for single-row operations (SELECT, INSERT, UPDATE, DELETE).
Explicit Cursor	Manually defined for processing multiple rows from a SELECT statement.
Cursor FOR Loop	Simplifies cursor handling (OPEN, FETCH, CLOSE are done automatically).
Parameterized Cursor	Accepts input parameters for flexible queries.
REF Cursor	Allows dynamic SQL and result set passing between procedures.

---

Conclusion

Cursors enhance PL/SQL programming by providing efficient ways to process query results row-by-row.

• Use implicit cursors for simple SELECT INTO statements.
• Use explicit cursors for handling multiple rows.
• Use FOR loops for easy cursor management.
• Use parameterized cursors for dynamic queries.
• Use REF cursors for advanced, flexible result handling.