create table staging
(key_id number(10),inv_id number(10), i_name varchar2(30), req_id number(10), status varchar2(50));
insert into staging values(110,1001,'test1',1,'Not Started');
insert into staging values(111,1002,'test2',2,'Started');
insert into staging values(112,1003,'test3',3,'In Dev');
insert into staging values(113,1003,'test4',4,'Completed');
create table target_tab
(key_id number(10), inv_id number(10), i_name varchar2(30), req_id number(10),status varchar2(50), status_change_date date);
Stored procedure
create or replace NONEDITIONABLE procedure sp_main_target(iv_req_id IN NUMBER)
is
lv_count number(10);
begin
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, status 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,
t.status = s.status
/*
Here I need when Previous status <> current status
then it should print status_change_date = today's date
In the table for inv_id 1003 and 1004 status is getting changed from In Dev to completed
How can I write it in conditions ??
*/
WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id,t.status)
VALUES (s.key_id,s.inv_id,s.i_name,s.req_id,s.status);
commit;
else
dbms_output.put_line('Request id is not available');
end if;
commit;
end sp_main_target;
Calling a procedure
set serveroutput on;
exec sp_main_target(3);
set serveroutput on;
exec sp_main_target(4);