basic PL/SQL questions

[UNIXNewBie]

Please excuse the basic nature of these questions - I'm just starting off.

This example is taken from Oracle PL/SQL 101 - Osborne/McGraw-Hill - ISBN
0-07-212606-X - page 314

Numbers to the left are line numbers for reference only. Hopefully I haven't
made any typing mistakes.

1. DECLARE

2. CURSOR product_cur IS

3. SELECT * FROM plsql101_produc t

4. FOR UPDATE OF product_price;

5. BEGIN

6. FOR product_rec IN product_cur

7. LOOP

8. UPDATE plsql101_produc t

9. SET product_price = (product_rec.pr oduct_price =
0.97)

10. WHERE CURRENT OF product_cur

11. END LOOP;

12. END;

Am I correct to say the following?

The way a cursor works is that once a record is fetched it is taken out of
the cursor. 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?

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.

There is no formal declaration of the cursor name "product_re c" first used
on line 6. Is this an example of an implicit cursor of table-based record
type?

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?

Thanks

J.

[Frank van Bortel]

UNIXNewBie wrote:

Please excuse the basic nature of these questions - I'm just starting off.

This example is taken from Oracle PL/SQL 101 - Osborne/McGraw-Hill - ISBN
0-07-212606-X - page 314

Numbers to the left are line numbers for reference only. Hopefully I haven't
made any typing mistakes.

1. DECLARE

2. CURSOR product_cur IS

3. SELECT * FROM plsql101_produc t

4. FOR UPDATE OF product_price;

5. BEGIN

6. FOR product_rec IN product_cur

7. LOOP

8. UPDATE plsql101_produc t

9. SET product_price = (product_rec.pr oduct_price =
0.97)

10. WHERE CURRENT OF product_cur

11. END LOOP;

12. END;

Am I correct to say the following?

The way a cursor works is that once a record is fetched it is taken out of
the cursor. No, fetched is fetched - it "stays" until your fetch the next one.

This works well until the last record is reached at which time fetch will continue to return the last record in the cursor
No - just give it a try - oracle will generate an error
unless you use the %FOUND and %NOTFOUND constructs to test for the last record?

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.
It's there: for x IN y : as long as there's an x fetched, do...
There is no formal declaration of the cursor name "product_re c" first used
on line 6. Is this an example of an implicit cursor of table-based record
type? Ehhh - not sure about the semantics, but it sounds familiar. Undoubtedly
you'll get a concise, yes/no answer.

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?

You know, or need to know. As a programmer.
It is checked during compile, if the above is part of a procedure.

Inline...

--

Regards,
Frank van Bortel

[Chris Leonard]

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_re c" 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%ROW TYPE

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
_______________ _______________ _____