2025.01.06 PL/SQL 12일차

복습)

# DML 문장 트리거

  - DML 문장 트리거는 영향을 받는 행이 전혀 없더라도 문장 트리거는 한 번 실행된다.

  - 특정 요일과 시간을 제한을 주고 싶을 땐 문장 트리거를 많이 쓴다.

 

# DML 행 트리거

  - DML 행 트리거는 영향을 받는 행이 없을 경우에는 트리거가 수행되지 않는다.

  - 영향을 받은 각 행에 대해서 트리거는 수행된다.

  -  FOR EACH ROW 절을 사용해야 한다.

  - CHECK 제약조건으로 안 되는 CHECK 제약조건을 만들고 싶을 때 행 트리거로 많이 구현한다.

 

ex) DML 행 트리거

 

 - 간단 테이블 생성

create table hr.emp
as
select employee_id id, salary sal, job_id job, department_id dept_id
from hr.employees;

 

-  'AD_PRES','AD_VP'  job이 아닌데 급여가 15000 이상이 들어오면 에러 발생

create or replace trigger hr.restrict_salary
before
insert or update of sal on hr.emp
for each row
begin
    if :new.job not in('AD_PRES','AD_VP') and :new.sal > 15000 then
        raise_application_error(-20000, 'Employee cannot earn more than 15,000');
    end if;
end restrict_salary;
/

insert into hr.emp(id, sal, job, dept_id)
values(300, 1000, 'IT_PROG', 10);

출력

▶ 문제 없이 insert 된다.

 

insert into hr.emp(id, sal, job, dept_id)
values(400, 20000, 'IT_PROG', 20);

출력

▶ 트리거에서 제한한 job 외에 job이 15000이상의 급여를 insert 했기 때문에 에러가 발생한다.

 

update hr.emp
set sal = 16000
where id = 200;

출력

▶ 200번 사원은 job이 AD_ASST이고 15000이상 받으면 에러를 발생하는 구문을 트리거에서 지정했기 때문에 에러가 발생한다.

 

update hr.emp
set sal = 30000
where id = 100;

출력

▶ 100번 사원은 AD_PRES이다. 그렇기 때문에 15000 이상 급여를 수정해도 에러가 발생하지 않고 정상 수정이 된 것이다.

 

ex) source 테이블에 새로운 값이 들어올 때 target 테이블에도 같이 매핑이 될 수 있도록 하고 싶을 경우

- 복제 테이블 구성

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);

 

- 트리거 생성

  - 트리거 안에서 커밋이나 롤백을 쓰면 안 된다. 영구히 저장이나 롤백을 하고 싶은 경우엔 insert 작업 다하고 그 후에 밖에서 커밋이나 롤백하면 된다.

create or replace trigger hr.emp_copy_trigger
after
insert or delete 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;
    commit;
end emp_copy_trigger;
/

▶ 주의 : 트리거 안에 트랜잭션이 실행 중이다. 근데 구문 안에 커밋이나 롤백을 하면 컴파일 에러가 발생한다. 트리거 안에선 트랜잭션을 제어하는 롤백이나 커밋을 쓰면 안 된다. 사용하게 되면 insert 구문까지 영향을 주기 때문에 트리거는 생성되어도 DML 작업 시 에러가 발생

출력

create or replace trigger hr.emp_copy_trigger
after
insert or delete 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 emp_copy_trigger;
/

insert into hr.emp_source(id, name, day, sal)
values(100, 'ORA1', default, 1000);

출력

 

select * from hr.emp_source;
select * from hr.emp_target;

▶ 2개의 테이블에 동일한 데이터가 들어간 걸 볼 수 있다.

 

update hr.emp_source
set sal = 2000
where id = 100;

commit;

출력

 

update hr.emp_source
set name = 'oracle'
where id = 100;

commit;

출력

 

delete from hr.emp_source where id = 100;

commit;

출력

 

# 값의 감사를 구현해야 할 때

  - SYS SESSION

create user green
identified by oracle
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant create session to green;

 

- HR SESSION

create table hr.audit_emp_sal
(name varchar2(30),
 day timestamp,
 id number,
 old_sal number,
 new_sal number);

 create or replace procedure hr.update_proc(p_id in number)
 is
 begin
    update hr.emp
    set sal = sal * 1.1
    where id = p_id;
 end update_proc;
 /

 

- 프로시저가 제대로 실행되는지 확인

select * from hr.emp where id = 200;
exec hr.update_proc(200)
select * from hr.emp where id = 200;

출력

 

- green에게 프로시저에 대한 excute 할 수 있는 권한 주기

 grant execute on hr.update_proc to green;

 

- 값에 대한 감사 Trigger 생성

create or replace trigger hr.emp_sal_audit
after
update of sal on hr.emp
for each row
begin
    if :old.sal != :new.sal then
        insert into hr.audit_emp_sal(name, day, id, old_sal, new_sal)
        values(user, systimestamp, :new.id, :old.sal, :new.sal);
    end if;
end emp_sal_audit;
/

▶ values 값에 user의 의미는 현재 접속한 유저로 받겠다는 의미이다.

▶ insert into 절에 id 값에 :new, :old 둘 중에 아무거나 써도 상관없다.

 

select * from hr.audit_emp_sal;

update hr.emp
set sal = sal * 1.1
where id = 200;

select * from hr.audit_emp_sal;

출력

▶ 현재 접속한 유저인 HR이 들어가고, 현재 시간이 들어가고 이전 급여가 얼마였고, 수정된 급여는 뭔지 나오는 게 감사이다.

 

# 롤백하고 수행하면

출력

 

- green으로 접속하여 권한 확인하기

select * from user_tab_privs;

출력

 

- excute 해보기 (green 유저에서 sqlplus에서 함)

exec hr.update_proc(200)

commit;

 

- 조회해보기 (HR SESSION)

select * from hr.audit_emp_sal;

출력

▶ 프로시저에 excute 권한만 green한테 주고 감사를 한 것이다. 이런 걸 간접 엑세스라고 한다.

 

■ INSTEAD OF TRIGGER

  - VIEW에 생성하는 트리거

  - 복합뷰는 DML 작업을 수행할 수 없다. 가능하도록 만들려면 INSTEAD OF TRIGGER를 생성하면 된다.

  - 뷰에 트리거는 행트리거만 사용해야 한다.

 

ex)

- 샘플 테이블 2개 생성

create table hr.new_emps
as
select employee_id, salary, hire_date, department_id
from hr.employees;

create table hr.new_depts
as
select d.department_id, d.department_name, sum(e.salary) tot_dept_sal
from hr.employees e, hr.departments d
where e.department_id = d.department_id
group by d.department_id, d.department_name;

 

- 샘플 테이블을 이용한 VIEW 생성

create view hr.emp_details
as
select e.employee_id, e.salary, e.hire_date, e.department_id, d.department_name, d.tot_dept_sal
from hr.new_emps e, hr.new_depts d
where e.department_id = d.department_id;

▶ JOIN이나 group by절이 들어가면 복합뷰이다. 복합뷰에서는 DML 작업이 불가능하다.

 

- 복합뷰이지만 hr.emp_details 뷰에 신규 회원의 데이터를 넣고 싶을 경우

  ▶트리거로 구현하면 된다고 하자!

  ▶ emp 사원테이블에는 insert가 되면 되고, depts 부서 테이블에는 update가 되게 하면 된다.

 

- INSTEAD OF TRIGGER 트리거 생성

create or replace trigger hr.emp_dept
instead of -- 뷰에 트리거를 걸 경우엔 무조건 instead of 사용 
insert or update or delete on hr.emp_details
for each row
begin
    if inserting then
        insert into hr.new_emps(employee_id, salary, hire_date, department_id)
        values(:new.employee_id, :new.salary, :new.hire_date, :new.department_id);
        
        update hr.new_depts
        set tot_dept_sal = tot_dept_sal + :new.salary
        where department_id = :new.department_id;
    elsif deleting then
        delete from hr.new_emps where employee_id = :old.employee_id;
        
        update hr.new_depts
        set tot_dept_sal = tot_dept_sal - :old.salary
        where department_id = :old.department_id;
    elsif updating('salary') then
        update hr.new_emps 
        set salary = :new.salary
        where employee_id = :old.employee_id;
        
        update hr.new_depts
        set tot_dept_sal = tot_dept_sal + (:new.salary - :old.salary)
        where department_id = :old.department_id;
    elsif updating('department_id') then
        update hr.new_emps
        set department_id = :new.department_id
        where employee_id = :old.employee_id;
        
        update hr.new_depts
        set tot_dept_sal = tot_dept_sal - :old.salary
        where department_id = :old.department_id;
        
        update hr.new_depts
        set tot_dept_sal = tot_dept_sal + :new.salary
        where department_id = :new.department_id;
    end if;
end emp_dept;
/

 

- 뷰를 통해 신규 사원을 넣고 데이터를 조회해보자

select * from hr.new_depts where department_id = 60;

insert into hr.emp_details(employee_id, salary, hire_date, department_id, department_name)
values(300, 1000, sysdate, 60, 'IT');

select * from hr.new_emps where employee_id = 300;

select * from hr.new_depts where department_id = 60;

출력

 

# 사원의 급여를 수정 

select * from hr.new_emps where employee_id = 300;

update hr.new_emps
set salary = 2000
where employee_id = 300;

commit;

select * from hr.new_depts where department_id = 60;

출력

▶ 사원테이블에서 수정해도 부서테이블에 수정이 되지 않았다. 그 이유는 update 구문에서 뷰를 통해서 수정을 해야 하는데 사원테이블인 실제 테이블에서 했기 때문이다.

 

# 60번에서 70번으로 부서 이동할 경우

select * from hr.new_emps where employee_id = 300;
select * from hr.new_depts where department_id = 60;
select * from hr.new_depts where department_id = 70;

update hr.emp_details
set department_id = 70
where employee_id = 300;

commit;

select * from hr.new_depts where department_id = 70;

 

# 사원 삭제

delete from hr.emp_details where employee_id = 300;

commit;

출력

 

# 트리거 생성 시 주의사항

  - 트리거 작성 시에 트리거 오류 발생했으면 빨리 디버깅해야 된다. 안 할 시엔 내가 시점을 준(insert, update, delete) 작업 시 계속 에러 난다. 그 외 작업엔 문제가 되진 않는다.

 

create or replace trigger hr.dept_row_before
before
insert on hr.dept
for each row
begin
    dbms_output.put_line('insert 하기 전 행 트리거 수행')
end dept_row_before;
/

출력

▶ 빨리 디버깅을 하는지 트리거를 DROP 하던지 해야 한다.

 

select status from user_triggers  where trigger_name = 'DEPT_ROW_BEFORE';

출력

▶ 기본값이 ENABLED이다.

 

# 해결방법

alter trigger hr.dept_row_before disable;
select status from user_triggers  where trigger_name = 'DEPT_ROW_BEFORE';

출력

▶ 비활성화시킨 거다. (insert 작업도 가능하다.)

▶ DISABLED로 바꿔놓으면 insert작업은 문제없이 돌아간다.

 

# 처음부터 오류날 것을 대비해서 DISABLE 상태로 만들 수 있다.

create or replace trigger hr.dept_row_before
before
insert on hr.dept
for each row
disable
begin
    dbms_output.put_line('insert 하기 전 행 트리거 수행')
end dept_row_before;
/

▶ 오류는 났지만 disable로 들어간 거다.

 

select status from user_triggers  where trigger_name = 'DEPT_ROW_BEFORE';

출력

 

# 오류 안 나고 disable 했을 경우

create or replace trigger hr.dept_row_before
before
insert on hr.dept
for each row
disable
begin
    dbms_output.put_line('insert 하기 전 행 트리거 수행');
end dept_row_before;
/

select status from user_triggers  where trigger_name = 'DEPT_ROW_BEFORE';

출력

▶ 컴파일 오류가 안 나고 정상 출력이 되면 꼭 활성화 상태인 ENABLE 상태로 바꿔줘야 트리거가 작동이 된다.

 

# 다시 활성화시키고 싶을 경우

alter trigger hr.dept_row_before enable;
select status from user_triggers  where trigger_name = 'DEPT_ROW_BEFORE';

출력

▶ 다시 insert 작업하면 잘 수행이 된다.

 

■ local subprogram (nested subprogram)

 

ex)

declare 
    type emp_id_type is table of number;
    v_id emp_id_type := emp_id_type(100, 101, 102);
    v_emp hr.employees%rowtype;
begin
    for i in v_id.first..v_id.last loop
        select *
        into v_emp
        from hr.employees
        where employee_id = v_id(i);
        
        dbms_output.put_line('사번 : '||v_emp.employee_id||' Tax : '||v_emp.salary * 0.8);
    end loop;
end;
/

출력

 

# 사원급여를 0.8 곱해서 리턴해주고 싶을 경우

  - object처럼 함수를 쓰는 것이 아니라 내가 만든 프로그램 안에서만 수행하는 함수를 만들고 싶을 경우

declare 
    type emp_id_type is table of number;
    v_id emp_id_type := emp_id_type(100, 101, 102);
    v_emp hr.employees%rowtype;
    
    function tax(p_sal in number) return number
    is
    begin
        return p_sal * 0.8;
    end tax;
begin
    for i in v_id.first..v_id.last loop
        select *
        into v_emp
        from hr.employees
        where employee_id = v_id(i);
        
        dbms_output.put_line('사번 : '||v_emp.employee_id||' Tax : '||tax(v_emp.salary));
    end loop;
end;
/

출력

▶ 선언 부분에 함수를 정의만 해주면 된다. (create or replace 빼고!)

 

# 프로시저도 정의할 경우

declare 
    type emp_id_type is table of number;
    v_id emp_id_type := emp_id_type(100, 101, 102);
    v_emp hr.employees%rowtype;
    
    function tax(p_sal in number) return number
    is
    begin
        return p_sal * 0.8;
    end tax;
    
    procedure message
    is
    begin
        dbms_output.put_line('꿈을 이루자...!!!');
    end message;
begin
    for i in v_id.first..v_id.last loop
        select *
        into v_emp
        from hr.employees
        where employee_id = v_id(i);
        
        dbms_output.put_line('사번 : '||v_emp.employee_id||' Tax : '||tax(v_emp.salary));
    end loop;
    message; -- 프로시저는 이렇게 출력하면 된다.
end;
/

출력