Explain Types of Cursors.

Q.8 Explain Types of Cursors.

Answer :- 

• In PL/SQL, a cursor is a pointer or a handle to a context area in memory, where SQL query results are processed and stored.

• It allows you to retrieve and manipulate query results row by row.

• Cursors are particularly useful when working with queries that return multiple rows, as they provide a structured way to iterate through these results.

Types of Cursors

• Cursors in PL/SQL are broadly classified into two categories:

• Implicit Cursors and Explicit Cursors.

• Each type serves different purposes and offers unique advantages.

1. Implicit Cursors :- 

• Created automatically by Oracle whenever a SQL statement (such as SELECT INTO, INSERT, UPDATE, or DELETE) is executed.

• Used when the SQL query affects only a single row or performs operations like DML commands.

• The programmer does not need to declare or manage implicit cursors explicitly.

Key Attributes of Implicit Cursors:

• %FOUND: Returns TRUE if a DML statement affects at least one row.

• %NOTFOUND: Returns TRUE if no rows are affected.

• %ROWCOUNT: Returns the number of rows affected by the SQL statement.

• %ISOPEN: Always FALSE for implicit cursors because they close automatically after execution.

Example :- 

BEGIN
UPDATE employees SET salary = salary + 500 WHERE department_id = 10;

IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ rows updated.’);
END IF;
END;

2. Explicit Cursors

• Declared and controlled explicitly by the programmer.

• Used for queries that return multiple rows, providing more control over row-by-row processing.

• Explicit cursors go through four stages: Declaration, Opening, Fetching, and Closing.

Steps to Use Explicit Cursors:

• Declare the cursor using the CURSOR keyword and the SQL query.

• Open the cursor to execute the query and allocate memory.

• Fetch rows from the cursor into variables.

• Close the cursor to release memory.

Example :- 

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10;
emp_id employees.employee_id%TYPE;
emp_name employees.first_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_salary;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || emp_id || ‘, Name: ‘ || emp_name || ‘, Salary: ‘ || emp_salary);
END LOOP;
CLOSE emp_cursor;
END;

Key Attributes of Explicit Cursors:

• %FOUND: Returns TRUE if a row is fetched successfully.

• %NOTFOUND: Returns TRUE if no rows are left to fetch.

• %ROWCOUNT: Returns the number of rows fetched so far.

• %ISOPEN: Returns TRUE if the cursor is currently open.