Control Structures  «Prev  Next»

Lesson 4 What are loops?
Objective Determine how to begin and end a simple loop within a PL/SQL block.

Programming PL/SQL Loops

A loop is a powerful way of building iterative logic. This lesson introduces you to loops.

PL/SQL LOOP Statement

The LOOP statement allows you to build iterative controls within your PL/SQL block. LOOP allows you to repeat a sequence of statements. The statements to be repeated are placed between the keywords LOOP and END-LOOP. The sequence of statements is executed, and then the control resumes at the top of the loop.

PL SQL Loop 
LOOP
 statement_1, ..., statement_n
END LOOP;
Example: 
LOOP
  IF pet_value > 100 THEN
    shipping_cost := 0 ;
  END IF;
  UPDATE order
  SET order_shippping_cost = shipping_cost
  WHERE order_id =1;
END LOOP;

PL SQL Loop: A sequence of statement that PL/SQL will execute multiple times.

If further processing is undesirable or impossible, you can use the EXIT statement to exit the loop. There are two forms of EXIT statements: EXIT and EXIT-WHEN.

EXIT

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement.
The EXIT statement must always be placed within a loop. You cannot use this statement to complete a PL/SQL block.

PL SQL Exit 
LOOP
  statement_1,...,statement_n;
  IF condition_1 is TRUE THEN
    EXIT
  END IF;
END LOOP;

Oracle PL/SQL Best Practices

EXIT-WHEN

The EXIT-WHEN statement allows a loop to complete conditionally. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition evaluates to TRUE, the loop completes and control passes to the next statement after the loop. The EXIT-WHEN statement replaces a simple IF statement.

Oracle exit when 
LOOP
  statement_1,...,statement_n;
  EXIT WHEN condition_1 is TRUE;
END LOOP;

Identifying Control Structures

Click the link below to learn about PL/SQL control structures.
Identifying Control Structures
In the next lesson, you will observe the structure of FOR and WHILE loops.