End Loop;
END LOOP;
END;
--------------------O U T P U T-----------------
=============================
Intro. to Info. Systems
=============================
Amanda
Amanda
Amanda
Brian
Brian
Brian
Daniel
Daniel
Daniel
Michael
Michael
Michael
Ruben
Ruben
Ruben
Sarah
Sarah
Sarah
=============================
Systems Analysis
=============================
Amanda
Amanda
Amanda
Brian
Brian
Brian
Daniel
Daniel
Daniel
Michael
Michael
Michael
Ruben
Ruben
Ruben
Sarah
Sarah
Sarah
=============================
Database Management
=============================
Amanda
Amanda
Brian
Brian
Daniel
Daniel
Michael
Michael
Ruben
Ruben
Sarah
Sarah
THE GOOD OUTPUT SHOULD LOOK LIKE THIS:
=============================
Intro. to Info. Systems
=============================
Amanda
Ruben
Michael
=============================
Systems Analysis
=============================
Daniel
Amanda
Michael
=============================
Database Management
=============================
Daniel
Ruben
Single cursor is a good idea.
You don't need current_item_id variable
just use ITEM_ROW.c_cID
How do you reference %ROWCOUNT in this one?
Otherwise good code
Code:
BEGIN
FOR ITEM_ROW IN
(SELECT
c.call_id c_callID, c.course_name c_cname, c.course_id c_cID,
cs.c_sec_id csec_secID, cs.course_id csec_cID, cs.term_id csec_termID
FROM
course c,
course_section cs
WHERE
c.course_id = cs.course_id and
cs.term_id = 6)
LOOP
DBMS_OUTPUT.PUT_LINE('=============================');
DBMS_OUTPUT.PUT_LINE(ITEM_ROW.c_cname);
DBMS_OUTPUT.PUT_LINE('=============================');
FOR INVENTORY_ROW IN
(SELECT distinct
s.s_first student_f, s.s_last student_l, s.s_id student_id,
e.s_id stu_en_id, e.c_sec_id course_sec_ID,
cs.c_sec_id c_section_ID, cs.term_id c_sec_termID, cs.course_id courseID
FROM
student s, enrollment e, course_section cs
WHERE
cs.course_id = ITEM_ROW.c_cID and
cs.term_id = 6 and
cs.c_sec_id = e.c_sec_id and
s.s_id = e.s_id)
LOOP
DBMS_OUTPUT.PUT_LINE (inventory_row.student_f);
END LOOP;
END LOOP;
END;
I don't see splitting the query away from the processing of the result set as a plus, because I keep having to page up/page down to understand what is happening, and i prefer to see both the query and the in-loop processing together.
Dave, I read the posting. And I read about this in Tom Kyte's Effective Oracle by Design.
The implicit cursors are "select into's". The select statement in the "for rec in (select..." are not implicit cursors. The select statements in the loop definition are TKyte's coding preference. He likes the select statement to be right there in the definition of the loop. I like it too in some situations for example I use this when I write scripts (anonymous blocks) where I know the select statements are not going to be reused in any other place. If I am to reuse the sql code I will either declare it as an explicit cursor or wrap it into the function that returns an array with all data fetched into it and then I can reference all kind of array methods (COUNT, PRIOR, NEXT, EXISTS, LAST).
In other words, your change in code is not an example of implicit vs. explicit cursors, it's where the select statement goes.
By the way, how do you reference %ROWCOUNT when you put a select statement directly in the for loop definition?
d
For the record Effective Oracle Design, page 607, subtitle 'Use Implicit Cursors for Result Sets with a Limited Number of Rows' gives the example of an implicit cursor as...
Code:
For x in ( select ... from ... where ...)
Loop
Process...
End Loop;
And the text clearly states that...
'the implicit cursor performs at least as well as, and in most cases better than, the explicit cursor (with similar performance characteristics to the SELECT INTO example in the previous section'
Although it doesn't appear to say how you get the %ROWCOUNT ;-)
Personally for a few rows I don't care much between explicit and implicit. The problem I have is that this 'nested cursor' approach does not scale to large datasets where performance generally comes from set based processing rather than single-row or multiple row cursor lookups.