BASIC LOOPS
Basic Loop pada PL/SQL hampir sama logikanya dengan DO-WHILE pada Bahasa Prosedural yang lain, contonhnya pada bahasa C. Pada Basic Loop Minimal 1 kali Terjadi looping.Syntax:
LOOPContoh :
statement1;
. . .
EXIT [WHEN condition];
END LOOP;
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
counter NUMBER(2) := 1;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
LOOPINSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
EXIT WHEN counter > 3;END LOOP;END;
/
FOR LOOPS
FOR pada PL/SQL logikanya sama dengan FOR pada Bahasa Prosedural yang lain, contonhnya pada bahasa C. Dan kita menggunakan FOR LOOP ketika kita sudah mengetahui batas angka perulangannyaSyntax:
FOR counter IN [REVERSE]Contoh :
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
Nb: bagi yang belum mengetahaui fungsi %TYPE silakan buka artikel Tipe Data ReferenceDECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id
FROM locations
WHERE country_id = countryid;
FOR i IN 1..3 LOOPINSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + i), new_city, countryid );END LOOP;END;
/
WHILE LOOPS
Kita menggunakan WHILE LOOPS ketika kita ingin mengecek terlebih dahulu kondisi yang ada, baru kemudian lakukan looping sampai syaratnya tidak terpenuhi.Syntax:
WHILE conditionContoh :
statement1;
statement2;
. . .
END LOOP;
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
WHILE counter <= 3 LOOPINSERT INTO locations(location_id, city, country_id)END LOOP;
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
END;
/
daftar pustaka : http://risnotes.com/2012/02/looping-di-plsql/