Forum Stats

  • 3,770,291 Users
  • 2,253,091 Discussions
  • 7,875,392 Comments

Discussions

How to take multiple input parameters from procedure in comma separated

Albert Chao
Albert Chao Member Posts: 131 Green Ribbon
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?

 

Answers

This discussion has been closed.