SQL 프로시저, 함수, 트리거

프로시저

 개념: 매개 변수를 받을 수 있고, 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록

 용도: 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 DB에 저장하기 위해 생성

 

 - 저장 프로시저(함수)를 사용하는 이유

  정보 캡슐화

  기능의 재사용

  트랜잭션 제어 

  DB 내에서 미리 컴파일 되어 저장되므로 필요할 때마다 매번 다시 변환해야 하는 SQL 문 보다 빠르게 실행

  저장 프로시저에서 발생하는 문법 오류는 실행 시간이 아닌 컴파일 때 바로 잡을 수 있음

 

 형식

  CREATE [OR REPLACE] PROCEDURE'프로시저 명'

  (IN argument

   OUT argument

   IN OUT argument)

  

  IS

   [변수 선언]

  

  BEGIN -- 필수

   [PL/SQL Block]

  

  [EXCEPTION]

 

  END; 

 

 특징

  - 실행 환경과 프로그램 사이에 값을 주고 받는 역할

  - 블록 안에서의 변수와 똑같이 일시적으로 값을 저장하는 역할

 

 

함수

 개념: 매개 변수를 받을 수 있고, 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록

 용도: 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 DB에 저장하기 위해 생성

 

 - 프로시저와의 차이점 및 특징

  결과값 리턴

  대부분 구성이 프로시저와 유사하지만, IN 패러미터만 사용 가능

  리턴될 값의 데이터 타입을 RETURN 문에 선언 

  PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 함

 

 형식

  CREATE [OR REPLACE] FUNCTION '함수 명'

   [(argument...)]

  

  RETURN datatype

   -- datatype은 반환되는 값의 datatype

  예) RETURN VARCHAR2

       DETERMINISTIC

 

  IS

   [변수 선언 부분]

 

  BEGIN

 

  END;  

 

 DETERMINISTIC 또는 NOT DETERMINISTIC이 옵션 절은 함수가 항상 지정된 인수 값에 대해 동일한 결과를 리턴하는지 여부(DETERMINISTIC) 또는 함수가 결과에 영향을 주는 일부 상태 값을 사용하는지 여부(NOT DETERMINISTIC)를 지정합니다.

즉, DETERMINISTIC 함수는 항상 동일한 입력으로 연속 호출로부터 동일한 테이블을 리턴해야 합니다.

 **결정적 함수는 데이터베이스의 상태가 같을 경우 특정 입력 값 집합으로 호출될 때마다 항상 동일한 결과를 반환. 비결정적 함수는 액세스하는 데이터베이스의 상태가 동일하게 유지되더라도 특정 입력 값 집합으로 호출될 때마다 다른 결과를 반환할 수 있습니다.

예를 들어 AVG 함수는 항상 위에서 설명된 조건에 따라 동일한 결과를 반환하지만 현재 날짜/시간 값을 반환하는 GETDATE 함수는 항상 다른 결과를 반환합니다.

트리거

 DB에 특정한 변경이 가해졌을 때 DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저

 프로시저와 함수는 그 실행이 외부적인 실행 명령에 의해 이루어지는데 반해, 트리거의 실행은 트리거링 사건(Triggering Event)에 의해 내부적으로 이루어짐

 

 트리거를 일으키는 사건(event): DB 테이블에 DML문이 발생할 때

 

 INSERT, UPDATE, DELETE문의 사용에 사건을 정의할 수 있으며 이들을 실행할 때 정의된 트리거도 자동 실행

 

 테이블과 별도로 데이터베이스에 저장

 

 뷰에 대해서가 아니라 테이블에 관해서만 정의

 

 - 트리거 구성

  사건(event): 트리거를 가동

  조건(condition): 트리거 수행 여부 검사

  동작(action): 트리거가 수행될 때 일어나는 일

 

 형식

  

  CREATE [OR REPLACE] TRIGGER '트리거 명'

  BEFORE | AFTER trigger_event ON '테이블 명'

  [FOR EACH ROW]

  [WHEN (condition)]

  PL/SQL block

 

 * trigger event: INSERT, UPDATE, DELETE 중 한 개 이상

 

 

트리거의 용도

 - 테이블 생성시 참조 무결성과 데이터 무결성 그밖의 다른 제약 조건으로 정의할 수 없는 복잡한 요구사항에 대한 제약조건을 생성

 - 테이블의 데이터에 생기는 작업을 감시, 보안할 수 있다.

 - 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행 시킬 수 있다.

출처: http://palpit.tistory.com/140 [palpit's log-b]