--create table 
    create table emp_info (info1 number,info2 varchar2(20),info3 varchar2(20),info4 varchar2(20));
  
    --getting emp details 
    create or replace procedure get_emp_info(p_deptno number,p_user varchar2,p_result out sys_refcursor) is    
    begin
    delete from emp_info where info4=p_user;
    commit;
    for i in( select deptno,dname,loc from dept where deptno=p_deptno) loop
      insert into emp_info values(i.deptno,i.dname,i.loc,p_user);
     
      for j in (select empno,ename,job from emp where deptno=i.deptno)loop
        insert into emp_info values(j.empno,j.ename,j.job,p_user);
      end loop;
    end loop;
    commit;
    open p_result for select * from emp_info where info4=p_user;    
    end get_emp_info;
    
    --testing 
    variable p_result refcursor;
    SET autoprint ON ;
    BEGIN
      get_emp_info(20,'xyz',:p_result);
      --get_emp_info(20,'yyy',:p_result);
    end;
    

MERGE INTO EMP_INFO EI
      USING (SELECT <required column list> FROM EMP E, DEPT D WHERE < JOIN CONDITIONS >)
         ON (EI.INFO4 = <required value and conditions>)
      WHEN MATCHED THEN 
         DELETE WHERE <conditional operation as required> 
         INSERT INTO EMP_INFO VALUES <required values> --these values are fetched from dept table
         INSERT INTO EMP_INFO VALUES <required values> --these values are fetched from emp table

open p_result for select * from emp_info where info4=p_user;