[ORACLE] 트리거 Trigger / 분석함수

※ 트리거

- 서브 프로그램 단위의 하나인 트리거는 테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL 블록으로,
  관련된 특정 사건(event)이 발생될 때마다 자동으로 해당 PL/SQL 블록이 실행됨

   ( = 마치 방아쇠(trigger) 를 당기면 총알이 발사되는 것 처럼

        특정 이벤트가 발생될때마다 자동으로 PL/SQL 블록이 실행된다 )
    
- 이벤트가 발생된다 = insert, update, delete 의 DML문이나 DDL문이 실행됨 = 트리거 Trigger

 

 

▶ 주요 트리거 유형

 [1] DML 트리거
        1) 문장 트리거 - 영향을 받는 행이 전혀 없더라도 트리거가 한번은 실행됨
        2) 행 트리거 - 테이블이 트리거 이벤트의 영향을 받을 때마다 실행되고,
            트리거 이벤트의 영향을 받는 행이 없을 경우에는 실행되지 않음
            
 [2] DML이 아닌 트리거
        1) DDL 이벤트 트리거
             - DML 트리거와 거의 동일하지만 트리거를 활용하여 DDL작업을 하는 것만 다름
          
        2) 데이터베이스 이벤트 트리거
            - 데이터베이스 내에서 생기는 일들을 관리하기 위해서 생성하는 트리거   

 

 

▶ 트리거 생성

 - 먼저 백업해둘 테이블 틀 똑같이(0=1) 해서 생성해놓기

 create or replace trigger 트리거 이름
        트리거 실행시점 [before/after]
        이벤트 [insert | update | delete]
        on {테이블이름 | 뷰이름 | 스키마 | 데이터베이스}
    [for each row]
    begin
        트리거 몸체
    end;

 

old - 변경할 때 변경 전의 값을 가지고 있음  = > :old.컬럼명
new - 데이터가 추가 혹은 변경되면 new 연산자로 변경 후의 값을 얻을 수 있음 => :new.컬럼명

user - 현재 접속중인 사용자를 나타냄 => user

 

 

 

 

▷ 문장 레벨 트리거

 

ex ) 입력시간이 15:50 ~ 16:00 일 경우만 입력을 허용하고, 그 외 시간일 경우는 에러를 발생시키는 트리거

create or replace trigger tr_check_time
before insert on t_order    // t_order 테이블에 입력되기전에 시행되어라
begin
    if to_char(sysdate, 'hh24:mi')not between '15:50' and '16:00' then
        raise_application_error(-20009, '15:50 ~ 16:00 일 경우만 입력가능 ! ');
    end if ;

end;

 

 

▷ 행 레벨 트리거

 

ex ) 테이블에 입력될 데이터 값을 지정('C100') 하고, 그 값 외에는 에러를 발생시키는 트리거

create or replace trigger tr_code_check
   before insert on t_order
for each row -- 행레벨트리거
begin 
    if :new.ord_code != 'C100' then     // order 의 ord_code 컬럼에 새롭게 입력되는 값이 C100 이 아니면
        raise_application_error(-20010,'제품코드가 C100인 제품만입력가능!');
    end if;
end;

 

 

▶ 백업트리거 - 행 레벨 트리거

 

▷ 수정될때 백업

ex ) 기존 테이블(t_test1)에 데이터가 수정(update)될 때 기존 내용을 백업 테이블(t_test1_bak)로 이동시키는 트리거

create or replace trigger tr_backup_t_test1
after update on t_test1
for each row
begin
        insert into t_test1_bak(no,name)
        values(:old.no, :old.name);
end;

 

▷ 삭제될때 백업

ex ) 기존 테이블(t_test2)의 데이터가 삭제될 때 기존 내용을 백업 테이블(t_test2_bak)로 이동시키며 이때 백업 테이블에 삭제한 시간, 삭제 전 데이터를 모두 기록하는 트리거

create or replace trigger tr_backup_t_test2
after delete on t_test2
for each row
begin
    insert into t_test2_bak
    values(:old.no, :old.name, sysdate);
end;

 

▷ 로그 테이블

ex ) 실습. 기존 테이블(t_test3)의 추가, 변경, 삭제된 내용을 별도의 로그 테이블을 생성하여 기록을 남기도록 트리거를 설정

create or replace trigger tr_log_t_test3
after update on t_test3
for each row
begin
 insert into t_test3_history(o_no,o_name, n_no, n_name, who, regdate, chk)
    values( :old.no, :old.name, :new.no, :new.name, user, sysdate,'U');
end;

 

 

▶ 트리거 삭제

drop trigger tr_check_time; -- 트리거 drop

 

 

 

 

---

 

 

 

※ 분석함수

 

▶ 순위함수

- rank() : 중복 순위 개수만큼 다음 순위 값을 증가 시킴 1,2,2,4

- dense_rank() : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함 1,2,2,3

- row_number()

rank | dense_rank | row_number()
    over(<partition by절>, <order by 절>)

 

 

▷ rank() , dense_rank() 차이 한눈에 보기

출처 : https://gent.tistory.com/266 

 

 

ex ) 급여가 높은 순서대로 순위를 부여하여 출력

select department_id, employee_id, first_name, hire_date, salary,
	dense_rank() over(order by salary desc) "전체 순위(dense_rank)",
	rank() over(partition by department_id order by salary desc) "부서내 순위(rank)",
    row_number() over(order by salary desc) "전체 순위(row_number)"
from employees
order by department_id, salary desc;

partition by :  ( = group by )

  - 부서별로 group 지어서 부서내에서 다시 order by

 

 

 

 

ex ) 급여가 가장 많은 사원 (1~5위)까지만 조회하기

 - rank 이용

 - rank 등 분석함수는 where 절에 올 수 없다. 따라서 inline view 이용

select *
from 
(
    select employee_id, first_name, salary, department_id, 
        rank() over (order by salary desc) RANK --별칭
    from employees
)
where RANK <= 5;

 

 

▷ rownum, row_number 차이점보기

 

ex ) 입사일 기준으로 정렬한 후 조회하기

select row_number() over(order by hiredate desc)  NO,
    empno, ename, hiredate
from emp;

- rownumber 는 바로쓸수있는데

 

select rownum,A.*
from
(
    select empno, ename, hiredate
    from emp
    order by hiredate desc
)A;

- rownum 은 inlineview 써야한다

 

 

 

근데 ' ~ 까지만 조회 ' 같은 조건이 붙으면

 

ex ) 최근에 입사한 7명을 순서대로 조회

select *
from
(
    select row_number() over(order by hiredate desc)  NO,
        empno, ename, hiredate
    from emp
)
where No <= 7;

select rownum,A.*
from
(
    select empno, ename, hiredate
    from emp
    order by hiredate desc
)A
where rownum<=7;

 둘 다 inline view 써야됨 ! 

 

 

 

 

▶ 누적 합계 구하기

ex ) 제품 코드별, 날짜별 누적 판매량

select p_code, p_date, qty_total 판매량,
    sum(qty_total) over(partition by p_code order by p_date) 누적판매량
from
(
    select p_code, p_date, sum(p_qty) as qty_total
    from panmae
    group by p_code, p_date
);

 

 

 

ex ) 전체 일자별로 누적 판매량, 누적 판매금액 구하기

select p_date 판매일자, qty 판매량, price 판매금액,
    sum(qty) over(order by p_date) 누적판매량,
    sum(price) over(order by p_date) 누적판매금액
from
(
    select p_date, sum(p_qty) qty, sum(p_total) price
    from panmae
    group by p_date
    order by p_date 
);

 

ex ) p_store별로 누적 판매금액 구하기, 판매일자로 정렬해서 누적

select p_store, p_date, price "판매금액",
    sum(price) over(partition by p_store order by p_date) 누적판매금액
from
(
    select p_store, p_date, sum(p_total) price
    from panmae
    group by p_store,p_date
);