Oracle PL/SQL

PL/SQL(Procedural Language/SQL)은 Oracle이 SQL에 변수·조건문·반복문·예외 처리를 더한 절차형 언어(procedural language)다. DB 안에서 함수·프로시저(stored procedure)·트리거(trigger)를 만들어 로직을 데이터 가까이에서 돌린다. 다른 DB에도 비슷한 것(PostgreSQL의 PL/pgSQL 등)이 있지만 문법은 제각각이다.

블록 구조

SET SERVEROUTPUT ON
DECLARE
  v_name  emp.ename%TYPE;      -- 컬럼 타입을 그대로 참조
  v_row   emp%ROWTYPE;         -- 행 전체를 담는 레코드
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = 7369;   -- 정확히 한 행이어야 함
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('없음');
END;
/

• SELECT ... INTO는 결과가 0행이거나 여러 행이면 예외가 난다
• %TYPE·%ROWTYPE은 테이블이 바뀌어도 따라가지만, 코드만 봐서는 타입을 알기 어렵다
• 배열 대신 TABLE OF ... INDEX BY 컬렉션, 여러 타입을 묶는 RECORD를 쓴다

커서

여러 행을 하나씩 처리하는 포인터(cursor)다.

• 암시적 커서(implicit cursor): DML(Data Manipulation Language)을 실행하면 자동으로 생긴다. SQL%ROWCOUNT로 영향받은 행 수를 본다
• 명시적 커서(explicit cursor): 선언 → OPEN → FETCH 반복(EXIT WHEN c%NOTFOUND) → CLOSE. 닫지 않으면 자원이 샌다
• 커서 FOR 루프: FOR r IN (SELECT ...) LOOP ... END LOOP; 열기·인출·닫기를 알아서 해 준다

함수와 프로시저

• FUNCTION: 값을 반환하고 SQL 안에서 호출할 수 있다
• PROCEDURE: 반환값 대신 OUT 파라미터로 결과를 내보낸다. 여러 행은 SYS_REFCURSOR를 OUT으로 열어 넘기고, 프로시저 안에서 FETCH하지 않는다(호출자가 읽는다)
• PACKAGE: 관련 함수·프로시저를 명세(spec)와 본문(body)으로 묶는다
• TRIGGER: 테이블의 INSERT·UPDATE·DELETE 전후에 자동 실행된다. :OLD·:NEW로 변경 전후 값을 본다
• 스케줄링: 예전 DBMS_JOB보다 DBMS_SCHEDULER가 권장된다

언제 쓰나

데이터를 대량으로 옮기는 배치처럼 왕복을 줄여야 할 때 강하다. 반면 업무 로직이 DB에 흩어지면 테스트·버전 관리·배포가 어려워지고 특정 DB에 묶인다. 트리거의 숨은 연쇄 동작은 최소 놀람을 깨기 쉽다. 자바에서는 JDBC(Java Database Connectivity)의 CallableStatement로 호출한다(JDBC와 JdbcTemplate).

출처: Oracle 문서: Overview of PL/SQL · PL/SQL Static SQL(커서)