오라클 스토어드 프로시저를 PostgreSQL 스토어드 프로시저로 변환하는 완벽 가이드: 문법, 예제, 팁까지!

 

 

오라클(Oracle)의 스토어드 프로시저(Stored Procedure)를 포스트그레SQL(PostgreSQL)의 스토어드 프로시저로 변환하려면 두 데이터베이스 시스템 간의 차이점을 이해하고, 이를 기반으로 적절히 조정하는 것이 중요합니다. Oracle과 PostgreSQL은 스토어드 프로시저를 구현하는 방식, 문법, 지원 기능 등에서 차이가 있으므로, 변환 시 주의해야 할 원칙과 규칙을 아래에 자세히 정리하겠습니다.

 

---

1. 기본 개념 및 용어 차이

• Oracle: 스토어드 프로시저는 PROCEDURE 키워드로 정의되며, PL/SQL을 사용하여 작성됩니다. 프로시저와 함수를 엄격히 구분합니다(FUNCTION은 값을 반환, PROCEDURE는 반환값 없음).
• PostgreSQL: PostgreSQL 11부터 CREATE PROCEDURE가 도입되었지만, 전통적으로 함수(CREATE FUNCTION)를 주로 사용해왔습니다. PostgreSQL에서는 함수와 프로시저의 경계가 상대적으로 모호하며, 함수로 대부분의 작업을 처리할 수 있습니다.
  • 변환 시, Oracle의 프로시저를 PostgreSQL의 PROCEDURE로 옮기거나, 필요에 따라 FUNCTION으로 구현할 수 있습니다.

원칙: Oracle의 프로시저가 반환값이 없는 경우 PostgreSQL의 PROCEDURE를 사용하고, 반환값이 필요한 경우 FUNCTION으로 변환하세요.

---

2. 언어 차이: PL/SQL vs PL/pgSQL

• Oracle: PL/SQL을 사용합니다. 변수 선언, 제어 구조, 예외 처리가 PL/SQL 문법에 기반합니다.
• PostgreSQL: PL/pgSQL을 주로 사용하며, PL/SQL과 유사하지만 몇 가지 차이점이 있습니다. PostgreSQL는 PL/pgSQL 외에도 PL/Python, PL/Perl 등 다른 언어를 지원합니다.

주요 차이점 및 변환 규칙:

• 변수 선언:
  • Oracle: 변수명 데이터타입; (예: v_count NUMBER;)
  • PostgreSQL: DECLARE 변수명 데이터타입; (예: DECLARE v_count INTEGER;)
• 할당 연산자:
  • Oracle: := (예: v_count := 10;)
  • PostgreSQL: := 또는 = 모두 사용 가능 (예: v_count := 10; 또는 v_count = 10;)
• 쿼리 실행:
  • Oracle: SELECT ... INTO로 변수에 값을 저장.
  • PostgreSQL: 동일하게 SELECT ... INTO 사용 가능. 단, PostgreSQL에서는 PERFORM을 사용하여 반환값 없이 쿼리만 실행할 수 있음.
     

    -- Oracle
    SELECT count(*) INTO v_count FROM employees;
    
    -- PostgreSQL
    SELECT count(*) INTO v_count FROM employees;
    -- 또는 결과 없이 실행만 할 경우
    PERFORM count(*) FROM employees;

원칙: PL/SQL 코드를 PL/pgSQL로 변환 시 변수 선언과 할당 문법을 PostgreSQL 스타일로 변경하고, 필요 시 PERFORM을 활용하세요.

---

3. 파라미터 처리

• Oracle: 파라미터에 IN, OUT, IN OUT 모드를 명시적으로 지정.
   

  CREATE PROCEDURE proc_name (p_in IN NUMBER, p_out OUT NUMBER) AS ...

• PostgreSQL:
  • PROCEDURE에서는 IN, OUT, INOUT을 지원 (PostgreSQL 11 이상).
  • FUNCTION에서는 반환값(RETURNS)으로 출력값을 처리하거나, OUT 파라미터를 사용하여 결과 반환.
   

  -- PROCEDURE
  CREATE PROCEDURE proc_name (p_in IN INTEGER, p_out OUT INTEGER) AS ...
  
  -- FUNCTION
  CREATE FUNCTION proc_name (p_in INTEGER, OUT p_out INTEGER) RETURNS INTEGER AS ...

원칙: Oracle의 OUT 파라미터가 있는 경우 PostgreSQL에서 PROCEDURE 또는 OUT 파라미터를 가진 FUNCTION으로 변환하세요.

---

4. 제어 구조

• IF 문:
  • Oracle: IF ... THEN ... ELSIF ... ELSE ... END IF;
  • PostgreSQL: IF ... THEN ... ELSIF ... ELSE ... END IF; (거의 동일)
• LOOP:
  • Oracle: FOR, WHILE, LOOP ... EXIT WHEN 등 다양.
  • PostgreSQL: 유사하지만, EXIT 대신 RETURN 또는 EXIT WHEN 사용.
     

    -- Oracle
    LOOP
      v_count := v_count + 1;
      EXIT WHEN v_count > 10;
    END LOOP;
    
    -- PostgreSQL
    LOOP
      v_count := v_count + 1;
      EXIT WHEN v_count > 10;
    END LOOP;

원칙: 제어 구조는 대체로 호환되므로 그대로 옮기되, PostgreSQL에서 RETURN과 EXIT 사용법을 조정하세요.

---

5. 예외 처리

• Oracle: EXCEPTION 블록에서 예외를 처리하며, 미리 정의된 예외(NO_DATA_FOUND, TOO_MANY_ROWS)나 사용자 정의 예외 사용.
   

  BEGIN
    SELECT ... INTO ...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_result := 0;
  END;

• PostgreSQL: EXCEPTION 블록을 지원하며, 예외 이름이 다름(NO_DATA_FOUND → NO_DATA 등).
   

  BEGIN
    SELECT ... INTO ...
  EXCEPTION
    WHEN NO_DATA THEN
      v_result := 0;
  END;

원칙: 예외 이름을 PostgreSQL에 맞게 변경하고, 필요 시 PostgreSQL의 에러 코드(SQLSTATE)를 활용하세요.

---

6. 트랜잭션 관리

• Oracle: 암묵적 트랜잭션 관리, COMMIT과 ROLLBACK 명시 가능.
• PostgreSQL: 명시적 트랜잭션 관리 필요. 프로시저 내부에서 COMMIT과 ROLLBACK 지원 (PostgreSQL 11 이상).

원칙: Oracle에서 트랜잭션 커밋이 포함된 경우, PostgreSQL에서 COMMIT/ROLLBACK을 적절히 삽입하세요.

---

7. 데이터 타입 매핑

 

Oracle 타입	PostgreSQL 타입
NUMBER	NUMERIC, INTEGER
VARCHAR2	VARCHAR, TEXT
DATE	TIMESTAMP, DATE
CLOB	TEXT
BLOB	BYTEA

원칙: Oracle의 데이터 타입을 PostgreSQL에서 호환되는 타입으로 매핑하세요. 특히 VARCHAR2는 VARCHAR나 TEXT로 변환하는 것이 일반적입니다.

---

8. 패키지와 모듈화

• Oracle: PACKAGE를 사용하여 프로시저와 함수를 그룹화.
• PostgreSQL: 패키지 개념이 없음. 대신 스키마(SCHEMA)를 활용하거나, 개별 함수/프로시저로 분리.

원칙: Oracle 패키지를 PostgreSQL로 변환하려면 패키지 내 프로시저를 개별 객체로 분리하고, 필요 시 스키마로 논리적 그룹을 만드세요.

---

9. 커서 처리

• Oracle: 명시적 커서(CURSOR)와 암묵적 커서를 모두 지원.
• PostgreSQL: 명시적 커서(DECLARE CURSOR)와 FOR 루프를 활용.
   

  -- Oracle
  CURSOR emp_cur IS SELECT * FROM employees;
  BEGIN
    FOR rec IN emp_cur LOOP
      ...
    END LOOP;
  END;
  
  -- PostgreSQL
  DO $$
  DECLARE
    emp_cur CURSOR FOR SELECT * FROM employees;
    rec RECORD;
  BEGIN
    FOR rec IN emp_cur LOOP
      ...
    END LOOP;
  END $$;

원칙: Oracle의 커서를 PostgreSQL의 커서 또는 FOR 루프로 변환하세요.

---

10. 주의 사항 및 추가 팁

• 성능 최적화: PostgreSQL은 쿼리 플래너가 다르므로, Oracle에서 잘 작동하던 쿼리가 느려질 수 있습니다. 변환 후 성능 테스트 필수.
• 호환성 확인: PostgreSQL 버전(특히 11 이상)에 따라 PROCEDURE 지원 여부가 다르므로, 사용 중인 버전을 확인하세요.
• 디버깅: PostgreSQL에서는 RAISE NOTICE를 활용해 디버깅 메시지를 출력 가능.

---

변환 예제

Oracle 코드:

 

CREATE OR REPLACE PROCEDURE update_salary (p_emp_id IN NUMBER, p_increase IN NUMBER)
AS
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
  v_salary := v_salary + p_increase;
  UPDATE employees SET salary = v_salary WHERE employee_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

PostgreSQL 코드:

 

CREATE OR REPLACE PROCEDURE update_salary (p_emp_id IN INTEGER, p_increase IN NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
  v_salary NUMERIC;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
  v_salary := v_salary + p_increase;
  UPDATE employees SET salary = v_salary WHERE employee_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN NO_DATA THEN
    RAISE NOTICE 'Employee not found';
END;
$$;

---

위 원칙과 예제를 참고하여 Oracle 스토어드 프로시저를 PostgreSQL로 변환하면 됩니다.