Recommend an efficient method of accessing data from 2 cursors - oracle 11g

Which form of fetching the records from the cursor is most efficient.
Could you recommend a more efficient way of looping through the cursor.
I read for loops renders poor performance.

CURSOR get_parent_depts IS
 
    SELECT dept.department_id, dept.name  unit_name, d_type.NAME dept_type  
    FROM  
       departments dept
    INNER JOIN
       dept_types d_type
    ON dept.dept_type_id = d_type.dept_type_id;
   
 
  CURSOR get_child_depts (p_dept_id  VARCHAR2) IS

    SELECT * from (SELECT parent_department_id, child_department_id, (select name from departments o where o.department_id = dept_aff.child_department_id ) child_dept
     FROM  
        dept_aff
     WHERE dept_relationship_type_id = 'MILLWORK')
     CONNECT BY NOCYCLE prior child_department_id  = parent_department_id
     START WITH  parent_department_id = p_dept_id;

BEGIN

FOR parent_rec in get_parent_depts
   LOOP
      FOR child_rec in get_child_depts(parent_rec.name)
      LOOP
        -- Insert parent_rec.name child_rec.child_dept
      END LOOP;
   -- Insert a row for parent to itself;
        -- Insert parent_rec.name parent_rec.name
END LOOP;

   RETURN total_val;

END;
cookiejarAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I would probably do two separate inserts:

insert into parent_depts
select ... from dept_table;

insert into child_depts
select * from child_table;

You should be able to fashion second select to grab the rows from the parent without a parameter.
0
 
cookiejarAuthor Commented:
In the snippet of code above, the RETURN total_val; should not be a part of the code.
0
 
cookiejarAuthor Commented:
So is the 'for loop' the best method to use in this case?  Is anyone out there?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.