5. PL/SQL 제어구조 작성
A. IF Statement
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
ex)
1) 간단한 IF 문
DECLARE
v_myage number := 30;
BEGIN
IF v_myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(‘ I am a child ‘);
END IF;
END;
/
2) if then else 문
declare
v_myage number := &myage;
begin
if v_myage < 20 then
dbms_output.put_line(‘ I am young ‘);
elsif v_myage < 30 then
dbms_output.put_line(‘ I am in my twenties ’);
elsif v_myage < 40 then
dbms_output.put_line(‘ I am in my thirties ’);
else
dbms_output.put_line(‘ I am always young ‘);
end if;
end;
/
B. IF 문의 NULL 값
declare
v_myage number;
begin
if v_myage < 11 then
dbms_output.put_line(‘ I am a child ‘);
else
dbms_output.put_line(‘ I am not a child ‘);
end if;
end;
/
– v_myage 변수가 초기화 되지 않았기 때문에 null 값을 반환하여 else 문으로 이동
C. CASE 식
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
….
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
ex) 두가지 방식의 사용법
1) v_grade 값을 표현식으로 사용
declare
v_grade char(1) := upper(‘&grade’);
v_appraisal varchar2(20) ;
begin
v_appraisal := case v_grade
when ‘A’ then ‘Excellent’
when ‘B’ then ‘Good’
when ‘C’ then ‘Loser!!’
else ‘No such grade’
end;
dbms_output.put_line (‘Grade: ‘ || v_grade || ‘ Appraisal ‘ || v_appraisal);
end;
/
2) when 절에 부울 값을 생성하는 표현식이 포함
declare
v_grade char(1) := upper(‘&grade’);
v_appraisal varchar2(20);
begin
v_apprasial := case
when v_grade = ‘A’ then ‘Excellent’
when v_grade in(‘B’, ’C’) then ‘Good’
else ‘No such grade’
end;
dbms_output.put_line (‘Grade: ‘ || v_grade || ‘Appraisal ‘ || v_appraisal);
end;
/
D. CASE 식과 CASE 문
CASE 문은 조건을 평가하여 작업을 수행
– CASE 문 예제
E. NULL 처리
– 논리테이블
F. 반복제어 : LOOP 문
1) Basic Loop
LOOP
statement1;
…
EXIT [WHEN condition];
END LOOP;
ex)
declare
v_countryid locations.country_id%type := ‘CA’;
v_loc_id locations.location_id%type;
v_counter number(2) := 1;
v_new_city locations.city%type := ‘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 > 3;
end loop;
end;
/
2) while Loop
WHILE condition loop
statement1;
statement2;
…
END LOOP;
ex)
declare
v_counterid 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_counteryid);
v_counter := v_counter + 1;
end loop;
end;
/
3) FOR Loop
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
…
END LOOP;
ex)
declare
v_counterid 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;
/
– FOR Loop 규칙
G. Loop 의 권장 사용법
H. 중첩 loop 및 label
ex)
declare
v_outerc number := 1;
v_innerc number := 1;
v_result number;
begin
<<outer_loop>>
loop
v_outerc:= v_outerc+1;
exit when v_outerc>15;
<<inner_loop>>
loop
exit outer_loop when v_outerc > 9;
v_result := v_outerc * v_innerc;
dbms_output.put_line(v_outerc || ‘ * ‘ || v_innerc || ‘ = ‘ || v_result);
v_innerc := v_innerc + 1;
exit when v_innerc > 9;
end loop inner_loop;
v_innerc := 1;
end loop outer_loop;
end;
/
I. CONTINUE
ex)
1)
declare
v_total simple_integer := 0;
begin
for i in 1..10 loop
v_total := v_total + i;
dbms_output.put_line(‘Total is: ‘ || v_total);
continue when i > 5;
v_total := v_total + i;
dbms_output.put_line(‘Out of Loop Total is: ‘|| v_total);
end loop;
end;
/
2)
declare
v_total number := 0;
begin
<<before>>
for i in 1..10 loop
v_total := v_total + 1;
dbms_output.put_line(‘Total is : ‘ || v_total);
for j in 1..10 loop
continue before when I + j > 5;
v_total := v_total + 1;
end loop;
end loop;
end;
/
3)
begin
for i in reverse 1 .. 10 loop
dbms_output.put_line (lpad (‘*’, i, ‘*’));
end loop;
end;
================================================
declare
cnt number :=1;
begin
while cnt <= 10 loop
dbms_output.put_line (lpad(‘*’,(11-cnt),’*’));
cnt := cnt + 1;
end loop;
end;
