01 | 오라클도 지원 가능한 임시 테이블

마이크로소프트사의 MSSQL을 주로 사용하던 개발자가 오라클의 PL/SQL을 접한 뒤 맨 처음 했던 질문이, “MSSQL에서는 프로시저 안에서 임시 테이블을 만들어 복잡한 연산을 수행한 후 임시 테이블에 데이터를 넣은 다음 선택해서 결과를 볼 수 있는데, 오라클에서도 가능한가요?”였다. 예를 들어, MSSQL에서는 아래와 같은 형태로 프로시저를 만들 수 있다.

입력

    -- MSSQL 프로시저 예
    CREATE PROCEDURE test_proc
    AS
      -- 임시 테이블 생성
      CREATE #ch13_physicist (
             ids      INT,
             names    VARCAR(30),
             birth_dt DATETIME );

      -- 비즈니스 로직 처리
      ....
      ....

      -- 임시 테이블에 데이터 insert
      INSERT INTO #ch13_physicist
      SELECT ....

      -- 마지막에 임시 테이블 SELECT
      SELECT *
        FROM #ch13_physicist

이렇게 프로시저를 생성한 뒤 실행하면 프로시저의 맨 마지막에 있는 임시 테이블을 선택한 결과가 출력된다.

입력

    EXEC test_proc'

결과

    Ids      names            birth_dt
    -------- ---------------- ----------------
    1        Albert           Einstein
    2
    ......
    ......

하지만 오라클에서는 이와 같은 기능은 지원하지 않는다. 오라클에서 프로시저는 어떤 결과를 반환하거나 보여주는 것이 아니라 특정 로직을 처리하는 역할만 수행하며 결과를 반환하는 것은 함수의 몫이다. 함수와 프로시저, 두 서브 프로그램의 역할을 분명하게 분리해 놓고 있다. 하지만 함수 역시 특정 데이터 타입을 반환할 뿐이지 위에서 예로 든 것처럼 SELECT문을 실행한 결과 집합을 반환하거나 보여주지는 않는다. 따라서 “오라클에서는 위와 같은 형태로 프로시저를 만들 수 없다!”라고 얘기할 수 밖에 없다.

사실 MSSQL에서 프로시저를 수행해 쿼리 결과를 볼 수 있는 기능은 개발자 입장에서 보면 상당히 매력적인 것이 사실이다. 보통 단일 SELECT문으로도 원하는 결과를 얻을 수 있지만 복잡한 로직을 처리해야 하는 경우 하나의 SELECT문으로만 구현하기란 쉽지 않다. 따라서 원하는 최종 결과 형태로 임시 테이블을 만들어 놓고, 프로시저 내에서 DML문을 이용해 복잡한 연산을 여러 단계에 걸쳐 수행하면서 데이터를 가공해 최종 결과를 임시 테이블에 넣고 맨 마지막에 이 임시 테이블을 SELECT하는 방식은 상당히 편리하고 좋은 기능이라 할 수 있다.

하지만 오라클에서는 임의의 로직 처리를 위한 연산 수행은 프로시저가, 연산을 수행한 결과를 반환하는 것은 함수가 처리하도록 엄격히 구분하고 있으며, 함수조차도 쿼리 결과 집합을 그대로 반환하지는 않는다. 하지만 오라클에서도 MSSQL 프로시저와 정확히 같은 기능은 아니지만 비슷한 기능은 지원하고 있다.