[Oracle] 프로시저, 사용자 정의 함수, 트리거

1. 절차형 SQL이란?

I) 개념 

 SQL도 일반 프로그램 언어와 유사하게 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.

 

II) 종류

1) 프로시저 

일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

2) 사용자 정의함수

일련의 SQL 처리를 수행하고 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

 

3) 트리거               

데이터베이스 시스템에서 CURD 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

III) 특징

- 단일 SQL 문장으로 실행하기 어려운 연속적인 작업을 처리하는데 적합하다.

- DBMS 내부에서 직접 처리하기 때문에 일반적으로 입출력 데이터가 적다.

 

IV) 필수 구성 요소

- DECLARE(선언) : 대상의 이름 및 변수등을 선언 

- BEGIN(시작)       : 실행 시 시점, 프로시저, 사용자 정의 함수가 실행되는 시작점이다.

- END(끝)              : 실행 시 종점, 프로시저, 사용자 정의 함수가 실행되는 종료점이다.

 

2. 프로시저

I) 프로시저 문법

CREATE [OR REPLACE] PROCEDURE 프로시저명
(파라미터명 [IN|OUT|INOUT] 데이터타입, ... )
IS
     변수선언
BEGIN
     명령어;
[COMMIT|ROLLBACK]
END;

II) 프로시저 호출 예시

SQL>EXECUTE p_DEPT_insert(10, 'dev', 'seoul', :rslt);

SQL> print rslt;

 

1) EXECUTE 구문을 이용해 정의한 프로시저를 실행한다.

2) 프로시저의 OUT변수인 rslt를 print할 때, DEPTNO(10)가 이미 존재한다면 이미 등록된 부서번호라 출력될 것이다.

   반대로 없다면 입력 완료로 출력된다.

 

III) 프로시저 호출 특징

- 외부 호출을 통해 실행된다. ex) 응용 프로그램에서 호출, 내부 스케쥴러 배치 작업 중 실행

- SQL 명령어를 활용하여 작성된 프로시저를 호출한다.

- SQL TOOL을 사용하지 않고 다른 프로시저에서 호출 시에는 별도 명령어 없이 "@프로시저명" 또는 프로시저 명칭만 사용하여 호출이 가능하다.

 

3. 사용자 정의 함수

I) 사용자 정의 함수 호출 예시

# 사용자 정의 함수 결과 값을 데이터 질의어에 활용
SQL> SELECT [사용자정의 함수명](매개변수1, 매개변수2, ...)
            FROM 테이블명;
            
# 사용자 정의 함수 결과 값을 데이터 조작어에 활용
SQL> UPDATE [테이블명]SET [필드명] =
            [사용자정의함수](매개변수1...)WHERE 조건..;

 

II) 사용자 정의 함수 특징

- 연산 처리 결과를 단일 값으로 반환할 수 있다.

- 사용자 정의 함수의 호출을 통해 실행되며, 반환되는 값을 CRUD에 재이용하는 것이 일반적이다.

 

 

4. 트리거

I) 트리거 문법

CREATE [OR REPLACE] TRIGGER '트리거 이름'()
   [BEFORE|AFTER] 유형 ON 테이블명
   [FOR EACH ROW]
BEGIN
END;

II) 트리거 호출 예시

DELETE TRIGGER TNAME ON INSERTION OF 갱신:
(UPDATE 회원
  SET 회원, 적립액 = 회원, 적립액 + (갱신.가격 * 갱신.적립비율)
  WHERE 갱신.회원번호 = 회원.회원번호);

III) 트리거 특징

- CRUD 이벤트가 발 생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다.

- 일반적으로 이벤트와 관련된 테이블에 데이터 삽입, 추가, 삭제 작업을 자동으로 실행시키는데 활용되나, 데이터의 무결성 유지, 로그 메세지 출력 등의 처리를 위해 사용하기도 한다.

 

5. 프로시저와 트리거 차이점

 

프로시저	트리거
- CREATE 프로시저  문법 사용 - EXECUTE 명령어로 실행 - COMMIT, ROLLBACK 가능	- CREATE 트리거 문법 사용 - 생성 후 자동 실행 - COMMIT, ROLLBACK 불가능