DEV Community

Pranav Bakare
Pranav Bakare

Posted on

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)