[2026-04-23] PL/SQL 심화: 함수, 프로시저, 트리거의 완벽한 분리와 SQL 실전 총정리

어제 학습한 PL/SQL 기본 제어문을 바탕으로, 데이터베이스 내부에 재사용 가능한 모듈을 구축하는 함수(FUNCTION), 프로시저(PROCEDURE), 그리고 트리거(TRIGGER)의 메커니즘을 완벽히 해부했다. 또한 수십 개의 실전 질의문을 풀며 SQL 역량을 최종 점검했다.

 

1. 사용자 정의 함수(FUNCTION): 데이터 가공과 쿼리 단순화

• 오라클이 제공하는 내장 함수 외에 개발자가 직접 필요한 로직을 구현하는 기능이다.
• 반드시 처리 결과값(RETURN)을 가져야 하며, 주로 SELECT 절 내부에서 호출된다.
• 데이터의 상태를 변경하는 INSERT, UPDATE, DELETE (DML) 작업에는 사용하지 않는 것이 원칙이다.
• 여러 테이블을 조인(JOIN)하거나 스칼라 서브쿼리를 반복적으로 작성해야 하는 상황(예: 부서 번호로 부서명 가져오기)을 함수로 대체하여, SQL 문장을 극적으로 단순화하고 가독성을 높였다.

2. 저장 프로시저(PROCEDURE): 비즈니스 로직의 캡슐화와 DML 제어

• 리턴형이 없는 기능 중심의 프로그램 모듈이다.
• 단순 조회가 목적인 함수와 달리, 데이터의 변경(INSERT, UPDATE, DELETE)과 트랜잭션 처리를 포함하는 공통 비즈니스 로직(예: 댓글 달기, 좋아요 등)을 일괄적으로 수행할 때 강력한 힘을 발휘한다.
• IN 변수를 통해 외부에서 값을 주입받고, OUT 변수를 통해 내부 연산 결과를 외부(자바 등)로 전달하는 매개변수 제어 메커니즘을 실습했다.
• 자바 환경에서는 CallableStatement를 사용하여 이러한 DB 프로시저를 호출하게 된다는 아키텍처적 흐름을 짚어보았다.

3. 트리거(TRIGGER): 데이터의 자동화 처리와 무결성 유지

• 테이블에서 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때, 미리 설정된 조건에 따라 자동으로 실행되어 연관된 다른 테이블의 데이터를 변경하는 객체다.
• 연쇄적인 무한 루프 에러를 막기 위해, 트리거는 반드시 이벤트가 발생한 테이블과 '다른 테이블'의 데이터를 제어할 때만 사용해야 한다.
• 상품이 '입고'되었을 때 '재고' 테이블의 수량을 올리거나(UPDATE) 새로 추가(INSERT)하고, '출고' 시 재고 수량을 차감하거나 데이터베이스에서 지우는(DELETE) 실무적인 자동화 로직을 구현했다.
• 행 단위(FOR EACH ROW) 제어 시, 새로 입력된 데이터는 :NEW로, 기존 데이터는 :OLD 키워드로 접근하여 복잡한 비즈니스 룰을 DB 단에서 안전하게 처리했다.
• 트리거를 사용하면 자바 애플리케이션에서 별도로 쿼리를 호출하지 않아도 되므로 보안과 속도가 향상되지만, 백엔드 개발자 입장에서는 로직의 실행 흐름이 숨겨져 있어 디버깅 및 가독성이 떨어질 수 있다는 트레이드오프를 명심해야 한다.

4. 실전 예제를 통한 SQL 총복습 및 튜닝 전략

• 그동안 배운 SQL 문법을 총동원하여 30여 개의 복잡한 질의문을 설계하고 해결하는 훈련을 진행했다.
• LIKE, REGEXP_LIKE, BETWEEN, IN 등의 다양한 연산자를 조합하여 원하는 데이터를 정밀하게 필터링했다.
• GROUP BY와 HAVING을 활용해 데이터를 그룹화하고 집계(SUM, AVG, MAX, COUNT)하는 로직을 다시 한번 견고히 다졌다.
• 내부 쿼리의 실행 결과를 조건으로 사용하는 서브쿼리와, 분산된 테이블을 묶어내는 다중 JOIN을 엮어서 작성하며 복잡한 비즈니스 요구사항을 하나의 쿼리로 도출해 내는 역량을 검증했다.