This content has been marked as final. Show 36 replies
I'm kind of done with this question, if only because two people kindly persisted long enough with me to make me realise what the underlying issue is. I can't help but just add, however:
So it although it 'implies' that a FETCH will retrieve multiple rows (and yes in this case the documentation isn't completely clear) the implication is that there is a loop needed for queries that return multiple rows.
And that's the issue I originally asked about, pretty much as I asked it. First, the documentation isn't completely clear. And secondly, it's IMPLIED that a loop is needed, but not explicitly stated.
Any language structure could "invites a logical problem" if the code hasn't been constructed properly by someone who knows how to use it
Of course. But in this case, a statement intended to fetch only one row was fetching two, and no-one noticed until I looked at it, because the syntax chosen by that particular developer 'hid' the problem. As I say, I can now understand why that logic bomb remained hidden, and also why by contrast if he'd used 'select into', it would have become obvious. I certainly see it as the developer's error -but one allowed to happen because of PL/SQL's choice of how to deal with FETCH (just as php, for example, chooses to do type-juggling when == is used, resulting sometimes in 'silent errors' that aren't easy to spot: it's the developer's problem, but one the language doesn't help squash).
Declare v_var number and then not initialising the variable is indeed (I think) a logic bomb waiting to happen. Plenty of languages choose to initialise variables for you (Java initialises a number to 0, for example); plenty choose to throw an error if you don't initialise it yourself (C, to name one). PL/SQL chooses to 'initialise' it to null (as does Python): it's a choice it could have made differently. Supposing that PL/SQL might do things differently isn't nonsense, I think. It is at least a valid way of exploring why PL/SQL does things the way it does.
And secondly, it's IMPLIED that a loop is needed, but not explicitly stated.From the PL/SQL Language Reference:
"The FETCH statement retrieves the current row of the result set, stores the column
values of that row into the variables or record, and advances the cursor to the next row.
Typically, you use the FETCH statement inside a LOOP statement,
which you exit when the FETCH statement runs out of rows."
Note the use of the word "typically", i.e. it's not a requirement nor should it be.
We disagree, and I already wrote about the use of the word typically in that quote (see above, basically).
I remain unpersuaded that fetch without a loop/bulk collect is right, since a loop of 1 would still work fine, but a 'forced' loop would deal with >1 iterations, too, so a loop would always be meaningful. But that's me, and my way of thinking, and I don't expect to be able to convert you or anyone else to thinking like that.
So can we just leave it there now, please?
I'm outta here, anyway.
I agree with SomeoneElse there is an answer, and Oracle is working perfectly well. No syntax error, no bug.
As already explained to you, the FETCH statement alone is designed to fetch a row or a number of rows depending on how it's used. The statement itself is independent of the opening and closing of the cursor. The way cursors work (and all SQL queries are cursors) is that the cursor is opened and the query executed against the database... but until a FETCH is performed (explicitly or implicitly), Oracle has no idea whatsoever if the cursor is going to return any rows at all, and even when fetching, it doesn't know how many rows there will be in total until all the rows have actually been fetched. It doesn't know this at run-time, let alone compile time, so it cannot possibly be an error or bug becuase a FETCH isn't used inside a LOOP. Using a FETCH inside a loop is a choice made by the developer based on the design of the database and the intended requirements. Sometimes the requirement is to fetch multiple rows, sometimes the requirement is to fetch one row. If it's just one row, there are several ways the row can be fetched, including using the explicit OPEN.. FETCH... CLOSE statements. That's the way the language is designed. That's the way cursors work.
You cannot say it's wrong when it's doing exactly what it's designed to do.
It may be that you don't want a loop if, for example you just wanted to pick out 3 records at random, such as a yearly employee lottery (if only I worked for such a company! :D )
A perfectly valid reason for doing individual fetches.
SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_lottery is 3 select empno, ename 4 from emp 5 order by dbms_random.random(); 6 v_lottery cur_lottery%rowtype; 7 v_prize_fund number := 10000; 8 begin 9 -- pick 3 lucky winners for our lottery at random from our list of entrants... 10 open cur_lottery; 11 dbms_output.put_line('So far we have selected '||cur_lottery%rowcount||' winners'); 12 fetch cur_lottery into v_lottery; 13 dbms_output.put_line('Winner '||cur_lottery%rowcount||' is ('||v_lottery.empno||') '||v_lottery.ename||' and receives $'||(v_prize_fund*.7)||' prize!'); 14 fetch cur_lottery into v_lottery; 15 dbms_output.put_line('Winner '||cur_lottery%rowcount||' is ('||v_lottery.empno||') '||v_lottery.ename||' and receives $'||(v_prize_fund*.2)||' prize!'); 16 fetch cur_lottery into v_lottery; 17 dbms_output.put_line('Winner '||cur_lottery%rowcount||' is ('||v_lottery.empno||') '||v_lottery.ename||' and receives $'||(v_prize_fund*.1)||' prize!'); 18 close cur_lottery; 19* end; SQL> / So far we have selected 0 winners Winner 1 is (7844) TURNER and receives $7000 prize! Winner 2 is (7566) JONES and receives $2000 prize! Winner 3 is (7782) CLARK and receives $1000 prize! PL/SQL procedure successfully completed.
Yes, you could probably do it in a loop such as...
but it works out as more lines of code.
SQL> ed Wrote file afiedt.buf 1 declare 2 cursor cur_lottery is 3 select empno, ename, decode(rownum,1,.7,2,.2,3,.1) as prize_share 4 from ( 5 select empno, ename 6 from emp 7 order by dbms_random.random() 8 ) 9 where rownum <= 3; 10 v_lottery cur_lottery%rowtype; 11 v_prize_fund number := 10000; 12 begin 13 -- pick 3 lucky winners for our lottery at random from our list of entrants... 14 open cur_lottery; 15 dbms_output.put_line('So far we have selected '||cur_lottery%rowcount||' winners'); 16 loop 17 fetch cur_lottery into v_lottery; 18 exit when cur_lottery%notfound; 19 dbms_output.put_line('Winner '||cur_lottery%rowcount||' is ('||v_lottery.empno||') '||v_lottery.ename||' and receives $'||(v_prize_fund*v_lottery.prize_share)||' prize!'); 20 end loop; 21 close cur_lottery; 22* end; SQL> / So far we have selected 0 winners Winner 1 is (7566) JONES and receives $7000 prize! Winner 2 is (7844) TURNER and receives $2000 prize! Winner 3 is (7698) BLAKE and receives $1000 prize! PL/SQL procedure successfully completed.
... and surprise surprise, there's no syntax error thrown if I include a LOOP statement without an EXIT, creating and infinate loop.... bug perhaps.... no, because a loop doesn't necessary need an exit... depending on the type of loop and whether you expect exceptions to break out of the loop etc.
PL/SQL is very flexible. Because of that it cannot cater for developers who don't know what they're doing, but it certainly not going to throw up errors or warnings for things that are perfectly valid to do, if they're used correctly.
Your argument about the FETCH statement has no substance. Plenty of people here have shown and explained the principle of cursors and fully understand why a fetch statement does not require a loop. You're the only person here who seems to think otherwise, but I think you'd find in most other languages that would do cursor looping, they also would allow individiaul fetches without loops, and without saying they're a bug or error of some sort.