We help IT Professionals succeed at work.

nested cursor to insert data into another table

countrymeister
on
17,316 Views
Last Modified: 2013-12-19
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;

 

 

Comment
Watch Question

I have put the CURSOR declaration for the CHILD in the DECLARE section and corrected the syntax at some other places. Not sure though what you are trying to achieve using ROWNUM.

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';

CURSOR
        LOAD_CHILD IS
      select *
            from (select *
                       from TABLE2  
                       order by EMP_ID desc)
            where rownum < 5;

  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
   
     FOR CHILD_REC IN LOAD_CHILD
     //NEED TO ADD CODE SO THAT WHEN THE CURSOR ROW IS N
    //POPULATE
   IF LOAD_CHILD.ROWCOUNt =1 then
         V_EMPID_1 = child_rec.emp_id;
         v_credir_1 = chil_rec.credit;
  elsif LOAD_CHILD.rowcount = 2
      V_EMPID_2 = child_rec.emp_id;
         v_credir_2 = chil_rec.credit;
       ...
end  if;
 

 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;
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi ! jinesh_kamdar:

There is one thing i missed in the child cursor I need to get the rows for the parent_id in the first cursor
WHERE THE PARENT ID = FK_ID IN THE CHILD. Can this be done by declaring the cursors beforehand,
Something along these lines.

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  where LOAD_REC.ERP_ID = TABLE2.FK_ERP_ID
                       order by EMP_ID desc)
            where rownum < 5;
     FOR CHILD_REC IN LOAD_CHILD
      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;

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi ! GGuzdziol:

That's an amazing query, which I would like to learn.
here is my scenario i have a parent TABLE, child TABLE, and destination table.
the child table could have one or more rows for the parent.
The business requirement is to get all parent rows that have  aproces flag ='N' and only 4 rows from the child table as the destination has only four emp_id and four credit_amt columns.
Also I need to know how many rows were inserted and passed back to the P_ROWS_inserted

Parent table
parent_id,
created_by,
code

Child Table
child_id
emp_id
emp_credit
fk_parent_id

Destination table
dest_id
created_by
code
emp1_id
emp2_id
emp3_id
emp4_id
credit1_amt
credit2_amt
credit3_amt
credit4_amt

Author

Commented:
Hi ! GGuzdziol:

Also another question, what would happen if the child table had less than four rows.
The data model supports the optional relationship from parent to child.
There could be a scenario where the parent has no child rows.

Thanks
CM

Author

Commented:
Jitesh and GGuzdziol:

thank you both for your help. yesterday I did some reading on pivit tables and was able to solve my problem with the example provided by GGuzdziol.

Jitest also your suggestion works and thanka sgain, but GGuzdziol was pretty cool.
>> GGuzdziol was pretty cool
He always is :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.