Normal Cursor:

A normal cursor is a static cursor that is defined with a fixed query at compile-time. The query associated with a normal cursor is predefined and cannot be changed during runtime. The cursor is declared in the declaration section of a PL/SQL block, and the query is executed when the cursor is opened in the execution section. Data is fetched row by row, and the cursor is closed after use. Normal cursors are typically used for processing a known, static result set within a single PL/SQL block, making them less flexible when compared to ref cursors.

Ref Cursor:

A ref cursor is a pointer to a result set and allows for dynamic query execution. The ref cursor is declared in the declaration section as a cursor variable, but the actual query is assigned during runtime using the OPEN statement. This dynamic nature allows the query to be changed as needed, providing greater flexibility. Ref cursors can hold references to different result sets and are particularly useful for returning result sets from procedures and functions, or for passing data between PL/SQL blocks and external programs like Java or Python. This flexibility makes ref cursors ideal for scenarios where the query structure is not known in advance.

1. Normal Cursor Declaration:

When you're declaring a normal cursor, you define it in the declaration section of your PL/SQL block (or procedure), and the query associated with it is static and predefined at compile-time. The query is fixed, and you cannot change it at runtime.

Where the Query is Defined: The query is statically defined within the declaration of the cursor itself. The query is part of the cursor declaration.

When to Open: After declaring it, you open the cursor in the execution section and then fetch the rows.

Example of Normal Cursor:

DECLARE
-- Declare the normal cursor (predefined query)
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;

emp_id NUMBER;
emp_name VARCHAR2(100);

BEGIN
-- Execution Section: Open and use the cursor
OPEN emp_cursor;
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 emp_cursor;
END;

Here, the query SELECT employee_id, employee_name FROM employees WHERE department_id = 10; is defined in the declaration section.

1. Ref Cursor Declaration (Dynamic):

In contrast, a Ref Cursor is a pointer to a result set, and the query associated with it is dynamic. You define the Ref Cursor in the declaration section, but the actual query is assigned dynamically at runtime (i.e., in the execution section when you open the cursor).

Where the Query is Defined: The query is defined dynamically in the execution section using OPEN with a dynamic SQL statement (e.g., SELECT ...), which allows you to assign different queries to the Ref Cursor during runtime.

When to Open: You use the OPEN statement in the execution section to associate the Ref Cursor with a specific query (which may change at runtime).

Example of Ref Cursor:

DECLARE
TYPE ref_cursor IS REF CURSOR;
emp_cursor ref_cursor; -- Declare a reference cursor

emp_id NUMBER;
emp_name VARCHAR2(100);

BEGIN
-- Execution Section: Dynamically assign query to the ref cursor
OPEN emp_cursor FOR
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;

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 emp_cursor;

END;

Here, the query SELECT employee_id, employee_name FROM employees WHERE department_id = 10; is assigned dynamically during the execution of the block using OPEN emp_cursor FOR .

Key Points in Your Understanding:

Normal Cursor: The query is declared statically and is fixed. The cursor is used to process this predefined query.

Ref Cursor: The cursor is declared, but the query is assigned dynamically during the execution (at runtime). The Ref Cursor can point to any result set based on the query executed at that time.

In summary:

Normal Cursor: Defined with a fixed query at compile-time (declaration section).

Ref Cursor: Query is defined at runtime when you open the cursor (execution section).