Cursors in PL/SQL

In PL/SQL, a cursor is a mechanism used to fetch more than one row in a PL/SQL block. It provides a means to access individual rows from the database tables in a controlled manner. There are two types of cursors in PL/SQL:

Implicit Cursors: These are automatically created by Oracle when an SQL statement is executed, when there is a need to manipulate the stored data. You don't explicitly declare or open these cursors.
Explicit Cursors: These are defined by PL/SQL developers when they need to fetch multiple rows from the database and process each row individually. The typical lifecycle of an explicit cursor includes the following steps:
- Declare: Define the SQL statement that returns the result set.
- Open: Execute the SQL statement and set up the result set.
- Fetch: Retrieve the next row from the result set.
- Close: Release the memory used by the result set.

Here's an example of using an explicit cursor:

DECLARE
   -- Declare a record to hold data fetched from the cursor
   CURSOR emp_cursor IS
      SELECT emp_id, emp_name, salary FROM employees;

   emp_record emp_cursor%ROWTYPE;  -- This record type will match the columns of the cursor
BEGIN
   -- Open the cursor
   OPEN emp_cursor;

   -- Fetch rows from the cursor one at a time until no more rows are found
   LOOP
      FETCH emp_cursor INTO emp_record;
      EXIT WHEN emp_cursor%NOTFOUND;  -- Exit when no more rows to fetch

      -- Process each row here (for the sake of this example, we'll just output the employee name)
      DBMS_OUTPUT.PUT_LINE(emp_record.emp_name || ' earns ' || emp_record.salary);
   END LOOP;

   -- Close the cursor
   CLOSE emp_cursor;
END;
/

Note:

%ROWTYPE attribute is used to declare a record that corresponds to a row in a table or a row fetched from a cursor.
%NOTFOUND is a cursor attribute that is TRUE if the last fetch did not return a row.

Cursor with Parameters: You can declare a cursor that takes parameters, which is useful when you want to use the same cursor for different queries based on different conditions.

Cursor FOR loop: PL/SQL provides a simpler way to fetch and process all rows from a cursor using the FOR loop.

DECLARE
   CURSOR emp_cursor IS
      SELECT emp_id, emp_name, salary FROM employees;
BEGIN
   FOR emp_record IN emp_cursor LOOP
      DBMS_OUTPUT.PUT_LINE(emp_record.emp_name || ' earns ' || emp_record.salary);
   END LOOP;
END;
/

Using the FOR loop with cursors like this automatically handles opening, fetching, and closing the cursor.

Cursors are crucial when you need to process individual rows of a result set in PL/SQL, but they should be used judiciously. Fetching large data sets with cursors can be resource-intensive, so always ensure you're fetching only the data you need.

How to Declare and Use Cursors in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
BEGIN
    -- Cursor logic here
END;

Implicit vs. Explicit Cursors in PL/SQL:

Implicit Cursor (No explicit declaration needed for SELECT INTO):

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT FirstName || ' ' || LastName INTO v_employee_name
    FROM Employees
    WHERE EmployeeID = 1;
END;

Explicit Cursor:

DECLARE
    CURSOR employee_cursor IS
        SELECT FirstName, LastName
        FROM Employees;
    v_employee_name VARCHAR2(100);
BEGIN
    OPEN employee_cursor;
    FETCH employee_cursor INTO v_employee_name;
    CLOSE employee_cursor;
END;

Using FETCH Statement with Cursors:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
    v_employee_rec employee_cursor%ROWTYPE;
BEGIN
    OPEN employee_cursor;
    FETCH employee_cursor INTO v_employee_rec;
    CLOSE employee_cursor;
END;

Opening, Closing, and Deallocating Cursors in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
BEGIN
    OPEN employee_cursor;
    -- Fetch and process data
    CLOSE employee_cursor;
END;

Cursor FOR LOOP in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
BEGIN
    FOR emp_rec IN employee_cursor LOOP
        -- Process each employee record
    END LOOP;
END;

Cursor Attributes in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
BEGIN
    IF employee_cursor%ISOPEN THEN
        CLOSE employee_cursor;
    END IF;
END;

Dynamic SQL with Cursors in PL/SQL:

DECLARE
    v_sql VARCHAR2(100);
    v_employee_name VARCHAR2(100);
    CURSOR dynamic_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees
        WHERE EmployeeID = 1;
BEGIN
    OPEN dynamic_cursor;
    FETCH dynamic_cursor INTO v_employee_name;
    CLOSE dynamic_cursor;
END;

Bulk Processing with BULK COLLECT and FORALL in PL/SQL:

DECLARE
    TYPE emp_type IS TABLE OF Employees%ROWTYPE;
    v_employees emp_type;
BEGIN
    SELECT * BULK COLLECT INTO v_employees FROM Employees WHERE ROWNUM <= 10;
    FORALL i IN 1..v_employees.COUNT
        INSERT INTO AnotherTable VALUES v_employees(i);
END;

Ref Cursors in PL/SQL:

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor ref_cursor_type;
BEGIN
    OPEN v_cursor FOR SELECT EmployeeID, FirstName, LastName FROM Employees;
    -- Use v_cursor as needed
    CLOSE v_cursor;
END;

Cursor Parameters in PL/SQL:

PROCEDURE get_employee_details(p_department_id IN NUMBER) IS
    CURSOR employee_cursor (v_department_id NUMBER) IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees
        WHERE DepartmentID = v_department_id;
    v_employee_rec employee_cursor%ROWTYPE;
BEGIN
    OPEN employee_cursor(p_department_id);
    FETCH employee_cursor INTO v_employee_rec;
    CLOSE employee_cursor;
END;

Exception Handling with Cursors in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
    v_employee_rec employee_cursor%ROWTYPE;
BEGIN
    OPEN employee_cursor;
    BEGIN
        FETCH employee_cursor INTO v_employee_rec;
        -- Handle exceptions here
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data found.');
    END;
    CLOSE employee_cursor;
END;

Cursors and Transaction Control in PL/SQL:

DECLARE
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
BEGIN
    FOR emp_rec IN employee_cursor LOOP
        -- Process each employee record
        SAVEPOINT before_processing;
        -- Perform some processing
        ROLLBACK TO before_processing;
    END LOOP;
END;

Cursors in Stored Procedures and Functions:

CREATE OR REPLACE PROCEDURE get_employee_info IS
    CURSOR employee_cursor IS
        SELECT EmployeeID, FirstName, LastName
        FROM Employees;
    v_employee_rec employee_cursor%ROWTYPE;
BEGIN
    OPEN employee_cursor;
    FETCH employee_cursor INTO v_employee_rec;
    CLOSE employee_cursor;
    -- Process v_employee_rec as needed
END get_employee_info;