SQL | 절차형 SQL 프로시저, 사용자 정의함수, 트리거

1. 절차형 SQL

 

절차형 SQL

: 일반적인 개발언어(C,JAVA...)처럼 SQL언어에서도 절차 지향적인 프로그램(조건문, 반복문)이 가능하도록 하는 트랜잭션 언어

 

절차형 SQL 종류(프사트)

프로시저	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리 집합
사용자 정의 함수	일련의 SQL처리를 수행하고 , 수행결과를 단일 값으로 반환할 수 있는 절차형 SQL (리턴함수가 필요)
트리거	데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

DBMS_OUTPUT

-메시지를 버퍼에 젖아하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지

-절차형 SQL이 정상적으로 구현되었는지 테스트하는 목적으로 사용

DBMS_OUTPUT.PUT(문자열);	개행없이 문자열을 출력하는 프로시저
DBMS_OUTPUT.PUT_LINE(문자열);	문자열을 출력 후 개행하는 프로시저

 

IF문

조건이 참인지 거짓인지에 따라 경로를 선택하는 조건문

IF 조건 THEN
  문장;
ELSIF 조건 THEN
  문장;
  ...
ELSE
  문장;
 END IF;

 

간단한 케이스문

-명확한 값을 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 떄 사용하는 조건문

-범위 같은 더 복잡한 매칭을 수행하려면, 검색된 CASE문을 사용해야 함

CASE 변수
WHEN 값1 THEN
  SET 명령어;
WHEN 값2 THEN
  SET 명령어;
  ...
ELSE
  SET 명령어;
END CASE;

 

검색된 케이스문

-명확한 값 및 범위를 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 떄 사용하는 조건문

CASE 
WHEN 조건1 THEN SET 명령어;
WHEN 조건2 THEN SET 명령어;
...
ELSE SET 명령어;
END CASE;

 

반복문

LOOP문

-특정 조건이 만족될 때 까지 반복해서 문장을 실행하는 반복문

-EXIT WHEN에 반복문 탈출 조건을 작성한다.

LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP

WHILE문

-시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행하는 명령문

-조건이 참일 경우 반복, 조건이 거직이거나 EXIT WHEN 조건이 만족하는 경우 반복문 빠져나옴

WHILE 반복조건 LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;

FOR LOOP

-시작값과 끝값을 지정해 해당 값이 그 구간 내에 있을 때 반복하는 반복문

FOR 인덱스 IN 시작값.. 종료값
LOOP
 문장;
END LOOP;

 

예외부

-실행 중 발생 가능한 예외상황을 수행하는 부분

EXCEPTION
WHEN 조건 THEN
SET 명령어;

 

 

2. 프로시저

 

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

 

프로시저 구성(디비컨 SET)

선언부 (DECLEAR)	-프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분
시작/ 종료부 (BEGIN/END)	-프로시저의 시작과 종료를 표현하며 BEGIN.END가 쌍을 이룸 -다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	-순차적으로 처리 -조건문과 반복문을 이용하여 문장을 처리
SQL	-DML을 주로 사용 -자주 사용되지 않지만 DDL중 TRUNCATE 사용
예외부 (EXCEPTION)	-BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외처리 방법 정의하는 처리부
실행부 (TRANSCATION)	-프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부

 

프로시저 문법

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

문법	설명
[OR REPLACE]	기존 프로시저 존재 시에 현재 컴파일하는 내용으로 덮어씀
모드	변수의 입출력을 구분하고, IN/OUT/INOUT 3가지로 구성
BEGIN	프로시저의 시작을 알려주는 키워드
COMMIT	하나의 트랜잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을 때 사용하는 연산
ROLL BACK	하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산
END	프로시저의 끝을 알려주는 키워드

 

 

3. 사용자 정의함수

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

 

-기본적인 사항은 프로시저와 동일하고 반환에서의 부분만 프로시저와 다르다

-사용자 정의함수의 호출을 통해 실행되며, 반환하는 단일 값을 조회 또는 삽입, 수정 작업에 이용하는것이 일반적

-기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일 값을 반환

 

구성(디비컨 SER)

선언부 (DECLEAR)	-프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분
시작/ 종료부 (BEGIN/END)	-사용자 정의함수의 시작과 종료를 표현하는데 필수적, BEGIN/END가 쌍을 이루어 추가되므로 브록으로 구성 -다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	-순차적으로 처리 -비교 조건에 따라 블록 또는 문장을 실행 -조건에 따라 반복 실행
SQL	-조회 용도로 SELECT 문을 사용 -데이터를 조작하는 INSERT, DELECT, UPSATE는 사용할 수 없음
예외부 (EXCEPTION)	-BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외처리 방법 정의하는 처리부
반환부 (RETURN)	-호출문에 대한 함숫값을 반환

 

 

4. 트리거

-트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행하는 절차형 SQL이다.

-이벤트는 전체 트랜잭션 대상과 각행에 의해 발생하는 경우 모두를 포함할 수 있으며 테이블과 뷰, DB작업을 대상으로 정의할 수 있다.

 

목적

-특정 테이블에 대한 데이터 변경을 시작점으로 설정하고, 그와 관련된 작업을 자동적으로 수행하기 위해 트리거 사용

-일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용

-데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 트리거 사용

 

종류

-행 트리거 : 데이터 변화가 생길 때마다 실행

-문장 트리거 : 트리거에 의해 단 한 번 실행

 

구성

-반환 값이 없다는 점, DML을 주된 목적으로 한다는 점에서는 프로시저의 유사함

-EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점, 외부변수 IN/OUT이 없다는 점은 프로시저나 사용자 정의함수와 다르다.