[PL/SQL] EXCEPTION, CURSOR, TRIGGER트리거

EXCEPTION

* 중복데이터 오류

컬럼에 중복데이터가 있을 때

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('데이터 중복');

DUP_VAL_ON_INDEX 를 통해 처리한다.

DUP_VAL은 DUPLICATE VALUE를 의미한다. (중복값)

 

* 사용자 정의 에러

사용자가 에러를 설정할 수 있다.

IF문에 따라 조건에 벗어날 경우 에러를 주는 상황이다.

IS
NOT_ENOUGH_PAY EXCEPTION;

먼저 EXCEPTION 선언은 테이블을 만들고 난 후 IS 문에 입력한다.

ELSIF SW_REC.급여<3000 THEN
RAISE NOT_ENOUGH_PAY;

EXCEPTION
WHEN NOT_ENOUGH_PAY THEN
RAISE_APPLICATION_ERROR(-10001,'급여가 충분하지 못함');
END;
/

BEGIN 이후 IF문에 조건을 주었으며 (생략함) 예외는 ELSIF조건에 주었다.

RAISE는 예외 명령어를 호출하는 기능이 있다.

하단의 EXCEPTION의 -10001은 에러가 났을 경우, 'ora-10001'이라는 메시지와 문자를 나타나게 한다.

 

 

CURSOR

SELECT한 결과를 CURSOR에 넣어 값을 찾을 수 있다.

 

사원아이디를 입력하면 사원이름과 구입한 제품명을 출력하도록 해보자.

CREATE OR REPLACE PROCEDURE P_JEPUM
(V_USERID IN VARCHAR2)
IS
  
V_CNT NUMBER;
  
   CURSOR SA_CUR
   IS
   SELECT C.USERID,C.USERNAME,S.PRODUCTNAME
   FROM CUSTOM C, SALES S
   WHERE C.USERID=S.USERID AND S.USERID=V_USERID;
 
BEGIN
 
DBMS_OUTPUT.PUT_LINE('아이디  이름  제품');
DBMS_OUTPUT.PUT_LINE('------------------');

FOR I IN SA_CUR LOOP
DBMS_OUTPUT.PUT_LINE(I.USERID||'  '||I.USERNAME||'  '||I.PRODUCTNAME);

V_CNT:=SA_CUR%ROWCOUNT;
 
END LOOP;

DBMS_OUTPUT.PUT_LINE('전체데이터의 갯수: '||TO_CHAR(V_CNT));
END;

커서명은 SA_CUR로 지정해주었다. 'SELECT한 결과가 들어간다.

CUSTOM 테이블과 SALES 테이블에서 JOIN문으로 SELECT하였다.

 

반복문 FOR문을 통해 값을 출력한다.

데이터 위에서부터 차례대로 값을 꺼낸다.

FOR문이 끝날 경우 시스템 변수를 사용할 수 없으므로 V_CNT를 END 전에 입력한다.

 

또 다른 FETCH 명령어를 사용할 수도 있다.

 

실행을 하려면

EXEC P_JEPUM('gd2527');

EXEC를 통해 USERID 값을 입력해주어 상응하는 값을 꺼내온다.

 

 

TRIGGER

방아쇠처럼 당기는 순간 연쇄적으로 일어나는 작업들이다.

 

AFTER TRIGGER와 BEFORE TRIGGER가 있다.

AFTER는 값을 받고 판단을 하여 실행하며

BEFORE은 판단을 먼저 하고 값을 받는다.

둘의 실행 순서에 차이가 있다!

 

* AFTER TRIGGER

INSERT TRIGGER를 알아보겠다.

 CREATE OR REPLACE TRIGGER TRI_HAKSANG_IN
AFTER INSERT
ON LEE.HAKSANG
FOR EACH ROW 
BEGIN
INSERT INTO MEMO VALUES (SE100.NEXTVAL,:NEW.NAME || ' 행이 INSERT되었습니다');
END;

INSERT INTO HAKSANG VALUES(111,'ABC');

먼저 TRIGGER를 만들어준다

ON문의 LEE는 스키마이며 누구의 테이블인지 나타낸다.

FOR EACH ROW를 통해 입력된 데이터를 모두 다 읽어온다. 업데이트, 삭제의 경우에도 다 가져온다.

 

INSERT문의 NEW는 새로 입력된 데이터를 말하며

OLD로 입력할 수도 있다. 의미와 같이 지워진 데이터 등을 말한다.

 

UPDATE와 DELETE를 실행할 경우에도 마찬가지로 적어주면 되며

UPDATE는 2번째 줄에서 

AFTER UPDATE OF NAME

과 같이, 특정 컬럼만 지정해서 업데이트할 수 있다.

 

* BEFORE TRIGGER

토,일요일과 오후 12~17시 외에는 DML 사용을 금지하도록 하겠다.

CREATE TABLE INSA
(EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
SAL NUMBER);

  CREATE OR REPLACE TRIGGER TRI_INSA
 BEFORE INSERT OR DELETE OR UPDATE
 ON LEE.INSA
 DECLARE

IMPOSSIBLE_TIME EXCEPTION;

  BEGIN
  IF TO_CHAR(SYSDATE,'DAY') IN('토요일','일요일') OR
   TO_CHAR(SYSDATE,'HH24')<12 OR TO_CHAR(SYSDATE,'HH24')>17 THEN
   RAISE IMPOSSIBLE_TIME;
   END IF;
 
   EXCEPTION
   WHEN IMPOSSIBLE_TIME THEN
   RAISE_APPLICATION_ERROR(-20007,'월~금요일 정오 12:00부터 오후5시까지만 DML 작업 가능');
   END;

테이블을 먼저 만들어준다.

그 다음 트리거를 만들며 AFTER TRIGGER고 ㅏ다르게

BEFORE 문에 해당되는 DML을 입력한다. 예시의 경우 3가지를 들었다.

 

DECLARE 선언으로 예외처리가 지정되었다.

 

IF문을 통해 조건을 달아주며 예외는 RAISE 명령어로 실행한다.

 

 

 

 

 

#국비46일차