I think, if I understood the question correctly, you want to evaluate the input parameter of the procedure either as a single value or a collection of them. You can't compare a single value in a table retrieved by a select statement against a collection of values. That will never work.
In that case, you might try something like this
Test Case
SQL> create table emp ( emp_id number , name varchar2(100) , manager varchar2(100) , emp_status varchar2(1) ) ;
Table created.
SQL> insert into emp values ( 1 , 'John' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 2 , 'Mike' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 3 , 'Sara' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 4 , 'Dany' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 5 , 'Mila' , 'Anne' , 'B' ) ;
SQL> insert into emp values ( 6 , 'Jean' , 'Anne' , 'B' ) ;
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL> commit ;
Commit complete.
SQL> create table dept ( dtpt_num number , cross_dept varchar2(3) ) ;
Table created.
SQL> insert into dept values ( 100 , 'DEV' ) ;
1 row created.
SQL> insert into dept values ( 200 , 'HRM' ) ;
1 row created.
SQL> col name for a20
SQL> col manager for a20
SQL> select * from emp ;
EMP_ID NAME MANAGER E
---------- -------------------- -------------------- -
1 John Bill A
2 Mike Bill A
3 Sara Bill A
4 Dany Bill A
5 Mila Anne B
6 Jean Anne B
6 rows selected.
SQL> select * from dept ;
DTPT_NUM CRO
---------- ---
100 DEV
200 HRM
Now, let's build a demo function to get a random salary based on the id
SQL> create or replace function get_salary ( pempid in number )
return number deterministic
is
out_sal number;
begin
select round(dbms_random.value(1000,3000),0) into out_sal from dual ;
return out_sal ;
end;
/
Function created.
Let's add a new column to the emp table in order to join both tables by department
SQL> alter table emp add dept_id number ;
Table altered.
SQL> update emp set dept_id = ( case when EMP_STATUS = 'A' then 100 else 200 end ) ;
6 rows updated.
SQL> commit ;
Commit complete.
SQL> select * from emp
2 ;
EMP_ID NAME MANAGER E DEPT_ID
---------- -------------------- -------------------- - ----------
1 John Bill A 100
2 Mike Bill A 100
3 Sara Bill A 100
4 Dany Bill A 100
5 Mila Anne B 200
6 Jean Anne B 200
6 rows selected.
We can now join both tables and use the demo function to get a random salary
SQL> select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num ;
EMP_ID NAME MANAGER CRO DTPT_NUM SALARY
---------- -------------------- -------------------- --- ---------- ----------
1 John Bill DEV 100 2129
2 Mike Bill DEV 100 1215
3 Sara Bill DEV 100 2930
4 Dany Bill DEV 100 1347
5 Mila Anne HRM 200 1664
6 Jean Anne HRM 200 1770
6 rows selected.
Now, let's build a procedure that will take in consideration when the input parameter might be either a single employee or a list of them split by comma and store the results in a temporary table ( just for demo purposes, you can use a sys_refcursor object as well )
SQL> create table tmp_results as
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where 1 = 2 ;
Table created.
create or replace procedure emp_test ( pemployee in varchar2 )
is
v_counter_records pls_integer;
v_num_of_values pls_integer;
curr_val varchar2(10);
begin
v_counter_records := regexp_count ( pemployee , ',' , 1 , 'i' );
if v_counter_records = 0
then
insert into tmp_results
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where e.emp_id = to_number(pemployee) ;
else
v_num_of_values := v_counter_records + 1;
for rec in 1 .. v_num_of_values
loop
curr_val := regexp_substr( pemployee, '[^,]+', 1 , rec );
insert into tmp_results
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where e.emp_id = to_number(curr_val);
end loop;
end if;
end;
/
Procedure created.
SQL> exec emp_test( '1,2,3' );
PL/SQL procedure successfully completed.
SQL> select * from tmp_results ;
EMP_ID NAME MANAGER CRO DTPT_NUM SALARY
---------- -------------------- -------------------- --- ---------- ----------
1 John Bill DEV 100 2386
2 Mike Bill DEV 100 1531
3 Sara Bill DEV 100 2202
Considerations
- My example is just to show you how to split a list of parameters used as a single one in the input of the procedure.
- You can also change the query to use
IN instead of = to evaluate the condition when there is more than a single value.
- I did not include any special logic for the function, because for the purposes of the example it was not needed.
- You can use your
WITH statement as well, to get a list of employees to be compared later on, but in your question was not clear when you are using that CTE construction.
