Re: basic PL/SQL questions

Responses inline...

> The way a cursor works is that once a record is fetched it is taken out of
> the cursor.
IIRC, what you mean to say is that once a record is fetched it is popped out of the "active set" (that is, the group of records meeting the query requirements that are waiting to be fetched from the server) and fetched into your execution context's (PGA) local memory.

> This works well until the last record is reached at which time
> fetch will continue to return the last record in the cursor unless you use
> the %FOUND and %NOTFOUND constructs to test for the last record?
Well, with a cursor for loop like your code is using, the test for CURSOR%NOTFOUND is implicit, so you'll never run into this problem. However, if you have code that attempts to use fetch to pop a value off of the active set into your execution context's memory, then the CURSOR%NOTFOUND is set, but your local variables don't change - not even the one you've fetched into. That's why you'll see the same item over and over - fetch doesn't return the last item repeatedly (at least not as far as I'm aware), you just keep reusing the same value which has not been replaced by any newly fetched value. Try this code in SCOTT's schema, and you'll see the last record repeated over several times because the loop (here, just a normal while loop and not a cursor for loop) does not check for CURSOR%NOTFOUND or CURSOR%FOUND:

declare
  cursor c1 is select ename from emp;
  myrec c1%rowtype;
  x number(2) := 0;
begin
  open c1;
  fetch c1 into myrec;
  while x < 40 loop
    dbms_output.put_line (myrec.ename);
    fetch c1 into myrec;
    x := x + 1;
  end loop;
end;
/

>
> The cursor loop used in the above example eliminates the need to open,
 close
> and fetch. It also eliminates the need to check for the last record.
/
Yes, the open, fetch, and close statements are implicit with the cursor for loop syntax.

> There is no formal declaration of the cursor name "product_rec" first used
> on line 6. Is this an example of an implicit cursor of table-based record
> type?
No, but you're close. It's not a table-based record type, but rather a cursor-based record type. It's as though the DECLARE section had a line at the end that read as follows:

    PRODUCT_REC PRODUCT_CUR%ROWTYPE
>
> On line 9 how does PL/SQL know that there is a product_price field in the
> cursor record? Is this also part of the implicit cursor definition?
It knows this because of the implicit declaration of the product_rec variable (see above).

Hope this helps!
Chris

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW