Home » SQL & PL/SQL » SQL & PL/SQL » question based on Oracle PL/SQL Programming book code
question based on Oracle PL/SQL Programming book code [message #389866] Tue, 03 March 2009 18:26 Go to next message
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 Smile





==================

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 #389867 is a reply to message #389866] Tue, 03 March 2009 18:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The while condition doesn't evaluate "if there are more rows TO fetch", it does evaluate "if there WERE rows fetched".

So it would evaluate to false, and nothing in the loop would get executed.

You can only evaluate %FOUND or %NOT_FOUND *AFTER* a fetch.

[Updated on: Tue, 03 March 2009 18:46]

Report message to a moderator

Re: question based on Oracle PL/SQL Programming book code [message #389869 is a reply to message #389867] Tue, 03 March 2009 19:24 Go to previous message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
thank you Smile

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

Previous Topic: How to read dynamic flat file in PL/SQL
Next Topic: SQL to sort a column containing numeric/characters/slphanumeric characters
Goto Forum:
  


Current Time: Wed Dec 07 06:51:55 CST 2016

Total time taken to generate the page: 0.21826 seconds