nested cursor to insert data into another table

I am using oracle 10g, I have  a parent table Table 1, child table table 2 and a third table Table 3, where I want to insert the data into from Table 1
There is also data from the child table that needs to be populated in Table 3.
In short Table 3 has a flat structure of therealtional data in Table1 and Table 2

Hereis what I have so far. I open a cursor for the parent and then loop through the parent cursor.
I would like to open the child cursor and fetch upto 4 rows, and then based on the child cursor row count,populate the variables v_empid_1, v_credit1, and then use the values to insert into Table3
Not sure if I have the right syntax


CREATE OR REPLACE PROCEDURE db_LOAD(

P_FROMDATE        IN varchar
,P_TODATE         IN varchar  
,P_LEGAL          IN varchar
,P_ASSET          IN varchar
,P_CURRENCY       IN varchar
,P_PROCESSED_BY   IN varchar
,P_ROWS_INSERTED  OUT NUMBER )

 

AS

v_empid_1 varchar;
v_empid_2 varchar;
v_empid_3 varchar;
v_empid_4 varchar;
v_credit_1 number;
v_credit_2 number;
v_credit_3 number;
v_credit_4 number;

CURSOR
LOAD_DATA is
select
  rowid                   ,
  ID                         ,
 CREATED_BY       ,
 CODE        

            FROM
         TABLE1
        WHERE
            PROCESS_FLAG = 'N';

  BEGIN    

  dbms_output.put_line('From Date - '|| P_FROMDATE );
  dbms_output.put_line('To Date -  ' || P_TODATE);
  dbms_output.put_line('Legal Entity - ' || P_LEGAL);
  dbms_output.put_line('Asset Class - ' || P_ASSET);
  dbms_output.put_line('Currency - ' || P_CURRENCY);
  dbms_output.put_line('Processed By - ' || P_PROCESSED_BY);

 FOR LOAD_REC in LOAD_DATA
v_empid_1:= '';
v_empid_2:=  '';
v_empid_3:=  '';
v_empid_4:=  '';
v_credit_1:= 0;
v_credit_2:= 0;
v_credit_3:= 0;
v_credit_4:= 0;

LOOP
   CURSOR
        LOAD_CHILD IS
      select *
            from (select *
                       from TABLE2  
                       order by EMP_ID desc)
            where rownum < 5;
     FOR CHILD_REC IN LOAD_CHILD
     //NEED TO ADD CODE SO THAT WHEN THE CURSOR ROW IS N
    //POPULATE
   IF LOAD_CHILD.ROWCOUNt =1
         V_EMPID_1 = child_rec.emp_id;
         v_credir_1 = chil_rec.credit;
  elseif LOAD_CHILD.rowcount = 2
      V_EMPID_2 = child_rec.emp_id;
         v_credir_2 = chil_rec.credit;

 

 INSERT INTO TABLE3
 (
  LINE_SEQ          ,
  CREATED_BY        ,
  CODE ,
  EMPYID1           ,
  EMPYID2           ,
  EMPYID3           ,
  EMPYID4           ,
  CREDIT_AMT1       ,
  CREDIT_AMT2       ,
  CREDIT_AMT3       ,
  CREDIT_AMT4    
  )
VALUES
(         db.LINENUMBERSEQUENCE.NEXTVAL,
          LOAD_REC.CREATED_BY ,
          LOAD_REC.CODE ,
          v_empid_1,
          v_empid_2,
          v_empid_3,
          v_empid_4,
         v_credit_1,
         v_credit_2,
         v_credit_3,
         v_credit_4        
          );

 

 UPDATE TABLE1

  SET PROCESS_FLAG = 'Y'
  where rowid = LOAD_REC.ROWID;

 

 

Start Free Trial