Cursors
A cursor is a pointer to a result set of a SQL query. It allows row-by-row processing of query results in PL/SQL.
1. Implicit Cursor (Automatically Managed by Oracle)
Used for DML statements (INSERT, UPDATE, DELETE) and single-row SELECT queries.
No need to explicitly declare, open, fetch, or close the cursor.
Oracle handles it automatically.
2. Explicit Cursor (User-Managed)
Used when processing multiple rows from a SELECT query.
Requires declaration, opening, fetching, and closing.
1. Implicit Cursor (Automatically Managed by Oracle)
PL/SQL automatically creates an implicit cursor when a DML statement (INSERT, UPDATE, DELETE) or a SELECT statement returns a single row.
Example: Using Implicit Cursor
DECLARE
emp_name employees.emp_name%TYPE;
BEGIN
-- Implicit cursor used in SELECT INTO (single-row query)
SELECT emp_name INTO emp_name FROM employees WHERE emp_id = 101;
-- Display result
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;
/
Key Points
✅ No need to declare, open, fetch, or close
✅ Used for single-row queries
🚫 Throws an error if more than one row is returned
Cursor Attributes for Implicit Cursors
PL/SQL provides cursor attributes to check the status of the last SQL statement:
Cursor Attribute
Description
%FOUND
Returns TRUE if a row is found
%NOTFOUND
Returns TRUE if no row is found
%ROWCOUNT
Returns the number of rows affected
%ISOPEN
Always FALSE for implicit cursors (they close automatically)
Example: Using Cursor Attributes
DECLARE
emp_name employees.emp_name%TYPE;
BEGIN
SELECT emp_name INTO emp_name FROM employees WHERE emp_id = 101;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Found: ' || emp_name);
ELSE
DBMS_OUTPUT.PUT_LINE('No Employee Found');
END IF;
DBMS_OUTPUT.PUT_LINE('Rows Affected: ' || SQL%ROWCOUNT);
END;
/
2. Explicit Cursor (User-Managed)
An explicit cursor is used when a SELECT query returns multiple rows.
It must be declared, opened, fetched, and closed manually.
Steps to Use an Explicit Cursor:
1. Declare the cursor (CURSOR cursor_name IS SELECT ...)
2. Open the cursor (OPEN cursor_name;)
3. Fetch the data (FETCH cursor_name INTO variables;)
4. Close the cursor (CLOSE cursor_name;)
Cursor Attributes for Explicit Cursors:
Cursor Attribute
Description
cursor_name%FOUND
Returns TRUE if a row is fetched
cursor_name%NOTFOUND
Returns TRUE if no row is fetched
cursor_name%ROWCOUNT
Returns the number of rows fetched
cursor_name%ISOPEN
Returns TRUE if the cursor is open
DECLARE
CURSOR emp_cursor IS SELECT emp_id FROM employees WHERE department_id = 20;
v_emp_id employees.emp_id%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Employees Fetched: ' || emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END;
/
3. Cursor FOR LOOP (Simplified Cursor Handling)
Definition
· The cursor FOR LOOP automatically opens, fetches, and closes the cursor.
· No need for explicit OPEN, FETCH, EXIT, or CLOSE statements.
Example: Cursor FOR LOOP
DECLARE
CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees WHERE department_id = 30;
BEGIN
-- Cursor FOR LOOP automatically opens, fetches, and closes
FOR emp_rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id || ', Name: ' || emp_rec.emp_name);
END LOOP;
END;
/
Key Points
✅ Easier to use (No need for manual OPEN, FETCH, CLOSE)
✅ Best for looping through result sets
🚫 Cannot fetch single rows manually
Definition
A parameterized cursor allows you to pass values dynamically to the cursor at runtime.
Example: Parameterized Cursor
DECLARE
-- Declare a cursor with a parameter
CURSOR emp_cursor (p_dept_id NUMBER) IS
SELECT emp_id, emp_name FROM employees WHERE department_id = p_dept_id;
BEGIN
-- Open cursor for department 40
FOR emp_rec IN emp_cursor(40) LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id || ', Name: ' || emp_rec.emp_name);
END LOOP;
END;
/
Key Points
✅ Allows dynamic filtering
✅ Makes the cursor reusable
5. REF CURSOR
A REF CURSOR (Reference Cursor) is a pointer to a result set that allows dynamic and flexible data retrieval.
Unlike regular cursors, REF CURSORs can be opened dynamically at runtime and passed as parameters to procedures/functions.
✅ Dynamic SQL Execution – Query structure can be decided at runtime.
✅ Flexible Data Retrieval – Can be used to fetch different result sets.
✅ Pass Between PL/SQL Programs – Can be passed as parameters to procedures/functions.
✅ Better Performance – Useful for handling large result sets efficiently.
There are two types of REF CURSORs:
Strongly Types :
The structure (columns & data types) is predefined.
When you know the exact structure of the query result.
Weakly Typed:
The structure is not fixed; it can return different column sets.
When the query structure is dynamic.
Example: Using Strongly Typed REF CURSOR
DECLARE
-- Define a strongly typed REF CURSOR
TYPE emp_ref_cursor IS REF CURSOR RETURN employees%ROWTYPE;
emp_cursor emp_ref_cursor; -- Cursor variable
emp_row employees%ROWTYPE; -- Record to store row data
BEGIN
-- Open REF CURSOR with a fixed structure
OPEN emp_cursor FOR SELECT * FROM employees WHERE department_id = 10;
-- Fetch and process rows
LOOP
FETCH emp_cursor INTO emp_row;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_row.emp_name || ', Salary: ' || emp_row.salary);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/
Example: Using Weakly Typed REF CURSOR
DECLARE
-- Define a weakly typed REF CURSOR
TYPE weak_ref_cursor IS REF CURSOR;
emp_cursor weak_ref_cursor; -- Cursor variable
-- Variables to store fetched data
v_emp_id employees.emp_id%TYPE;
v_emp_name employees.emp_name%TYPE;
BEGIN
-- Open cursor dynamically with any query
OPEN emp_cursor FOR SELECT emp_id, emp_name FROM employees WHERE department_id = 20;
-- Fetch and process rows
LOOP
FETCH emp_cursor INTO v_emp_id, v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_emp_name);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/
Passing REF CURSOR to a Procedure
-- Declare a weakly typed REF CURSOR
TYPE ref_cursor_type IS REF CURSOR;
-- Procedure to return employees based on department ID
PROCEDURE get_employees_by_dept(p_dept_id IN NUMBER, p_emp_cursor OUT ref_cursor_type) AS
BEGIN
OPEN p_emp_cursor FOR SELECT emp_id, emp_name FROM employees WHERE department_id = p_dept_id;
END get_employees_by_dept;
/
No comments:
Post a Comment