21.2.16(화) DB/PL(SQL)(2), OBJECT(TRIGGER)

민국·2021년 2월 18일
db
0

2. BEGIN(조건문)

1) IF 조건식 THEN 실행내용 END IF;

<예시>
-- 사번 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스율(%) 출력
-- 단, 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다' 출력

DECLARE
    EID EMPLOYEE.EMP_ID&TYPE;
    ENAME EMPLOYEE.EMP_NAME&TYPE;
    SALARY EMPLOYEE.SALARY&TTYPE;
    BONUS EMPLOYEE.BONUS&TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번: ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름: ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여: ' || SALARY);
    
    IF (BONUS = 0)
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('보너스율: ' || BONUS *100 || '%');
END;
/
SET SERVEROUTPUT ON;

2) IF 조건식1 THEN 실행내용1 ELSIF 조건식2 THEN 실행내용2... (ELSE 실행내용N) END IF;

<예시>
-- 국내팀, 해외팀 구분
DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE; 
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
    TEAM VARCHAR2(10);
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('소속: ' || TEAM);
END;

3. 반복문

1) BASIC LOOP문

[표현법]
LOOP
반복적으로 실행할 구문
END LOOP;
=> 반복문을 빠져나갈 수 있는 구문 (2가지)
----- 1) IF 조건식 THEN EXIT;
----- 2) EXIT WHEN 조건식;

-- 1~5 까지 순차적으로 1씩 증가하는 값 출력

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

2) FOR LOOP문

[표현법]
FOR 변수 IN(REVERSE) 초기값... 최종값
LOOP
반복적으로 실행할 구문
END LOPP;

-- 1~5 순차적   
BEGIN 
    FOR I IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/
-- 5~1 반대로
BEGIN 
    FOR I IN REVERSE 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

-- 반복문을 이용한 데이터 삽입

CREATE TABLE TEST2(
    TEST_NO NUMBER PRIMARY KEY,
    TEST_DATE DATE
);

CREATE SEQUENCE SEQ_TEST2
START WITH 100;

BEGIN 
    FOR I IN 1..50
    LOOP   
        INSERT INTO TEST2 VALUES (SEQ_TEST2.NEXTVAL, SYSDATE);
    END LOOP;
END;
/

3) WHILE LOOP문

[표현법]
WHILE 반복이 수행될 조건
LOOP
반복적으로 수행될 구문
END LOOP;

<예시>
-- 6보다 작을 때 까지 반복수행
DECLARE 
    I NUMBER := 1;

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

3. 예외처리부

: 예외(EXCEPTION) - 실행 중 발생하는 오류
= 자바의 TRY CATCH 느낌

[표현법]
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 제약조건에 위배되었을 경우
    ...

(1) 나누기 연산시 0 오류

-- 사용자가 입력한 수로 나눗셈 연산한 결과 출력
DECLARE 
    RESULT NUMBER;
BEGIN
    RESULT := 10 / &숫자;
    DBMS_OUTPUT.PUT_LINE('결과 : ' || RESULT);
EXCEPTION
   -- WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌 순 없습니다.');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌 순 없습니다.');
END;
/

(2) UNIQUE 제약조건 위배시

-- 202번인 노옹철 사원의 사번을 바꿀때 중복되는 것을 피하고자 한다면?
BEGIN 
	UPDATE EMPLOYEE
    SET EMP_ID = '&변경할 사번'
    WHERE EMP_NAME = '노옹철';
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
END;
/

(3) NO_DATA_FOUND , TOO_MANY_ROWS

<예시>
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 NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('조회결과가 없습니다');
    --WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('너무 많은 행이 조회되었습니다');
    WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE('조회결과가 없거나 너무 많습니다'); --> 한꺼번에 출력
END;
/

OBJECT(TRIGGER)

: 내가 지정한 테이블에 INSERT, UPDATE, DELETE 와 같은 DML문에 의해 변경되었을 경우
(테이블에 이벤트 발생했을 경우) 자동(묵시적)으로 매번 실행될 내용을 정의해둘 수 있는 객체

활용 예시)

  • 입출고에 대한 데이터가 기록(INSERT)될 때 마다 해당 상품에 대한 재고수량 매번 수정(UPDATE)해야될 경우
  • 회원탈퇴시 기존의 회원테이블에 데이터 DELETE 후 탈퇴된 회원들만 따로 보관하는 테이블 INSERT 시켜야 될 경우

트리거 종류

1) SQL문의 실행시기에 따른 분류

  • BEFORE TRIGGER : 내가 지정한 테이블에 이벤트가 발생되기 전 트리거 실행
  • AFTER TRIGGER : 내가 지정한 테이블에 이벤트가 발생된 후 트리거 실행

2) SQL문에 의해 영향을 받는 각 행에 따른 분류

  • STATEMENT TRIGGER(문장 트리거) :
  • ROW TRIGGER(행 트리거) : 해당 SQL문 실행될 때마다 매번 트리거 실행
    트리거 생성 구문 작성시 FOR EACH ROW 옵션 기술해야됨
    => :OLD : BEFORE UPDATE(수정 전 자료), BEFORE DELELTE(삭제 전 자료)
    => :NEW : AFTER INSERT(입력 후 자료)

트리거 생성구문

[표현법]
CREATE (OR REPLACE) TRIGGER 트리거명(TRG_)
( BEFORE | AFTER ) , ( INSERT | UPDATE | DELETE ) ON 테이블명
(FOR EACH ROW) --> 딱 한 행만
DECLARE
변수선언;
BEGIN
해당 위에 지정된 이벤트 발생 시 자동(묵시적)으로 실행할 구문;
EXCEPTION
예외처리구문;
END;
/

<예시>
-- EMP 테이블에 새로운 행이 INSERT 될 때마다 자동으로 메시지 출력하는 트리거정의
CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT ON EMPLOYEE
BEGIN 
   DBMS_OUTPUT.PUT_LINE('신입사원님 환영합니다');
END;
/

-- TB_PRODETAIL 테이블에 INSERT 후 
-- 자동으로 TB_PRODUCT 테이블에 UPDATE 되게끔 트리거 정의

-- NEW: 활용, FOR EACH ROW 활용, 조건문 활용
CREATE OR REPLACE TRIGGER TRG_02
AFTER INSERT ON TB_PRODETAIL
FOR EACH ROW 
BEGIN 
   IF (:NEW.STATUS = '입고')
       THEN 
           UPDATE TB_PRODUCT
           SET STOCK = STOCK + :NEW.AMOUNT
           WHERE PCODE = :NEW.PCODE;
   END IF;
   IF (:NEW.STATUS = '출고')
       THEN 
           UPDATE TB_PRODUCT
           SET STOCK = STOCK - :NEW.AMOUNT
           WHERE PCODE = :NEW.PCODE;
   END IF;
END;
/
profile
민국
새싹개발자
이전 포스트

21.2.16(화) DB/TCL(2), PL(SQL)(1)

다음 포스트

2/19(금) JDBC

0개의 댓글