(DB실무_SQLD 범위 제외) Part80-SQL 기본 및 활용-8.PL_SQL
2026년 11월 8일 공유
PL/SQL의 개요와 프로시저·함수·트리거
개요
- 콘텐츠 유형: 강의
- PL/SQL은 SQL 안에서 반복문과 조건문 같은 절차적 처리를 할 수 있도록 지원하는 언어다.
- 이 강의의 핵심 목표는 코드를 직접 작성하는 것보다 프로시저, 사용자 정의 함수, 트리거의 형태와 동작 차이를 구분하는 것이다.
핵심 개념
PL/SQL의 특징
- 여러 SQL 문과 프로그램 로직을 블록 단위로 묶어 실행할 수 있다.
- SQL 문 사이에 값을 전달할 수 있도록 변수와 상수를 사용할 수 있다.
- DBMS가 제공하는 예외와 사용자가 정의한 예외를 처리할 수 있다.
- 블록으로 묶어 서버에 전달하면 여러 문장을 효율적으로 처리할 수 있다.
PL/SQL 블록의 기본 구조
- 선언부: 변수 등 선언
- 실행부: SQL 문과 절차적 로직 수행
- 예외 처리부: 오류 처리
- 프로시저·함수·트리거는 이 기본 구조를 공유하지만, 생성 방식과 실행 방식이 다르다.
프로시저
CREATE OR REPLACE PROCEDURE로 생성한다.
- 매개변수 모드는 다음과 같다.
- IN: 외부에서 값을 전달받는다. 기본 모드다.
- OUT: 처리 결과를 외부로 전달한다.
- IN OUT: 값을 입력받아 수정한 뒤 다시 전달한다.
- 함수처럼
RETURN으로 값을 반환하지 않고, 주로 OUT 매개변수로 결과를 전달한다.
- 일반적으로
EXECUTE명령으로 명시적으로 실행한다.
- 예: 부서 번호가 이미 있는지 확인한 뒤, 중복이면 안내 메시지를 전달하고 없으면 부서 정보를 삽입한다. 오류가 발생하면
ROLLBACK할 수 있다.
사용자 정의 함수
CREATE OR REPLACE FUNCTION으로 생성한다.
- 프로시저와 유사하게 SQL과 절차적 로직을 포함하지만, 반드시
RETURN으로 값을 반환해야 한다.
- 반환값은 함수 선언부에 자료형을 명시한다.
- 프로시저와 달리 함수 이름을 식이나 SQL 문에서 값처럼 사용할 수 있다.
- 예: 입력값이 음수이면 부호를 바꾸고, 그렇지 않으면 그대로 반환해 절댓값을 구하는 함수.
트리거
- 특정 테이블에 대한 INSERT, UPDATE, DELETE 등 DML 작업을 계기로 자동 실행되는 프로그램이다.
- 프로시저처럼 직접 호출하지 않는다. 트리거를 생성한 뒤 조건에 맞는 DML이 실행되면 자동으로 동작한다.
- 무결성 유지, 업무 규칙 적용, 감사, 집계 정보 갱신 등에 활용할 수 있다.
BEFORE또는AFTER로 실행 시점을 정하고,FOR EACH ROW의 사용 여부로 실행 단위를 정한다.
- 행 수준 트리거:
FOR EACH ROW를 사용하며, 영향을 받는 각 행마다 실행된다.
- 문장 수준 트리거:
FOR EACH ROW를 사용하지 않으며, SQL 문장 전체에 대해 한 번 실행된다.
- 행 수준 트리거에서는
:OLD와:NEW로 변경 전후의 값을 참조할 수 있다.
- UPDATE:
:OLD는 변경 전,:NEW는 변경 후 값
- INSERT:
:OLD는 값이 없고:NEW에 입력값이 있음
- DELETE:
:OLD에 삭제 전 값이 있고:NEW는 값이 없음
%TYPE을 사용하면 테이블 컬럼과 같은 자료형의 변수를 선언할 수 있다.
상세 정리
프로시저와 함수 비교
구분 | 프로시저 | 사용자 정의 함수 |
|---|---|---|
결과 전달 | 주로 OUT 매개변수 | 필수 RETURN |
실행 방식 | 보통 EXECUTE로 명시적 실행 | 이름을 식이나 SQL 문에서 사용 |
공통점 | SQL과 절차적 로직을 포함하는 PL/SQL 프로그램 | SQL과 절차적 로직을 포함하는 PL/SQL 프로그램 |
트리거의 활용과 주의점
- 주문이 추가될 때 판매 집계 테이블을 갱신하거나, 관련 데이터의 무결성을 유지하는 데 사용할 수 있다.
- 트리거가 다른 트리거를 연쇄적으로 실행할 수 있으므로, 한 번의 DML이 여러 작업으로 이어질 수 있다.
- 트리거에서 오류가 발생해 트랜잭션이 롤백되면, 해당 작업으로 파생된 연산도 함께 취소된다. 따라서 트리거는 관련 작업을 하나의 트랜잭션 흐름으로 이해해야 한다.
- 트리거 안에서
COMMIT이나ROLLBACK을 직접 수행할 수 없다.
- 트리거를 과도하게 사용하면 실행 흐름을 파악하기 어려워지고 성능에 부담을 줄 수 있으므로 필요한 경우에 신중하게 사용한다.
- 행 수준 트리거와 문장 수준 트리거는 실행 시점과 실행 횟수가 다르다. 여러 행이 변경되면 행 수준은 행마다 실행되고, 문장 수준은 문장 전체에 대해 한 번 실행된다.