trigger와 stored procedure

트리거(Triggers) * 트리거란? 결합된 테이블에 대하여 insert, update, delete명령이 발생될때 implicit하게 생행되는 프로시저 트리거는 하나의 단위로서 실행되는 sql과 pl/sql문을 포함할 수 있으며 다른 프로시저와 트리거를 호출할 수 있다. 트리거는 insert, update, delete명령 전후에 호출되며, 제작자는 DML문과 관련하여 언제 실행될 것인지를 지정한다 트리거는 테이블의 뷰나 시노님에서가 아니라 테이블에서 정의되지만 뷰의 베이스 테이블에 있는 그 뷰에 대하여 DML문이 발생될 때도 실행된다 * 트리거 사용 이유 1. 파생 열 값을 자동적으로 생성한다. 2. 보안 허가와 제한을 집행한다. 3. 투명 이벤트 로깅을 제공한다. 4. 테이블 액세스에 대한 통계를 수집한다. 5. 오라클 내부 오디팅 시스템을 보충하는데도 사용할 수 있다. 6. 분산 데이타베이스의 서로 다른 노드에 위치한 복제 테이블을 동기적으로 유지하는 데에도 사용 할 수 있다. 스냅샷에 이런 방식으로 트리거를 사용하는 것이 한 예이다. * 트리거를 위한 지침 1. 미리 정의된 다른 연산이 실행될 때마다 특정 연산을 수행하고 싶을 때에만 트리거를 사용한다. 2. 트리거링 문을 위하여 실행되어야 할 중앙화된 글로벌 연산을 위하여 트리거를 사용한다. 3. 반복적인 트리거를 만들지 않도록 조심하여야 한다. 4. 코드를 되도록 작게 하기 위하여 트리거를 만드는 것을 삼가야 한다. 5. sql문이 실행될때 프로세스되는 행의 순서에 의존하여 트리거를 만들면 안된다 * 트리거 파트(요소) 1. 트리거 문: 어떤 sql문이 오라클로 하여금 트리거를 지시하게 만드는지 지정한다. 이 트리거문은 insert, update, delete문이 될 수 있다. 2. 트리거 제한: 지시할 트리거를 위하여 참이 되어야 할 조건을 지정. 이 조건은 pl/sql 조건이 아니라 sql조건이어야 한다. 그 조건은 when절에 상주할 수 있다. 3. 트리거 활동: 이것은 트리거가 지시될 때 오라클 싱행하는 pl/sql블록을 지정한다 * 트리거 타입 트리거 활동이 실행되는 횟수를 지정한다. 1. 행 트리거: 트리거 문에 의하여 테이블이 영향을 받을 때마다 실행된다. 예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거가 30회 지시된다. 2. 문장 트리거: 트리거 문을 위하여 단 한번 지시된다. 예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거는 단 1번 실행된다 * 트리거 타이밍 언제 시작할 지를 지정한다. before트리거는 트리거링 문 앞에서 실행된다. 다음을 위해 실행된다. 1. insert나 update문의 트리거링을 끝내기 전에 열 값을 끌어내기 위하여 2. 트리거 활동을 실행해야 할 것인지 여부를 결정하기 위하여 after트리거는 트리거 문이 실행된 후 트리거 활동을 실행한다. 다음을 위해 시행됨 1. 트리거 활동 이전에 트리거링 문을 끝내고 싶을때 2. before트리거 활동 외에 추가의논리를 시행하기 위하여 * 트리거 만들기 create trigger 'trigger command' trigger command: before, after, delete, insert, update, of, on, for each row when, or replace create trigger parts_delete after delete on mst_parts for each row when(part_price < 100) declare . . . end; * 트리거 유.무효화 하기 alter trigger t_inv_count disable * 트리거 실행 sql문은 트리거를 4개까지 실행할 수 있다. 이 4개의 트리거는 before row, after row, before statement, after statement트리거 이다. * 트리거 변경 트리거를 명시적으로 변경할 수는 없다. create trigger or replace를 사용하여야 한다. 또는 트리거를 drop했다가 다시 만들어야 한다. * 트리거 재컴파일하기 기존 트리거의 재컴파일을 집행하려면 alter trigger명령을 사용한다. 트리거는 재컴파일될 때 유효화될 수도 있고 무효화될 수도 있다. alter triggfer명령은 기존 트리거의 정의를 변경하지 않는다. * 스냅샷 로그 트리거 테이블을 위하여 스냅샷 로그가 만들어질때 오라클은 그 테이블에 after row트리거를 명시적으로 만든다. 그러므로 사용자가 정의한 after row 트리거는 이 동일한 테이블에서는 만들어질 수 없다. 내장 프로시저 * 내장 프로시저(stored procedure) 특정 작업을 수행하는 논리적으로 그룹지어진 sql과 pl/sql문의 셋이다. 선언파트와 실행파트로 구성되어 있다. * 내장 프로시저 사용 이유 1. pl/sql은 특정 요구에 맞도록 프로시저를 맞출수 있게 해준다. 2. 이 프로시저들은 모듈적이다. 하나의 프로그램을 관리할 수 있는 잘 정의된 유니트로 구성되어 있다. 3. 프로시저들은 데이타베이스에 내장되기 때문에 재사용할 수 있다. 일단 유효화되면, 네트워크를 가로질러 재컴파일하거나 분포하지 않고도 반복해서 사용할 수 있다. 4. 데이타의 보완성을 개선한다. 5. 공유메모리 자원을 활용함으로써 메모리를 개선할 수 있다. * 내장 프로시저 사용 필요조건 데이터베이스 관리자는 catproc.sql스크립트를 실행할 책임이 있다. * 내장 프로시저 만들기 create procedure명령은 독립형 프로시저를 만든다. * create문 에서 사용할 수 있는 파라미터의 리스트 1. in : 호출되는 서브 프로그램에 값을 패스해야 한다고 지정한다. 2. out: 호출하는 프로시저에 값을 리턴한다는 것을 지정한다. 3. inout : 위의 둘다 4. or release : 프로시저가 존재할 경우 다시 만든다. create procedure user_o1.parts (part_id number, qty number) as begin update journal set journal.qty = journal.qty + qty where journal_id = part_id; end; * return문 프로시저에서 return문은 표현식을 포함할 수 없다. * 내장프로시저 재컴파일하기 alter procedure로 가능하며, 독립형 내장 프로시저에서만 사용해야 하며 패키지의 일부인 프로시저에서 사용해서는 안된다. * 내장 프로시저 호출하기 parts_sum(qty, wip_nbr); execute parts_sum(qty, wip_nbr);