RefCursor without SYS_REFCURSOR declaration | RefCursor with SYS_REFCURSOR declaration

Let's walk through the examples of RefCursor without SYS_REFCURSOR declaration and RefCursor with SYS_REFCURSOR declaration, explaining the differences and their outputs.

1. 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.

1. 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:

1. 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).

1. 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.