3. PL/SQL 제어문.

3장. PL/SQL 제어문.

 

* 오라클이 제공하는 제어문

- 조건문(분기문) : if문 ,case문

- 반복문 : Basic Loop문장, while문 / for문(반복회수지정 가능o)

 

1. 조건문(IF 문) :

유형  1. IF~END IF 문장 :이 유형은 조건이 여러 개 일 경우에 사용하는 가장 기본적인  IF 문장입니다.

[ 문 법 ]

IF   (조 건) THEN   실행 문장;  END IF ;

 

예: EMP 테이블에서  EMPNO , ENAME, DEPTNO , DNAME 을 출력하세요.
   단 DNAME 의 값은 아래와 같습니다.
      DEPTNO 가 10 이면  ’ACCOUNT’ ,
      DEPTNO 가 20 이면  ‘RESEARCH’ ,
      DEPTNO 가 30 이면  ‘SALES’ ,

      DEPTNO 가 40 이면  ‘OPERATIONS’ 로 출력하세요

 

SQL> DECLARE 2     vempno  emp.empno%TYPE; 3     vename  emp.ename%TYPE; 4     vdeptno emp.deptno%TYPE; 5     vdname  VARCHAR2(20) := null ; 6  BEGIN 7    SELECT  empno , ename , deptno   8    INTO vempno , vename , vdeptno   9    FROM  emp   10   WHERE  empno=7900; 11   IF (vdeptno = 10) THEN    -- IF 문이 시작됩니다. 12     vdname := 'ACCOUNT'; 13   END IF ;   -- IF 문이 끝나면 반드시  END IF 로 마쳐야 합니다. 14   IF (vdeptno = 20) THEN 15      vdname := 'RESEARCH'; 16   END IF ; 17   IF (vdeptno = 30) THEN 18      vdname := 'SALES'; 19   END IF ; 20   IF (vdeptno = 40) THEN 21      vdname := 'OPERATIONS'; 22   END IF ; 23   DBMS_OUTPUT.PUT_LINE (vempno||'   '||vename||'   '||vdeptno||'   '||vdname); 24   END ; 25   /

 

유형  2. IF ~ THEN ~ELSIF ~END IF 문장  ( 조건이 여러 개 일 경우 사용)

문  법:

 

IF  ( 조 건  ) THEN    실행 문장  ; ELSIF ( 조 건  )  THEN    실행 문장  ; ELSIF ( 조 건  )  THEN    실행 문장  ; END IF ;

 

사용 예: EMP 테이블에서  EMPNO , ENAME, DEPTNO , DNAME 을 출력하세요.
         단 DNAME 의 값은 아래와 같습니다.
           DEPTNO 가 10 이면  ’ACCOUNT’ ,
          DEPTNO 가 20 이면  ‘RESEARCH’ ,
          DEPTNO 가 30 이면  ‘SALES’ ,
          DEPTNO 가 40 이면  ‘OPERATIONS’ 로 출력하세요

 

 

SQL>DECLARE   2     vempno  emp.empno%TYPE;   3     vename  emp.ename%TYPE;   4     vdeptno emp.deptno%TYPE;   5     vdname  VARCHAR2(20) := null ;   6   BEGIN   7     SELECT  empno , ename , deptno     8     INTO  vempno , vename , vdeptno     9     FROM emp     10    WHERE empno=7900;   11                                12   IF (vdeptno = 10) THEN  13     vdname := 'ACCOUNT';  14   ELSIF (vdeptno = 20) THEN  15     vdname := 'RESEARCH';  16   ELSIF (vdeptno = 30) THEN  17     vdname := 'SALES';  18   ELSIF (vdeptno = 40) THEN  19     vdname := 'OPERATIONS';  20   END IF;   21   22     DBMS_OUTPUT.PUT_LINE (vempno||'   '||vename||'   '||vdeptno||'   '||vdname);   23  END ;   24   /

 

유형  3. IF~THEN~ELSE~END IF ( 조건이  2개 일 경우 사용)
 

 문법:

IF   ( 조 건  ) THEN   실행 문장  ; ELSE   실행 문장  ; END IF ;

 

예  : EMP 테이블에서 사원번호가 7900 번인 사원의  EMPNO , ENAME , COMM 을 출력하되
    해당 사원의 COMM 이 0보다 크면 COMM 의 금액을 출력하고, 0 보다 작으면  
     ‘사원의 보너스는 없습니다’ 라는 문장을 출력하세요.

 

 

SQL>DECLARE    2    vempno  emp.empno%TYPE;    3    vename  emp.ename%TYPE;    4    vcomm   emp.comm%TYPE := NULL;    5    6  BEGIN    7    SELECT  empno , ename , comm  INTO  vempno , vename , vcomm      8    FROM emp      9    WHERE empno=7900;   10    11    IF vcomm > 0  THEN  12       DBMS_OUTPUT.PUT_LINE (vename||' 사원의 보너스는  '||vcomm||'입니다');  13    ELSE  14       DBMS_OUTPUT.PUT_LINE (vename||' 사원의 보너스는 없습니다');  15    END IF ;   16  END ;   17   /

 : 많은 데이터가 있는 것 중 하나를 찾으려고 인덱스는 안쓰는게 좋다. 적은 수가 있는 것 중 데이터를 하나 찾을 때 인덱스를 쓰는 게 좋다. 그러므로 반복문을 활용하면 좋음.

 

---

 < IF 문 연습문제 >

 

1. 사용자로부터 사원번호를 입력 받아 해당 사원번호의  사원의 보너스가  0 보다 클 경우 해당
사원의 보너스 금액을 출력하고 만약 보너스가  0 보다 작을 경우  ‘보너스가 없습니다’ 라는 문장
을 출력하세요. (변수명은 임의로 정하세요)

 

 

2. 사번을 입력 받아 사원(EMP)테이블에서 그 해당 사원의 이름과 연봉, 세금을 출력하세요.
  (단, 세금은 연봉액수에 따라  5000 만원 이상이면 5%,  
                   4000만원부터 4999 만원까지는 4% ,  
                   3000만원부터 3999 만원까지는 3% ,  
                   2000만원부터 2999 만원까지는 2% ,  
                  나머지는 1% 로 차등 계산하세요)   
 

---

 

2. CASE 문

 

[문법]

 

CASE    [ 조건  ]    WHEN 조건  1   THEN   결과  1     WHEN 조건  2   THEN   결과  2     …     WHEN 조건  n  THEN   결과 n [ ELSE   기본값  ] END ;

:주의 사항은 CASE 내부의 여러 조건들을 구분할 때   , (콤마) 를 사용하지 않습니다.

 tip _ SQL에서는 부등식 쓸 때는 case문이 좋고, 등식을 쓸 때는 decode가 좋다.

 

- 사용 예  1: 사용자로부터 사원 번호를 입력 받은 후   EMP 테이블에서  EMPNO , ENAME,  
            DEPTNO , DNAME 을 출력하되  
             DEPTNO 가  10 이면  ’ACCOUNT’ ,
             DEPTNO 가  20 이면  ‘RESEARCH’ ,
             DEPTNO 가  30 이면  ‘SALES’ ,
             DEPTNO 가  40 이면  ‘OPERATIONS’ 로 출력하세요

 

SCOTT> DECLARE    2        v_empno    emp.empno%TYPE ;    3         v_ename    emp.ename%TYPE ;    4         v_deptno   emp.deptno%TYPE ;    5         v_dname    varchar2(10) ;    6  BEGIN    7         SELECT empno , ename , deptno    8        INTO  v_empno , v_ename , v_deptno    9        FROM  emp   10       WHERE empno = &empno ;  11         v_dname := CASE v_deptno  12                     WHEN 10 THEN 'ACCOUNT'  13                     WHEN 20 THEN 'RESEARCH'  14                     WHEN 30 THEN 'SALES'  15                     WHEN 40 THEN 'OPERATIONS'  16                    END ;   17       DBMS_OUTPUT.PUT_LINE (v_empno||'  '||v_ename||'  '||v_deptno||'  '||v_dname);   18   END ;   19   /   위 예에서  11번 줄부터  16번 줄의 CASE 부분을 아래와 같이 변형해도 결과는 동일하게 나옵니다. => CASE 뒷부분에 바로 써도 되고 WHEN 부분에 넣어도 된다     11    v_dname := CASE   12                 WHEN v_deptno = 10 THEN 'ACCOUNT'   13                 WHEN v_deptno = 20 THEN 'RESEARCH'   14                 WHEN v_deptno = 30 THEN 'SALES'   15                 WHEN v_deptno = 40 THEN 'OPERATIONS'   16               END ;

 

- 사용 예  2: EMP 테이블을 사용하여 사용자로부터 사원 번호를 입력 받아 해당 사원의
    empno , ename , sal , deptno,  인상후 연봉(up_sal) 을 출력하세요.
   단 부서번호가  10 번 부서는 현재 연봉의 10% 인상하고,
      부서번호가  20 번  , 30 번 부서는 20 % 인상하고  ,  
      부서번호가  30 번 보다 클 경우 30% 를 인상하세요.

 

 

SCOTT> DECLARE    2          v_empno    emp.empno%TYPE ;    3          v_ename    emp.ename%TYPE ;    4          v_sal      emp.sal%TYPE ;    5          v_deptno   emp.deptno%TYPE ;    6          v_up_sal    emp.sal%TYPE ;    7  BEGIN    8         SELECT empno , ename , sal , deptno    9          INTO  v_empno , v_ename , v_sal , v_deptno   10          FROM  emp   11         WHERE empno = &empno ;   12         v_up_sal := CASE   13                       WHEN v_deptno = 10 THEN v_sal * 1.1   14                        WHEN v_deptno IN (20,30) THEN v_sal*1.2   15                       WHEN v_deptno > 30 THEN v_sal*1.3   16                        ELSE v_sal   17                     END ;   18  DBMS_OUTPUT.PUT_LINE ('사원번호'||'       '||'이름'||'        '||'급여'||'       '||'부서번호'||'      '||'인상후급여');   19   DBMS_OUTPUT.PUT_LINE (v_empno||'     '||v_ename||'     '||v_sal||'   '||v_deptno||'      '||v_up_sal);   20    END ;   21    / Enter value for empno: 7900 사원번호  이름        급여  부서번호  인상후급여 7900     JAMES     950         30        1140

 

3. 반복문  
반복문은 반복 횟수를 알 수 없는 경우에 사용하는  BASIC LOOP 문과 WHILE 문이 있고 반복횟수
를 지정하는  FOR 문이 있습니다. 우선  BASIC LOOP 문부터 살펴 보겠습니다.
 1) BASIC LOOP 반복문 
 
문  법: 
 
LOOP
    PL/SQL 문장  ;
    PL/SQL 문장  ;
    EXIT [ 조건  ] ;  : loop먼저 돌고, 조건이 만족하면 loop종료
END LOOP ;  

 

2) WHILE 반복문

  LOOP 문과의 차이점은 해당 조건을 검사하는 시점이 다르다는 것과  
LOOP 문장은 조건이 참이 되면  LOOP 가 끝나지만 WHILE 문은 조건이 참이 되면  LOOP 가 시작
이 된다는 점도 아주 큰 차이점입니다.

즉  BASIC LOOP 문은 나중에 조건을 검색하는 것이고 WHILE 문은 시작부터 조건을 먼저 검사한
후  PL/SQL 문장을 수행하게 됩니다. 그래서  BASIC LOOP 문은 조건이 틀려도  PL/SQL 문장이  1회
는 실행이 되지만 WHILE 문은 아예 실행이 되지 않습니다.

 

WHILE  조건   LOOP     PL/SQL 문장       PL/SQL 문장   END LOOP ;    조건이 틀리면 loop종료

 

 

---

 

< BASIC LOOP 문과 WHILE 반복문 연습 문제>

BASIC LOOP 문과 WHILE 문을 사용하여 아래의 결과처럼 나오도록 각각 작성하세요.

출력 결과 화면
 
5
4
3
2
1
0

 

 

 

 

3) FOR 반복문

 
문  법: 
 

FOR counter IN [REVERSE] start . . end LOOP     : start 숫자1 .. end 숫자2   숫자1부터 숫자2까지
   Statement1 ;
   Statement2 ;
…
END LOOP ;

 

위 문법에서  counter 는 반복을 카운트 할 변수입니다. 원래  PL/SQL 에서는 사용할 변수를 사용
하기 전에 미리  DECLARE 부분에서 선언해야 하지만  FOR 반복문 내에서 사용할 변수는 미리 선
언을 하지 않아도 사용 가능합니다.
그리고  IN 다음에  start 부분에 시작번호를 쓰고  .. (점 두 개) 뒤에  end 부분에 끝 숫자를 적으시
면 됩니다.
만약 역순으로 반복하려면  IN 다음에  REVERSE 를 쓰고 반복할 횟수를 쓰면 되는데 주의 사항은
REVERSE 를 쓰고 시작번호와 끝 번호는 작은 숫자부터 써야 한다는 것입니다.
 
Start 와  end 부분에 꼭 숫자 값이 들어가야 하는 것은 아닙니다. 컬럼이나 커서 등으로 대체할
수 도 있으며 아래의 예로 자세하게 살펴보겠습니다.

 

FOR 반복문 예  1:

위에서 살펴보았던  0 – 5 까지 숫자를 화면에 출력하는 것을  FOR 반복문으로 구현 해보겠습니다.

 

FOR 반복문 예  2:
 
위 예  1번에서 살펴본 숫자를 역순으로 출력합니다.

 

 

 

4) CONTINUE 문  (11g 에서 추가된 기능입니다) : 안씀.

 

 

 

---

* 반복문 연습문제
 
1. For 반복문과 While 반복문을 사용하여 아래 화면과 같이 구구단  2 단을 출력하세요.

 

 

2. For 반복문과 While 반복문을 사용하여 아래 화면과 같이 사용자로부터 출력을 원하는 단 수
를 입력 받은 후 해당 구구단을 출력하세요.