PL/SQL Basic 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;

Leave a Reply

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