PL/SQL. Loop Cycl.

[baileigh]

Why am I getting a back-up from the LOOP cycle? My code is:

SET SERVEROUTPUT ON
DECLARE
 CURSOR get_row_from_offices IS
 SELECT DISTINCT * FROM OFFICES;
  v_res get_row_from_offices%ROWTYPE;
BEGIN
  -- create loop and fetching row to v_res
  OPEN get_row_from_offices;
  LOOP
    EXIT WHEN get_row_from_offices%NOTFOUND;
    FETCH get_row_from_offices INTO v_res;
    DBMS_OUTPUT.enable;
    DBMS_OUTPUT.put_line(v_res.OFFICE||' 
    '||v_res.CITY||' '||v_res.REGION||'
    '||TO_CHAR(v_res.MGR)||' 
    '||TO_CHAR(v_res.TARGET)||' 
    '||TO_CHAR(v_res.SALES));
  END LOOP;
CLOSE get_row_from_offices;
END;

Result:

PL/SQL procedure successfully completed.
1

Kiew Ukraine

12

2000

5000

2

Gdansk Poland

20

10000

50000

3

Gdynya Poland

50

20000

5000

4

Poznan Poland

87

2500

5000

5

Frankfurt-am-Main Germany

100

1000

4321

6

Hamburg Germany

90

3000

5000

7

Koln Germany

100

2800

5300

8

Mumbai India

120

3000

9000

9

Lima Peru

150

2900

7000

9

Lima Peru

150

2900

7000

Why did the "Lima" tape go twice?

[magpie]

The problem with the original code is that you're testing. EXIT WHEN get_row_from_offices%NOTFOUND; before you do FETCH♪ Because a sign NOTFOUND Only FETCH can be installed at the cadet when trying to read the data already completed. If the record was only one, the first iteration is the following: FETCH He reads the line, but he doesn't know it's over and over. NOTFOUND I don't know if you're going to the second iteration, EXIT It doesn't work, FETCH He's trying to read the next recording, no, he's setting up. NOTFOUNDbut in the variable courseor, the previous record remains. You print them. It's the right thing to use the cadets.

LOOP
 FETCH get_row_from_offices INTO v_res;
 EXIT WHEN get_row_from_offices%NOTFOUND;
 ...

In your case, the use of clear cadets is superfluous, I suggest a simple synthaxis:

SET SERVEROUTPUT ON
DECLARE
BEGIN
 DBMS_OUTPUT.enable;
 FOR v_res IN(SELECT DISTINCT * FROM OFFICES) LOOP
   DBMS_OUTPUT.put_line(v_res.OFFICE||' 
     '||v_res.CITY||' '||v_res.REGION||'
     '||TO_CHAR(v_res.MGR)||' 
     '||TO_CHAR(v_res.TARGET)||' 
     '||TO_CHAR(v_res.SALES));
 END LOOP;
END;