데이터 베이스/SQL

절차형 SQL 주요 문법

개발공부개발공부 2020. 1. 3. 11:21

 

절차형 SQL 에서는 일반 프로그래밍 언어와 같이 제어문/반복문 사용이 가능하다 

1. 조건문 : 일반적으로 IF - THEN - ELESIF -ELSE 구조를 사용하거나 CASE  ~ WHEN ~ THEN 구조를 사용한다

 
1
2
3
4
5
6
7
8
9
10
11
12
13
IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSIF x = 4 THEN
   sequence_of_statements_4;
ELSIF x = 5 THEN
   sequence_of_statements_5;
ELSE
   sequence_of_statements_N;
END IF;
 
1
2
3
4
5
6
7
8
9
CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   WHEN x = 4 THEN sequence_of_statements_4;
   WHEN x = 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;
 
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter

 

2. 반복문 (LOOP /FOR)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
LOOP
    statements
 
    <<child_loop>>
    loop
        statements
        exit parent_loop when <condition>; -- Terminates both loops
        exit when <condition>; -- Returns control to parent_loop
    end loop child_loop;
        if <condition> then
           continue; -- continue to next iteration
        end if;
 
    exit when <condition>;
END LOOP parent_loop;
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare
    var number;
begin
     /*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */
     for var in 0 .. 10 loop
          dbms_output.put_line(var);
     end loop;
 
     if (var is null) then
          dbms_output.put_line('var is null');
     else
          dbms_output.put_line('var is not null');
     end if;
end;
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter