PL/SQL CTE with parameters

In Oracle PL/SQL, a Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs provide a way to organize complex queries by breaking them down into smaller, more manageable parts. They are defined using the WITH clause and can include parameters to enhance their flexibility.

CTE with parameters

Here is an example of how you can use CTEs with parameters in Oracle PL/SQL:

CREATE TABLE employees (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    department_id NUMBER
);

INSERT INTO employees VALUES (1, 'John Doe', 101);
INSERT INTO employees VALUES (2, 'Jane Smith', 102);
INSERT INTO employees VALUES (3, 'Bob Johnson', 101);
INSERT INTO employees VALUES (4, 'Alice Williams', 103);

-- Define a parameterized CTE
WITH EmployeeCTE (dept_id_param) AS (
    SELECT 101 AS dept_id_param FROM dual
)
-- Use the CTE in a SELECT statement
SELECT e.employee_id, e.employee_name, e.department_id
FROM employees e
JOIN EmployeeCTE ecte ON e.department_id = ecte.dept_id_param;

In this example, a CTE named EmployeeCTE is defined with a parameter dept_id_param. The parameter is given a specific value (101 in this case) in the SELECT statement that uses the CTE. The CTE is then joined with the employees table based on the department_id, and only the rows that match the specified department_id are returned.

This is a basic example, and you can modify it according to your specific requirements. Parameters in CTEs provide a way to make your queries more dynamic and reusable.

CTE with parameters in cursor

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_salary NUMBER
);

INSERT INTO employees VALUES (1, 'John Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane Smith', 60000);
INSERT INTO employees VALUES (3, 'Bob Johnson', 70000);

-- Define the PL/SQL block
DECLARE
    -- Declare variables to hold parameter values
    p_min_salary NUMBER := 60000;
    
    -- Declare a cursor using a CTE with parameters
    CURSOR employees_cursor (min_salary NUMBER) IS
    WITH filtered_employees AS (
        SELECT emp_id, emp_name, emp_salary
        FROM employees
        WHERE emp_salary >= min_salary
    )
    SELECT *
    FROM filtered_employees;
    
    -- Declare variables to store cursor results
    v_emp_id employees.emp_id%TYPE;
    v_emp_name employees.emp_name%TYPE;
    v_emp_salary employees.emp_salary%TYPE;
BEGIN
    -- Open the cursor and fetch data
    OPEN employees_cursor(p_min_salary);
    
    LOOP
        FETCH employees_cursor INTO v_emp_id, v_emp_name, v_emp_salary;
        EXIT WHEN employees_cursor%NOTFOUND;
        
        -- Process the fetched data (you can perform any desired operations here)
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_emp_name || ', Salary: ' || v_emp_salary);
    END LOOP;
    
    -- Close the cursor
    CLOSE employees_cursor;
END;
/

In this example, a CTE named filtered_employees is used within a cursor definition, and it accepts a parameter min_salary. The cursor is then opened with a specified minimum salary, and the fetched data is processed within the PL/SQL block.

You can modify the value of the parameter in the CTE declaration or use it dynamically based on your specific requirements. This flexibility allows you to reuse the CTE with different parameter values in various parts of your SQL statements.