[용어정리] 프로시저(Procedure), 트리거(Trigger), 사용자 정의 함수

 

 

절차형 SQL의 개념

- 연속적인 실행이나, 분기, 반복 등의 제어가 가능한 SQL은 "절자형 SQL" 이다.

- 종류 (프로시저, 트리거, 사용자 정의 함수)

 

 

 

 

1. 프로시저(Procedure)

- 설명 -

■ 프로시저는 처리결과를 반환하지 않거나 한 개 이상의 값을 반환합니다.

■ 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL작업을 수행한다.

 

 

 

 

- 설명 : 구성도 -

DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부이다.
BEGIN / END : 프로시저의 시작과 종료를 의미한다.
CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의 한다.
TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부이다.

 

 

 

- 설명 : 생성 -

 

OR REPLACE : 기존의 동일한 프로시저명이 있다면, 그 프로시저를 다시 오버리이드, 즉 대체를 하게 됩니다.
IN : 프로시저의 값을 전달 할 때(= 입력 값)
OUT : 값을 반환하는 거(= 출력 값)
INOUT : 입력 값, 출력 값 동시에 하는거
매개변수명 : 실제로 전달할 그런 값들을 지정하는 매개변수명
자료형 : 매개변수에 대한 자료형을 표기한다

 

 

 

- 설명 : 생성 -

* 변수를 사용하지 않으므로, 예약어 IS만 입력한다.
* 변수를 선언한다면, IS 다음에다가 적어주면 된다.

 

 

 

 

 

- 설명 : 생성 -

 

 

 

- 설명 : 실행 -

 

 

 

 

- 설명 : 실행 -

 

 

 

- 설명 : 실행 -

 

 

 

 

- 설명 : 삭제 -

 

 

 

 

 

<연습1> - 오라클
(IN으로 해본거)

 

 

 

 

<연습2> - 오라클
(OUT으로 해본거)

 

 

 

 

<연습3> - 오라클
(이런 방식도 있음)

 

 

 

 

 

 

 

 

 

 

 

 

2. 트리거(Trigger)

- 설명 -

트리거는 데이터베이스 시스템에서
데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때마다
자동적으로 수행되는 절차형 SQL을 의미합니다.

 

 

- 설명 -

■ 데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트(Event)가 발생할때마다 관련 작업이 자동으로 수행된다.

■ 트리거는 사전적 의미로 '방아쇠'를 뜻한다. 방아쇠를 당기면 '자동'으로 총알이 나가듯이, 트리거는 테이블에 무슨일이 일어나면 '자동'으로 실행된다.

■ 트리거는 삽입,수정,삭제 등의 작업(이벤트)가 발생 시에 자동으로 작동되는 개체이다.

■ 트리거는 테이블 또는 뷰와 관련되어 DML 문(Insert, Update, Delete 등)의 이벤트가 발생될 때 작동하는 데이터베이스 개체 중 하나다.

■ 트리거가 부착된 테이블에 이벤트(입력, 수정, 삭제)가 발생하면 자동으로 부착된 트리거가 실행된다.

■ 트리거 문에는 DCL(권한 같은거, 등)을 사용 할 수 없음.

■ 트리거 문에는 DDL, DML 사용할 수 있음.

 

 

- 설명 : 구성도 -

 

 

 

 

- 설명 : 트리거 생성 -

* 동작시기
AFTER : 해당하는 테이블이 변경된 후에 "트리거 동작"을 시킬 건지
BEFORE : 테이블이 변경 되기전에  "트리거 동작"을 시킬 건지
(말그대로, 트랜잭션이 언제 시작 될건지를 지정하는거)

* 동작
INSERT, DELETE, UPDATE

* 
NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미한다.
OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미한다.

*
FOR EACH ROW : 모든 각 튜플마다 트리거를 적용한다는 의미이다.

*
WHEN

 

* 사용예

 

 

 

 

- 설명 : 트리거 생성 -

 

 

- 설명 : 트리거 삭제 -

 

 

 

 

- 설명 : 트리거 생성 -

-연습 (순서대로 보면 됨)-

(1) 이렇게 사용할 테이블 만듬.

 

(2) 트리거 연습 ㄱㄱㄱㄱ

 

(3) INSERT는 "트리거"하도록 설정을 안했음. 그냥 나옴

 

(4) UPDATE는 "트리거"하도록 설정을 했음, 그래서 트리거 실행시 작동되는 코드 출력됨!!

 

(5) DELETE는 "트리거"하도록 설정을 했음, 그래서 트리거 실행시 작동되는 코드 출력됨!!

 

 

 

 

- 트리거 종류 -

 

 

 

 

 

 

 

3. 사용자 정의 함수

프로시저외 유사하고, RETURN을 사용하여 처리 결과를 단일값으로 반환한다.

 

 

 

- 설명 : 구성도 -

 

 

- 설명 : 생성 -

 

 

- 설명 : 생성 -

 

 

- 설명 : 실행 -

 

 

 

- 설명 : 실행 -

 

 

 

 

 

- 설명 : 제거 -