Search code examples
oracleplsqlcursor

Build cursor query dynamic in nature?


I have below cursor which i am using in PL/SQL function i.e getEmployeeInfo

CURSOR employeeDepartment IS
        SELECT *
        FROM Employee
        LEFT OUTER JOIN Department ON Department.empID = Employee.ID
        WHERE Employee.STATE = 'Washington'
        ORDER BY Employee.JoiningDate desc;

I need to modify getEmployeeInfo function to take employeeId as input parameter and append to cursor employeeDepartment if employeeId>0.

I am new to PL/SQL programming. Looks like i need to move in direction of REF-CURSOR but i am not able make it up exactly?

So cursor query should be below if Employee.id > 0 otherwise it should be same as above

CURSOR employeeDepartment IS
        SELECT *
        FROM Employee
        LEFT OUTER JOIN Department ON Department.empID = Employee.ID
        WHERE Employee.STATE = 'Washington'
        and Employee.id= 100
        ORDER BY Employee.JoiningDate desc;

UPDATE :- @APC solution worked. By Going with that solution if i user want to pass array of empId's instead of single Id i tried below

First created the array type with create type p_employee_arr as table of number

CURSOR employeeDepartment  (p_employee_arr IN num_array) IS
    SELECT *
    FROM Employee
    LEFT OUTER JOIN Department ON Department.empID = Employee.ID
    WHERE Employee.STATE = 'Washington'
    and (Employee.id in  (SELECT column_value  FROM TABLE( p_employee_arr )) or p_employee_arr is null)
    ORDER BY Employee.JoiningDate desc;

But still it return complete resultset if i provide one element in array


Solution

  • Your question is a bit ambiguous but I think you want to restrict the result set when an Employee ID is passed, otherwise you want all the records for the state .

    CURSOR employeeDepartment  (p_employee_id in number) IS
        SELECT *
        FROM Employee
        LEFT OUTER JOIN Department ON Department.empID = Employee.ID
        WHERE Employee.STATE = 'Washington'
        and ( p_employee_id = 0 or Employee.id= p_employee_id )
        ORDER BY Employee.JoiningDate desc;