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
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;