PL/SQL LOOP Statement

There are three loop types:


  1. Basic loop
  2. FOR loop
  3. WHILE loop

Basic LOOP


Use the basic loop when the statements inside the loop must execute at least once.

Syntax:

LOOP
statement;
. . .
EXIT [WHEN condition];
END LOOP;

Example:

DECLARE
   v_count number :=1;
BEGIN
LOOP
   dbms_output.put_line('sequence :'||v_count);
   v_count :=v_count+1;
   exit when v_count >4;
END LOOP;
END;

Output:

Inserting data using for LOOP

Example

DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_counter NUMBER(2) := 1;
v_new_city varchar2(20) := 'Montreal';

BEGIN
SELECT MAX(location_id)INTO v_loc_id FROM locations
WHERE country_id = v_countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
v_counter := v_counter + 1;
EXIT WHEN v_counter >=4;
END LOOP;
END;

While loop


WHILE conditionLOOP
statement1;
statement2;
. . .
END LOOP;

Use the WHILE loop to repeat statements while a condition is TRUE

Note: Use the WHILE loop if the condition must be evaluated at the start of each iteration

Example:

DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_new_city locations.city%TYPE := 'Montreal';
v_counter NUMBER := 1;

BEGIN

SELECT MAX(location_id) INTO v_loc_id FROM locations
WHERE country_id = v_countryid;
WHILE v_counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
v_counter := v_counter + 1;
END LOOP;
END;

For LOOP


When to use for loop

    • Use a FOR loop to shortcut the test for the number of iterations.

 

Note: Do not declare the counter, it is declared implicitly Syntax:

FOR counter IN [REVERSE]
lower_bound..upper_boundLOOP
statement1;
statement2;
. . .
END LOOP;

Example:

DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO v_loc_id
FROM locations
WHERE country_id = v_countryid;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_loc_id + i), v_new_city, v_countryid );
END LOOP;
END;

Leave a Reply

Your email address will not be published. Required fields are marked *