Introduction to Cursors

Cursors are powerful constructs in Oracle PL/SQL that allow you to process the individual rows returned by a SQL query. Think of a cursor as a pointer or a handle to a private SQL area in the Oracle server's memory, where the results of a query are stored. They provide a mechanism to iterate through a set of rows, one row at a time, giving you fine-grained control over data manipulation. This is crucial when your SQL statement returns multiple rows and you need to perform operations on each row independently. Understanding Oracle cursors, how to use explicit cursors, and cursor management are key skills for any PL/SQL developer.

Note: Oracle PL/SQL cursors, SQL cursor, explicit cursors, implicit cursors, cursor processing, multi-row data handling.

What are Cursors?

In essence, a cursor is a named private SQL area that stores the results of a SQL statement. When you execute a SELECT statement in PL/SQL that returns more than one row, Oracle internally uses an implicit cursor. However, for more control and explicit row-by-row processing, you define and manage explicit cursors. Cursors are vital for tasks like reporting, data migration, and complex business logic where you need to iterate through a result set. Mastering cursor concepts is a significant step in becoming proficient in Oracle database programming.

Note: PL/SQL cursor definition, how cursors work, cursor types, Oracle data processing, row-by-row operations.

Explicit Cursors

Explicit cursors are user-defined cursors that you declare, open, fetch from, and close manually. They provide greater control over query processing compared to implicit cursors, which Oracle manages automatically for single-row queries or DML statements. Explicit cursors are preferred when you expect multiple rows from a query and need to perform specific actions on each row. This includes tasks such as applying conditional logic, performing calculations, or updating data based on each row's values. Understanding explicit cursor lifecycle is fundamental for advanced PL/SQL programming.

Note: explicit cursor PL/SQL, defining cursors, cursor control, advanced PL/SQL topics, Oracle programming best practices.

Declaring Cursors

Declaring an explicit cursor involves giving it a name and associating it with a SELECT statement. This declaration goes in the declarative section of your PL/SQL block (e.g., before BEGIN). The SELECT statement within the cursor declaration does not execute immediately; it merely defines the query that the cursor will represent. This step reserves a named area in memory for the query results. Correct cursor declaration is the first step in effective cursor management in Oracle PL/SQL.

Note: declare cursor in PL/SQL, PL/SQL cursor syntax, cursor declaration examples, Oracle PL/SQL tutorial.

Example 1: Declaring a Simple Cursor

DECLARE
    -- Declare an explicit cursor named c_employees
    -- This cursor will select employee_id, first_name, and last_name
    -- from the employees table.
    CURSOR c_employees IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 60; -- Filter for a specific department
BEGIN
    -- DBMS_OUTPUT.PUT_LINE is used for displaying output in SQL Developer or SQL*Plus
    DBMS_OUTPUT.PUT_LINE('Cursor c_employees declared successfully.');
END;
/

Explanation

This example demonstrates the declaration of a basic explicit cursor named c_employees. The CURSOR keyword is used, followed by the chosen cursor name (c_employees), then IS, and finally the SELECT statement that defines the data the cursor will process. In this case, it's selecting employee ID, first name, and last name from the employees table for those in department 60. The BEGIN...END block simply shows that the declaration is part of a PL/SQL block, and a message is printed to confirm the declaration.

Example 2: Declaring a Cursor with an ORDER BY Clause

DECLARE
    -- Declare a cursor to get customer details, ordered by last name.
    CURSOR c_customers IS
        SELECT customer_id, customer_name, city, state
        FROM customers
        ORDER BY customer_name DESC; -- Order results in descending order by customer name
BEGIN
    DBMS_OUTPUT.PUT_LINE('Cursor c_customers declared with ORDER BY clause.');
END;
/

Explanation

This code snippet illustrates declaring a cursor c_customers that retrieves customer information. It includes an ORDER BY clause to sort the results by customer_name in descending order. This shows that any valid SELECT statement can be used within a cursor declaration, including clauses like ORDER BY for sorting data within the cursor's result set.

Example 3: Declaring a Cursor with Joins

DECLARE
    -- Declare a cursor that joins employees and departments tables
    CURSOR c_emp_dept IS
        SELECT e.first_name, e.last_name, d.department_name
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE d.location_id = 1700; -- Filter by location
BEGIN
    DBMS_OUTPUT.PUT_LINE('Cursor c_emp_dept declared with a JOIN.');
END;
/

Explanation

Here, the c_emp_dept cursor is declared to fetch employee names and their corresponding department names. It demonstrates how to use joins within a cursor declaration to retrieve data from multiple tables, which is a common requirement in real-world applications. The WHERE clause further refines the result set based on the department's location ID.

Example 4: Declaring a Cursor with Aggregates and GROUP BY

DECLARE
    -- Declare a cursor to get total salary per department
    CURSOR c_dept_salaries IS
        SELECT d.department_name, SUM(e.salary) AS total_department_salary
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        GROUP BY d.department_name
        HAVING SUM(e.salary) > 50000; -- Filter departments with total salary > 50000
BEGIN
    DBMS_OUTPUT.PUT_LINE('Cursor c_dept_salaries declared with GROUP BY and HAVING.');
END;
/

Explanation

This example showcases a more complex cursor declaration, c_dept_salaries. It uses aggregate functions (SUM) and a GROUP BY clause to calculate the total salary for each department. Additionally, a HAVING clause is used to filter the grouped results, demonstrating the full power of SQL within cursor declarations for analytical queries.

Example 5: Declaring a Cursor with Union All

DECLARE
    -- Declare a cursor to combine data from two tables using UNION ALL
    CURSOR c_combined_data IS
        SELECT customer_id AS id, customer_name AS name, 'Customer' AS type
        FROM customers
        WHERE city = 'Seattle'
        UNION ALL
        SELECT supplier_id AS id, supplier_name AS name, 'Supplier' AS type
        FROM suppliers
        WHERE city = 'Seattle';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Cursor c_combined_data declared with UNION ALL.');
END;
/

Explanation

This example demonstrates declaring a cursor that combines results from two different tables (customers and suppliers) using the UNION ALL operator. This is useful when you need to retrieve a unified set of data from similar structures across different entities. The AS aliases are used to ensure compatible column names and data types for the UNION ALL operation.

Opening Cursors

Opening a cursor executes the SELECT statement associated with it. This Populates the active set, which is the collection of rows that satisfy the query criteria. When you open a cursor, Oracle performs syntax checking, semantic analysis, and binds any variables. However, no rows are actually retrieved into program variables at this stage; they are merely made available for fetching. Opening cursors is a critical step in the cursor lifecycle, preparing the data for subsequent processing.

Note: open cursor PL/SQL, cursor open statement, PL/SQL cursor lifecycle, executing a cursor query.

Example 1: Opening a Simple Cursor

DECLARE
    CURSOR c_employees IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 60;
BEGIN
    -- Open the cursor c_employees
    OPEN c_employees;
    DBMS_OUTPUT.PUT_LINE('Cursor c_employees opened successfully.');
    -- In a real scenario, you would now fetch data
    CLOSE c_employees; -- Good practice to close immediately after opening for this example
END;
/

Explanation

This example shows how to open the previously declared c_employees cursor. The OPEN statement executes the SELECT query defined in the cursor declaration. At this point, the result set is identified and ready for fetching. For demonstration purposes, the cursor is immediately closed after opening.

Example 2: Opening a Cursor within a Loop (Illustrative)

DECLARE
    CURSOR c_products IS
        SELECT product_id, product_name
        FROM products
        WHERE status = 'Active';
BEGIN
    -- Open the cursor c_products
    OPEN c_products;
    DBMS_OUTPUT.PUT_LINE('Cursor c_products opened.');
    -- (Imagine fetching and processing rows here in a loop)
    -- This example only demonstrates the OPEN statement.
    CLOSE c_products;
END;
/

Explanation

This example demonstrates opening a cursor named c_products. While it doesn't include the fetching logic (which comes next), it clearly shows the OPEN c_products; statement in action. This is a common pattern where a cursor is opened before a loop that will process its rows.

Example 3: Opening Multiple Cursors

DECLARE
    CURSOR c_sales_reps IS
        SELECT employee_id, first_name FROM employees WHERE job_id = 'SA_REP';

    CURSOR c_it_staff IS
        SELECT employee_id, first_name FROM employees WHERE job_id = 'IT_PROG';
BEGIN
    -- Open the first cursor
    OPEN c_sales_reps;
    DBMS_OUTPUT.PUT_LINE('Cursor c_sales_reps opened.');

    -- Open the second cursor
    OPEN c_it_staff;
    DBMS_OUTPUT.PUT_LINE('Cursor c_it_staff opened.');

    -- In a full program, you would process data from both cursors here
    CLOSE c_sales_reps;
    CLOSE c_it_staff;
END;
/

Explanation

This code demonstrates that multiple cursors can be declared and opened within the same PL/SQL block. c_sales_reps and c_it_staff are both opened, showing how you can manage independent result sets concurrently for different business needs. Each OPEN statement activates its respective SELECT query.

Example 4: Error Handling during Cursor Open (Illustrative)

DECLARE
    CURSOR c_invalid_query IS
        SELECT non_existent_column FROM employees; -- This query will fail
BEGIN
    BEGIN
        OPEN c_invalid_query;
        DBMS_OUTPUT.PUT_LINE('Cursor c_invalid_query opened successfully.');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error opening cursor: ' || SQLERRM);
    END;
END;
/

Explanation

This example illustrates a common scenario where an error might occur during the OPEN operation, such as an invalid column name in the SELECT statement. The EXCEPTION block catches the error and prints a descriptive message using SQLERRM, demonstrating the importance of error handling when working with cursors.

Example 5: Opening a Cursor with a Complex Query (No Data Fetch)

DECLARE
    CURSOR c_high_value_orders IS
        SELECT o.order_id, c.customer_name, SUM(oi.quantity * oi.unit_price) AS total_order_value
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
        JOIN order_items oi ON o.order_id = oi.order_id
        GROUP BY o.order_id, c.customer_name
        HAVING SUM(oi.quantity * oi.unit_price) > 10000;
BEGIN
    OPEN c_high_value_orders;
    DBMS_OUTPUT.PUT_LINE('Cursor c_high_value_orders opened.');
    -- No data is fetched here, just the opening process
    CLOSE c_high_value_orders;
END;
/

Explanation

This example demonstrates opening a cursor associated with a complex query involving multiple joins, an aggregate function, and a HAVING clause. Even with such complexity, the OPEN statement remains simple. It highlights that the OPEN command is solely responsible for preparing the active set, regardless of the query's intricacy.

Fetching Data from Cursors

Once a cursor is open, you can retrieve individual rows from its active set using the FETCH statement. Each FETCH operation retrieves one row from the cursor's result set and populates corresponding PL/SQL variables with the column values. The FETCH statement advances the cursor pointer to the next row. You typically use a loop (e.g., LOOP...EXIT WHEN or WHILE) to repeatedly fetch rows until all rows in the active set have been processed. Efficient data fetching is key to optimizing PL/SQL cursor performance.

Note: fetching data from cursor, PL/SQL fetch statement, cursor row processing, loop through cursor, Oracle PL/SQL examples.

Example 1: Fetching Data from a Simple Cursor

DECLARE
    CURSOR c_employees IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 60;
    
    v_employee_id    employees.employee_id%TYPE;
    v_first_name     employees.first_name%TYPE;
    v_last_name      employees.last_name%TYPE;
BEGIN
    OPEN c_employees;
    
    -- Loop to fetch each row
    LOOP
        -- Fetch the next row into PL/SQL variables
        FETCH c_employees INTO v_employee_id, v_first_name, v_last_name;
        
        -- Exit the loop when no more rows are found
        EXIT WHEN c_employees%NOTFOUND;
        
        -- Display the fetched data
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || 
                             ', Name: ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    
    CLOSE c_employees;
END;
/

Explanation

This example demonstrates the core functionality of fetching data. After opening c_employees, a LOOP is used. Inside the loop, FETCH c_employees INTO ... retrieves values from the current row into the declared PL/SQL variables. c_employees%NOTFOUND is a cursor attribute that becomes TRUE when the last row has been fetched and the next FETCH attempts to retrieve a non-existent row, triggering the EXIT WHEN condition to terminate the loop.

Example 2: Fetching with Cursor Attributes (%FOUND)

DECLARE
    CURSOR c_departments IS
        SELECT department_id, department_name
        FROM departments;
        
    v_department_id    departments.department_id%TYPE;
    v_department_name  departments.department_name%TYPE;
BEGIN
    OPEN c_departments;
    
    LOOP
        FETCH c_departments INTO v_department_id, v_department_name;
        
        -- Use %FOUND to check if a row was successfully fetched
        IF c_departments%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_department_id || 
                                 ', Name: ' || v_department_name);
        ELSE
            -- No more rows, exit the loop
            EXIT;
        END IF;
    END LOOP;
    
    CLOSE c_departments;
END;
/

Explanation

This example uses the c_departments%FOUND cursor attribute. Unlike %NOTFOUND (which is true after a failed fetch), %FOUND is TRUE if the FETCH operation successfully retrieved a row. This approach often leads to cleaner code by checking for a successful fetch before processing.

Example 3: Fetching with %ROWTYPE

DECLARE
    -- Declare a cursor for employees in department 80
    CURSOR c_sales_employees IS
        SELECT employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary
        FROM employees
        WHERE department_id = 80;
        
    -- Declare a record variable based on the cursor's row structure
    r_sales_employee c_sales_employees%ROWTYPE;
BEGIN
    OPEN c_sales_employees;
    
    LOOP
        -- Fetch the entire row into the record variable
        FETCH c_sales_employees INTO r_sales_employee;
        
        EXIT WHEN c_sales_employees%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Employee: ' || r_sales_employee.first_name || ' ' || r_sales_employee.last_name ||
                             ', Email: ' || r_sales_employee.email ||
                             ', Salary: ' || r_sales_employee.salary);
    END LOOP;
    
    CLOSE c_sales_employees;
END;
/

Explanation

This code demonstrates fetching an entire row into a RECORD variable using the %ROWTYPE attribute. r_sales_employee c_sales_employees%ROWTYPE; declares a record variable that has the same structure as the rows returned by c_sales_employees. This simplifies code by allowing you to refer to columns as r_sales_employee.column_name, especially useful when dealing with many columns.

Example 4: Fetching with a WHILE Loop

DECLARE
    CURSOR c_locations IS
        SELECT location_id, street_address, city
        FROM locations;
        
    v_location_id      locations.location_id%TYPE;
    v_street_address   locations.street_address%TYPE;
    v_city             locations.city%TYPE;
BEGIN
    OPEN c_locations;
    
    -- Initialize the first fetch to make WHILE condition work
    FETCH c_locations INTO v_location_id, v_street_address, v_city;
    
    -- Loop while a row was found
    WHILE c_locations%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('Location ID: ' || v_location_id || 
                             ', City: ' || v_city ||
                             ', Address: ' || v_street_address);
                             
        -- Fetch the next row for the next iteration
        FETCH c_locations INTO v_location_id, v_street_address, v_city;
    END LOOP;
    
    CLOSE c_locations;
END;
/

Explanation

This example shows how to use a WHILE loop for fetching data. It requires an initial FETCH before the WHILE condition is evaluated. The loop continues as long as c_locations%FOUND is TRUE. Another FETCH is placed at the end of the loop body to prepare for the next iteration.

Example 5: Conditional Processing during Fetch

DECLARE
    CURSOR c_jobs IS
        SELECT job_id, job_title, min_salary, max_salary
        FROM jobs;
        
    r_job_info c_jobs%ROWTYPE;
BEGIN
    OPEN c_jobs;
    
    LOOP
        FETCH c_jobs INTO r_job_info;
        EXIT WHEN c_jobs%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Job Title: ' || r_job_info.job_title);
        
        IF r_job_info.min_salary < 5000 THEN
            DBMS_OUTPUT.PUT_LINE('  (Entry-level job)');
        ELSIF r_job_info.max_salary > 20000 THEN
            DBMS_OUTPUT.PUT_LINE('  (Senior-level job)');
        END IF;
    END LOOP;
    
    CLOSE c_jobs;
END;
/

Explanation

This example demonstrates performing conditional logic on each fetched row. After fetching job details into r_job_info, an IF-ELSIF statement checks the min_salary and max_salary to categorize the job as 'Entry-level' or 'Senior-level'. This highlights how you can apply business rules to individual rows as they are processed.

Closing Cursors

After you have finished processing all the rows from an explicit cursor, it's crucial to close it using the CLOSE statement. Closing a cursor releases the resources (memory and locks) associated with it in the private SQL area. Failing to close cursors can lead to resource leaks, which can negatively impact database performance, especially in applications with high concurrency. Always ensure your cursors are closed, even if an error occurs during processing. Proper cursor closing is a fundamental aspect of efficient PL/SQL coding.

Note: close cursor PL/SQL, cursor management, releasing cursor resources, PL/SQL performance tips, best practices for cursors.

Example 1: Closing a Simple Cursor

DECLARE
    CURSOR c_countries IS
        SELECT country_id, country_name
        FROM countries;
        
    v_country_id   countries.country_id%TYPE;
    v_country_name countries.country_name%TYPE;
BEGIN
    OPEN c_countries;
    
    LOOP
        FETCH c_countries INTO v_country_id, v_country_name;
        EXIT WHEN c_countries%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Country: ' || v_country_name);
    END LOOP;
    
    -- Close the cursor after all rows have been processed
    CLOSE c_countries;
    DBMS_OUTPUT.PUT_LINE('Cursor c_countries closed successfully.');
END;
/

Explanation

This example demonstrates the essential CLOSE statement after a cursor has been opened and its data has been fetched. After the LOOP finishes processing all countries, CLOSE c_countries; is executed to release the resources held by the cursor.

Example 2: Closing Multiple Cursors

DECLARE
    CURSOR c_managers IS
        SELECT employee_id, first_name FROM employees WHERE job_id LIKE '%MAN%';
        
    CURSOR c_analysts IS
        SELECT employee_id, first_name FROM employees WHERE job_id LIKE '%ANALYST%';
        
    v_emp_id       employees.employee_id%TYPE;
    v_first_name   employees.first_name%TYPE;
BEGIN
    OPEN c_managers;
    -- Process managers
    LOOP
        FETCH c_managers INTO v_emp_id, v_first_name;
        EXIT WHEN c_managers%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Manager: ' || v_first_name);
    END LOOP;
    CLOSE c_managers; -- Close the first cursor

    OPEN c_analysts;
    -- Process analysts
    LOOP
        FETCH c_analysts INTO v_emp_id, v_first_name;
        EXIT WHEN c_analysts%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Analyst: ' || v_first_name);
    END LOOP;
    CLOSE c_analysts; -- Close the second cursor
    
    DBMS_OUTPUT.PUT_LINE('All cursors closed.');
END;
/

Explanation

This code demonstrates closing multiple cursors independently. After processing all managers with c_managers, it's closed. Similarly, after processing analysts with c_analysts, that cursor is also closed. This pattern ensures that resources are released as soon as a cursor is no longer needed.

Example 3: Closing Cursor in an Exception Handler

DECLARE
    CURSOR c_risky_data IS
        SELECT column1, 1/0 FROM dual; -- This will cause a ZERO_DIVIDE error
        
    v_col1 VARCHAR2(100);
    v_col2 NUMBER;
BEGIN
    OPEN c_risky_data;
    
    LOOP
        FETCH c_risky_data INTO v_col1, v_col2;
        EXIT WHEN c_risky_data%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Data: ' || v_col1 || ' ' || v_col2);
    END LOOP;
    
    CLOSE c_risky_data; -- This might not be reached if an error occurs
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        -- Crucial: Close the cursor if an error occurs
        IF c_risky_data%ISOPEN THEN
            CLOSE c_risky_data;
            DBMS_OUTPUT.PUT_LINE('Cursor c_risky_data closed in exception handler.');
        END IF;
END;
/

Explanation

This is a critical example showing how to ensure a cursor is closed even if an error occurs during its processing. A ZERO_DIVIDE error is intentionally introduced. The EXCEPTION WHEN OTHERS THEN block catches the error. Inside the exception handler, c_risky_data%ISOPEN checks if the cursor is still open, and if so, it's explicitly closed, preventing resource leaks.

Example 4: Re-opening and Closing a Cursor

DECLARE
    CURSOR c_products IS
        SELECT product_id, product_name
        FROM products
        WHERE category_id = 1;
        
    v_product_id    products.product_id%TYPE;
    v_product_name  products.product_name%TYPE;
BEGIN
    -- First iteration: category 1
    OPEN c_products;
    DBMS_OUTPUT.PUT_LINE('--- Products in Category 1 ---');
    LOOP
        FETCH c_products INTO v_product_id, v_product_name;
        EXIT WHEN c_products%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Product: ' || v_product_name);
    END LOOP;
    CLOSE c_products;

    -- Second iteration: change the query implicitly (not recommended for dynamic queries)
    -- This example is purely for demonstrating open/close multiple times on same cursor name
    -- In real scenarios, use parameterized cursors or ref cursor for dynamic queries.
    -- (Hypothetically, imagine the query for c_products changes here)
    -- For demonstration, let's assume the query changes implicitly or you are running this in different blocks.
    -- In a single block, the cursor is defined once.
    -- To truly change the query, you'd use a REF CURSOR.
    -- For simplicity of this example, we just re-open the same defined cursor.

    DBMS_OUTPUT.PUT_LINE('--- Re-opening the same cursor ---');
    OPEN c_products; -- Re-open the cursor
    DBMS_OUTPUT.PUT_LINE('Cursor c_products re-opened.');
    -- No fetching here to keep example focused on open/close.
    CLOSE c_products;
    DBMS_OUTPUT.PUT_LINE('Cursor c_products closed again.');
END;
/

Explanation

This example demonstrates that a cursor can be closed and then re-opened. When c_products is opened the first time, it processes category 1 products. After closing, it's then re-opened. While the underlying SELECT statement remains the same in this explicit cursor example (for truly dynamic queries, REF CURSORS are used), it illustrates the ability to reset and re-execute a cursor's query.

Example 5: Closing when No Rows are Found (No Fetch Loop)

DECLARE
    CURSOR c_non_existent_data IS
        SELECT 'X' FROM dual WHERE 1 = 2; -- This query will return no rows
        
    v_dummy VARCHAR2(1);
BEGIN
    OPEN c_non_existent_data;
    DBMS_OUTPUT.PUT_LINE('Cursor c_non_existent_data opened.');
    
    -- Attempt to fetch (will immediately set %NOTFOUND to TRUE)
    FETCH c_non_existent_data INTO v_dummy;
    
    IF c_non_existent_data%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows found for c_non_existent_data.');
    END IF;
    
    -- Always close the cursor, even if no rows were fetched
    CLOSE c_non_existent_data;
    DBMS_OUTPUT.PUT_LINE('Cursor c_non_existent_data closed.');
END;
/

Explanation

This example shows that you must always close a cursor, even if the query returns no rows. The cursor c_non_existent_data is opened, but its SELECT statement (WHERE 1=2) ensures no rows are returned. Even in this case, the CLOSE statement is vital to release the allocated resources.

Cursor FOR Loops

The Cursor FOR Loop is a much simpler and more convenient way to process explicit cursors, especially when you need to fetch and process every row returned by a query. It implicitly declares a record variable, opens the cursor, fetches each row into the record, and closes the cursor automatically when all rows have been processed or when the loop is exited. This significantly reduces the amount of boilerplate code required for cursor management, making your PL/SQL code cleaner and less prone to errors. It's highly recommended for most cursor processing scenarios.

Note: cursor FOR loop, PL/SQL cursor loop, implicit cursor handling, simplify cursor code, Oracle PL/SQL examples.

Example 1: Basic Cursor FOR Loop

DECLARE
    -- Declare a simple cursor
    CURSOR c_departments IS
        SELECT department_id, department_name, location_id
        FROM departments
        ORDER BY department_name;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Departments List ---');
    
    -- Use a Cursor FOR Loop to iterate through the departments
    FOR dept_rec IN c_departments LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || dept_rec.department_id || 
                             ', Name: ' || dept_rec.department_name || 
                             ', Location ID: ' || dept_rec.location_id);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('--- End of List ---');
END;
/

Explanation

This example demonstrates the simplicity of the cursor FOR loop. You declare the cursor c_departments as usual. The FOR dept_rec IN c_departments LOOP statement automatically handles opening the cursor, declaring a record variable (dept_rec) with the structure of the cursor's result set, fetching rows into it, and finally closing the cursor. You access the columns directly using dot notation (e.g., dept_rec.department_id).

Example 2: Cursor FOR Loop with Inline Query

BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Employees in IT Department ---');
    
    -- Cursor FOR Loop with an inline SELECT statement (no explicit cursor declaration needed)
    FOR emp_rec IN (SELECT employee_id, first_name, last_name, email
                    FROM employees
                    WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'))
    LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name || 
                             ', Email: ' || emp_rec.email);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('--- End of List ---');
END;
/

Explanation

This example showcases an even more concise way to use a cursor FOR loop: by providing the SELECT statement directly within the FOR loop, without an explicit CURSOR declaration in the DECLARE section. Oracle implicitly creates and manages an internal cursor for this query. This is often preferred for simple, one-time cursor usage.

Example 3: Cursor FOR Loop with Joins

BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Employee Salaries by Department ---');
    
    -- Cursor FOR Loop joining employees and departments
    FOR emp_dept_rec IN (SELECT e.first_name, e.last_name, d.department_name, e.salary
                         FROM employees e
                         JOIN departments d ON e.department_id = d.department_id
                         ORDER BY d.department_name, e.last_name)
    LOOP
        DBMS_OUTPUT.PUT_LINE('Name: ' || emp_dept_rec.first_name || ' ' || emp_dept_rec.last_name || 
                             ' | Dept: ' || emp_dept_rec.department_name || 
                             ' | Salary: ' || emp_dept_rec.salary);
    END LOOP;
END;
/

Explanation

This example demonstrates using a cursor FOR loop with a SELECT statement that involves a JOIN. It's a common scenario to combine data from multiple tables. The emp_dept_rec record variable automatically adapts to the combined columns from both tables, making it easy to access department_name, first_name, and salary.

Example 4: Conditional Logic within Cursor FOR Loop

DECLARE
    v_total_salary NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- High Earners and Total Salary ---');
    
    FOR emp_rec IN (SELECT first_name, last_name, salary
                    FROM employees
                    WHERE job_id IN ('SA_MAN', 'AD_PRES', 'IT_PROG'))
    LOOP
        v_total_salary := v_total_salary + emp_rec.salary;
        
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name || 
                             ', Salary: ' || emp_rec.salary);
                             
        IF emp_rec.salary > 15000 THEN
            DBMS_OUTPUT.PUT_LINE('  (High earner!)');
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total Salary for selected jobs: ' || v_total_salary);
END;
/

Explanation

This example shows how to perform conditional processing and calculations within a cursor FOR loop. It iterates through a filtered set of employees, calculates their total salary, and flags "High earners" based on their individual salary. The cursor FOR loop provides a clean structure for such row-by-row operations.

Example 5: Nesting Cursor FOR Loops (Carefully!)

BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Departments and Their Employees ---');
    
    -- Outer loop for departments
    FOR dept_rec IN (SELECT department_id, department_name FROM departments ORDER BY department_name) LOOP
        DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name || ' (ID: ' || dept_rec.department_id || ')');
        
        -- Inner loop for employees within the current department
        FOR emp_rec IN (SELECT first_name, last_name
                        FROM employees
                        WHERE department_id = dept_rec.department_id -- Correlated subquery
                        ORDER BY last_name)
        LOOP
            DBMS_OUTPUT.PUT_LINE('  - Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(''); -- Blank line for readability
    END LOOP;
END;
/

Explanation

This advanced example demonstrates nested cursor FOR loops. The outer loop iterates through each department. For each department, an inner cursor FOR loop retrieves and displays the employees belonging to that specific department. This uses a correlated subquery in the inner loop's WHERE clause (WHERE department_id = dept_rec.department_id), meaning the inner query executes for each row of the outer query. While powerful, nested loops can impact performance on large datasets, so use them judiciously.

Cursors with Parameters

Cursors with parameters allow you to pass values to the WHERE clause of a cursor's SELECT statement at the time you open the cursor. This makes your cursors more flexible and reusable, as you can retrieve different subsets of data based on the input parameters without having to declare a new cursor for each variation. Parameters are defined in the cursor declaration, similar to procedure or function parameters. This is particularly useful when you need to fetch data based on user input or dynamic criteria.

Note: parameterized cursors, PL/SQL cursor parameters, dynamic cursor queries, reusable cursors, Oracle PL/SQL best practices.

Example 1: Cursor with a Single Parameter

DECLARE
    -- Declare a cursor that accepts a department ID as a parameter
    CURSOR c_employees_by_dept (p_department_id employees.department_id%TYPE) IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_department_id; -- Use the parameter in the WHERE clause
        
    v_employee_id    employees.employee_id%TYPE;
    v_first_name     employees.first_name%TYPE;
    v_last_name      employees.last_name%TYPE;
    v_salary         employees.salary%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Employees in Department 50 ---');
    -- Open the cursor, passing 50 as the parameter value
    OPEN c_employees_by_dept(50);
    LOOP
        FETCH c_employees_by_dept INTO v_employee_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN c_employees_by_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
    END LOOP;
    CLOSE c_employees_by_dept;
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- Employees in Department 90 ---');
    -- Open the cursor again with a different parameter value
    OPEN c_employees_by_dept(90);
    LOOP
        FETCH c_employees_by_dept INTO v_employee_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN c_employees_by_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
    END LOOP;
    CLOSE c_employees_by_dept;
END;
/

Explanation

This example demonstrates a cursor c_employees_by_dept that takes p_department_id as a parameter. When OPEN c_employees_by_dept(50); is called, the value 50 is passed to the p_department_id parameter, filtering the results for department 50. The same cursor can then be re-opened with OPEN c_employees_by_dept(90); to retrieve employees from department 90, illustrating reusability.

Example 2: Cursor with Multiple Parameters

DECLARE
    -- Cursor with two parameters: job ID and minimum salary
    CURSOR c_eligible_employees (p_job_id employees.job_id%TYPE, p_min_salary employees.salary%TYPE) IS
        SELECT employee_id, first_name, last_name, job_id, salary
        FROM employees
        WHERE job_id = p_job_id
        AND salary >= p_min_salary;
        
    r_employee_info c_eligible_employees%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Sales Representatives with Salary >= 10000 ---');
    -- Pass 'SA_REP' and 10000 to the cursor
    OPEN c_eligible_employees('SA_REP', 10000);
    LOOP
        FETCH c_eligible_employees INTO r_employee_info;
        EXIT WHEN c_eligible_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Name: ' || r_employee_info.first_name || ' ' || r_employee_info.last_name || 
                             ', Job: ' || r_employee_info.job_id || ', Salary: ' || r_employee_info.salary);
    END LOOP;
    CLOSE c_eligible_employees;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- IT Programmers with Salary >= 8000 ---');
    -- Pass 'IT_PROG' and 8000 to the cursor
    OPEN c_eligible_employees('IT_PROG', 8000);
    LOOP
        FETCH c_eligible_employees INTO r_employee_info;
        EXIT WHEN c_eligible_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Name: ' || r_employee_info.first_name || ' ' || r_employee_info.last_name || 
                             ', Job: ' || r_employee_info.job_id || ', Salary: ' || r_employee_info.salary);
    END LOOP;
    CLOSE c_eligible_employees;
END;
/

Explanation

This example shows a cursor c_eligible_employees that accepts two parameters: p_job_id and p_min_salary. When opening the cursor, you provide values for both parameters (e.g., 'SA_REP', 10000). This allows for more granular filtering and increased flexibility in retrieving specific subsets of data.

Example 3: Cursor FOR Loop with Parameters

DECLARE
    -- Cursor with a parameter for location ID
    CURSOR c_location_departments (p_location_id departments.location_id%TYPE) IS
        SELECT department_name
        FROM departments
        WHERE location_id = p_location_id
        ORDER BY department_name;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Departments in Location 1700 (Seattle) ---');
    -- Use Cursor FOR Loop, passing 1700 to the parameter
    FOR dept_rec IN c_location_departments(1700) LOOP
        DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- Departments in Location 1800 (Toronto) ---');
    -- Re-use the cursor FOR loop with a different parameter value
    FOR dept_rec IN c_location_departments(1800) LOOP
        DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name);
    END LOOP;
END;
/

Explanation

This example combines parameterized cursors with the convenience of the cursor FOR loop. The FOR dept_rec IN c_location_departments(1700) LOOP syntax implicitly opens, fetches, and closes the cursor, passing 1700 as the parameter for the location ID. This is a very clean and efficient way to handle parameterized queries.

Example 4: Default Values for Cursor Parameters

DECLARE
    -- Cursor with a parameter that has a default value
    CURSOR c_employees_with_default (p_department_id employees.department_id%TYPE DEFAULT 80) IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = p_department_id;
        
    r_emp_data c_employees_with_default%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Employees (Default Department 80) ---');
    -- Open without providing a parameter, default value (80) is used
    OPEN c_employees_with_default;
    LOOP
        FETCH c_employees_with_default INTO r_emp_data;
        EXIT WHEN c_employees_with_default%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee: ' || r_emp_data.first_name || ' ' || r_emp_data.last_name);
    END LOOP;
    CLOSE c_employees_with_default;
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- Employees in Department 10 ---');
    -- Open with a specific parameter, overriding the default
    OPEN c_employees_with_default(10);
    LOOP
        FETCH c_employees_with_default INTO r_emp_data;
        EXIT WHEN c_employees_with_default%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee: ' || r_emp_data.first_name || ' ' || r_emp_data.last_name);
    END LOOP;
    CLOSE c_employees_with_default;
END;
/

Explanation

This example introduces default values for cursor parameters. The p_department_id employees.department_id%TYPE DEFAULT 80 in the cursor declaration means if you open c_employees_with_default without providing a parameter, 80 will be used. You can still explicitly pass a value (e.g., 10) to override the default.

Example 5: Using Cursor Parameters in More Complex Queries

DECLARE
    -- Cursor to find orders within a date range for a specific customer
    CURSOR c_customer_orders_by_date (p_customer_id customers.customer_id%TYPE,
                                      p_start_date  DATE,
                                      p_end_date    DATE) IS
        SELECT o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total_order_value
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.customer_id = p_customer_id
        AND o.order_date BETWEEN p_start_date AND p_end_date
        GROUP BY o.order_id, o.order_date
        ORDER BY o.order_date;

    r_order_summary c_customer_orders_by_date%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Orders for Customer 101 from 2007 to 2008 ---');
    -- Using the parameterized cursor with specific values
    OPEN c_customer_orders_by_date(101, TO_DATE('2007-01-01', 'YYYY-MM-DD'), TO_DATE('2008-12-31', 'YYYY-MM-DD'));
    LOOP
        FETCH c_customer_orders_by_date INTO r_order_summary;
        EXIT WHEN c_customer_orders_by_date%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Order ID: ' || r_order_summary.order_id || 
                             ', Date: ' || TO_CHAR(r_order_summary.order_date, 'YYYY-MM-DD') ||
                             ', Total Value: ' || r_order_summary.total_order_value);
    END LOOP;
    CLOSE c_customer_orders_by_date;
END;
/

Explanation

This example demonstrates a parameterized cursor c_customer_orders_by_date with multiple parameters of different data types (customer_id, start_date, end_date). These parameters are used to filter a complex query involving joins and an aggregate function. This highlights how parameterized cursors are essential for building flexible and robust reporting or data retrieval functionalities.