bobmagan wrote:Combining the cursors into one sounds like a good idea. Using nested cursors may be eaiser to understand and to code, but it tends to be much slower.
I have a very large procedures that executes many cursors in it. Basically, there is 1 main cursor that gets the employee and within the FOR loop executes lots of others. Most of these other cursors return a couple records per employee, but they are beinge executed 20-30,000 times withing the main cursor. Thought about executing the cursor independenty for all employees and storingt he data in PL/SQL tables, btu not sure if that will help the performance of the procedure any. Does anyone have suggestions?
I would write:
loop fetch empno into v_empno; select data from somewhere where empno = v_empno; ... end loop;
Just replace each little "one shot" cursor by a subquery that gets all the data and the crucial column that you can join on.
with sub_query as ( select empno, data from somewhere ) select emp.*, sub_query.data from emp, sub_query where emp.empno = sub_query.empno
above can be optimized as below
FOR x in get_students LOOP
l_fk_stu_base := x.fk_stu_base;
FOR y in get_pccs LOOP
And that is exactly the same as your code is doing:
I don't see any sample code in your example. Just 'NULL'
where, for each row returned by dc, you run ec.
DECLARE this_deptno scott.dept.deptno%TYPE; CURSOR dc IS SELECT deptno FROM scott.dept WHERE dname < 'T' ORDER BY dname; CURSOR ec IS SELECT ename FROM scott.emp WHERE deptno = this_deptno AND sal < 3000 ORDER BY ename; BEGIN FOR dr IN dc LOOP this_deptno := dr.deptno; FOR er IN ec LOOP dbms_output.put_line (er.ename); END LOOP; END LOOP; END; /
Note that subquery dc in the second example is exactly like cursor dc in the first example, except that, since it's a sub-query, it doesn't have an ORDER BY clause.
DECLARE CURSOR dec IS WITH dc AS ( SELECT deptno FROM scott.dept WHERE dname < 'T' ) SELECT ec.ename FROM scott.emp ec JOIN dc ON ec.deptno = dc.deptno WHERE ec.sal < 3000 ORDER BY dc.deptno , ec.ename; BEGIN FOR der IN dec LOOP dbms_output.put_line (der.ename); END LOOP; END; /
 anyone that programmed in Cobol or wrote software in the pre commercial database days where data was on magnetic tape, will be very familiar with it.http://en.wikipedia.org/wiki/Control_break
 This is made worse by the standard education developers receive today, where something as simple as a control break algorithm is beyond their ability, or understanding the basic concept of modularisation
Marwim wrote:That's part of what I mean.
if you can read the data in a single cursor as Frank has suggested, you can process it using "control break". Maybe this is what Stew called the "COBOL way".