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 [1]
    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;
  • 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;


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

Oracle PL/SQL Best Practices

EXIT and EXIT-WHEN

  • 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;
  • 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 PL/SQL Control Structures

  1. IF-ELSIF-ELSE: This statement allows you to control structure to execute a sequence of statements conditionally.
  2. LOOP-END LOOP: This statement allows you to execute a sequence of statements multiple times.
  3. EXIT: This statement allows you to exit from a loop immediately.
  4. EXIT-WHEN: This statement allows you to exit a loop when the given condition is met.
  5. WHILE-LOOP: This statement allows you to execute a sequence of statements while the given condition is TRUE.
  6. FOR-LOOP: This statement allows you to execute a sequence of statements for the given number of times.

  • Searched CASE Expression:
    A searched CASE expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 4-1. A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.
  • Example 4 - 1 Using a Search Condition with a CASE Statement 
    SQL> DECLARE
2 grade CHAR(1) := 'B';
3 appraisal VARCHAR2(120);						
4 id NUMBER := 8429862;
5 attendance NUMBER := 150;
6 min_days CONSTANT NUMBER := 200;
7
8 FUNCTION attends_this_school (id NUMBER)
9 RETURN BOOLEAN IS
10 BEGIN
11 RETURN TRUE;
12 END;
13
14 BEGIN
15 appraisal :=
16 CASE
17 WHEN attends_this_school(id) = FALSE
18 THEN 'Student not enrolled'
19 WHEN grade = 'F' OR attendance < min_days
20 THEN 'Poor (poor performance or bad attendance)'
21 WHEN grade = 'A' THEN 'Excellent'
22 WHEN grade = 'B' THEN 'Very Good'
23 WHEN grade = 'C' THEN 'Good'
24 WHEN grade = 'D' THEN 'Fair'
25 ELSE 'No such grade'
26 END;
27 DBMS_OUTPUT.PUT_LINE
28 ('Result for student ' || id || ' is ' || appraisal);
29 END;
30 /
Result for student 8429862 is Poor (poor performance or bad attendance)
PL/SQL procedure successfully completed.
SQL>

In the next lesson, you will observe the structure of FOR and WHILE loops.
[1] PL SQL Loop: A sequence of statement that PL/SQL will execute multiple times.

SEMrush Software