🧀 프로시저
1. 개념
절차형 SQL을 활용해 특정 기능을 수행할 수 있는 트랜잭션 언어
2. 구성
(1) 선언부(DECLARE)
CREATE [OR REPLACE] PROCEDURE 프로시저명
(파라미터명 [MODE] 데이터타입 ...)
IS- [MODE]
- IN : 운영체제 → 프로시저
- OUT : 운영체제 ← 프로시저
- INOUT : 운영체제 ↔ 프로시저
- 데이터타입
- CHAR : 고정 길이 문자열, 나머지 공간은 공백으로 채움
- VARCHAR : 가변 길이 문자열, 나머지 공간은 비워둠
- NUMBER : 정수, 실수
(2) 시작/종료부(BEGIN/END)
(3) 제어부(CONTROL)
- 조건문
- IF문 : 다양한 조건식을 실행할 때
- SIMPLE CASE문 : 명확한 값을 가지는 집합에 대한 표현식의 값을 매칭할 때
- SEARCHED CASE문 : IF문과 비슷하지만 가독성이 더 좋음
- 반복문
- LOOP문 : 특정 조건이 만족될 때까지 반복해서 실행
- WHILE문 : 조건이 참(TRUE)일 경우 반복하고, 조건이 거짓(FALSE)이거나 EXIT WHEN 조건이 만족하는 경우 반복문을 빠져나옴
- FOR LOOP문 : 시작 값과 종료 값을 지정하여 해당 값이 구간 내에 있을 때 반복해서 실행
(4) SQL
- SELECT, INSERT, UPDATE, DELETE 사용
(5) 예외부(EXCEPTION)
EXCEPTION
WHEN 조건 THEN
SET 명령어;(6) 실행부(TRANSACTION)
-
COMMIT : 하나의 트랜잭션이 정상적으로 종료되고 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을 때 사용하는 연산
-
ROLLBACK : 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나 부분적으로 취소하는 연산
3. 호출
SQL> EXECUTE 프로시저명(파라미터1, 파라미터2, ...);🧀 사용자 정의 함수
1. 개념
절차형 SQL을 활용해 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
종료 시 단일 값을 반환하는 것이 특징
2. 구성
(1) 선언부(DECLARE)
CREATE [OR REPLACE] FUNCTION 함수명
(파라미터명 [MODE] 데이터타입 ...)
IS
...
RETURN 데이터타입(2) 시작/종료부(BEGIN/END)
(3) 제어부(CONTROL)
(4) SQL
- SELECT 사용
(5) 예외부(EXCEPTION)
(6) 반환부(RETURN)
3. 호출
SQL> 함수명(파라미터1, 파라미터2, ...)🧀 트리거
1. 개념
특정 테이블에 데이터 변경 이벤트가 발생하면 DBMS에서 자동으로 실행하도록 구현된 프로그램
이벤트는 전체 트랜잭션 대상과 각행에 의해 발생되는 경우 모두를 포함할 수 있으며 테이블과 뷰, DB 작업을 대상으로 정의 가능
2. 목적
특정 테이블에 대한 데이터 변경을 시작점으로 설정하고, 그와 관련된 작업을 자동적으로 수행하기 위해 사용
데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 사용
3. 종류
-
행 트리거 : 데이터 변화가 생길 때마다 실행
-
문장 트리거 : 트리거에 의해 단 한 번 실행
4. 구성
(1) 선언부(DECLARE)
CREATE [OR REPLACE] TRIGGER 트리거명 (2) 이벤트부(BEGIN/END)
순서 유형 ON 테이블명
[FOR EACH ROW]- 순서
- BEFORE : 데이터 변경 전 트리거 실행
- AFTER : 성공적인 데이터 변경 후 트리거 실행
- 유형
- INSERT : 새로운 행 삽입
- UPDATE : 기존 행 변경
- DELETE : 기존 행 삭제
- [FOR EACH ROW] : 행 트리거로 지정
(3) 시작/종료부(BEGIN/END)
(4) 제어부(CONTROL)
(5) SQL
- 행 트리거 안에서 OLD 및 NEW 수식자 접두어를 붙여 데이터를 참조 (ex.
:OLD.열A)
(6) 예외부(EXCEPTION)
- OPTIONAL
5. 작성 시 주의사항
-
TCL 사용 불가 : 트랜잭션 제어어(TCL) 사용 시 컴파일 에러 발생
-
오류 주의 : 트리거 실행 중 오류 발생 시 실행 원인이 된 데이터 작업에도 영향, 특정 테이블에 데이터를 추가한 후 실행되는 트리거에서 오류가 발생한 경우에는 트리거 이후의 작업이 진행되지 않거나 데이터가 추가되지 않음
🐭 참고문헌
- NCS 정보처리기술사 연구회. (2020). 수제비 정보처리기사 실기 (2판). 건기원.
