오라클 프로시저와 포스트그레스 SQL 프로시저의 차이

Oracle과 PostgreSQL에서의 Procedure는 기능적으로 비슷하지만, 두 데이터베이스가 사용하는 방식이나 특성에서 몇 가지 차이점이 오라클과 포스트그레스 SQL은 대표적인 관계형 데이터베이스 관리 시스템(RDBMS)으로, 각각 고유한 특징과 강점을 가지고 있습니다. 두 데이터베이스 모두 프로시저를 지원하여 복잡한 비즈니스 로직을 구현하고 데이터베이스 작업을 자동화할 수 있습니다. 하지만 오라클과 포스트그레스 SQL의 프로시저는 구문, 기능, 성능 등 다양한 측면에서 차이점을 보입니다. 본 글에서는 오라클의 PL/SQL과 포스트그레스 SQL의 PL/pgSQL 프로시저를 중심으로 두 데이터베이스의 프로시저 차이점을 심층적으로 분석하고, 각각의 특징과 활용 방법을 자세히 설명합니다.

---

1. 기본 개념 및 사용

Oracle

• Oracle의 Procedure는 PL/SQL(Procedural Language/SQL)을 기반으로 작성됩니다.
• 데이터베이스에서 작업을 수행할 수 있는 독립적인 단위로 설계되며, 주로 복잡한 비즈니스 로직을 구현하는 데 사용됩니다.
• OUT 매개변수를 통해 값을 반환하거나 DBMS_OUTPUT.PUT_LINE을 사용하여 디버깅 정보를 출력할 수 있습니다.
• 트랜잭션 제어(예: COMMIT, ROLLBACK)를 내부에서 처리할 수 없습니다. 트랜잭션 처리는 호출하는 클라이언트가 책임집니다.

PostgreSQL

• PostgreSQL의 Procedure는 PL/pgSQL(Procedural Language/PostgreSQL)을 기반으로 작성됩니다.
• PostgreSQL은 11버전 이후 Procedure와 Function을 구분하기 시작했습니다.
  • PROCEDURE: 트랜잭션 제어(COMMIT, ROLLBACK)를 지원합니다.
  • FUNCTION: 트랜잭션 제어를 지원하지 않고, 값을 반환하는 데 특화되어 있습니다.
• 절차적인 작업과 트랜잭션 관리를 처리할 수 있습니다.

---

728x90

 

2. 트랜잭션 제어

Oracle

• Oracle의 Procedure 내부에서 COMMIT이나 ROLLBACK과 같은 트랜잭션 제어는 기본적으로 허용되지 않습니다.
  • 트랜잭션 관리가 필요하다면, Procedure 외부에서 처리해야 합니다.
• 예외적인 경우, Autonomous Transactions를 사용하여 독립적인 트랜잭션 처리를 구현할 수 있습니다:

  CREATE OR REPLACE PROCEDURE autonomous_proc
  IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO log_table (log_id, message) VALUES (1, 'Log entry');
    COMMIT; -- 독립적으로 처리
  END;
  

PostgreSQL

• PostgreSQL의 Procedure는 트랜잭션 제어가 가능합니다.
  • CALL을 사용하여 Procedure를 호출하며, 내부에서 COMMIT이나 ROLLBACK을 명시적으로 실행할 수 있습니다.

  CREATE PROCEDURE example_proc()
  LANGUAGE plpgsql
  AS $$
  BEGIN
    INSERT INTO log_table (message) VALUES ('Log entry');
    COMMIT; -- 명시적인 트랜잭션 제어
  END;
  $$;
  

---

3. 값 반환

Oracle

• Procedure는 OUT 매개변수를 통해 값을 반환할 수 있습니다.
  • 반환된 값은 여러 개일 수 있으며, 이를 호출자가 받을 수 있습니다.

  CREATE OR REPLACE PROCEDURE get_employee_info(
      emp_id IN NUMBER,
      emp_name OUT VARCHAR2,
      emp_salary OUT NUMBER
  )
  IS
  BEGIN
      SELECT name, salary INTO emp_name, emp_salary
      FROM employees
      WHERE id = emp_id;
  END;
  

  호출 예:

  DECLARE
      name VARCHAR2(50);
      salary NUMBER;
  BEGIN
      get_employee_info(1001, name, salary);
      DBMS_OUTPUT.PUT_LINE('Name: ' || name || ', Salary: ' || salary);
  END;
  

PostgreSQL

• Procedure는 직접 값을 반환하지 않습니다.
  • 대신, 값을 반환하려면 OUT 매개변수나 RETURNS TABLE 같은 방식은 사용 불가능하며, Function을 사용하는 것이 일반적입니다.
  • 필요한 경우 결과를 임시 테이블이나 커서를 사용해 반환할 수 있습니다.

  CREATE PROCEDURE get_employee_info(emp_id INT)
  LANGUAGE plpgsql
  AS $$
  BEGIN
    RAISE NOTICE 'Employee ID: %', emp_id;
  END;
  $$;
  

---

728x90

 

4. 호출 방식

Oracle

• Procedure는 EXEC 혹은 BEGIN ... END; 블록에서 호출됩니다. 
• EXEC get_employee_info(1001, :name, :salary); -- 또는 BEGIN get_employee_info(1001, :name, :salary); END;

PostgreSQL

• Procedure는 CALL 키워드를 사용하여 호출합니다.

  CALL example_proc();
  

---

5. 에러 핸들링

Oracle

• PL/SQL의 EXCEPTION 블록을 통해 에러를 처리합니다.

  CREATE OR REPLACE PROCEDURE example_proc
  IS
  BEGIN
      -- 작업 수행
  EXCEPTION
      WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('An error occurred');
  END;
  

PostgreSQL

• PL/pgSQL의 EXCEPTION 블록을 통해 에러를 처리합니다.

  CREATE PROCEDURE example_proc()
  LANGUAGE plpgsql
  AS $$
  BEGIN
      -- 작업 수행
  EXCEPTION
      WHEN OTHERS THEN
          RAISE NOTICE 'An error occurred';
  END;
  $$;
  

---

6. 주요 차이 요약

 

특징	Oracle	PostgreSQL
트랜잭션 제어	불가능 (Autonomous Transaction으로 우회 가능)	가능
값 반환	OUT 매개변수 사용	직접 반환 불가 (Function 사용)
호출 방식	EXEC, BEGIN ... END;	CALL
에러 핸들링	EXCEPTION 블록	EXCEPTION 블록
내부 언어	PL/SQL	PL/pgSQL

---

1. 언어 구조 및 문법

• PL/SQL: 오라클의 프로시저 언어로, SQL과 제어 구조를 결합하여 복잡한 로직을 구현할 수 있습니다. 비교적 오래된 언어로, 풍부한 기능과 방대한 커뮤니티를 가지고 있습니다.
• PL/pgSQL: 포스트그레스 SQL의 프로시저 언어로, PL/SQL과 유사한 구조를 가지지만 더 간결하고 현대적인 문법을 채택했습니다. SQL 표준에 가깝게 설계되어 다른 데이터베이스 시스템과의 호환성이 높습니다.

2. 데이터 타입 및 변수

• PL/SQL: 다양한 내장 데이터 타입을 제공하며, 사용자 정의 데이터 타입을 정의할 수 있습니다.
• PL/pgSQL: SQL 표준 데이터 타입을 지원하며, 배열, 레코드 등의 복합 데이터 타입을 사용할 수 있습니다.

3. 함수와 프로시저

• PL/SQL: 함수와 프로시저를 명확하게 구분하여 사용합니다. 함수는 단일 값을 반환하고, 프로시저는 여러 값을 반환하거나 부수 효과를 발생시킬 수 있습니다.
• PL/pgSQL: 함수와 프로시저의 구분이 상대적으로 명확하지 않으며, 필요에 따라 함수처럼 사용할 수 있는 프로시저를 정의할 수 있습니다.

4. 커서

• PL/SQL: 커서를 사용하여 결과 집합을 행 단위로 처리할 수 있습니다.
• PL/pgSQL: 커서를 사용하여 결과 집합을 처리하는 방식이 PL/SQL과 유사합니다.

5. 트리거

• PL/SQL: BEFORE, AFTER 트리거를 지원하며, 행 레벨 트리거와 문장 레벨 트리거를 사용할 수 있습니다.
• PL/pgSQL: BEFORE, AFTER, INSTEAD OF 트리거를 지원하며, 행 레벨 트리거를 주로 사용합니다.

6. 패키지

• PL/SQL: 관련된 프로시저, 함수, 변수 등을 하나의 단위로 묶어 관리하는 패키지를 지원합니다.
• PL/pgSQL: 패키지를 지원하지만 PL/SQL만큼 발전된 기능은 제공하지 않습니다.

7. 성능

• PL/SQL: 오랜 기간 동안 발전되어 온 만큼 성능 최적화 기능이 매우 뛰어납니다.
• PL/pgSQL: 상대적으로 새로운 언어이지만, 지속적인 개선을 통해 성능이 향상되고 있습니다.

8. 호환성

• PL/SQL: 오라클 전용 언어로, 다른 데이터베이스 시스템과의 호환성이 낮습니다.
• PL/pgSQL: SQL 표준에 가깝게 설계되어 다른 데이터베이스 시스템과의 호환성이 높습니다.

9. 개발 환경

• PL/SQL: 오라클 SQL Developer, PL/SQL Developer 등 전문 개발 도구를 제공합니다.
• PL/pgSQL: PostgreSQL과 연동되는 다양한 IDE 및 텍스트 편집기를 사용할 수 있습니다.

결론

오라클과 포스트그레스 SQL의 프로시저는 각각 고유한 특징과 강점을 가지고 있으며, 선택은 개발 환경, 프로젝트 요구사항, 개발자의 선호도에 따라 달라질 수 있습니다.

• PL/SQL: 강력한 기능과 성능을 필요로 하는 대규모 엔터프라이즈 시스템에 적합합니다.
• PL/pgSQL: 오픈 소스, 높은 확장성, 다양한 개발 환경을 지원하며, 중소형 프로젝트나 빠른 개발이 필요한 경우에 적합합니다.

• Oracle Procedure는 강력한 비즈니스 로직 처리에 적합하며, OUT 매개변수와 PL/SQL 언어의 풍부한 기능을 제공합니다.
• PostgreSQL Procedure는 트랜잭션 제어 기능을 포함하며, 데이터 조작과 트랜잭션 관리가 결합된 작업에 적합합니다.
• 두 시스템의 목적과 아키텍처에 따라 적합한 방식으로 설계하는 것이 중요합니다.