12. PL/SQL

ㅁ PL/SQL은 데이터베이스 객체같은 것은 아니고 문법이다.

 

 

ㅁ < PL/SQL >

- Procedure Language extension to SQL

- 오라클 내에 내장되어 있는 절차적 언어

- 변수의 정의, 조건/반복처리

 

 

ㅁ PL/SQL 구조

- 선언부 : DELCARE로 시작. 변수 및 상수를 선언 및 초기화 가능.

- 실행부 : BEGIN으로 시작. 실행부는 필수로 작성해야 한다. SQL문 또는 제어문(조건/반복) 로직 기술 가능.

- 예외처리부 : EXCEPTION으로 시작. 예외발생시 실행할 구문을 미리 정의.

 

 

ㅁ 화면에 문구 출력

 

 

ㅇ 출력결과를 보기 위해서 SERVEROUTPUT 옵션을 ON으로 설정해야 한다. (기본값은 OFF이다)

 

 

SET SERVEROUTPUT ON;

 

 

 

ㅇ BEGIN~END; 는 필수다. 그리고 "/"까지 넣어줘야 한다. (EXCEPTION은 END 전에 넣는다)

 

 

BEGIN

 

    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');

 

END;

/

 

- PL/SQL 문 입력이 끝났음을 SQL *Plus 에 알리기 위해, 마지막에 슬래시(/) 를 입력해야 한다. 

- 슬래시 자체가 한 행에 나와야 한다

- PL/SQL의 실행부(혹은 예외처리부)까지 모두 작성한 후에는 슬래시를 이용해 프로그램 작성이 끝났음을 알리고 실행한다.

- PL/SQL문 작성을 마치고 실행하기 위해 마지막에 슬래시(/)를 사용.

 

 

 

 

ㅁ DECLARE 선언부

- 변수 및 상수를 선언, 초기화하는 공간

- 일반타입 변수, 레퍼런스타입 변수, ROW타입 변수 3가지를 만들 수 있다. 

 

(1) 일반타입 변수 선언 및 초기화

 

변수명 [CONSTANT] 데이터타입 [ := 값 ];

 

 

- CONSTANT를 붙여서 상수로 선언할 수 있다.

 

 

 

- 선언부에서 선언된 변수는 실행부에서 사용 가능.

- 쌍따옴표 쓰면 안 됨. 오라클은.

- 자바는 '+'로 문자열을 연결하지만, 오라클은 연결연산자( || )를 사용한다.

 

 

 

ㅇ & 사용

- SQLPlus에서 & 기호로 시작하는 변수는 사용자에게 값을 입력받는 매개변수(parameter)로 사용될 수 있습니다.

 

 

 

- EID와 ENAME에 데이터를 입력하면 그 값이 출력된다.

 

 

 

 

(2) 레퍼런스 타입 변수 선언 및 초기화

- 특정 테이블의 특정 컬럼의 데이터타입을 참조해서 동일하게 지정한다.

 

변수명 테이블명.컬럼명%TYPE;

 

- %TYPE은 이 컬럼의 데이터타입과 맞춰주겠다는 뜻.

- 선언한 테이블의 컬럼타입과 같은 타입으로 설정

 

 

 

 

- EMP_ID는 문자 타입이다. 물론 그냥 숫자를 써도 자동 형변환되긴 하지만 타입을 맞춰주는 것이 좋다.

- 사용자에게 입력창이 뜨고, 거기에 200을 입력하면 저렇게 나온다.

 

 

 

ㅇ예제

 

레퍼런스타입 변수로 EID, ENAME, JCODE, SAL, DTITLE을 선언하고

각 자료형을 EMPLOYEE(EMP_ID, EMP_NAME, JOB_CODE, SALARY), DEPARTMENT(DEPT_TITLE)로 참조.

 

사용자가 입력한 사번의 사원 조회

사번, 사원명, 직급코드, 급여, 부서명을 조회한 후 각 변수에 담아 출력.

 

 

- SELECT INTO로 넣을 때는 데이터가 없어도 안되고, 여러 행이어도 안 됨. 한 행이어야 한다. 

 

 

- DBMS_OUTPUT.PUT_LINE( ) 안에 컴마로 여러 변수 출력 불가능.

DBMS_OUTPUT.PUT_LINE 함수는 인자로 하나의 문자열만을 받을 수 있습니다.

여러 개의 변수를 출력하려면 각 변수를 문자열로 변환하여 연결해야 합니다.

DBMS_OUTPUT.PUT_LINE(EID || ENAME || SAL || JCODE || DTITLE);

 

 

 

(3) ROW 타입 변수 선언

- 테이블의 한 행에 대한 모든 컬럼값을 한번에 담을 수 있는 변수

 

 

변수명 테이블명%ROWTYPE;

 

 

 

- E 라는 변수에 13개의 컬럼값이 통째로 담긴다.
- E라는 변수 통째로 출력 가능하냐 .불가능. 오류남.

E에 담겨있는 값중 어떤 컬럼의 값을 가져올 걸지 지정해줘야 한다. 

 

- 보너스가 NULL이라 안 나옴.

- DBMS_OUTPUT.PUT_LINE('보너스: ' || NVL(E.BONUS, 0)); 

이러면 NULL이면 0으로 나옴.

 

 

 

====================================================================================

 

 

 

ㅁ BEGIN 실행부

- 실행할 SQL문, 제어문(조건문/반복문) 등의 로직 기술 가능.

 

 

(1) 조건문 

 

 

IF 조건식1

    THEN 실행내용1

 

ELSIF 조건식2

    THEN 실행내용2

 

ELSE 실행내용N

 

 

END IF;

 

 

- ELSIF 부분과 ELSE 부분은 생략 가능.

 

 

 

ㅇ 사번을 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스율(%)을 출력하기.

단, 보너스를 받지 않는 사원은 보너스를 출력전 '보너스를 지급받지 않는 사원입니다' 출력

 

 

 

- 만약 SELECT절에서 NVL(BONUS, 0)으로 작성했다면, IF 조건식을 IF BONUS = 0으로 해야 함.

 

 

 

ㅇ 예제

 

레퍼런스타입 변수(EID, ENAME, DTITLE, NCODE) 선언

일반타입 변수(TEAM 문자타입) 선언 <- '국내팀' 또는 '해외팀' 대입 예정

 

NCODE는 NATIONAL코드. LOCATION 테이블, NATIONAL 테이블에 있다.

 

사용자가 입력한 사번의 사원을 조회

사번, 이름, 부서명, 근무국가코드 조회 후 레퍼런스타입 변수에 대입

이때 NCODE값이 'KO'일 경우 TEAM변수에 '국내팀' 대입, 그게 아니면 '해외팀' 대입.

 

결과 출력 (사번, 이름, 부서명, 소속)

 

 

 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
    TEAM VARCHAR2(9);
    
BEGIN

    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
    INTO EID, ENAME, DTITLE, NCODE
    FROM EMPLOYEE
    JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
    JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
    WHERE EMP_ID = '&사번'
    ;

     IF NCODE = 'KO'
        THEN TEAM := '국내팀';
     ELSE 
        TEAM := '해외팀';
     END IF;   

     DBMS_OUTPUT.PUT_LINE(EID);
     DBMS_OUTPUT.PUT_LINE(ENAME);
     DBMS_OUTPUT.PUT_LINE(DTITLE);
     DBMS_OUTPUT.PUT_LINE(NCODE);

END;
/
    

- THEN 써야 함. 또 그곳에 세미콜론 써야 함.

- PL/SQL에서 변수에 대입할 때는 := 써야 함.

 

 

 

ㅇ 예제

 

사용자에게 입력받은 사번의 사원의 급여를 조회해서 SAL 변수에 대입.

급여가 500만 이상이면 '고급'

급여가 300만 이상이면 '중급'

급여가 300만 미만이면 '초급'

 

'해당 사원의 급여등급은 XX입니다'

 

 

DECLARE 
    SAL EMPLOYEE.SALARY%TYPE;
    GRADE VARCHAR2(10);
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
 
    IF SAL >= 5000000
        THEN GRADE := '고급';
    ELSIF SAL >= 3000000
        THEN GRADE := '중급';
    ELSE 
        GRADE := '초급';
    END IF;    
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다');
    
END;
/

 

 

 

(2) 조건문 (CASE WHEN THEN END)

 

 

CASE 비교대상자

    WHEN 비교값1 THEN 결과값1

    WHEN 비교값2 THEN 결과값2

    ....

    ELSE 결과값N

END;

 

 

ㅇ 예시

 

DECLARE
    EMP EMPLOYEE%ROWTYPE;
    GEN CHAR(6);
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    GEN := CASE SUBSTR(EMP.EMP_NO, 8, 1) 
                      WHEN '1' THEN '남자'
                      WHEN '2' THEN '여자'
                      WHEN '3' THEN '남자'
                      WHEN '4' THEN '여자'
                 END;    

    DBMS_OUTPUT.PUT_LINE(GEN || '입니다');    

END;
/

 

 

 

(3) 반복문(BASIC LOOP문)

 

 

LOOP

              반복적으로 실행할 구문;

 

              = 반복문을 빠져나갈 수 있는 구문(어떤 경우에 반복 종료할건지)

 

END LOOP;

 

- 기본적으로 BASIC LOOP문은 무한반복이다.

 

- 반복문을 빠져나가는 구문 2가지.

 

i) IF 조건식

      THEN EXIT;

   END IF;

 

ii) EXIT WHEN 조건식;

 

 

ㅇ 예시

 

DECLARE
    I NUMBER := 1;
BEGIN
    LOOP
         DBMS_OUTPUT.PUT_LINE(I);
         I := I+1;
         
         IF I=6
            THEN EXIT;
         END IF;   
         
    END LOOP;
END;
/

 

 

 

(4) 반복문 (FOR LOOP문)

 

 

FOR 변수 IN [REVERSE] 초기값 .. 최종값

LOOP  

       반복적으로 실행할 구문;

END LOOP;

 

 

- 변수가 초기값부터 최종값까지 1씩 증가함.

- IN REVERSE로 하면 최종값부터 초기값까지 1씩 감소함.

 

 

 

ㅇ 예시

 

BEGIN
    FOR I IN 1..5
    LOOP
         DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;

END;
/

- 얘는 DECLARE도 필요없다. 변수를 선언할 필요도 없음.

- .이 두 개다.

 

 

 

ㅇ 예시

 

 

DROP TABLE TEST;

 

CREATE TABLE TEST ( 
    TNO NUMBER PRIMARY KEY,
    TDATE DATE
);

CREATE SEQUENCE SEQ_TNO
    MAXVALUE 1000
    NOCYCLE
    NOCACHE;

 

- TNO에 절대 겹치지 않는 숫자가 들어가게 할 것.

 

 

BEGIN
    FOR I IN 1..100
    LOOP
        INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDATE-I);
    END LOOP;
END;
/

SELECT *
FROM TEST;

 

- 샘플 데이터 넣을 때 이렇게 반복문 활용 가능하다.

 

 

- FOR LOOP문을 한번 더 실행하고 조회하면 시퀀스라 그 다음 값(101)이 나옴.

 

 

 

 

(5) 반복문 (WHILE LOOP)

 

 

WHILE 반복문이수행될조건

LOOP

    반복적으로 실행할 구문;

END LOOP;

 

- 반복문이수행될조건이 FALSE가 되면 반복이 종료됨.

 

 

ㅇ 예시

 

DECLARE 
    I NUMBER := 1;
BEGIN

    WHILE I<6
    LOOP
         DBMS_OUTPUT.PUT_LINE(I);
         I := I+1;
    END LOOP;

END;
/

 

 

===============================================================================

 

 

ㅁ 예외처리부

 

 

EXCEPTION 

        WHEN 예외명1 THEN 예외처리구문1;

        WHEN 예외명2 THEN 예외처리구문2;

        ...

        WHEN OTHERS THEN 예외처리구문N;

 

 

 

※ 시스템예외 (오라클에서 미리 정의해둔 예외. 엄청 많음)

- NO_DATA_FOUND : SELECT한 결과가 한 행도 없을 경우

- TOO_MANY_ROWS : SELECT한 결과가 여러 행일 경우

- ZERO_DIVIDE : 0으로 나눌 때

- DUP_VAL_ON_INDEX : UNIQUE 제약조건에 위배되었을 경우

 

 

 

ㅇ ZERO_DIVIDE 예시

 

- 예외처리부를 넣지 않았을 때.

 

 

- 예외명을 모르겠다면 OTHERS를 써도 됨. 

OTHERS로 써놓고 0을 입력해도 똑같이 나옴.

 

 

 

ㅇ UNIQUE 제약조건 위배 예시 ( DUP_VAL_ON_INDEX  )

 

BEGIN
    UPDATE EMPLOYEE
    SET EMP_ID = '&변경할사번'
    WHERE EMP_NAME = '노옹철';
END;
/
    

- 999 입력하면 잘 됨. 999 사번은 존재하지 않음.

200 입력하면 "unique constraint (%s.%s) violated" 뜸. 200은 이미 존재함.

 

 

- 이미 존재하는 사번 입력시 '이미 존재하는 사번입니다.'가 출력됨.

 

 

 

ㅇ NO_DATA_FOUND, TOO_MANY_FOWS 예시

 

 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME
    INTO EID, ENAME
    FROM EMPLOYEE
    WHERE MANAGER_ID = '&사수사번';

 

    DBMS_OUTPUT.PUT_LINE(EID);   
    DBMS_OUTPUT.PUT_LINE(ENAME);   

 

EXCEPTION
    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('너무 많은 행이 조회되었습니다.');   
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다.');     

END;
/

 

- 발생될만한 예외는 두 가지. 데이터가 없거나 두 개이상이거나. 무조건 한 개여야 함.

- "no data found", "exact fetch returns more than requested number of rows(여러 행인 경우)"