Parameterized Cursor FAQS

1. What is a Parameterized Cursor?

A parameterized cursor is an explicit cursor that accepts one or more parameters when it is opened.

These parameters allow you to pass values dynamically into the cursor’s query.

2. Why do we use parameterized cursors?

We use parameterized cursors when:

• The WHERE condition changes dynamically
• The same query structure is reused with different values
• We want flexible and reusable cursor logic
• Avoid writing multiple cursors for similar queries

3. What is the syntax of a parameterized cursor?

DECLARE

   CURSOR emp_cursor (p_dept_id NUMBER) IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept_id;

 

   v_id employees.employee_id%TYPE;

   v_name employees.first_name%TYPE;

 

BEGIN

   OPEN emp_cursor(10);

 

   LOOP

      FETCH emp_cursor INTO v_id, v_name;

      EXIT WHEN emp_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);

   END LOOP;

 

   CLOSE emp_cursor;

END;

4. When are cursor parameters assigned values?

Cursor parameters receive values:

👉 At the time of OPEN statement

Example:

OPEN emp_cursor(20);

5. Can a parameterized cursor accept multiple parameters?

Yes.

Example:

DECLARE

   CURSOR emp_cursor (p_dept NUMBER, p_salary NUMBER) IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept

      AND salary > p_salary;

BEGIN

   OPEN emp_cursor(10, 5000);

END;

6. Can parameterized cursors be used with a Cursor FOR Loop?

Yes, and this is the preferred way.

DECLARE

   CURSOR emp_cursor (p_dept NUMBER) IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept;

BEGIN

   FOR rec IN emp_cursor(30)

   LOOP

      DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);

   END LOOP;

END;

✔ No need to manually OPEN or CLOSE.

7. What is the difference between parameterized cursor and procedure parameter?

Feature	Parameterized Cursor	Procedure Parameter
Scope	Used inside cursor query	Used in entire procedure
Assigned at	OPEN statement	Procedure call
Purpose	Dynamic query filtering	Passing input to procedure

8. Can default values be given to cursor parameters?

Yes.

Example:

CURSOR emp_cursor (p_dept NUMBER DEFAULT 10) IS

   SELECT * FROM employees

   WHERE department_id = p_dept;

If no value is passed, it uses default value.

9. What are common mistakes with parameterized cursors?

• Forgetting to pass parameter during OPEN
• Mismatch of parameter data type
• Confusing cursor parameters with PL/SQL variables
• Using parameter before OPEN

10. What are the advantages of parameterized cursors?

✔ Reusable query logic
✔ Cleaner code
✔ Flexible filtering
✔ Reduces redundancy
✔ Better maintainability

11. Can parameterized cursors improve performance?

Not directly.

They improve:

• Code organization
• Maintainability

For performance improvement with large data, use:

• BULK COLLECT
• FORALL

12. What is the difference between simple explicit cursor and parameterized cursor?

Feature	Simple Cursor	Parameterized Cursor
Dynamic filtering	No	Yes
Parameters	No	Yes
Flexibility	Limited	High
Reusability	Low	High

13. When should you use a parameterized cursor?

Use it when:

• Query condition changes frequently
• Same query used for different inputs
• Writing reusable PL/SQL programs
• Working in procedures with multiple filtering options