유레카
RAISE_APPLICATION_ERROR에 대해
DECLARE
v_num number := 100;
err_b EXCEPTION;
PRAGMA EXCEPTION_INIT(err_b, -20000);
BEGIN
if v_num > 1 THEN
RAISE_APPLICATION_ERROR(-20000,'바보');
END IF;
EXCEPTION
WHEN err_b THEN
dbms_output.put_line('안녕');
END;
/raise_application_error는 사실 그냥 비정상적인 프로그램 종료를 시키는게 아니라 해당 오류번호에 대한 EXCEPTION문이 있는지 확인해 봤을때 따로 EXCEPTION문을 선언해 주지 않아 비정상적인 프로그램 종료가 되었던 것이었다. 따라서 해당 오류 번호에 대한 오류를 DECLARE절에 선언을 해주면 EXCEPTION문 WHEN 으로 빠져 정상적인 프로그램 종료가 가능하다.
AUTO TCL 과 수동 TCL의 차이
오토 롤백, 커밋 같은 경우 해당 프로그램안에서 일어난 트랜잭션에 대해서만 롤백, 커밋을 수행한다. 예를 들어 raise_application_error 같은 비정상적인 프로그램 종료에 의해 생긴 자동 롤백은 해당 트랜잭션만 롤백하게 된다. 하지만 수동적으로 내가 작성한 롤백, 커밋 같은 경우 해당 세션에서 진행중인 전체 트랜잭션에 대해 롤백과 커밋을 수행한다.
복습
- 행 단위 트리거
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE
INSERT OR UPDATE OF salary ON hr.emp
FOR EACH ROW -- 여기까지 헤더
BEGIN
IF NOT :new.job_id IN ('AD_PRES','AD_VP') AND :new.salary > 15000 THEN
RAISE_APPLICATION_ERROR(-20000,'급여를 큰 값으로 사용할 수 없습니다');
END IF;
END;
/- insert
INSERT INTO hr.emp VALUES(300,'SCOTT',10000, 'IT_PROG', 10); -- job_id가 조건에 해당하지 않지만 급여가 15000 이하 이기 때문에 정상작동
INSERT INTO hr.emp VALUES(300,'SCOTT',20000, 'IT_PROG', 10); /* 급여가 15000 이상으로 트리거 발동 , 오류 발생은 문장단위에서 실행된다. 포인트를 가지고 작동된다.
만약 해당 트리거에 rollback이 작성되어 있었다면, 앞에 insert transaction까지 취소 되었을거다. */
select * from hr.emp;
transaction 은 프로그램안에서는 하나로 묶이지만 프로그램 밖에서는 개별 transaction으로 발생된다. 따라서 프로그램 밖 개별 DML문에서 발생한 trigger에서 rollback이 발생하여도 다른 transaction에는 영향을 주지 않는다.
- 복제 트리거 만들기
<소스 테이블, 타켓 테이블 만들기>
CREATE TABLE hr.emp_target(
id NUMBER,
name VARCHAR2(30),
day TIMESTAMP DEFAULT SYSTIMESTAMP,
sal NUMBER);
CREATE TABLE hr.emp_source(
id NUMBER,
name VARCHAR2(30),
day TIMESTAMP DEFAULT SYSTIMESTAMP,
sal NUMBER);
<복제 트리거>
CREATE OR REPLACE TRIGGER emp_copy_trigger
BEFORE
DELETE OR INSERT OR UPDATE ON hr.emp_source
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO hr.emp_target(id,name,day,sal)
VALUES(:new.id, :new.name, :new.day, :new.sal);
ELSIF DELETING THEN
DELETE FROM hr.emp_target WHERE id = :old.id;
ELSIF UPDATING('sal') THEN
UPDATE hr.emp_target
SET sal = :new.sal
WHERE id = :old.id;
ELSIF UPDATING('name') THEN
UPDATE hr.emp_target
SET name = :new.name
WHERE id = :old.id;
END IF;
END;
/<insert 해보기>
INSERT INTO hr.emp_source(id,name,day,sal) VALUES(1,'SCOTT',default,1000);
SELECT * FROM hr.emp_source;
SELECT * FROM hr.emp_target;소스 테이블
타켓 테이블
INSTEAD OF TRIGGER
- 뷰에만 생성하는 트리거, 테이블에 생성 못한다.
- 행트리거에서만 사용 가능하다.
- 테이블 생성
CREATE TABLE hr.new_emp
AS
SELECT employee_id, salary,job_id, department_id
FROM hr.employees;
CREATE TABLE hr.new_dept
AS
SELECT d.department_id, d.department_name, total_dept_sal
FROM (SELECT department_id, sum(salary) as total_dept_sal
FROM hr.employees
GROUP BY department_id)e, hr.departments d
WHERE e.department_id = d.department_id;- 복합뷰 생성
CREATE OR REPLACE VIEW emp_details
AS
SELECT e.employee_id, e.salary, e.job_id, e.department_id, d.department_name, d.total_dept_sal
FROM hr.new_emp e , hr.new_dept d
WHERE e.department_id = d.department_id;- 트리거 생성
- 복합뷰는 DML할 수 없다.
가능하게 하려면 INSTEAD OF TRIGGER로 구현하면 된다
CREATE OR REPLACE TRIGGER emp_dept_trigger
INSTEAD OF -- 오직 뷰를 위한 타이밍
INSERT OR UPDATE OR DELETE ON emp_details
FOR EACH ROW
BEGIN
IF INSERTING THEN -- 사원 추가할 경우
INSERT INTO hr.new_emp(employee_id, salary, job_id, department_id)
VALUES (:new.employee_id, :new.salary, :new.job_id,:new.department_id);
UPDATE hr.new_dept
SET total_dept_sal = total_dept_sal + :new.salary
WHERE department_id = :new.department_id;
ELSIF DELETING THEN -- 사원 삭제할 경우
DELETE FROM hr.new_emp WHERE employee_id = :old.employee_id;
UPDATE hr.new_dept
SET total_dept_sal = total_dept_sal - :old.salary
WHERE department_id = :old.department_id;
ELSIF UPDATING('salary') THEN -- 급여 변경
UPDATE hr.new_emp
SET salary = :new.salary
WHERE employee_id = :old.employee_id;
UPDATE hr.new_dept
SET total_dept_sal = total_dept_sal + (:new.salary - :old.salary)
WHERE department_id = :old.department_id ;
ELSIF UPDATING('department_id') THEN -- 부서이동
UPDATE hr.new_emp
SET department_id = :new.department_id
WHERE employee_id = :old.employee_id;
UPDATE hr.new_dept
SET total_dept_sal = total_dept_sal - :old.salary -- 이전부서에서는 전체 급여를 삭제
WHERE department_id = :old.department_id;
UPDATE hr.new_dept
SET total_dept_sal = total_dept_sal + :new.salary -- 새로운부서에는 전체급여 추가
WHERE department_id = :new.department_id;
END IF;
END emp_dept_trigger;
/값에 대한 감사
- 새로운 green 유저 생성
CREATE USER green
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO green;- hr 세션에서 감사 테이블 생성
CREATE TABLE hr.audit_emp_sal(
name varchar2(30),
day timestamp,
id number,
old_sal number,
new_sal number);- 간접 access를 위한 프로시저 생성
- 간접 ACCESS
- 테이블에 직접 ACCESS하게 하는건 위험부담이 크기 때문에 프로그램을 통한 간접 ACCESS를 추구함
CREATE OR REPLACE PROCEDURE update_proc(
p_id IN number)
IS
BEGIN
UPDATE hr.emp
SET salary = salary * 1.1
WHERE employee_id = p_id;
END update_proc;
/- 감사 테이블에 emp 테이블 업데이트시 자동 insert 되는 트리거 생성
CREATE OR REPLACE TRIGGER sal_audit
AFTER
UPDATE OF salary ON hr.emp
FOR EACH ROW
WHEN (old.salary != new.salary)
BEGIN
INSERT INTO hr.audit_emp_sal(name, day,id,old_sal,new_sal)
VALUES (USER,SYSTIMESTAMP,:new.employee_id,:old.salary,:new.salary);
END sal_audit;
/- green 유저에 execute 권한 부여
GRANT execute ON hr.update_proc TO green;- green 유저에서 프로시저 실행
execute hr.update_proc(100);6-1. green 세션에서 본 hr.audit_emp_sal
6-2. hr 세션에서 본 hr.audit_emp_sal
- 읽기 일관성에 의해 green 세션에서 조회되는 audit_emp_sal 테이블은 commit 전까지 hr 세션에서 조회 할 수 없다.
- 트리거 내부에서는 함부로 commit 또는 rollback을 할 수 없다. 왜냐하면 트리거 밖의 transaction까지 제어 하기 때문이다.
독립 트랜잭션
PRAGMA AUTONOMOUS_TRANSACTION;
- 프로그램 안에서 독립 트랜잭션을 수행하기 위해 지시어
- COMMIT, ROLLBACK은 필수 입력
- 테이블 생성
CREATE TABLE hr.log_table(
username varchar2(30),
date_time timestamp,
message varchar2(100));
CREATE TABLE hr.temp_table(
n number);- 1번 시나리오
CREATE OR REPLACE PROCEDURE log_message(
p_message IN varchar2)
IS
BEGIN
INSERT INTO log_table(username, date_time, message)
VALUES (user, systimestamp, p_message);
END log_message;
/
SELECT * FROM hr.log_table;
SELECT * FROM hr.temp_table;
BEGIN
log_message('오늘 하루도 행복하자!!');
INSERT INTO hr.temp_table(n) VALUES(1234);
log_message('미래를 위해서 열심히 공부하자!!');
ROLLBACK;
END;
/<log_table>
<temp_table>
- 전체 session에 대한 rollback 하였기 때문에 insert된 데이터는 없다.
- 2번 시나리오
CREATE OR REPLACE PROCEDURE log_message(
p_message IN varchar2)
IS
BEGIN
INSERT INTO log_table(username, date_time, message)
VALUES (user, systimestamp, p_message);
COMMIT; -- 해당 프로시저 밖의 TRANSACTION까지 영향을 준다.
END log_message;
/
SELECT * FROM hr.log_table;
SELECT * FROM hr.temp_table;
-- 같은 session 안에서는 transaction은 전체적으로 하나로 묶인다.
BEGIN
log_message('오늘 하루도 행복하자!!');
INSERT INTO hr.temp_table(n) VALUES(1234);
log_message('미래를 위해서 열심히 공부하자!!');
ROLLBACK;
END;
/<log_table>
<temp_table>
- 마지막 프로시저에서 commit을 했기때문에 뒤에 rollback을 해도 이미 commit되어있는 상태이다.
- 3번 시나리오
CREATE OR REPLACE PROCEDURE log_message(
p_message IN varchar2)
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- 독립트랜잭션 제어, 해당 지시어를 사용할때는 프로시저 안에 COMMIT 또는 ROLLBACK을 사용해야 한다.,
BEGIN
INSERT INTO log_table(username, date_time, message)
VALUES (user, systimestamp, p_message);
COMMIT;
END log_message;
/
SELECT * FROM hr.log_table;
SELECT * FROM hr.temp_table;
-- 같은 session 안에서는 transaction은 전체적으로 하나로 묶인다.
BEGIN
log_message('오늘 하루도 행복하자!!'); -- 커밋
INSERT INTO hr.temp_table(n) VALUES(1234); -- 롤백
log_message('미래를 위해서 열심히 공부하자!!'); --커밋
ROLLBACK;
END;
/<log_table>
<temp_table>
- PRAGMA AUTONOMOUS_TRANSACTION을 사용하였기 때문에 각 프로시저안에서 이루어진 개별 TRANSACTION에 대한 COMMIT만 이루어졌고, 밖의 transaction에는 영향을 주지 않았기 때문에 마지막 rollback에 의해 취소되었다.
- TRIGGER 에서 독립 트랜잭션 처리 하는 방법
CREATE OR REPLACE TRIGGER sal_audit
AFTER
UPDATE OF salary ON hr.emp
FOR EACH ROW
WHEN (old.salary != new.salary)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO hr.audit_emp_sal(name, day,id,old_sal,new_sal)
VALUES (USER,SYSTIMESTAMP,:new.employee_id,:old.salary,:new.salary);
COMMIT;
END sal_audit;
/- 트리거에 PRAGMA AUTONOMOUS_TRANSACTION을 선언하면 트리거 내부의 transaction을 개별 적으로 관리할 수 있어 따로 update를 하는 세션에서 commit 또는 rollback을 하지 않아도 자동으로 감시테이블에 데이터가 쌓이게 된다. 하지만 update 된 테이블은 green세션에서 따로 commit을 해주지 않는 이상 hr세션에서는 변경값이 조회가 안된다.
