question based on Oracle PL/SQL Programming book code [message #389866] |
Tue, 03 March 2009 18:26 |
happyjoshua777
Messages: 33 Registered: February 2009 Location: United States
|
Member |
|
|
I encountered the following problem in the code bellow.
It says in the book the following with regards to this code:
'...Note that the FETCH statement appears twice: once before the loop and once after the loop processing. This is necessary so that the loop condition c_historyStudents%FOUND will be evaluated for each loop iteration'.
the way i understood it is that before the WHILE loop FETCH creates the very 1st row IF there are more rows to FETCH then WHILE loop is going to execute. Right before the WHILE loop ends a consecutive row will be created.
What is wrong with omitting the very first FETCH which is above this WHILE loop?
Lets say it was omitted. Then WHILE condition is going to evaluate if there are more rows to FETCH. If there is at least one row, then WHILE loop will execute and at the end of the loop FETCH will retrieve the very 1st row???
thank you SO much
==================
DECLARE
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
-- Declare a record to hold the fetched information.
v_StudentData c_HistoryStudents%ROWTYPE;
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;
-- Retrieve the first row, to set up for the WHILE loop
FETCH c_HistoryStudents INTO v_StudentData;
-- Continue looping while there are more rows to fetch
WHILE c_HistoryStudents%FOUND LOOP
-- Process the fetched rows, in this case sign up each
-- student for History 301 by inserting them into the
-- registered_students table. Record the first and last
-- names in temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentData.ID, 'HIS', 301);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentData.ID,
v_StudentData.first_name || ' ' || v_StudentData.last_name);
-- Retrieve the next row. The %FOUND condition will be checked
-- before the loop continues again.
FETCH c_HistoryStudents INTO v_StudentData;
END LOOP;
-- Free resources used by the cursor
CLOSE c_HistoryStudents;
END;
/
|
|
|
|
Re: question based on Oracle PL/SQL Programming book code [message #389869 is a reply to message #389867] |
Tue, 03 March 2009 19:24 |
happyjoshua777
Messages: 33 Registered: February 2009 Location: United States
|
Member |
|
|
thank you
what does the text mean by, 'This is necessary so that the loop condition c_historyStudents%FOUND will be evaluated for each loop iteration'?
I do not think it means that the loop condition will evaluate to FALSE and therefore the loop will never even execute...
The way you explained it makes sense 100%. When i read that line (quote above) I got very confused.
thank you again
[Updated on: Tue, 03 March 2009 19:26] Report message to a moderator
|
|
|