절차형 SQL: PL/SQL·프로시저·함수·트리거

핵심 요약

PL/SQL 블록과 저장 프로시저·사용자 정의 함수·트리거의 호출 방식, 트랜잭션 및 동적 DDL 차이를 구분한다.

학습 목표

• 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;
/

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은 호출한 트랜잭션과 별도인 자율 트랜잭션을 명시하는 특수 기능이다. 모든 프로시저와 함수가 자동으로 하나의 고정 트랜잭션이 되는 것도 아니고, 모든 호출이 자동으로 자율 트랜잭션이 되는 것도 아니다.

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 비교

SQL 안에서 Function이 수십만 번 호출되고 내부 SQL까지 수행하면 호출 횟수만큼 Recursive SQL과 Logical I/O가 누적될 수 있습니다. 가능한 경우 Join·CASE·집계 같은 집합 SQL로 바꿉니다.

Transaction 주의

저장 Procedure가 임의로 COMMIT하면 상위 업무가 여러 Procedure를 하나의 원자적 단위로 묶기 어렵습니다. 일반적인 업무 Procedure는 Transaction 종료를 호출자에게 맡기고, Autonomous Transaction은 본 업무와 독립적으로 남아도 되는 제한적 로그에만 사용합니다.

시험 포인트

1. 누가 실행하는지 본다. 직접 호출이면 프로시저·함수, 이벤트로 자동 실행이면 트리거다.
2. 값을 반환해야 하는지 본다. SQL 표현식에서 사용할 반환값이면 함수를 우선 생각한다.
3. DDL을 PL/SQL 내부에서 실행하는지 확인한다. 그렇다면 EXECUTE IMMEDIATE가 필요하다.
4. COMMIT, ROLLBACK, TRUNCATE가 나오면 객체 종류보다 먼저 트랜잭션 경계를 따진다.

자주 틀리는 부분

• 트리거는 사용자가 매번 직접 호출하는 저장 프로그램이 아니다.
• 사용자 정의 함수가 트리거처럼 자동으로 무결성을 지켜 주는 것은 아니다.
• 일반 트리거에서 임의로 TCL을 수행할 수 있다고 판단하지 않는다.
• EXECUTE 'DELETE FROM temp_orders'가 아니라 EXECUTE IMMEDIATE 'DELETE FROM temp_orders'가 Oracle 동적 SQL 문법이다.