Chapter 2. Loops

Beginner

Q:

2-1.

There are four kinds of PL/SQL loops:

  1. Simple or infinite loop:

    LOOP
   ...
END LOOP;

  2. Numeric FOR loop:

    FOR loop_index IN [REVERSE] low_value .. high_value
LOOP
   ...
END LOOP;

  3. Cursor FOR loop:

    FOR loop_index IN cursor_name
LOOP
   ...
END LOOP;

    or:

    FOR loop_index IN (SELECT ...)
LOOP
   ...
END LOOP;

  4. WHILE loop:

    WHILE condition
LOOP
   ...
END LOOP;

Q:

2-2.

You can stop the execution of a simple loop in at least three ways:

  1. Issue an EXIT or EXIT WHEN statement:

    LOOP
   lotsa-code
   EXIT WHEN salary_count > 10000;
END LOOP;

  2. Raise an exception:

    LOOP
   lotsa-code
   RAISE VALUE_ERROR;
END LOOP;

  3. Use the GOTO command:

    LOOP
   lotsa-code
   GOTO <<outa_here>>
END LOOP;
<<outa_here>>
NULL;

Q:

2-3.

The loop does not contain an EXIT statement. It therefore never stops executing unless an exception is raised, which doesn’t appear likely. It assigns SYSDATE to myDate an infinite number of times.

Q:

2-4.

This loop executes 10 times, unless calc_sales raises an exception for one of the years between 1990 and 1999.

Q:

2-5.

This is an example of a phony loop. It wasn’t needed in the least, and the IF statements were needed only because the loop was used. You can simply write this code to get the job done. (Whether or not they deserve it. That’s not up to you; you just write the code.)

give_bonus (president_id, 2000000);
give_bonus (ceo_id, 5000000);

Q:

2-6.

Each function is run a single time; the low and high values in a numeric FOR loop range are evaluated once, before the loop body is executed even a single time.

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial