Pranav BakareLet’s extend the previous example by demonstrating the difference between a normal cursor and a reference cursor, along with a scenario where the reference cursor solves a problem that the normal cursor cannot.
Basics of Cursor;
Key Differences between Normal Cursor and Reference Cursor:
Normal Cursor:
- A normal cursor is static, meaning it is bound to a fixed SQL query at compile-time.
- You cannot pass parameters to change the query dynamically.
- The structure and query are defined at the time of declaration and cannot be changed.
Reference Cursor (REF CURSOR):
- A reference cursor is dynamic, meaning it allows you to define the query at runtime.
- You can pass a reference cursor as a parameter to a procedure or function, allowing more flexible query execution.
- It is a pointer to a query result set, which can be opened for any SELECT statement dynamically.
Problem with Normal Cursor:
With a normal cursor, the query is fixed. Suppose we want to write a PL/SQL procedure where the query could vary based on different conditions, for example, fetching data from different tables or using dynamic conditions. A normal cursor cannot handle such cases easily.
A reference cursor solves this problem by allowing dynamic query assignment at runtime.
Scenario:
We want to create a PL/SQL block that fetches employee details based on a dynamic condition. If the condition is to fetch employees with EMPLOYEE_ID < 103, we use one query. If the condition is to fetch all employees, we use another query.
Using a Normal Cursor (Fixed Query):
This approach only works when the query is fixed and cannot be changed dynamically.Using a Reference Cursor (Dynamic Query):
This approach allows us to dynamically pass queries based on the condition.
Step 1: Create the EMPLOYEES Table and Insert Data (As before)
Table Creation:
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50)
);
Insert Data:
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (101, 'John', 'Doe');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (102, 'Jane', 'Smith');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (103, 'Mark', 'Taylor');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (104, 'Lucy', 'Williams');
Step 2: Demonstrate Using a Normal Cursor (Fixed Query)
In this example, the query is fixed and cannot change dynamically.
PL/SQL Block with a Normal Cursor:
DECLARE
-- Step 1: Declare a normal cursor (fixed query)
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID < 103; -- Fixed condition in query
-- Step 2: Record variable to hold the result of the fetch
emp_record EMPLOYEES%ROWTYPE;
BEGIN
-- Step 3: Open the cursor
OPEN emp_cursor;
-- Step 4: Fetch data and process
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Employee ID: ' || emp_record.EMPLOYEE_ID ||
', First Name: ' || emp_record.FIRST_NAME ||
', Last Name: ' || emp_record.LAST_NAME);
END LOOP;
-- Step 5: Close the cursor
CLOSE emp_cursor;
END;
Output (Normal Cursor):
Employee ID: 101, First Name: John, Last Name: Doe
Employee ID: 102, First Name: Jane, Last Name: Smith
Limitation:
The query is fixed, so we can only fetch employees with EMPLOYEE_ID < 103. To change the query, we’d need to modify the cursor declaration itself, which isn’t practical in dynamic scenarios.
Step 3: Demonstrate Using a Reference Cursor (Dynamic Query)
Here, we solve the limitation by using a reference cursor that allows us to pass dynamic queries.
PL/SQL Block with a Reference Cursor:
DECLARE
-- Step 1: Declare a reference cursor (dynamic cursor)
TYPE ref_cursor_type IS REF CURSOR;
emp_ref_cursor ref_cursor_type;
-- Step 2: Record variable to hold the result of the fetch
emp_record EMPLOYEES%ROWTYPE;
-- Step 3: Declare a variable to determine the condition dynamically
emp_condition NUMBER := 103;
-- This can be changed to different values dynamically
BEGIN
-- Step 4: Open the reference cursor with dynamic query based on condition
IF emp_condition < 103 THEN
OPEN emp_ref_cursor FOR
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID < emp_condition;
ELSE
OPEN emp_ref_cursor FOR
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES;
END IF;
-- Step 5: Fetch data and process dynamically
LOOP
FETCH emp_ref_cursor INTO emp_record;
EXIT WHEN emp_ref_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('Employee ID: ' || emp_record.EMPLOYEE_ID ||
', First Name: ' || emp_record.FIRST_NAME ||
', Last Name: ' || emp_record.LAST_NAME);
END LOOP;
-- Step 6: Close the reference cursor
CLOSE emp_ref_cursor;
END;
Output (Reference Cursor with Dynamic Condition):
If emp_condition is set to 103:
Employee ID: 101, First Name: John, Last Name: Doe
Employee ID: 102, First Name: Jane, Last Name: Smith
If emp_condition is set to a higher value (e.g., 104),
then all employees are fetched:
Employee ID: 101, First Name: John, Last Name: Doe
Employee ID: 102, First Name: Jane, Last Name: Smith
Employee ID: 103, First Name: Mark, Last Name: Taylor
Employee ID: 104, First Name: Lucy, Last Name: Williams
Explanation:
Normal Cursor: The query is fixed at compile-time and cannot be changed. This means you cannot use it dynamically to fetch data based on runtime conditions.
Reference Cursor: The query is assigned dynamically at runtime. You can use different queries based on conditions, which makes it much more flexible.
When to Use a Reference Cursor:
- When you need to execute different queries dynamically at runtime based on certain conditions.
- When you need to pass a cursor as a parameter to a procedure or function for flexible query execution.
- When you want to write more generic code that can handle different queries without changing the cursor declaration.
Conclusion:
A reference cursor offers more flexibility than a normal cursor by allowing dynamic query execution, solving the problem of static queries that normal cursors present.
