PL/SQL 그대로 옮겼다가 다 터진 썰 (Oracle DBA를 위한 PL/pgSQL 완벽 비교)

PL/SQL 그대로 옮겼다가 다 터진 썰 (Oracle DBA를 위한 PL/pgSQL 완벽 비교)

테스트 환경: PostgreSQL 17, Rocky Linux 9 / Oracle 19c

Oracle DBA에게 PL/SQL은 자부심입니다. 15년간 프로시저, 패키지, 트리거로 온갖 로직을 짜왔죠. 그래서 저는 PostgreSQL로 넘어갈 때 자신만만했습니다. "PL/SQL 잘하니까 PL/pgSQL도 금방이겠지."

그리고 기존 PL/SQL 프로시저를 그대로 복사해서 붙여넣었습니다. 결과는?

ERROR: syntax error at or near "CREATE OR REPLACE PACKAGE"

"어? 패키지가 없어?"

네, PostgreSQL엔 패키지가 없습니다. 그것뿐이 아닙니다. 프로시저 선언 방식, 변수 선언, 예외 처리... 비슷한 듯 미묘하게 다 다릅니다. 자신만만하게 옮겼다가 여기저기서 터졌습니다.

이번 편은 실무 시리즈의 세 번째 글입니다. PL/SQL을 PL/pgSQL로 옮기는 실전 비교입니다.

• 프로시저·함수 문법 비교 (미묘하게 다 다름)
• 변수 선언, 제어문, 예외 처리
• ★ "패키지가 없다"는 충격과 대안
• 트리거 작성 비교
• PL/SQL → PL/pgSQL 변환 실전

PL/SQL을 아신다면 개념은 다 아시니, 문법 차이만 짚으면 됩니다.

 

첫 충격: 함수/프로시저 선언부터 다르다

가장 먼저 부딪히는 벽입니다. 선언 방식이 다릅니다.

Oracle PL/SQL

-- Oracle 함수
CREATE OR REPLACE FUNCTION get_bonus(p_salary NUMBER)
RETURN NUMBER
IS
    v_bonus NUMBER;
BEGIN
    v_bonus := p_salary * 0.1;
    RETURN v_bonus;
END;
/

PostgreSQL PL/pgSQL

-- PostgreSQL 함수
CREATE OR REPLACE FUNCTION get_bonus(p_salary NUMERIC)
RETURNS NUMERIC
AS $$
DECLARE
    v_bonus NUMERIC;
BEGIN
    v_bonus := p_salary * 0.1;
    RETURN v_bonus;
END;
$$ LANGUAGE plpgsql;

뭐가 다른가

Oracle              PostgreSQL
─────────────────────────────────────
RETURN NUMBER    →  RETURNS NUMERIC (S 붙음!)
IS               →  AS $$ ... $$ (달러 인용!)
변수 선언 (IS 뒤) →  DECLARE 블록 안
/ (실행)         →  $$ LANGUAGE plpgsql;

충격 포인트 1: 달러 인용($$)

PostgreSQL은 함수 본문을 $$ ... $$로 감쌉니다. 이게 처음엔 정말 낯섭니다. 문자열 안의 따옴표 문제를 피하려는 방식인데, $body$ ... $body$처럼 이름을 붙일 수도 있습니다.

충격 포인트 2: RETURNS (복수형)

Oracle은 RETURN, PostgreSQL은 RETURNS. 사소하지만 계속 틀립니다.

 

PROCEDURE vs FUNCTION

PostgreSQL도 11 버전부터 PROCEDURE를 지원합니다. 하지만 Oracle과 쓰임이 약간 다릅니다.

-- PostgreSQL 프로시저 (트랜잭션 제어 가능)
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id INT, p_amount NUMERIC)
AS $$
BEGIN
    UPDATE employees SET salary = salary + p_amount 
    WHERE emp_id = p_emp_id;
    COMMIT;  -- 프로시저 안에서 커밋 가능!
END;
$$ LANGUAGE plpgsql;

-- 호출 (Oracle의 EXEC 대신 CALL)
CALL update_salary(1, 500);

Oracle: EXEC update_salary(1, 500);
PostgreSQL: CALL update_salary(1, 500);

차이:

• FUNCTION은 값을 반환, PROCEDURE는 반환 없이 작업 수행 (양쪽 동일)
• PostgreSQL 프로시저는 내부에서 COMMIT/ROLLBACK 가능
• 호출은 EXEC 대신 CALL

 

변수 선언과 제어문

변수 선언

-- Oracle: IS와 BEGIN 사이
-- PostgreSQL: DECLARE 블록
DECLARE
    v_count INTEGER := 0;
    v_name  VARCHAR(100);
    v_emp   employees%ROWTYPE;   -- %ROWTYPE 동일하게 지원!
    v_id    employees.emp_id%TYPE; -- %TYPE도 동일!
BEGIN
    -- ...
END;

반가운 소식: %TYPE, %ROWTYPE은 PostgreSQL도 똑같이 지원합니다. Oracle DBA에게 익숙한 이 문법이 통합니다.

제어문 (거의 동일)

-- IF (동일)
IF v_salary > 5000 THEN
    v_grade := 'A';
ELSIF v_salary > 3000 THEN
    v_grade := 'B';
ELSE
    v_grade := 'C';
END IF;

-- LOOP (동일)
LOOP
    EXIT WHEN v_count > 10;
    v_count := v_count + 1;
END LOOP;

-- FOR (동일)
FOR i IN 1..10 LOOP
    -- ...
END LOOP;

-- WHILE (동일)
WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

제어문은 대부분 똑같습니다! IF, LOOP, FOR, WHILE 모두 Oracle과 거의 동일합니다. 이 부분은 부담이 없습니다.

커서 (약간 다름)

-- PostgreSQL 커서
DECLARE
    cur CURSOR FOR SELECT emp_id, emp_name FROM employees;
    v_id INTEGER;
    v_name VARCHAR;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_id, v_name;
        EXIT WHEN NOT FOUND;   -- Oracle: cur%NOTFOUND
        -- 처리
    END LOOP;
    CLOSE cur;
END;

Oracle: EXIT WHEN cur%NOTFOUND;
PostgreSQL: EXIT WHEN NOT FOUND;

꿀팁: 사실 대부분의 경우 명시적 커서보다 FOR ... IN SELECT 루프가 더 간결합니다.

-- 이게 더 간단 (양쪽 다 지원)
FOR rec IN SELECT emp_id, emp_name FROM employees LOOP
    -- rec.emp_id, rec.emp_name 사용
END LOOP;

 

예외 처리 - 개념은 같고 이름이 다르다

예외 처리 구조는 같지만, 예외 이름과 방식이 다릅니다.

-- PostgreSQL 예외 처리
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (1, '홍길동');
EXCEPTION
    WHEN unique_violation THEN   -- Oracle: DUP_VAL_ON_INDEX
        RAISE NOTICE '중복된 ID입니다';
    WHEN others THEN
        RAISE NOTICE '오류: %', SQLERRM;
END;

예외 이름 매핑

Oracle PostgreSQL

NO_DATA_FOUND	no_data_found
DUP_VAL_ON_INDEX	unique_violation
TOO_MANY_ROWS	too_many_rows
OTHERS	others
SQLERRM	SQLERRM (동일)

차이:

• PostgreSQL 예외명은 소문자, 좀 더 구체적입니다.
• RAISE로 메시지 출력: RAISE NOTICE '값: %', v_val; (%가 자리표시자)

-- 사용자 정의 예외 발생
RAISE EXCEPTION '잔액 부족: 현재 %, 필요 %', v_balance, v_amount;

 

★ 가장 큰 충격: 패키지가 없다

Oracle DBA가 PL/pgSQL에서 가장 당황하는 부분입니다. PostgreSQL엔 PACKAGE가 없습니다.

Oracle 패키지의 역할

-- Oracle: 관련 함수/프로시저/변수를 묶음
CREATE OR REPLACE PACKAGE emp_pkg IS
    FUNCTION get_bonus(p_id NUMBER) RETURN NUMBER;
    PROCEDURE raise_salary(p_id NUMBER, p_amt NUMBER);
    g_company_name VARCHAR2(100) := 'MyCompany';  -- 패키지 변수
END emp_pkg;

패키지는 관련 로직을 묶고, 전역 변수를 갖고, public/private을 구분하는 강력한 구조였습니다. 이게 없습니다.

PostgreSQL의 대안: 스키마 + 함수

PostgreSQL은 스키마로 함수들을 그룹핑합니다.

-- 스키마를 패키지처럼 사용
CREATE SCHEMA emp_pkg;

-- 함수들을 스키마 안에
CREATE FUNCTION emp_pkg.get_bonus(p_id INT) RETURNS NUMERIC
AS $$ ... $$ LANGUAGE plpgsql;

CREATE PROCEDURE emp_pkg.raise_salary(p_id INT, p_amt NUMERIC)
AS $$ ... $$ LANGUAGE plpgsql;

-- 호출 (패키지.함수 형태와 유사!)
SELECT emp_pkg.get_bonus(1);
CALL emp_pkg.raise_salary(1, 500);

스키마.함수() 형태가 Oracle의 패키지.함수()와 비슷해서, 이렇게 하면 패키지 느낌을 낼 수 있습니다.

패키지 변수의 대안

Oracle 패키지 전역 변수 → PostgreSQL 대안:
1. 별도 설정 테이블에 저장
2. 커스텀 GUC (SET app.company_name = ...)
3. 임시 테이블

전역 변수가 꼭 필요하면 설정 테이블을 만들어 관리하는 게 일반적입니다.

extension이라는 또 다른 대안

복잡한 로직 묶음이라면 PostgreSQL의 extension으로 패키징할 수도 있습니다. 다만 이건 고급 주제라 여기선 "스키마로 그룹핑"만 기억하시면 됩니다.

 

트리거 작성 비교

트리거도 구조가 약간 다릅니다. PostgreSQL은 트리거 함수 + 트리거 연결의 2단계입니다.

-- 1단계: 트리거 함수 생성
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER
AS $$
BEGIN
    NEW.modified_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2단계: 테이블에 트리거 연결
CREATE TRIGGER trg_update_time
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_time();

Oracle: 트리거 안에 로직을 바로 작성 (1단계)
PostgreSQL: 트리거 함수 따로 + 연결 따로 (2단계)

장점: 트리거 함수를 여러 테이블에서 재사용할 수 있습니다. 위의 update_modified_time() 하나를 만들어 여러 테이블에 붙일 수 있죠.

NEW/OLD: Oracle의 :NEW, :OLD가 PostgreSQL에선 NEW, OLD(콜론 없음)입니다.

 

★ AI로 PL/SQL → PL/pgSQL 변환하기

PL/SQL 코드가 많다면 이걸 손으로 다 바꾸는 건 고역입니다. Claude를 쓰면 훨씬 빠릅니다.

프롬프트:
"이 Oracle PL/SQL을 PostgreSQL 17의 PL/pgSQL로 
변환해줘. 다음을 지켜서:
- RETURN → RETURNS, IS → AS $$ 
- 예외명을 PostgreSQL 방식으로
- 패키지가 있으면 스키마+함수로 재구성
- 바뀐 부분을 주석으로 표시

[PL/SQL 코드 붙여넣기]"

특히 패키지 변환은 구조를 다시 짜야 해서 AI 도움이 큽니다. 패키지를 스키마로 재구성하는 작업을 자동화할 수 있습니다.

⚠️ 검증 필수: 변환된 함수는 반드시 실제 실행해서 테스트하세요. 특히:

• 예외 처리 로직 (이름이 미묘하게 다름)
• 트랜잭션 제어 (COMMIT 위치)
• NULL 처리 (5편의 함정)

이런 부분은 문법은 맞아도 동작이 다를 수 있습니다.

 

PL/SQL 이관 시 자주 터지는 것 TOP 5

실제로 옮길 때 가장 자주 터지는 것들입니다.

1. 패키지 (당연히)

CREATE PACKAGE → 없음 → 스키마로 재구성

2. 달러 인용 빠뜨림

IS ... END; → AS $$ ... $$ LANGUAGE plpgsql;

3. RETURN vs RETURNS

RETURN NUMBER → RETURNS NUMERIC

4. 예외명

DUP_VAL_ON_INDEX → unique_violation
NO_DATA_FOUND → no_data_found

5. 시퀀스 문법

seq.NEXTVAL → nextval('seq')

이 5개만 알아도 이관 시 터지는 것의 80%는 예방됩니다.

 

마무리

PL/SQL을 PL/pgSQL로 옮기는 핵심입니다.

1. 선언부가 다름 — RETURNS, AS $$, DECLARE
2. 제어문은 거의 동일 — IF/LOOP/FOR/WHILE 부담 없음
3. %TYPE, %ROWTYPE 지원 — 반가운 공통점
4. 패키지는 없음 — 스키마+함수로 대체
5. 트리거는 2단계 — 함수 따로, 연결 따로

PL/SQL 고수라면 PL/pgSQL도 금방 익힙니다. 개념은 완전히 같고 문법만 다르니까요. 다만 "패키지가 없다"는 점만은 사고의 전환이 필요합니다. 스키마로 그룹핑하는 방식에 익숙해지면, 오히려 더 유연하다고 느끼실 수도 있습니다.

다음 편은 이 시리즈의 하이라이트입니다. PostgreSQL만의 강력한 기능들 — JSONB, 배열, CTE, 윈도우 함수, UPSERT를 몰아서 다룹니다. Oracle 사용자가 부러워할 기능들의 향연입니다.

여러분이 PL/SQL 이관에서 겪은 고생담이나, 패키지를 어떻게 대체했는지 경험이 있다면 댓글로 공유해주세요.

 

 

공식 링크

• PL/pgSQL 공식 문서

이 글은 PostgreSQL 17 기준입니다. PL/pgSQL 세부 문법은 공식 문서에서 확인하세요.