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.

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

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