[Oracle] PL/SQL 반복문 (Loop, While, For Loop, Continue)

 

BASIC LOOP

 

loop
  pl/sql sentences;
  exit(condition);
end loop;

DECLARE 
	v_num NUMBER := 0;

BEGIN
	LOOP 
		dbms_output.put_line(v_num);
		v_num := v_num + 1;
		EXIT WHEN v_num > 10;
	END LOOP;

END;
/

 

 

WHILE

 

DECLARE
	v_num NUMBER := 0;
BEGIN
	WHILE v_num < 11 LOOP  
		dbms_output.put_line(v_num);
		v_num := v_num + 1;
	END LOOP;
END;
/

 

 

FOR

 

FOR cnt IN start..end LOOP

-- 정방향
BEGIN
	FOR cnt IN 0..10 LOOP
		dbms_output.put_line('count: ' || cnt);
	END LOOP;
END;
/

-- 역방향
BEGIN
	FOR cnt IN reverse 0..10 LOOP
		dbms_output.put_line('count: ' || cnt);
	END LOOP;
END;
/

 

• 예제 - EMPLOYEES Table에서 사원 이름의 길이만큼 # 찍기

BEGIN
	FOR name IN (select LAST_NAME || ' ' || FIRST_NAME name 
    			from EMPLOYEES e2 where DEPARTMENT_ID='30') LOOP 
		dbms_output.put_line(name.name);
		FOR i IN 0..length(name.name) LOOP
			dbms_output.put('#');
		END LOOP;
		dbms_output.put_line('');
	END LOOP;
END;
/

 

 

CONTINUE

Oracle 11g부터 추가 된 기능

 

DECLARE
	tot NUMBER := 0;
BEGIN
	FOR i IN 1..5 LOOP
		tot := tot + 1;
		dbms_output.put_line('i: '|| i || ', total: ' || tot);
		CONTINUE when(i > 4);
		tot := tot + i;
		dbms_output.put_line('i: '|| i || ', total: ' || tot);
	END LOOP;
END;
/

 

- Output