Let's walk through the examples of RefCursor without SYS_REFCURSOR declaration and RefCursor with SYS_REFCURSOR declaration, explaining the differences and their outputs.
- RefCursor without SYS_REFCURSOR Declaration:
In this case, we'll define a user-defined ref cursor type in the declaration section and use it to execute a query dynamically.
Example:
DECLARE
-- Declare a user-defined ref cursor type
TYPE ref_cursor IS REF CURSOR;
-- Declare a variable of that ref cursor type
emp_cursor ref_cursor;
emp_id NUMBER;
emp_name VARCHAR2(100);
BEGIN
-- Dynamically open the ref cursor with a query
OPEN emp_cursor FOR
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;
-- Fetch and display the results
LOOP
FETCH emp_cursor INTO emp_id, emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_name);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
Explanation:
Ref Cursor Declaration: The ref_cursor is declared as a user-defined type of REF CURSOR.
Dynamic Query: The query is defined dynamically at runtime when the cursor is opened using OPEN emp_cursor FOR.
Execution: The cursor fetches rows, and the results are printed for each employee in the department with department_id = 10.
Expected Output (Example):
Employee ID: 101, Name: John Doe
Employee ID: 102, Name: Jane Smith
Here, the emp_cursor dynamically fetches the data based on the SELECT statement, and the output displays the employee details.
- RefCursor with SYS_REFCURSOR Declaration:
In this case, we use Oracle's system-defined SYS_REFCURSOR type, which eliminates the need to define a custom ref cursor type. The behavior of fetching data and executing queries remains the same.
Example:
DECLARE
-- Declare a variable of type SYS_REFCURSOR
my_cursor SYS_REFCURSOR;
emp_id NUMBER;
emp_name VARCHAR2(100);
BEGIN
-- Dynamically open the SYS_REFCURSOR with a query
OPEN my_cursor FOR
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;
-- Fetch and display the results
LOOP
FETCH my_cursor INTO emp_id, emp_name;
EXIT WHEN my_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_name);
END LOOP;
-- Close the cursor
CLOSE my_cursor;
END;
Explanation:
Ref Cursor Declaration: Here, we use SYS_REFCURSOR, which is predefined by Oracle. There is no need to create a custom ref cursor type.
Dynamic Query: The OPEN my_cursor FOR statement still dynamically assigns a query to the ref cursor at runtime.
Execution: The cursor fetches rows, and the results are printed similarly to the first example.
Expected Output (Example):
Employee ID: 101, Name: John Doe
Employee ID: 102, Name: Jane Smith
The output is the same as the previous example because the cursor is still fetching the same set of data, but the declaration is different.
Key Differences:
- RefCursor without SYS_REFCURSOR Declaration:
You need to define a custom cursor type (TYPE ref_cursor IS REF CURSOR;).
The custom type (ref_cursor) is used to declare the cursor variable (emp_cursor).
- RefCursor with SYS_REFCURSOR Declaration:
You use the predefined SYS_REFCURSOR type, so no custom type declaration is needed.
The cursor variable (my_cursor) is directly declared as SYS_REFCURSOR without any prior type definition.
Summary:
RefCursor without SYS_REFCURSOR: You define a custom cursor type for greater flexibility if needed (for example, if you want to reuse the type in multiple places).
RefCursor with SYS_REFCURSOR: You leverage Oracle’s predefined SYS_REFCURSOR, simplifying the code when you don’t need a custom type.
Top comments (0)