[PL/SQL] PL/SQL 기본구조

PL/SQL

구조적인 SQL언어와 달리 SQL를 절차적인 방식에 의해 사용하도록 설계한 언어.
PL/SQL의 단위는 블록이며 하나의 블록은 아래와 같은 세부분으로 나뉜다.

(1) 선언부 : 변수나 상수를 선언하는 부분
(2) 실행부 : 실제 처리할 로직을 담당하는 부분
(3) 예외처리부 : 실행부에서 처리하던 중 발생하는 각종 오류에 대한 처리 부분

1. PL/SQL의 구조

DECLARE            -- 선언부
변수선언
BEGIN              -- 실행부
실행구문
EXCEPTION          -- 예외처리
예외처리구문	
END;
/  -- PL/SQL실행


예1) 
SET SERVEROUTPUT ON
DECLARE
    I_MSG VARCHAR2(20);
BEGIN
    I_MSG :='HELLO';
    DBMS_OUTPUT.PUT_LINE('MSG:' || I_MSG);
END;
/
결과
MSG:HELLO

예2)
DECLARE
	NUM1 NUMBER(4);
    NUM2 NUMBER(4);
    NUM3 NUMBER(4);
BEGIN
	NUM1 := &NUM1; --데이터 입력받기
    NUM2 := &NUM2;
    NUM3 := NUM1+NUM2;
    DBMS_OUTPUT.PUT_LINE(NUM1||'+'||NUM2||'='||NUM3);
END;
/
결과
1+3=4

2. IF ~ ELSE 문

형식1)
IF 조건식 THEN
실행문;
ELSE
실행문;
END IF;

형식2)
IF 조건식 THEN
실행문;
ELSIF 조건식 THEN
실행문;
ELSIF 조건식 THEN
실행문;
..
END IF;

예1) 정수 입력받아 짝수인지 홀수인지 판별
DECLARE
	NUM1 NUMBER(5);
BEGIN
	NUM1 := &NUM1; --데이터 입력받기
	IF NUM1 MOD 2 = 0 THEN
	    DBMS_OUTPUT.PUT_LINE(NUM1||'은 짝수');
    ELSE
    	DBMS_OUTPUT.PUT_LINE(NUM1||'은 홀수');
	END IF;
END;
/

Q1) 두 정수를 입력받아 두 수중 큰 수 구하기
DECLARE
	NUM1 NUMBER(5);
    NUM2 NUMBER(5);
BEGIN
	NUM1 := &NUM1; --데이터 입력받기
    NUM2 := &NUM2;
	IF NUM1>NUM2 THEN
	    DBMS_OUTPUT.PUT_LINE('둘 중 더 큰 수는 '||NUM1);
    ELSE
    	DBMS_OUTPUT.PUT_LINE('둘 중 더 큰 수는 '||NUM2);
	END IF;
END;
/

Q2) 두 점수를 입력받아 평균이 80이상이면 '합격' 아니면 '불합격' 출력하기
DECLARE
	NUM1 NUMBER(5);
    NUM2 NUMBER(5);
    NUM3 NUMBER(5);
BEGIN
	NUM1 := &NUM1;
    NUM2 := &NUM2;
    NUM3 := (NUM1+NUM2)/2;
    --평균을 나타내는 변수이름을 AVG 라고 할수 없음
    --ORACLE이 갖고 있는 함수 이름이라서 변수로 지정 못함
	IF NUM3>=80 THEN
	    DBMS_OUTPUT.PUT_LINE('합격');
    ELSE
    	DBMS_OUTPUT.PUT_LINE('불합격');
	END IF;
END;
/

3. FOR문

-형식
FOR 변수 IN 초기값..마지막값 LOOP
반복수행할 문장;
END LOOP;

예1)
SET SERVEROUTPUT ON
BEGIN
	FOR I IN 1..100 LOOP
    	DBMS_OUTPUT.PUT(I||' ');
        IF I MOD 10 = 0 THEN
        	DBMS_OUTPUT.PUT_LINE(' ');
        END IF;
    END LOOP;
END;
/
결과)
1 2 3 4 5 6 7 8 9 10  
11 12 13 14 15 16 17 18 19 20  
21 22 23 24 25 26 27 28 29 30  
31 32 33 34 35 36 37 38 39 40  
41 42 43 44 45 46 47 48 49 50  
51 52 53 54 55 56 57 58 59 60  
61 62 63 64 65 66 67 68 69 70  
71 72 73 74 75 76 77 78 79 80  
81 82 83 84 85 86 87 88 89 90  
91 92 93 94 95 96 97 98 99 100 

예2)
BEGIN
	FOR I IN REVERSE 1..100 LOOP --REVERSE 역순으로
        	DBMS_OUTPUT.PUT(I||' ');
        IF I MOD 10 = 1 THEN
        	DBMS_OUTPUT.PUT_LINE(' ');
        END IF;
    END LOOP;
END;
/

결과)
100 99 98 97 96 95 94 93 92 91  
90 89 88 87 86 85 84 83 82 81  
80 79 78 77 76 75 74 73 72 71  
70 69 68 67 66 65 64 63 62 61  
60 59 58 57 56 55 54 53 52 51  
50 49 48 47 46 45 44 43 42 41  
40 39 38 37 36 35 34 33 32 31  
30 29 28 27 26 25 24 23 22 21  
20 19 18 17 16 15 14 13 12 11  
10 9 8 7 6 5 4 3 2 1  
    

Q1) 1부터 100까지 수 중 3의 배수출력하고 3의 배수합 구해서 출력하기
SET SERVEROUTPUT ON
DECLARE
NUM1 NUMBER(5):=0; -- 0으로 초기화시켜야함!!!!!
BEGIN
	FOR I IN 1..100 LOOP
        IF I MOD 3 = 0 THEN
        	DBMS_OUTPUT.PUT(I||' ');
            NUM1 := NUM1+I; -- 얘가 실행안되는중 =>해결. NUM초기화 안함
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('총합='||NUM1);
END;
/

결과)
3 6 9 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75 78 81 84 87 90 93 96 99  
총합=1683


Q2) 단 입력받아 구구단 출력하기
DECLARE
	DAN NUMBER(5);
BEGIN
	DAN := &NUM1;
    DBMS_OUTPUT.PUT(DAN||'단 ');
        FOR I IN 1..9 LOOP
        	DBMS_OUTPUT.PUT(DAN||'*'||I||'='||DAN*I||' ');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(' ');
END;
/
3단 3*1=3 3*2=6 3*3=9 3*4=12 3*5=15 3*6=18 3*7=21 3*8=24 3*9=27  
-- 3입력시 3단 전체가 출력됨

	
	
Q3) 구구단 출력하기(2단부터 9단까지)
	
2단 2*1=2 2*2=4 ........   2*9=18
3단 3*1=3 ..

..
9단 9*1=9 9*2=18 .......   9*9=81

	
BEGIN
	FOR DAN IN 2..9 LOOP
    DBMS_OUTPUT.PUT(DAN||'단 ');
        FOR I IN 1..9 LOOP
        	DBMS_OUTPUT.PUT(DAN||'*'||I||'='||DAN*I||' ');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(' ');
    END LOOP;
END;
/

- EXIT : FOR문을 강제로 빠져나가기
        
예2) 정수입력받아 소수 판별하기

4. INTO : SELECT로 조회된 컬럼값을 변수에 저장

예1) 사원번호가 7369인 사원의 보너스 구하기
부서번호가 10번이면 급여의 10%
부서번호가 20번이면 급여의 20%
부서번호가 30번이면 급여의 30%

DECLARE
    ENO NUMBER(4); --사원번호
    DNO NUMBER(4); --부서번호
    ESAL NUMBER(7,2); --사원급여
    BONUS NUMBER(7,2); --보너스
BEGIN
    SELECT EMPNO,DEPTNO,SAL INTO ENO,DNO,ESAL
    FROM EMP WHERE EMPNO=7369;
    IF DNO=10 THEN
        BONUS := ESAL*0.1;
    ELSIF DNO=20 THEN
        BONUS := ESAL*0.2;
    ELSIF DNO=30 THEN
        BONUS := ESAL*0.3;
    ELSE
        BONUS:=0;
    END IF;
    DBMS_OUTPUT.PUT_LINE('사원번호:'||ENO||'부서번호:'||DNO||'급여:'||ESAL||'보너스:'||BONUS);
END;
/

결과
사원번호:7369부서번호:20급여:800보너스:160
   
Q1) 7369사원의 급여가 800이상이면 급여의 50%,2000이상이면 급여의 30%,
3000이상이면 급여의 10%를 인상하세요.
DECLARE
    CSAL NUMBER(7,2); --기존급여
    USAL NUMBER(7,2); --인상된 급여
BEGIN
    SELECT SAL INTO CSAL
    FROM EMP WHERE EMPNO=7369;
    IF CSAL>=800 THEN
        USAL := CSAL*1.5;
    ELSIF CSAL>=2000 THEN
        USAL := CSAL*1.3;
    ELSIF CSAL>=3000 THEN
        USAL := CSAL*1.1;
    ELSE
    	USAL := CSAL;
    END IF;
    DBMS_OUTPUT.PUT_LINE('사원번호 7369번의 인상된 급여:'||USAL);
END;
/
결과
사원번호 7369번의 인상된 급여:1200

5. BASIC LOOP

형식1)
LOOP
반복문;
EXIT WHEN 루프를 빠져나갈 조건절;
END LOOP;

예시)
DECLARE
    TOT NUMBER(5):=0;
    I NUMBER(5):=0;
BEGIN
    LOOP
        I:=I+1;
        TOT := TOT + I;
        EXIT WHEN I>=100;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1부터 100까지 합: '||TOT);
END;
/
결과
1부터 100까지 합: 5050

형식2)
WHILE 조건식 LOOP
반복실행할 구문;
..
END LOOP;

Q1) 단 입력받아 구구단 출력해 보세요.

DECLARE
    DAN NUMBER(3):=&DAN;
    I NUMBER(3):=1;
BEGIN
    DBMS_OUTPUT.PUT_LINE(DAN||'단');
    WHILE I<=9 LOOP
        DBMS_OUTPUT.PUT_LINE(DAN||'*'||I||'='||DAN*I);
        I := I+1;
    END LOOP; 
END;
/

결과
4단
4*1=4
4*2=8
4*3=12
4*4=16
4*5=20
4*6=24
4*7=28
4*8=32
4*9=36