절차형 SQL: PL/SQL·프로시저·함수·트리거
Oracle PL/SQL 블록과 프로시저·함수·트리거의 실행 구조, 동적 SQL, 트랜잭션 주의사항을 SQLP 고급 SQL 보충 범위로 정리합니다.
핵심 요약
PL/SQL 블록과 저장 프로시저·사용자 정의 함수·트리거의 호출 방식, 트랜잭션 및 동적 DDL 차이를 구분한다.
핵심 질문
- 절차형 SQL: PL/SQL·프로시저·함수·트리거에서 반드시 구분해야 할 개념과 결과 규칙은 무엇인가?
- 0건·1건·여러 건과 NULL·동점·중복 데이터에서 결과가 어떻게 달라지는가?
- 비슷해 보이는 문법과 결과가 같아지는 조건, 달라지는 조건은 무엇인가?
- 작은 샘플 데이터를 이용해 결과를 실수 없이 예측하는 순서는 무엇인가?
학습 목표
- PL/SQL 블록의 선언부·실행부·예외 처리부를 구분한다.
- 프로시저, 사용자 정의 함수와 트리거의 호출 방식을 비교한다.
- PL/SQL 안에서 DDL을 실행할 때 동적 SQL이 필요한 이유를 설명한다.
개념 지도
입력 → 변수·분기·반복 → SQL 실행 → 예외 처리 → Transaction 경계
핵심 구조
PL/SQL은 SQL에 변수, 조건문, 반복문과 예외 처리를 더한 Oracle의 절차형 언어다. 절차 코드는 PL/SQL 엔진이 처리하고 블록 안의 SQL은 SQL 실행 엔진에 전달된다.
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp;
DBMS_OUTPUT.PUT_LINE(v_count);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
| 객체 | 실행 방식 | 대표 용도 |
|---|---|---|
| Procedure | 사용자가 이름으로 호출 | 여러 SQL과 업무 절차 수행 |
| Function | 값을 반환하며 SQL이나 PL/SQL에서 호출 | 계산·변환 |
| Package | 관련 타입·변수·프로시저·함수를 묶음 | 모듈화와 캡슐화 |
| Trigger | 지정 이벤트에 DB가 자동 호출 | 감사, 파생 처리, 제한된 무결성 보조 |
DDL과 동적 SQL
정적 PL/SQL 문장 위치에는 TRUNCATE TABLE 같은 DDL을 직접 쓸 수 없다. 문자열로 만든 문장을 EXECUTE IMMEDIATE로 실행한다.
CREATE OR REPLACE PROCEDURE reload_dept
AUTHID CURRENT_USER
AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE dept';
INSERT /*+ APPEND */ INTO dept
SELECT * FROM tmp_dept;
COMMIT;
END;
/
DELETE는 DML이므로 롤백할 수 있지만 Oracle의 TRUNCATE는 DDL이며 묵시적 커밋과 연결된다. 따라서 “롤백할 수 없게 전체 행과 사용 공간을 정리”하는 요구에서는 둘을 구분해야 한다.
트리거와 트랜잭션
트리거는 테이블·뷰의 DML, 스키마 또는 데이터베이스 이벤트에 반응해 자동 실행된다. 문장 단위와 행 단위 트리거가 있으며, 행 단위 트리거는 :OLD, :NEW로 변경 전후 값을 참조한다. 일반 트리거는 자신을 발생시킨 트랜잭션의 일부이므로 독립적으로 COMMIT이나 ROLLBACK을 수행할 수 없다.
PRAGMA AUTONOMOUS_TRANSACTION은 호출한 트랜잭션과 별도인 자율 트랜잭션을 명시하는 특수 기능이다. 모든 프로시저와 함수가 자동으로 하나의 고정 트랜잭션이 되는 것도 아니고, 모든 호출이 자동으로 자율 트랜잭션이 되는 것도 아니다.
문제에 적용하는 순서
- 누가 실행하는지 본다. 직접 호출이면 프로시저·함수, 이벤트로 자동 실행이면 트리거다.
- 값을 반환해야 하는지 본다. SQL 표현식에서 사용할 반환값이면 함수를 우선 생각한다.
- DDL을 PL/SQL 내부에서 실행하는지 확인한다. 그렇다면
EXECUTE IMMEDIATE가 필요하다. COMMIT,ROLLBACK,TRUNCATE가 나오면 객체 종류보다 먼저 트랜잭션 경계를 따진다.
흔한 오해와 주의점
- 트리거는 사용자가 매번 직접 호출하는 저장 프로그램이 아니다.
- 사용자 정의 함수가 트리거처럼 자동으로 무결성을 지켜 주는 것은 아니다.
- 일반 트리거에서 임의로 TCL을 수행할 수 있다고 판단하지 않는다.
EXECUTE 'DELETE FROM temp_orders'가 아니라EXECUTE IMMEDIATE 'DELETE FROM temp_orders'가 Oracle 동적 SQL 문법이다.
PL/SQL Block의 기본 구조
DECLARE
v_count PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM orders
WHERE status = 'READY';
DBMS_OUTPUT.PUT_LINE(v_count);
EXCEPTION
WHEN OTHERS THEN
-- 필요한 정보를 기록한 뒤 호출자에게 오류를 다시 전달
RAISE;
END;
/
DECLARE는 선택 영역이고 BEGIN NULL; END;는 필수입니다. 예외를 잡고 아무 처리 없이 삼키면 호출자는 실패를 성공으로 오해할 수 있으므로, 복구 가능한 예외만 구체적으로 처리하고 나머지는 다시 발생시키는 것이 안전합니다.
Procedure·Function·Trigger 비교
| 객체 | 호출 방식 | 반환 | 대표 용도 | 주의점 |
|---|---|---|---|---|
| Procedure | 명시적 호출 | OUT 또는 결과 없음 | 업무 명령·배치 | Transaction 경계를 호출자와 합의 |
| Function | 식·PL/SQL에서 호출 | RETURN 필수 | 계산·조회 | SQL 행마다 호출되면 Context Switch |
| Trigger | 사건에 의해 자동 실행 | 직접 반환 없음 | 제한적 감사·무결성 | 숨은 Side Effect·재귀·Mutating 위험 |
SQL 안에서 Function이 수십만 번 호출되고 내부 SQL까지 수행하면 호출 횟수만큼 Recursive SQL과 Logical I/O가 누적될 수 있습니다. 가능한 경우 Join·CASE·집계 같은 집합 SQL로 바꿉니다.
Transaction 주의
저장 Procedure가 임의로 COMMIT하면 상위 업무가 여러 Procedure를 하나의 원자적 단위로 묶기 어렵습니다. 일반적인 업무 Procedure는 Transaction 종료를 호출자에게 맡기고, Autonomous Transaction은 본 업무와 독립적으로 남아도 되는 제한적 로그에만 사용합니다.
결과를 검증하는 순서
- 각 Query Block이 만드는 한 행의 의미를 먼저 적습니다.
- 조건을 적용하기 전 원본 행과 적용 후 남는 행을 작은 표로 그립니다.
- NULL 비교가
TRUE,FALSE,UNKNOWN중 무엇인지 구분합니다. - 중복 제거, 그룹화, 정렬과 행 제한이 적용되는 순서를 확인합니다.
- 데이터가 0건·1건·여러 건일 때도 같은 규칙이 성립하는지 검증합니다.
실무와 시험에서 함께 확인할 항목
ORDER BY가 없다면 결과 순서를 가정하지 않습니다.- 문자열·숫자·날짜 비교에서는 데이터 타입과 명시적 형변환을 확인합니다.
- 같은 결과처럼 보이는 SQL도 NULL과 중복이 있을 때 달라질 수 있습니다.
- 문법을 외우기 전에 샘플 데이터 3~5행으로 결과를 직접 계산합니다.
마지막 점검
- 작성 순서가 아니라 SQL의 논리적 처리 순서로 결과를 계산합니다.
- NULL을 0이나 빈 값과 같은 것으로 취급하지 않습니다.
ORDER BY가 없는 결과 순서와 DISTINCT 없는 중복 제거를 가정하지 않습니다.- 비슷한 문법은 0건·다건·NULL 데이터를 넣어 결과가 정말 같은지 확인합니다.
복습 문제
- DML 이벤트에 자동 반응하는 저장 객체는 무엇인가?
- PL/SQL 안에서
TRUNCATE TABLE을 수행하는 문법은? - 일반 트리거가 호출 트랜잭션과 별도로 COMMIT할 수 없는 이유는?
- 샘플 데이터 3행으로 결과를 직접 계산할 수 있는가?