Cursors

Cursors in PL/SQL are used to handle query results one row at a time. They allow you to iterate through result sets and perform operations on each row.

1. Introduction to Cursors

A cursor is a pointer to the context area, where SQL statements and their results are processed. Cursors allow row-by-row processing of query results.

Types of Cursors:

1. Implicit Cursor:
   • Automatically created by PL/SQL for SQL statements that return a single row or manipulate data (INSERT, UPDATE, DELETE).
2. Explicit Cursor:
   • Defined explicitly by the programmer to handle queries that return multiple rows.

2. Implicit Cursors

Implicit cursors are automatically created by PL/SQL for simple SQL operations.

Example: Using Implicit Cursor

plsql
Copy code
DECLARE
   v_emp_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_emp_count FROM employees;
   DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_emp_count);
END;
/

Key Points:

• You don't explicitly declare or control the cursor.
• Used for SQL statements returning only one row.

3. Explicit Cursors

Explicit cursors are declared by the programmer for queries that return multiple rows.

Steps to Use Explicit Cursors:

1. DECLARE: Define the cursor with the SQL query.
2. OPEN: Open the cursor to establish the result set.
3. FETCH: Retrieve rows one at a time.
4. CLOSE: Release the resources associated with the cursor.

Syntax:

plsql
Copy code
DECLARE
   CURSOR cursor_name IS
      SELECT column1, column2 FROM table_name;
BEGIN
   OPEN cursor_name;
   LOOP
      FETCH cursor_name INTO variable1, variable2;
      EXIT WHEN cursor_name%NOTFOUND;
      -- Process the data
   END LOOP;
   CLOSE cursor_name;
END;
/

Example: Using Explicit Cursor

plsql
Copy code
DECLARE
   CURSOR emp_cursor IS
      SELECT first_name, last_name FROM employees WHERE department_id = 10;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_first_name, v_last_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
   END LOOP;
   CLOSE emp_cursor;
END;
/

Explanation:

• Declares a cursor emp_cursor to fetch data from the employees table.
• Loops through each row and processes the fetched data.

4. Cursor FOR Loops

Cursor FOR loops simplify the use of explicit cursors by handling OPEN, FETCH, and CLOSE operations automatically.

Syntax:

plsql
Copy code
FOR record_variable IN cursor_name LOOP
   -- Process the data
END LOOP;

Example:

plsql
Copy code
DECLARE
   CURSOR emp_cursor IS
      SELECT first_name, last_name FROM employees WHERE department_id = 10;
BEGIN
   FOR emp_rec IN emp_cursor LOOP
      DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
   END LOOP;
END;
/

Explanation:

• The FOR loop automatically opens, fetches, and closes the cursor.
• emp_rec is an implicit record variable that holds the fetched data.

5. Parameterized Cursors

Parameterized cursors allow you to pass parameters to the cursor query.

Syntax:

plsql
Copy code
DECLARE
   CURSOR cursor_name (parameter_name datatype) IS
      SELECT columns FROM table_name WHERE column = parameter_name;
BEGIN
   -- Use the cursor
END;

Example:

plsql
Copy code
DECLARE
   CURSOR emp_cursor (dept_id NUMBER) IS
      SELECT first_name, last_name FROM employees WHERE department_id = dept_id;
BEGIN
   FOR emp_rec IN emp_cursor(10) LOOP
      DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
   END LOOP;
END;
/

Explanation:

• The dept_id parameter is passed to filter employees by department.

6. Cursor Attributes

Cursor attributes provide information about the state of a cursor.

Example: Using Cursor Attributes

plsql
Copy code
DECLARE
   CURSOR emp_cursor IS
      SELECT first_name, last_name FROM employees;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_first_name, v_last_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
      DBMS_OUTPUT.PUT_LINE('Rows Fetched: ' || emp_cursor%ROWCOUNT);
   END LOOP;
   CLOSE emp_cursor;
END;
/

7. Example: Fetching Data from a Table Using Cursors

Scenario: Fetch employees from the employees table whose salary is greater than 5000.

plsql
Copy code
DECLARE
   CURSOR emp_cursor IS
      SELECT first_name, last_name, salary FROM employees WHERE salary > 5000;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_first_name, v_last_name, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
   END LOOP;
   CLOSE emp_cursor;
END;
/

Summary

1. Implicit Cursors:

   • Automatically created by PL/SQL for simple SQL queries.

2. Explicit Cursors:

   • Used for queries that return multiple rows.
   • Must be explicitly declared, opened, fetched, and closed.

3. Cursor FOR Loops:

   • Simplify cursor handling by automating OPEN, FETCH, and CLOSE.

4. Parameterized Cursors:

   • Allow passing parameters to filter data dynamically.

5. Cursor Attributes:

   • Provide information about cursor state and operations:
     • %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT.

By mastering cursors, you can efficiently handle query result sets and perform row-by-row processing, enabling robust database applications.