PL/SQL vs PL/pgSQL: 오라클과 PostgreSQL의 절차적 언어 비교 가이드

1. PL/SQL과 PL/pgSQL 개요

Oracle의 PL/SQL

PL/SQL은 Oracle 데이터베이스 내에서 비즈니스 로직을 구현하기 위한 강력한 언어입니다. SQL의 기능을 확장하여 오류 처리, 트랜잭션 관리 및 조건부 논리를 추가할 수 있도록 설계되었습니다.

PL/SQL의 주요 특징:

• 고급 오류 처리: NO_DATA_FOUND 및 TOO_MANY_ROWS와 같은 기본 제공 예외와 사용자 정의 예외를 통해 세밀한 오류 관리를 제공합니다.
• Oracle 도구와의 통합: SQL*Plus, Oracle Forms, APEX 등과의 깊은 통합을 통해 워크플로우를 쉽게 관리할 수 있습니다.

PostgreSQL의 PL/pgSQL

PL/pgSQL은 PostgreSQL에서 절차적 로직을 구현할 수 있게 해주는 언어입니다. PL/SQL과 비슷한 목표를 공유하지만 PostgreSQL의 오픈 소스 아키텍처와 긴밀하게 연계되어 있으며 간결한 구문이 특징입니다.

PL/pgSQL의 주요 기능:

• 간소화된 구문: 다른 프로그래밍 언어에 익숙한 개발자들이 쉽게 배울 수 있도록 직관적인 구문을 갖추고 있습니다.
• 확장성: PostGIS와 pg_stat_statements와 같은 PostgreSQL 확장과 원활하게 작동하여 고급 분석, 지리 정보 조회 및 성능 모니터링이 가능합니다.

2. 구문 및 구조적 차이점

변수 선언

변수 선언 구조는 유사하지만, PL/SQL과 PL/pgSQL의 구문은 다릅니다.

PL/SQL 예제:

DECLARE
    v_emp_id NUMBER := 1001;
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;

PL/pgSQL 예제:

DO $$
DECLARE
    v_emp_id INT := 1001;
    v_emp_name TEXT;
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
    RAISE NOTICE '%', v_emp_name;
END $$ LANGUAGE plpgsql;

차이점

• PL/pgSQL에서는 변수 유형을 명시적으로 지정해야 하며 PostgreSQL의 엄격한 타입 시스템을 따릅니다.
• Oracle의 NUMBER 유형은 PostgreSQL의 NUMERIC 또는 INTEGER로 매핑됩니다.

3. 문자열 처리: NVL vs COALESCE 및 DECODE vs CASE

문자열 처리에서 NVL과 DECODE 함수는 PostgreSQL에서는 각각 COALESCE와 CASE로 대체됩니다.

NVL vs COALESCE 사용

Oracle에서 null을 지정한 값으로 대체하는 데 NVL을 사용합니다.
PostgreSQL에서는 COALESCE를 사용하여 여러 인수 중 첫 번째 null이 아닌 값을 반환합니다.

--PL/SQL :
SELECT NVL(employee_name, 'Unknown') AS emp_name FROM employees;

-- PL/pgSQL:
SELECT COALESCE(employee_name, 'Unknown') AS emp_name FROM employees;

DECODE vs CASE 사용

DECODE는 Oracle의 조건 함수로, 간단한 IF-THEN-ELSE 논리를 사용합니다.
PostgreSQL에서는 보다 강력한 CASE를 사용합니다.

-- PL/SQL 예제:
SELECT DECODE(department_id, 10, 'Sales', 20, 'HR', 30, 'IT', 'Other') AS department_name FROM departments;

-- PL/pgSQL 예제:
SELECT CASE department_id WHEN 10 THEN 'Sales' WHEN 20 THEN 'HR' WHEN 30 THEN 'IT' ELSE 'Other' END AS department_name FROM departments;

4. 트랜잭션 관리: COMMIT, ROLLBACK, SAVEPOINT

PostgreSQL의 PL/pgSQL 함수 내에서는 독립 트랜잭션 관리가 불가능하지만, 트랜잭션 블록 외부에서 COMMIT 및 ROLLBACK을 사용할 수 있습니다. PostgreSQL의 트랜잭션 처리는 주로 함수 호출 외부에서 수행되며, 필요 시 함수 내 트랜잭션 관리는 EXCEPTION 절을 사용해 예외 처리와 함께 구현할 수 있습니다.

-- PL/SQL 예시:
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (1002, 'Jane');
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;

-- PL/pgSQL 예시:

DO $$
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (1002, 'Jane');
    -- 트랜잭션은 DO 블록 내에서 자동으로 커밋됨
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Transaction error occurred';
END $$ LANGUAGE plpgsql;

5. 고급 오류 처리 및 트랜잭션

PL/SQL에서는 EXCEPTION 블록을 통해 내장된 예외 및 사용자 정의 예외를 처리할 수 있습니다. PL/pgSQL에서는 다양한 메시지 수준으로 오류를 제어할 수 있습니다.

-- PL/SQL 예시:
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No record found.');

-- PL/pgSQL 예시:
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No record found';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'An error occurred';

• RAISE 구문은 PostgreSQL의 다양한 메시지 수준 (NOTICE, INFO, WARNING)을 통해 오류 메시지를 조정할 수 있습니다.

6. PL/pgSQL로 마이그레이션 팁: 모범 사례

코드 리팩토링

• PostgreSQL의 구조에 맞게 코드를 최적화하고, PL/SQL 코드를 그대로 옮기지 말고, 불필요한 패키지 의존성을 줄여야 합니다.

데이터 유형 매핑

• PostgreSQL의 타입 시스템을 충분히 이해하고, Oracle과의 차이점을 정확히 매핑하는 것이 중요합니다.

PostgreSQL의 강점 활용

• PostgreSQL의 JSON 데이터 타입, 외부 데이터 래퍼, 확장 모듈 등을 적극 활용하여 기능을 확장할 수 있습니다.

결론

Oracle의 PL/SQL에서 PostgreSQL의 PL/pgSQL로 마이그레이션하는 과정에서 두 언어의 주요 차이점과 PostgreSQL의 고유 기능을 이해하면 마이그레이션이 원활하게 이루어질 수 있습니다.