절차형 SQL 개념 및 종류(프로시저, 사용자정의함수, 트리거)

1. 절차형 SQL이란

절차형 SQL이란 일반적인 언어형식과 같이 절차적으로 명령을 수행하는 SQL을 뜻한다. 

DBMS 벤더별로  PL, SQL, SQL/PL, T-SQL 등의 절차형 SQL이 있다.

 

절차형 SQL은 DBMS 엔진에서 실행되고 단일 SQL 문장으로 실행하기 어려운 연속적인 작업 처리가 가능하다.

또한 로직을 캡슐화할 수 있기 때문에 반복 적인 DB 작업을 효율적으로 수행하고 소프트웨어 개발 생산성을 높인다는 장점이 있다.

 

2. 절차형 SQL 종류

1) 프로시저(Procedural Language) :

일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. 자주 사용되는 SQL문을 DB 객체로 생성하여 저장한 후 필요시에 프로시저명을 호출해서 사용할 수 있다.

 

- 프로시저 구조

CREATE OR REPLACE PROCEURE "원하는 프로시저이름" ( // 프로시저 생성
	"파라미터 이름" [IN\OUT\INOUT 중에 선택] "데이터 타입", ...)
IS
    변수선언 
BEGIN	//프로시저 시작
	명령어;
[COMMIT\ROLLBACK]
END; // 프로시저 끝

 

- 프로시저 문법

OR REPLACE : 기존 프로시저 존재 시 내용을 대체 한다는 명령어이다.

IN : 변수값을 입력받고 프로시저로 전달한다.

OUT: 프로시저로 처리된 결과를 리턴한다.

INOUT: 변수값을 받고 프로시저 처리 후 리턴한다.

COMMIT: 하나의 트랜잭션이 성공적으로 끝나고 데이터베이스가 일관적인 상태로 끝났을 때 사용한다

ROLLBACK: 하나의 트래잭션이 비정상적으로 끝나 처음부터 다시 시작하거나 부분적으로 연산을 취소할 때 사용한다.

 

2) 사용자 정의함수(User-Defined Function):

절차형 SQL을 사용하여 일련의 SQL 처리를 수행한다. 프로시저와 사용법이 동일하다. 하지만 수행 결과를 단일 값으로 반환 한다는 특징이 있다.

 

- 사용자 정의함수 구조 (데이터 타입과 변수를 리턴하는 부분의 프로시저와 다르다.)

CREATE OR REPLACE FUNCTION "원하는 사용자 함수 이름" ( // 사용자함수 생성
	"파라미터 이름" [IN\OUT\INOUT 중에 선택] "데이터 타입", ...)
RETURN 데이터 타입  // 데이터 타입을 리턴
IS
    변수선언 
BEGIN	//사용자 함수 시작
	명령어;
	RETURN 변수;	// 변수를 리턴
END; // 사용자 함수 끝

- 사용자 정의함수 문법:

프로시저와 동일

 

3) 트리거(Trigger)

트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다. 사용자가 직접 호출하는 것이 아닌 DBMS에서 자동적으로 호출하는 것이 특징이다.

 

- 트리거 종류:

행 트리거: DML의 영향을 받는 모든 행 레코드마다 트리거가 작동되는 트리거를 뜻한다. 데이터 변화가 생길 때마다 실행된다.(FOR EACH ROW 명령어를 사용하면 행 트리거)

명령문 트리거: 데이터가 삽입, 삭제, 갱신되더라도 트리거에 의해 단 한 번 실행되는 트리거를 뜻한다.

 

- 트리거 구조:

CREATE OR REPLACE TRIGGER "원하는 트리거 이름"
[BEFORE/AFTER 선택] 삽입,삭제,갱신(INSERT, DELETE, UPDATE) ON 테이블명
[FOR EACH ROW] // 행트리거, 명령문트리거라면 생략가능
BEGIN
END;

 

- 트리거 문법:

BEFORE: 테이블명에 대한 작업을 수행하기 전에 트리거가 실행하게 하는 명령어

AFTER: 테이블명에 대한 작업을 성공적으로 실행했을 때 트리가 실행하도록 하는 명령어

INSERT: 새로운 행 삽입

DELETE: 기존 행 삭제

UPDATE: 기존 행 갱신

FOR EACH ROW:  매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어(행 트리거 판별)