오라클 PL/SQL 트리거

자바에도 있는 예외처리! 오라클에서도 만나니까 반가웠다.

 

--예외처리

DECLARE

v_num NUMBER := 0;

BEGIN

v_num := 10 / 0;

 

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('0으로 나눌 수 없습니다.');

DBMS_OUTPUT.PUT_LINE ('SQL ERROR CODE:' || SQLCODE);

DBMS_OUTPUT.PUT_LINE ('ERROR MSG: ' || SQLERRM);

END;

 

연습 문제를 풀고, 트리거에 대해서 배웠다.

한마디로 정의하면 백업 공간!? 이라고 할 수 있다고 한다.

 

/*

trigger는 테이블에 부착한 형태로서, INSERT, UPDATE, DELETE 작업이 수행될 때,

특정 코드가 작동되도록 하는 구문입니다.

 

트리거는 실행할 때 범위를 지정하고 F5버튼으로 부분 실행해야 합니다.

그렇지 않으면 하나의 구문으로 인식되어 정상 동작하지 않습니다.

*/

DROP TABLE tbl_test;

CREATE TABLE tbl_test(

id NUMBER(10),

text VARCHAR2(20)

);

CREATE OR REPLACE TRIGGER trg_test

AFTER DELETE OR UPDATE --삭제, 수정 이후에 동작시키겠다!

ON tbl_test -- 부착할 테이블

FOR EACH ROW -- 각 행에 적용

BEGIN

DBMS_OUTPUT.PUT_LINE('트리거가 동작합니다!'); -- 실행될 코드를 begin ~ end 사이에 넣습니다.

END;

INSERT INTO tbl_test VALUES(1, '홍길동');

UPDATE tbl_test SET text = '홍길동2' WHERE id = 1;

DELETE FROM tbl_test WHERE id = 1;

 

여러가지 트리거에 대해서 배웠다.

 

/*

AFTER TRIGGER: INSERT, UPDATE, DELETE 작업 이후에 동작하는 트리거를 의미합니다.

BEFORE TRIGGER: INSERT, UPDATE, DELECE 작업 이전에 동작하는 트리거를 의미합니다.

INSTEAD OF TRIGGER: INSERT, UPDATE, DELECE 작업 이전에 동작하는 트리거인데,

VIEW에만 부착할 수 있다.

 

:OLD = 참조 전의 열의 값 (INSERT: 입력 전 자료, UPDATE: 수정 전 자료, DELETE: 삭제할 값)

:NEW = 참조 후 열의 값 (INSERT: 입력할 자료, UPDATE: 수정된 자료

 

테이블에 UPDATE나 DELETE를 시도하면 수정 또는 삭제된 데이터를

별도의 테이블에 보관해 놓는 형식으로 트리거를 사용할 수 있다.

*/

CREATE TABLE tbl_user(

id VARCHAR2(20) PRIMARY KEY,

name VARCHAR2(20),

address VARCHAR2(30)

);

CREATE TABLE tbl_user_backup(

id VARCHAR2(20) PRIMARY KEY,

name VARCHAR2(20),

address VARCHAR2(30),

update_date DATE DEFAULT sysdate, --변경 시간

m_type VARCHAR2(10), --변경 타입

m_user VARCHAR2(20) --변경한 사용자

);

--AFTER TRIGGER

CREATE OR REPLACE TRIGGER trg_user_backup

AFTER UPDATE OR DELETE

ON tbl_user

FOR EACH ROW

DECLARE --사용할 변수를 선언하는 곳입니다

v_type VARCHAR2(10);

BEGIN

IF UPDATING THEN --UPDATING은 시스템 자체에서 상태에 대한 내용을 지원하는 빌트인 구문입니다.

v_type := '수정';

ELSIF DELETING THEN

v_type := '삭제';

END IF;

 

--실행 구문 시작 (:OLD는 테이블, DELETE, UPDATE가 적용되기 전 기존 데이터, 즉 변경 전 데이터입니다.)

INSERT INTO tbl_user_backup

VALUES(:OLD.id, :OLD.name, :OLD.address, sysdate, v_type, USER());

END;

--확인

INSERT INTO tbl_user VALUES('test01', 'admin', '서울');

INSERT INTO tbl_user VALUES('test02', 'admin', '경기');

INSERT INTO tbl_user VALUES('test03', 'admin', '부산');

SELECT * FROM tbl_user;

SELECT * FROM tbl_user_backup;

UPDATE tbl_user SET address = '인천' WHERE id = 'test01';

--BEFORE TRIGGER

CREATE OR REPLACE TRIGGER trg_user_insert

BEFORE INSERT

ON tbl_user

FOR EACH ROW

BEGIN

:NEW.name := SUBSTR(:NEW.name, 1, 1) || '**';

END;

INSERT INTO tbl_user VALUES ('test04', '홍길동', '대구');

SELECT * FROM tbl_user;

/*

- 트리거의 활용

INSERT -> 주문 테이블 -> 주문 테이블 INSERT 트리거 실행 (물품 테이블 UPDATE)

*/

--주문 히스토리

CREATE TABLE order_history(

history_no NUMBER(5) PRIMARY KEY,

order_no NUMBER(5),

product_no NUMBER(5),

total NUMBER(10),

price NUMBER(10)

);

--상품

CREATE TABLE product (

product_no NUMBER(5) PRIMARY KEY,

product_name VARCHAR2(20),

total NUMBER(5),

price NUMBER(5)

);

CREATE SEQUENCE order_history_seq NOCACHE;

CREATE SEQUENCE prduct_seq NOCACHE;

INSERT INTO product VALUES(product_seq.NEXTVAL, '피자', 100, 10000);

INSERT INTO product VALUES(product_seq.NEXTVAL, '치킨', 100, 15000);

INSERT INTO product VALUES(product_seq.NEXTVAL, '햄버거', 100, 5000);

--주문 히스토리에 데이터가 들어오면 실행하겠습니다.

CREATE OR REPLACE TRIGGER trg_order_history

AFTER INSERT

ON order_history

FOR EACH ROW

DECLARE

v_total NUMBER;

v_product_no NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('트리거 실행!');

SELECT :NEW.total

INTO v_total

FROM dual;

v_product_no := NEW.product_no;

 

UPDATE product SET total = total - v_total

WHERE product_no = v_prodcut_no;

 

END;

INSERT INTO order_history VALUES(order_history_seq.NEXTVAL,200, 1, 5, 50000);

INSERT INTO order_history VALUES(order_history_seq.NEXTVAL,200, 2, 1, 15000);

INSERT INTO order_history VALUES(order_history_seq.NEXTVAL,200, 3, 10, 15000);

SELECT * FROM order_history;

SELECT * FROM product;

 

와! PL/SQL도 종료. 내일부터는 jsp에 대해서 다시! 학습할 것 같다.