Oracle Parameterized Cursor Faqs With Examples

1. What is a Parameterized Cursor?

A parameterized cursor is a cursor declaration that contains one or more parameters.

Syntax:

CURSOR cursor_name (parameter_name datatype) IS
    SELECT_statement;

Example:

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    NULL;
END;
/

Here, p_dept_id is the cursor parameter.

2. Why do we use Parameterized Cursors?

The main purpose is reusability. Instead of creating separate cursors for each department, you can define one cursor and use it with different values.

OPEN emp_cursor(10);
OPEN emp_cursor(20);
OPEN emp_cursor(30);

3. Basic Syntax

CURSOR cursor_name (
    parameter1 datatype,
    parameter2 datatype
) IS
    SELECT ...
    FROM ...
    WHERE ...;

Example:

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary;
BEGIN
    NULL;
END;
/

4. How do we OPEN a Parameterized Cursor?

Pass the parameter values when opening the cursor.

OPEN emp_cursor(10, 50000);

Here, p_dept_id = 10 and p_min_salary = 50000.

5. Complete Example with OPEN, FETCH and CLOSE

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;


v_id     employees.employee_id%TYPE;
v_name   employees.first_name%TYPE;
v_salary employees.salary%TYPE;


BEGIN
OPEN emp_cursor(10);


LOOP
    FETCH emp_cursor INTO v_id, v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_id || ' - ' ||
        v_name || ' - ' ||
        v_salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

Flow:

DECLARE cursor
      ↓
OPEN cursor with parameter
      ↓
FETCH rows
      ↓
PROCESS rows
      ↓
%NOTFOUND
      ↓
CLOSE cursor

6. Can we use the same cursor with different values?

Yes. This is one of the biggest advantages.

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    OPEN emp_cursor(10);
    CLOSE emp_cursor;


OPEN emp_cursor(20);
CLOSE emp_cursor;

OPEN emp_cursor(30);
CLOSE emp_cursor;


END;
/

7. Can a Parameterized Cursor have multiple parameters?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary;
BEGIN
    OPEN emp_cursor(10, 50000);
    CLOSE emp_cursor;
END;
/

8. Can parameters have default values?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER DEFAULT 10
    ) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    OPEN emp_cursor;
    CLOSE emp_cursor;


OPEN emp_cursor(20);
CLOSE emp_cursor;


END;
/

9. Can we use a VARCHAR2 parameter?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_name VARCHAR2
    ) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE first_name = p_name;
BEGIN
    OPEN emp_cursor('Ravi');
    CLOSE emp_cursor;
END;
/

10. Can we use DATE parameters?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_hire_date DATE
    ) IS
        SELECT employee_id, first_name, hire_date
        FROM employees
        WHERE hire_date >= p_hire_date;
BEGIN
    OPEN emp_cursor(DATE '2025-01-01');
    CLOSE emp_cursor;
END;
/

11. Can we use parameters in the WHERE clause?

Yes. This is the most common use.

CURSOR emp_cursor(p_dept_id NUMBER) IS
    SELECT employee_id, first_name
    FROM employees
    WHERE department_id = p_dept_id;

12. Can parameters be used in other parts of the query?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_min_salary NUMBER
    ) IS
        SELECT employee_id,
               first_name,
               salary,
               salary * 12 AS annual_salary
        FROM employees
        WHERE salary >= p_min_salary
        ORDER BY salary DESC;
BEGIN
    OPEN emp_cursor(50000);
    CLOSE emp_cursor;
END;
/

13. Can we use a Parameterized Cursor with a Cursor FOR LOOP?

Yes. This is a very common and convenient approach.

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' ||
            emp.first_name || ' - ' ||
            emp.salary
        );
    END LOOP;
END;
/

No explicit OPEN, FETCH, or CLOSE is required.

14. Can we use different parameter values in different FOR LOOPS?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE('Dept 10: ' || emp.first_name);
    END LOOP;


FOR emp IN emp_cursor(20)
LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 20: ' || emp.first_name);
END LOOP;


END;
/

15. Normal Cursor vs Parameterized Cursor

Normal Cursor Parameterized Cursor
Fixed query values Values can be supplied
Less reusable More reusable
No cursor parameters Has cursor parameters
OPEN emp_cursor OPEN emp_cursor(10)

16. Are cursor parameters variables?

They behave as input values to the cursor query.

17. Are cursor parameters IN, OUT, or IN OUT?

Cursor parameters are input parameters.

18. Can we change a cursor parameter after opening the cursor?

No. If you need a different value, close the cursor and open it again.

19. Can we use variables as parameter values?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;


v_dept_id NUMBER := 10;


BEGIN
OPEN emp_cursor(v_dept_id);
CLOSE emp_cursor;
END;
/

20. Can we use expressions as parameter values?

Yes.

OPEN emp_cursor(5 + 5);

21. Can we use a parameter in a Cursor FOR LOOP?

Yes.

DECLARE
    CURSOR emp_cursor(p_min_salary NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE salary >= p_min_salary;
BEGIN
    FOR emp IN emp_cursor(60000)
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name || ' - ' || emp.salary
        );
    END LOOP;
END;
/

22. Can we use FOR UPDATE with a Parameterized Cursor?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id
        FOR UPDATE;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        IF emp.salary < 50000 THEN
            UPDATE employees
            SET salary = salary + 5000
            WHERE CURRENT OF emp_cursor;
        END IF;
    END LOOP;


COMMIT;


END;
/

23. Can we use WHERE CURRENT OF?

Yes, when the cursor is appropriately declared with FOR UPDATE.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id
        FOR UPDATE;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        UPDATE employees
        SET salary = salary + 1000
        WHERE CURRENT OF emp_cursor;
    END LOOP;


COMMIT;


END;
/

24. Can a Parameterized Cursor return zero rows?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(9999)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;


DBMS_OUTPUT.PUT_LINE('Processing complete');


END;
/

25. Can a Parameterized Cursor return multiple rows?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

26. Can we use a parameterized cursor inside another loop?

Yes. This is useful for parent-child processing.

BEGIN
    FOR dept IN (
        SELECT department_id, department_name
        FROM departments
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Department: ' || dept.department_name
        );


    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = dept.department_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            '  Employee: ' || emp.first_name
        );
    END LOOP;
END LOOP;


END;
/

27. Complete Example with Multiple Parameters

DECLARE
    CURSOR emp_cursor(
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id,
               first_name,
               salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary
        ORDER BY salary DESC;
BEGIN
    FOR emp IN emp_cursor(10, 50000)
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp.employee_id ||
            ', Name: ' || emp.first_name ||
            ', Salary: ' || emp.salary
        );
    END LOOP;
END;
/

28. Parameterized Cursor with Default Parameter

DECLARE
    CURSOR emp_cursor(
        p_dept_id NUMBER DEFAULT 10
    ) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

29. Parameterized Cursor with Record

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor(10);


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_emp.employee_id || ' - ' ||
        v_emp.first_name || ' - ' ||
        v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

30. What is the scope of a cursor parameter?

The parameter can be referenced within the cursor's query.

31. What happens if we don't supply a required parameter?

If a parameter has no default value, you must supply it when opening the cursor or using it in a Cursor FOR LOOP.

32. What is the main advantage of Parameterized Cursors?

Reusability. One cursor can be used for different conditions.

33. What is the difference between a Parameterized Cursor and a Dynamic SQL statement?

A parameterized cursor changes the values used by a predefined query. Dynamic SQL is used when the SQL statement itself needs to be constructed or changed at runtime.

34. What are the advantages of Parameterized Cursors?

  1. Reusable cursor definition
  2. Accept different input values
  3. Avoid duplicate cursor declarations
  4. Useful for filtering data dynamically
  5. Easy to combine with Cursor FOR LOOP
  6. Can have multiple parameters
  7. Parameters can have default values
  8. Can be used with FOR UPDATE

35. What are the disadvantages?

Parameterized cursors are still row-oriented cursor processing. If the entire operation can be performed with a single SQL statement, a set-based approach may be simpler and more efficient.

36. Parameterized Cursor vs Normal Explicit Cursor

Feature Normal Cursor Parameterized Cursor
Parameters No Yes
Reusable with different values Limited Yes
Example OPEN emp_cursor OPEN emp_cursor(10)
Query Fixed values/conditions Values supplied at runtime

37. Parameterized Cursor vs Cursor FOR LOOP

They are not alternatives in the same sense. A parameterized cursor defines the reusable query, while a Cursor FOR LOOP controls how the rows are processed. They can be used together.

38. Frequently Asked Questions

Question Answer
What is a Parameterized Cursor? A cursor that accepts input parameters when it is opened or invoked.
Why use it? For reusability with different values.
How do you declare one? CURSOR emp_cursor(p_dept_id NUMBER) IS SELECT ...
How do you open one? OPEN emp_cursor(10);
Can it have multiple parameters? Yes.
Can parameters have default values? Yes.
Can we use variables as parameter values? Yes.
Can we use a Parameterized Cursor with a Cursor FOR LOOP? Yes.
Can we use FOR UPDATE? Yes.
Can we use WHERE CURRENT OF? Yes, with an appropriate FOR UPDATE cursor.

Important Exam Points

  1. Parameterized Cursor = Explicit cursor with parameters.
  2. Parameters are supplied when the cursor is opened or invoked.
  3. Basic declaration: CURSOR c(p_value NUMBER) IS SELECT ...;
  4. Open syntax: OPEN c(value);
  5. Parameters are input values to the cursor query.
  6. A cursor can have multiple parameters.
  7. Parameters can have default values.
  8. Parameterized cursors are useful for reusability.
  9. They work with Cursor FOR LOOP.
  10. They can be used with FOR UPDATE.
  11. WHERE CURRENT OF can be used with an appropriate FOR UPDATE cursor.
  12. Different cursor instances can use different parameter values.
  13. A required parameter must be supplied unless a default value is defined.
  14. If you need a different value for an already-open cursor, close it and open it again.
  15. Use set-based SQL instead of row-by-row cursor processing when the task can be expressed cleanly as one SQL statement.

Easy Memory Trick

PARAMETERIZED CURSOR
        ↓
     One Cursor
        ↓
  +-----+-----+
  |     |     |
  10    20    30
  |     |     |
Dept10 Dept20 Dept30

Parameterized Cursor = One reusable cursor + different input values.
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)