create table staging
(key_id number(10),inv_id number(10), i_name varchar2(30), req_id number(10));
insert into staging values(110,1001,'test1',1);
insert into staging values(111,1002,'test2',2);
create table target_tab
(key_id number(10), inv_id number(10), i_name varchar2(30), req_id number(10));
Stored Procedure :
create or replace NONEDITIONABLE procedure sp_main_target(iv_req_id IN VARCHAR)
is
lv_count number(10);
begin
for r in(--should I select request id from staging table)loop
select count(1) into lv_count from staging where req_id = iv_req_id;
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
USING (SELECT key_id, inv_id, i_name, req_id FROM staging
WHERE req_id = iv_req_id) S
ON (t.inv_id = S.inv_id)
WHEN MATCHED THEN UPDATE SET
t.key_id = s.key_id,
t.i_name = s.i_name,
t.req_id = s.req_id
WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
commit;
end sp_main_target;
I want to give multiple input parameters for iv_request_id and then have to process each request-id individually. How to achieve this? How will I implement multiple input parameters in comma separated?