다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Presentation on theme: "다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL"— Presentation transcript:

1 다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL
서진수 저

2 16장 PL/SQL 제어문 익히기 1

3 16. PL/SQL 제어문 익히기 - 조건문과 반복 문으로 나눌 수 있음. - 조건 문 : IF 문 , CASE 문
- 반복 문 : Basic Loop 문 , While 문 – 반복 횟수를 알 수 없을 경우 FOR 문 – 반복 횟수를 지정할 경우 1. 조건 문 (IF 문) : 유형 1. IF~END IF 문장 IF (조 건) THEN 실행 문장; END IF ; 2

4 16. PL/SQL 제어문 익히기 유형 1번의 예: 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 ; 다음 장에 계속… 3

5 16. PL/SQL 제어문 익히기 앞 장에서 계속…. 4 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 문이 시작됩니다. vdname := 'ACCOUNT'; 13 END IF ; -- IF 문이 끝나면 반드시 END IF 로 마쳐야 합니다. 14 IF (vdeptno = 20) THEN vdname := 'RESEARCH'; 16 END IF ; 17 IF (vdeptno = 30) THEN vdname := 'SALES'; 19 END IF ; 20 IF (vdeptno = 40) THEN vdname := 'OPERATIONS'; 22 END IF ; 23 DBMS_OUTPUT.PUT_LINE (vempno||' '||vename||' '||vdeptno||' '||vdname); 24 END ; 25 / 앞 장에서 계속…. 4

6 16. PL/SQL 제어문 익히기 유형 2. IF ~ THEN ~ELSIF ~END IF 문장 ( 조건이 여러 개 일 경우 사용) IF ( 조 건 ) THEN 실행 문장 ; ELSIF ( 조 건 ) THEN END IF ; 예: EMP 테이블에서 EMPNO , ENAME, DEPTNO , DNAME 을 출력하세요. 단 DNAME 의 값은 아래와 같습니다. DEPTNO 가 10 이면 ’ACCOUNT’ , DEPTNO 가 20 이면 ‘RESEARCH’ , DEPTNO 가 30 이면 ‘SALES’ , DEPTNO 가 40 이면 ‘OPERATIONS’ 로 출력하세요 5

7 16. PL/SQL 제어문 익히기 앞 장에서 계속…. 6 12 IF (vdeptno = 10) THEN
vdname := 'ACCOUNT'; 14 ELSIF (vdeptno = 20) THEN vdname := 'RESEARCH'; 16 ELSIF (vdeptno = 30) THEN vdname := 'SALES'; 18 ELSIF (vdeptno = 40) THEN vdname := 'OPERATIONS'; 20 END IF; 21 DBMS_OUTPUT.PUT_LINE (vempno||' '||vename||' '||vdeptno||' '||vdname); 23 END ; 24 / 앞 장에서 계속…. 6

8 16. PL/SQL 제어문 익히기 유형 3. IF~THEN~ELSE~END IF ( 조건이 2개 일 경우 사용)
실행 문장 ; ELSE END IF ; 예 : EMP 테이블에서 사원번호가 7900 번인 사원의 EMPNO , ENAME , COMM 을 출력하되 해당 사원의 COMM 이 0보다 크면 COMM 의 금액을 출력하고, 0 보다 작으면 ‘사원의 보너스는 없습니다’ 라는 문장을 출력하세요. 7

9 16. PL/SQL 제어문 익히기 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 DBMS_OUTPUT.PUT_LINE (vename||' 사원의 보너스는 '||vcomm||'입니다'); 13 ELSE DBMS_OUTPUT.PUT_LINE (vename||' 사원의 보너스는 없습니다'); 15 END IF ; 16 END ; 17 / 8

10 16. PL/SQL 제어문 익히기 < IF 문 연습문제 >
1. 사용자로부터 사원번호를 입력 받아 해당 사원번호의 사원의 보너스가 0 보다 클 경우 해당 사원의 보너스 금액을 출력하고 만약 보너스가 0 보다 작을 경우 ‘보너스가 없습니다’ 라는 문장을 출력하세요( 조건이 2가지 이므로 IF – THEN – ELSE – END IF 문장을 사용하세요). 2. 사 번을 입력 받아 사원(EMP)테이블에서 그 해당 사원의 이름과 연봉, 세금을 출력하세요. (단, 세금은 연봉액수에 따라 5000 만원 이상이면 5%, 4000만원부터 4999 만원까지는 4% , 3000만원부터 3999 만원까지는 3% , 2000만원부터 2999 만원까지는 2% , 나머지는 1% 로 차등 계산하세요) 조건이 여러 가지 이므로 IF – THEN – ELSIF – ELSE - END IF 문장을 사용하세요 9

11 16. PL/SQL 제어문 익히기 2. CASE 문 과 CASE 식 10 CASE [ 조건 ]
WHEN 조건 1 THEN 결과 1 WHEN 조건 2 THEN 결과 2 … WHEN 조건 n THEN 결과 n [ ELSE 기본값 ] END ; 10

12 16. PL/SQL 제어문 익히기 - CASE 문 사용 예 1:
사용자로부터 사원 번호를 입력 받은 후 EMP 테이블에서 EMPNO , ENAME, DEPTNO , DNAME 을 출력하세요. 단 DNAME 의 값은 아래와 같습니다. DEPTNO 가 10 이면 ’ACCOUNT’ , DEPTNO 가 20 이면 ‘RESEARCH’ , DEPTNO 가 30 이면 ‘SALES’ , DEPTNO 가 40 이면 ‘OPERATIONS’ 로 출력하세요 SCOTT> DECLARE v_empno emp.empno%TYPE ; v_ename emp.ename%TYPE ; v_deptno emp.deptno%TYPE ; v_dname varchar2(10) ; 다음 장에 계속…. 11

13 16. PL/SQL 제어문 익히기 앞 장에서 계속…. 12 6 BEGIN
SELECT empno , ename , deptno INTO v_empno , v_ename , v_deptno FROM emp WHERE empno = &empno ; v_dname := CASE v_deptno WHEN 10 THEN 'ACCOUNT' WHEN 20 THEN 'RESEARCH' WHEN 30 THEN 'SALES' WHEN 40 THEN 'OPERATIONS' END ; DBMS_OUTPUT.PUT_LINE (v_empno||' '||v_ename||' '||v_deptno||' '||v_dname); 18 END ; 19 / Enter value for empno: 7900 7900 JAMES 30 SALES PL/SQL procedure successfully completed. 앞 장에서 계속…. 11 v_dname := CASE WHEN v_deptno = 10 THEN 'ACCOUNT' WHEN v_deptno = 20 THEN 'RESEARCH' WHEN v_deptno = 30 THEN 'SALES' WHEN v_deptno = 40 THEN 'OPERATIONS' END ; 12

14 16. PL/SQL 제어문 익히기 - CASE 문 사용 예 2:
EMP 테이블을 사용하여 사용자로부터 사원 번호를 입력 받아 해당 사원의 empno , ename , sal , deptno, 인상후 연봉(up_sal) 을 출력하세요. 단 부서번호가 10 번 부서는 현재 연봉의 10% 인상하고, 부서번호가 20 번 , 30 번 부서는 20 % 인상하고 , 부서번호가 30 번 보다 클 경우 30% 를 인상하세요. SCOTT> DECLARE v_empno emp.empno%TYPE ; v_ename emp.ename%TYPE ; v_sal emp.sal%TYPE ; v_deptno emp.deptno%TYPE ; v_up_sal emp.sal%TYPE ; 다음 장에 계속…. 13

15 16. PL/SQL 제어문 익히기 앞 장에서 계속…. 14 7 BEGIN
SELECT empno , ename , sal , deptno INTO v_empno , v_ename , v_sal , v_deptno FROM emp WHERE empno = &empno ; v_up_sal := CASE WHEN v_deptno = 10 THEN v_sal * 1.1 WHEN v_deptno IN (20,30) THEN v_sal*1.2 WHEN v_deptno > 30 THEN v_sal*1.3 ELSE v_sal 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 사원번호 이름 급여 부서번호 인상후급여 JAMES 앞 장에서 계속…. 14

16 16. PL/SQL 제어문 익히기 3. 반복문 반복문은 반복 횟수를 알 수 없는 경우에 사용하는 BASIC LOOP 문과 WHILE 문이 있고 반복횟수를 지정하는 FOR 문이 있습니다 1) BASIC LOOP 반복문 LOOP PL/SQL 문장 ; EXIT [ 조건 ] ; END LOOP ; 문장을 먼저 수행 후 조건을 확인함 15

17 16. PL/SQL 제어문 익히기 BASIC LOOP 문 사용 예 1 :

18 16. PL/SQL 제어문 익히기 2) WHILE 반복문 조건을 먼저 확인 후 문장을 수행함
While 조건 LOOP PL/SQL 문장 END LOOP ; 조건을 먼저 확인 후 문장을 수행함 BASIC LOOP 문은 나중에 조건을 검색하지만 WHILE 문은 시작부터 조건을 먼저 검사한 후 PL/SQL 문장을 수행하게 됩니다. 즉 BASIC LOOP 문은 조건이 틀려도 PL/SQL 문장이 1회는 실행이 되지만 WHILE 문은 아예 실행이 되지 않습니다. 17

19 16. PL/SQL 제어문 익히기 WHILE 반복문 사용 예 1:
18

20 16. PL/SQL 제어문 익히기 < BASIC LOOP 문과 WHILE 반복문 연습 문제>
BASIC LOOP 문과 WHILE 문을 사용하여 아래의 결과처럼 나오도록 각각 작성하세요. 출력 결과 화면 5 4 3 2 1 19

21 16. PL/SQL 제어문 익히기 20

22 16. PL/SQL 제어문 익히기 3) FOR 반복문 – 반복 횟수를 지정 가능함
FOR counter IN [REVERSE] start . . end LOOP Statement1 ; Statement2 ; … END LOOP ; 위 문법에서 counter 는 반복을 카운트 할 변수입니다. 원래 PL/SQL 에서는 사용할 변수를 사용하기 전에 미리 DECLARE 부분에서 선언해야 하지만 FOR 반복문 내에서 사용할 변수는 미리 선언을 하지 않아도 사용 가능합니다. 그리고 IN 다음에 start 부분에 시작번호를 쓰고 .. (점 두 개) 뒤에 end 부분에 끝 숫자를 적으시면 됩니다. 만약 역순으로 반복하려면 IN 다음에 REVERSE 를 쓰고 반복할 횟수를 쓰면 되는데 주의 사항은 REVERSE 를 쓰고 시작번호와 끝 번호는 작은 숫자부터 써야 한다는 것입니다. 21

23 16. PL/SQL 제어문 익히기 - FOR 반복문 예 1: 0 – 5 까지 숫자를 화면에 출력하세요. 22

24 16. PL/SQL 제어문 익히기 - FOR 반복문 예 2: 0 – 5 까지의 숫자를 역순으로 출력하세요. 23

25 16. PL/SQL 제어문 익히기 - For 반복문 예제 3
사원번호를 입력 받아 사원 이름의 글자수 만큼 화면에 * 를 찍는 문장을 작성하세요 출력 예 Enter value for eno: 7900 * ** *** **** ***** 24

26 16. PL/SQL 제어문 익히기 4) CONTINUE 문 (11g 에서 추가된 기능입니다) 25

27 16. PL/SQL 제어문 익히기 26 1. total 변수를 0 으로 초기화합니다. (2번 라인)
3. 1 을 출력합니다 (6번 라인 , 결과화면의 첫 번째 출력결과) 4. CONTINUE 문의 조건을 확인합니다 (i 가 5보다 큰가?) 5. 아니라고 판단되면 total 변수에 있는 값에 i 의 값을 더합니다 (8번 라인) 6. 더해진 값을 Out total 이라는 내용과 함께 화면에 출력합니다 (9번 라인) 기존 total 에 1이 있었고 i 변수에도 1이므로 화면에 2가 출력됩니다. 7. 다시 5번 라인으로 돌아가서 total 변수 값 2+1 을 하고 화면에 3을 출력합니다 (6번라인) 8 . CONTINUE 문의 조건을 다시 확인한 후 (7번라인) total 변수값 3 + 2(i 변수값) 을 계산해 화면에 5를 출력합니다 (출력화면 4번째 줄) 9. 위와 같은 방식으로 계속 루프를 돌다가 i 값이 5가 되면 CONTINUE 조건에 걸리게 되므로 8번, 9번 줄의 연산을 하지 않게 됩니다. 그래서 출력 화면을 보면 Total 값만 나오고 Out total 값은 나오지 않게 됩니다. 26