5장. 제어 구조 작성

1.IF 문

ex1)

SET SERVEROUTPUT ON

DECLARE

             v_myage NUMBER := 10 ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             END IF;

END;

/

 I am a child

PL/SQL procedure successfully completed.

 

ex2)

DECLARE

             v_myage NUMBER := 31 ;

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;

/

I am not a child

PL/SQL procedure successfully completed.

 

 

Ex3) 초기화가 되지 않은 변수에대한 IF문

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;

/

I am not a child

PL/SQL procedure successfully completed.

 

-> NULL은 비교가 불가 하므로 ELSE로 넘어가서 I am not a child 출력.

 

 

Ex4) ELSIF

 

DECLARE

             v_myage NUMBER := 31 ;

BEGIN

             IF v_myage < 11 THEN

              DBMS_OUTPUT.PUT_LINE(' I am a child ');

             ELSIF 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;

/

I am in my thirties

PL/SQL procedure successfully completed.

 

2. CASE 표현식 :  값을 할당할때, 또 다른 명령을 실행시킬때 사용

ex1)값을 할당할 때 

DECLARE

             v_grade CHAR(1) := UPPER('&grade') ;

             v_appraisal VARCHAR2(20) ;

BEGIN

             v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent'

                                                                   WHEN 'B' THEN 'Very Good'

                                                                   WHEN 'C' THEN 'Good'

                                                                   ELSE 'No such grade'

                                                                   END;

             DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal);

END;

/

 Enter value for grade: B

old 2: v_grade CHAR(1) := UPPER('&grade') ;

new 2: v_grade CHAR(1) := UPPER('B') ;

 Grade: B Appraisal Very Good

PL/SQL procedure successfully completed.

  

 ex2)또 다른 명령어를 실행하기 위한 CASE문

DECLARE

             v_sum NUMBER ;

             v_deptno NUMBER := &deptid ;

BEGIN

             CASE v_deptno

             WHEN 10 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 10 ;

             WHEN 20 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 20 ;

             WHEN 30 THEN

                           SELECT SUM(sal) INTO v_sum

                           FROM emp

                           WHERE deptno = 30 ;

             ELSE

                           SELECT SUM(sal) INTO v_sum FROM emp ;

             END CASE ;

DBMS_OUTPUT.PUT_LINE ( v_sum ) ;

END ;

/

 Enter value for deptid: 30

old 3: v_deptno NUMBER := &deptid ;

new 3: v_deptno NUMBER := 30 ;

 9400

PL/SQL procedure successfully completed.

 

3. Loop 문 : 반복수행

EX1) Basic Loop

SQL> SET SERVEROUTPUT ON

DECLARE

             v_count NUMBER(2) := 1 ;

BEGIN

             LOOP

                           DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;

                           v_count := v_count + 1 ;

                           EXIT WHEN v_count = 4 ;

             END LOOP ;

END;

/

 

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed.

 

Ex2) While Loop

BEGIN

 WHILE v_count <= 3 

 LOOP

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;

  v_count := v_count + 1 ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed.

 

Ex3) For Loop, 반복 횟수를 알고있을 때 사용

BEGIN

 FOR i IN 1..3 

 LOOP  

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed

 

Ex4)For  Loop는 암시적으로 카운터가 생긴다.

BEGIN

 FOR i IN 1..3 LOOP

  DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

 END LOOP ;

END ;

/

count: 1

count: 2

count: 3

PL/SQL procedure successfully completed

 

Ex5)  Reverse For Loop 거꾸로연산.

BEGIN

 FOR i IN REVERSE 1..3 

  LOOP

   DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;

  END LOOP ;

END ;

/

count: 3

count: 2

count: 1

PL/SQL procedure successfully completed.

 

※ Nested Loops  중첩된 Loop를 빠져나갈때 Exit

DECLARE

  x NUMBER := 3 ;

  y NUMBER ;

BEGIN

  <<OUTER_LOOP>>

  LOOP

    y := 1 ;

    EXIT WHEN x > 5 ;

       <<INNER_LOOP>>

             LOOP

                DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;

                y := y + 1 ;

                EXIT WHEN y > 5 ;

             END LOOP INNER_LOOP ;

    x := x + 1 ;

 END LOOP OUTER_LOOP ;

END ;

/

 

 

3 * 1 = 3

3 * 2 = 6

3 * 3 = 9

3 * 4 = 12

3 * 5 = 15

4 * 1 = 4

4 * 2 = 8

4 * 3 = 12

4 * 4 = 16

4 * 5 = 20

5 * 1 = 5

5 * 2 = 10

5 * 3 = 15

5 * 4 = 20

5 * 5 = 25

PL/SQL procedure successfully completed.

 

 

 DECLARE

x NUMBER := 3 ;

y NUMBER ;

BEGIN

<<OUTER_LOOP>>

LOOP

y := 1 ;

EXIT WHEN x > 5 ;

<<INNER_LOOP>>

LOOP

DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;

EXIT OUTER_LOOP WHEN x*y > 15 ;

y := y + 1 ;

EXIT WHEN y > 5 ;

END LOOP INNER_LOOP ;

x := x + 1 ;

END LOOP OUTER_LOOP ;

END ;

/

 

4. CONTINUE 문 :

11g부터 생긴것. Continue에 만족하는 사항이 생기면, 밑에 있는 것은 싫행하지말고첫번째 시퀀스부터 다시 시작함.

ex1)

SQL> SET SERVEROUTPUT ON

DECLARE

             v_total SIMPLE_INTEGER := 0;

BEGIN

             FOR i IN 1..5 LOOP

              v_total := v_total + i;

              DBMS_OUTPUT.PUT_LINE ('Total is: '|| v_total) ;

              CONTINUE WHEN i > 3 ;

              v_total := v_total + i;

              DBMS_OUTPUT.PUT_LINE ('Out of Loop Total is: '|| v_total);

             END LOOP;

END;

/

 Total is: 1 <= 0 + 1 (i)

Out of Loop Total is: 2 <= 1 + 1 (i)

Total is: 4 <= 2 + 2 (i)

Out of Loop Total is: 6 <= 4 + 2 (i)

Total is: 9 <= 6 + 3 (i)

Out of Loop Total is: 12 <= 9 + 3 (i)

Total is: 16 <= 12 + 4 (i)

Total is: 21 <= 16 + 5 (i)

PL/SQL procedure successfully

 

ex2)

SQL> SET SERVEROUTPUT ON

DECLARE

  v_total NUMBER := 0;

BEGIN

  <<BeforeTopLoop>>

  FOR i IN 1..5

  LOOP

    v_total := v_total + 1;

    DBMS_OUTPUT.PUT_LINE ('Outer Total is: ' || v_total) ;

    FOR j IN 1..5

    LOOP

      CONTINUE BeforeTopLoop

    WHEN i               + j > 5 ;

      v_total := v_total + 1;

      DBMS_OUTPUT.PUT_LINE ('Inner Total is: ' || v_total) ;

    END LOOP;

  END LOOP;

END ;

/

Outer Total is: 1 <= 0 + 1 (i=1)

Inner Total is: 2 <= 1 + 1 (i=1 , j=1)

Inner Total is: 3 <= 2 + 1 (i=1 , j=2)

Inner Total is: 4 <= 3 + 1 (i=1 , j=3)

Inner Total is: 5 <= 4 + 1 (i=1 , j=4)

Outer Total is: 6 <= 5 + 1 (i=2)

Inner Total is: 7 <= 6 + 1 (i=2 , j=1)

Inner Total is: 8 <= 7 + 1 (i=2 , j=2)

Inner Total is: 9 <= 8 + 1 (i=2 , j=3)

Outer Total is: 10 <= 9 + 1 (i=3)

Inner Total is: 11 <= 10 + 1 (i=3 , j=1)

Inner Total is: 12 <= 11 + 1 (i=3 , j=2)

Outer Total is: 13 <= 12 + 1 (i=4)

Inner Total is: 14 <= 13 + 1 (i=4 , j=1)

Outer Total is: 15 <= 14 + 1 (i=5)

PL/SQL procedure successfully completed.