0210 오라클 DB 입문 - 커서와 예외처리, 트리거

1 커서와 예외 처리

1. 특정열을 처리하는 커서

0) 정의

 

 

1) 파라미터 사용하기 

--파라미터를 사용하는 커서
DECLARE
   -- 커서 데이터를 입력할 변수 선언
   V_DEPT_ROW DEPT%ROWTYPE;
   -- 명시적 커서 선언(Declaration)
   CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS
      SELECT DEPTNO, DNAME, LOC
        FROM DEPT
       WHERE DEPTNO = p_deptno;
BEGIN
   -- 10번 부서 처리를 위해 커서 사용
   OPEN c1 (10);
      LOOP
         FETCH c1 INTO V_DEPT_ROW;
         EXIT WHEN c1%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('10번 부서 - DEPTNO : ' || V_DEPT_ROW.DEPTNO
                                     || ', DNAME : ' || V_DEPT_ROW.DNAME
                                     || ', LOC : ' || V_DEPT_ROW.LOC);
      END LOOP;
   CLOSE c1;
   -- 20번 부서 처리를 위해 커서 사용
   OPEN c1 (20);
      LOOP
         FETCH c1 INTO V_DEPT_ROW;
         EXIT WHEN c1%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('20번 부서 - DEPTNO : ' || V_DEPT_ROW.DEPTNO
                                     || ', DNAME : ' || V_DEPT_ROW.DNAME
                                     || ', LOC : ' || V_DEPT_ROW.LOC);
      END LOOP;
   CLOSE c1;
END;
/
--

- 경우에 따라 다른 수행 값을 입력하고 싶을 때 사용.

- 부서번호를 입력해서 돌린다는 소리 (2자리의 숫자가 되는)

- where 에 존재하는 deptno은 명시적 커서 선언에서 만들어진 p_deptno와 조인을 시켜준다

- into에 존재하는 공통 변수는 v_dept_row

- 링크를 걸고 태그를 걸어서 입력해주는 느낌

 

2) 파라미터를 입력 받아서 출력하기

--커서에 사용할 파라미터를 입력할 수 있음
DECLARE
   -- 사용자가 입력한 부서 번호를 저장하는 변수선언
   v_deptno DEPT.DEPTNO%TYPE;
   -- 명시적 커서 선언(Declaration)
   CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS
      SELECT DEPTNO, DNAME, LOC
        FROM DEPT
       WHERE DEPTNO = p_deptno;
BEGIN
   -- INPUT_DEPTNO에 부서 번호 입력받고 v_deptno에 대입
   v_deptno := &INPUT_DEPTNO;
   -- 커서 FOR LOOP 시작. c1 커서에 v_deptno를 대입
   FOR c1_rec IN c1(v_deptno) LOOP
      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO
                      || ', DNAME : ' || c1_rec.DNAME
                      || ', LOC : ' || c1_rec.LOC);
   END LOOP;
END;
/

입력하라는 창이 뜸 (처음에는)

20을 입력했을 경우

없는 번호를 입력 했을 경우

- 구 11 : 아무것도 입력되지 않은 상태

- 신 11 : 입력 받은 상태

 

2-1) 여러 행의 데이터를 커서에 저장하여 사용하기 (LOOP 사용) 463P

 

 

 

2-2) FOR LOOP 문을 활용하여 커서 사용하기 465P

 

 

 

 

 

 

3) 묵시적 커서

* 정의

- 묵시적으로 커서를 정의하는

- UPDATE = DML

- ISOPEN은 항상 FALSE를 반환

 

* 부서 번호가 없는 경우 

--묵시적 커서
--부서 번호가 없는 경우
BEGIN
   UPDATE DEPT SET DNAME='DATABASE'
    WHERE DEPTNO = 50;
   DBMS_OUTPUT.PUT_LINE('갱신된 행의 수 : ' || SQL%ROWCOUNT);

   IF (SQL%FOUND) THEN
      DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : true');
   ELSE
      DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : false');
   END IF;

   IF (SQL%ISOPEN) THEN
      DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : true');
   ELSE
      DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : false');
   END IF;
END;
/

- 업데이트할 수 있는 값이 없기 때문에 ROWCOUNT는 0개

 

* 부서 번호가 있는 경우

--부서 번호가 있는 경우
BEGIN
   UPDATE DEPT SET DNAME='DATABASE'
    WHERE DEPTNO = 40;
   DBMS_OUTPUT.PUT_LINE('갱신된 행의 수 : ' || SQL%ROWCOUNT);

   IF (SQL%FOUND) THEN
      DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : true');
   ELSE
      DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : false');
   END IF;

   IF (SQL%ISOPEN) THEN
      DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : true');
   ELSE
      DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : false');
   END IF;
END;
/

1. 오류가 발생해도 비정상으로 종료 되지 않도록 하는 예외 처리

0) 정의

 

1) 오류

--예외 처리 = 오류가 발생해도 비정상 종료되지 않음
--실행 오류 = 예외가 발생하는 PL/SQL
DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
     FROM DEPT
    WHERE DEPTNO = 10;
END;
/
--

4행 오류

- 문자열과 숫자열의 충돌

 

2) 예외 처리

--
--예외 처리하는 PL/SQL
DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
     FROM DEPT
    WHERE DEPTNO = 10;
EXCEPTION
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
END;
/
--

오류 발생

- VALUE_ERROR를 통해 에러를 스킵할 수 있음.

 

3) 예외 발생 후의 명령어는 실행 X

--
--예외 발생 후의 명령어는 실행되지 않음
DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
     FROM DEPT
    WHERE DEPTNO = 10;

   DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다');

EXCEPTION
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
END;
/

위와 같은 결과 값

4) 사전 정의된 예외 사용

DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
     FROM DEPT
    WHERE DEPTNO = 10;

   DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다');

EXCEPTION
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 요구보다 많은 행 추출 오류 발생');
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 사전 정의 외 오류 발생');
END;
/
--

결과값.

5) 오류 코드 & 오류 메시지 사용

--오류 코드 & 오류 메시지 사용
DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
     FROM DEPT
    WHERE DEPTNO = 10;

   DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다');

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('예외 처리 : 사전 정의 외 오류 발생');
      DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || TO_CHAR(SQLCODE));
      DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
END;
/

2개의 오류에 대한 값이 있기에 출력 불가

 

 

2 저장 서브프로그램

1. 트리거

0) 정의

 

1) 트리거 

* 생성

--트리거 = 데이터베이스 안의 특정 상황이나 동작, 즉 이벤트가 발생할 경우에 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램
--EMP 테이블 복사
CREATE TABLE EMP_TRG
    AS SELECT * FROM EMP;
SELECT * FROM EMP_TRG;
--DML 실행 전에 수행하는 트리거 생성(P.511)
CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
BEFORE
INSERT OR UPDATE OR DELETE ON EMP_TRG
BEGIN
   IF TO_CHAR(sysdate, 'DY') IN ('금', '일') THEN
      IF INSERTING THEN
         raise_application_error(-20000, '금요일/일요일 사원정보 추가 불가');
      ELSIF UPDATING THEN
         raise_application_error(-20001, '금요일/일요일 사원정보 수정 불가');
      ELSIF DELETING THEN
         raise_application_error(-20002, '금요일/일요일 사원정보 삭제 불가');
      ELSE
         raise_application_error(-20003, '금요일/일요일 사원정보 변경 불가');
      END IF;
   END IF;
END;
/

저장된 트리거

* 작동 확인

--트리거 사용
--데이터 변경
SELECT * FROM EMP_TRG WHERE EMPNO = 7788;
UPDATE emp_trg SET sal = 3500 WHERE empno = 7788;
--테이블 조회
SELECT * FROM EMP_TRG WHERE EMPNO = 7788;
--금요일에는 변경 안됨

트리거가 작동된 모습

실제로 업데이트 안된 것을 확인할 수 있음

2) 로그 테이블 생성 및 새로운 트리거 생성 후 작동 

* 로그 테이블 생성

--로그 테이블 생성
CREATE TABLE EMP_TRG_LOG(
   TABLENAME VARCHAR2(10), -- DML이 수행된 테이블 이름
   DML_TYPE VARCHAR2(10),  -- DML 명령어의 종류
   EMPNO NUMBER(4),        -- DML 대상이 된 사원 번호
   USER_NAME VARCHAR2(30), -- DML을 수행한 USER 이름
   CHANGE_DATE DATE        -- DML이 수행된 날짜
);
SELECT * FROM EMP_TRG_LOG;
--

* DML 실행 후 수행할 트리거

--DML 실행 후에 수행하는 트리거 생성(P.513)
CREATE OR REPLACE TRIGGER trg_emp_log
AFTER
INSERT OR UPDATE OR DELETE ON EMP_TRG
FOR EACH ROW

BEGIN

   IF INSERTING THEN
      INSERT INTO emp_trg_log
      VALUES ('EMP_TRG', 'INSERT', :new.empno,
               SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);

   ELSIF UPDATING THEN
      INSERT INTO emp_trg_log
      VALUES ('EMP_TRG', 'UPDATE', :old.empno,
               SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);

   ELSIF DELETING THEN
      INSERT INTO emp_trg_log
      VALUES ('EMP_TRG', 'DELETE', :old.empno,
               SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
   END IF;
END;
/
--

DML 실행후 트리거 생성

new.empno를 입력한 이유는 검색하기 쉽게 기능을 넣은 것.

 

* 데이터 추가부터 최종 변경, 테이블 조회, 로그 테이블 조회까지

--데이터 추가
INSERT INTO EMP_TRG
VALUES(9999, 'TestEmp', 'CLERK', 7788,
       TO_DATE('2018-03-03', 'YYYY-MM-DD'), 1200, null, 20);
--최종 변경
COMMIT;
--데이터 추가된 테이블 조회
SELECT * FROM EMP_TRG;
--로그 테이블 조회
SELECT * FROM EMP_TRG_LOG;
--

데이터 추가 및 커밋

테이블 조회 시 15개의 행이 선택된 모습

INSERT 했다는 로그가 남겨진 모습

* 업데이트부터 트리거 정보 조회

--데이터 변경
UPDATE EMP_TRG
   SET SAL = 1300
 WHERE MGR = 7788;
--최종 변경
COMMIT;
--데이터 변경된 테이블 조회
SELECT * FROM EMP_TRG;
--로그 테이블 조회
SELECT * FROM EMP_TRG_LOG;
--
--트리거 정보 조회
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
  FROM USER_TRIGGERS;
--트리거 변경
--트리거 삭제
--

 

테이블 조회 결과

INSERT, UPDATE한 것 까지 입력된 모습

트리거 정보 조회까지

 

 

 

핵심

- 에러는 그 뒤에 수 많은 명령어가 있어도 실행 되지 않고 그냥 그 자리에서 멈춘다

- DISABLE을 쓰면 트리거 비활성화가 가능