Oracle PL/SQL Programming: A Developer's Workbook

Chapter 2. Loops

PL/SQL has four basic looping (or iterative) structures: the simple loop, the FOR loop, the cursor-based FOR loop, and the WHILE loop; these repeatedly execute a block of code. There are three things to worry about when designing a loop:

• How (or whether) the loop terminates

• When the test for termination takes place

• Whether the type of loop is suited to the particular task at hand

If you know the number of times a loop must execute (for example, to process the months in a year), you can be reasonably confident that a FOR loop is the best structure. If you don’t know the number in advance (for example, the number of records in a table), you’re probably better off using a WHILE loop. This chapter tests your ability to make these kinds of decisions.

Beginner

2-1.	What are the different kinds of loops available in PL/SQL?
2-2.	How do you stop a simple loop from continuing?
2-3.	Why is the following form of a simple loop called an infinite loop? DECLARE myDate DATE; BEGIN LOOP myDate := SYSDATE; END LOOP; END;
2-4.	How many times does the body of the following loop execute? FOR year_index IN 1990 .. 1999 LOOP calc_sales (year_index); END LOOP;
2-5.	Rewrite the following loop so that you do not use a loop at all: FOR i IN 1 .. 2 LOOP IF i = 1 THEN give_bonus (president_id, 2000000); ELSIF i = 2 THEN give_bonus (ceo_id, 5000000); END IF; END LOOP;
2-6.	How many times are the first_year and last_year functions executed in the following loop? FOR yearnum IN first_year (company_id => 1056) .. ...