PLSQL Cursor

        A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
Cursors are used to retrieve and manipulate data from a query. They act as pointers to a result set and are particularly useful when you need to fetch rows one at a time.

Types of Cursor

Implicit Cursors
Explicit Cursors

Implicit Cursors:

Automatically created by Oracle for SQL statements like SELECT INTO, INSERT, UPDATE, and DELETE. These are managed by Oracle internally.

Attributes:

1. Sql%notfound
2. Sql%found
3. Sql %rowcount

Example

DECLARE  
total_rows number(2);
BEGIN
UPDATE employess
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;  
END;
/

Explicit Cursors:

Defined by the programmer to have more control over the context and the row processing.

Attributes

• %ISOPEN: Returns TRUE if the cursor is open.
• %FOUND: Returns TRUE if the last fetch returned a row.
• %NOTFOUND: Returns TRUE if the last fetch did not return a row.
• %ROWCOUNT: Returns the number of rows fetched so far.

Example 1:

DECLARE
-- Declare cursor
CURSOR emp_cursor IS
SELECT employee_id, employee_name
FROM employees;

-- Declare variables to hold cursor data
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cursor;
 
-- Fetch data and process
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ' Name: ' || v_employee_name);
END LOOP;
 
-- Close the cursor
CLOSE emp_cursor;
END;

Example 2:
DECLARE
-- Define an explicit cursor
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
    
-- Variables to hold the fetched data
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
 
-- Variable to count the number of rows fetched
v_row_count INTEGER := 0;
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Loop to fetch rows and process them
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
    
-- Check if there are no more rows to fetch
EXIT WHEN emp_cursor%NOTFOUND;

-- Increment the row count
v_row_count := v_row_count + 1;
    
-- Display fetched data
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ' Name: ' || v_employee_name);
    
-- Check if cursor is open (it should be)
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is still open.');
END IF;
END LOOP;

-- Display the number of rows fetched
  DBMS_OUTPUT.PUT_LINE('Total rows fetched: ' || v_row_count);
 
-- Check if any rows were found
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('At least one row was found.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows were found.');
END IF;
 
-- Close the cursor
CLOSE emp_cursor;
 
-- Check if the cursor is closed (it should be)
IF NOT emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is closed.');
END IF;
 
-- Check the row count attribute
DBMS_OUTPUT.PUT_LINE('Final row count: ' || emp_cursor%ROWCOUNT);
 
EXCEPTION
WHEN OTHERS THEN
-- Handle any errors
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
IF emp_cursor%ISOPEN THEN
-- Ensure the cursor is closed in case of an error
CLOSE emp_cursor;
END IF;
END;

Bulk Collecter

Bulk Collect is used to fetch multiple rows of data at once into a PL/SQL collection (such as a TABLE or ARRAY). It improves performance by reducing the number of context switches between SQL and PL/SQL engines.

Bulk Collect Example:

DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
emp_table emp_table_type;
 
BEGIN
SELECT * BULK COLLECT INTO emp_table FROM employees;
 
FOR i IN 1 .. emp_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_table(i).employee_id || ' Name: ' || emp_table(i).employee_name);
END LOOP;
END;

Cursor with Bulk Collect Example:

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
    
TYPE emp_rec IS RECORD (
employee_id employees.employee_id%TYPE,
employee_name employees.employee_name%TYPE);
 
TYPE emp_table_type IS TABLE OF emp_rec;
emp_table emp_table_type;
BEGIN
OPEN emp_cursor;
 
FETCH emp_cursor BULK COLLECT INTO emp_table;
 
CLOSE emp_cursor;
 
FOR i IN 1 .. emp_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_table(i).employee_id || ' Name: ' || emp_table(i).employee_name);
END LOOP;
END;

Notes:

• Cursors provide a way to process rows one at a time.
• Bulk Collect allows fetching multiple rows at once to improve performance.
• Combining them can be useful for handling large datasets efficiently.