Skip to content

Cursor with Parameters ⚙️

Prerequisites: PL/SQL Cursors

Mentor's Note: Imagine writing a separate query for every single department in your company. That would be hundreds of cursors! Instead, we write one cursor and pass the "Department ID" into it as a Parameter. 💡

🌟 The Scenario: The Custom Filter 🔍

Imagine a high-pressure kitchen. - You have a cursor that "Gets Orders". - Instead of a generic list, the chef can say: "Get orders for Table 5" or "Get orders for Table 10". - The query is the same, but the Target changes.

💻 1. The Basic Syntax

You define the parameter in the CURSOR declaration and pass the value when you OPEN it.

DECLARE
   -- 1. Define Parameter (name type)
   CURSOR c_emp (p_dept_id NUMBER) IS 
      SELECT first_name FROM employees 
      WHERE department_id = p_dept_id;

   v_name employees.first_name%TYPE;
BEGIN
   -- 2. Pass the value 10
   OPEN c_emp(10);
   LOOP
      FETCH c_emp INTO v_name;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Admin Staff: ' || v_name);
   END LOOP;
   CLOSE c_emp;

   -- 3. Pass a different value 20
   OPEN c_emp(20);
   -- ... process Marketing staff ...
   CLOSE c_emp;
END;

💻 2. Using with Cursor FOR LOOP

This is even cleaner!

DECLARE
   CURSOR c_dept_emp (p_id NUMBER) IS 
      SELECT first_name FROM employees WHERE department_id = p_id;
BEGIN
   FOR r IN c_dept_emp(30) LOOP -- 🚀 Just pass parameter here
      DBMS_OUTPUT.PUT_LINE('IT Staff: ' || r.first_name);
   END LOOP;
END;

🛡️ 3. Benefits (Architect's Note)

  1. Reusability: One cursor handles many scenarios.
  2. Security: Parameters help prevent SQL Injection because the values are treated as data, not part of the query text.
  3. Performance: Oracle can reuse the same Execution Plan for the cursor, even if the parameter value changes. This is much faster for the server! ⚡

📈 Learning Path