오라클 12C의 유용한 배열 처리기능

현재 내가 있는 프로젝트는 오라클 12c를 쓰고 있다.
하지만 치매걸린 DBA는 오라클 8i 시절의 기술을 요구한다.
예를 들면, ANSI JOIN 쓰지 말고 오라클 전용 JOIN 문 작성, 가능한 단일 쿼리로 모두 해결하라 등…
오라클이 그렇게 ANSI JOIN 써라 써라 해도 치매걸린 새끼들은 한국 IT의 하느님 오라클 말도 씹고 그러는데 뭐.

이럴 거면 시발 8i를 깔지 뭐하러 비싸게 12c 구축하고 지랄이야.
하긴 10g 이하는 더이상 판매도 하지 않는데 뭐…

뭐 다른 건 모르겠고, 한가지 국내 오라클 개발자들이 구미가 당길만한 새로운 기능을 소개하고자 글을 쓴다.
12c 나온지 좀 됐긴 하지만, 12c 구축한 곳 치고는 12c의 혜택을 보는 프로젝트는… 절래절래 할 것이다.
당장 내가 소속된 프로젝트부터 2000년대부터 치매걸린 아키새끼들이 프로젝트 설계했는데 뭘 더 바라겠냐만은…

본문으로 들어가서, 오라클의 SQL에서는 TABLE이라는 함수가 있다.
다들 알다시피, 테이블 함수를 테이블로 이끌기 위해 많이들 쓸 것이다.

SELECT * FROM TABLE(MY_TABLE_FUNC('FOO'))

하지만 그 뿐. 한계가 많았는데, 먼저 배열 타입(TABLE OF, VARRAY()) 조차도 지원 안한다. 테이블 함수와 외부 테이블 등을 지원한다.
예를 들면, 11g 까지는 이렇게 쿼리 짜면 안돌아간다.

CREATE OR REPLACE TYPE emp_typ IS OBJECT (
   emp_name VARCHAR2(16);
   emp_rank NUMBER(1);
);
CREATE OR REPLACE TYPE emp_arr IS TABLE OF my_texts;
/
DECLARE

  my_emp emp_arr;

BEGIN

  SELECT emp_name, emp_rank
  BULK COLLECT INTO my_emp
  FROM emp
  WHERE emp_rank < 10;

  FOR my_row IN (
    SELECT * FROM TABLE(my_emp)
  ) LOOP

    DBMS_OUTPUT.PUT_LINE(my_row.emp_name);

  END LOOP;

END;

테스트는 안해봤지만, 문법에 문제가 없다 해도 “cannot access rows from a non-nested table” 오류가 뜰 것이다.
이제 12c부터는 배열이 지원된다. CREATE TYPE 문을 통해 선언한 배열은 당연히 지원되고, 심지어 CREATE PACKAGE 안에 있는 배열까지 지원한다.
백문이 불여일견, 예제를 보자.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS RECORD (
    empno NUMBER(4),
    ename VARCHAR2(10)
  );

  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1 (l_tab1 test_api.t_tab);
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 (l_tab1 test_api.t_tab) IS
  BEGIN
    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/


declare

tt test_api.t_tab;

begin

select 1,'a'
bulk collect into tt
from dual;

test_api.test1(tt);

end;

안타깝게도 SQLFIDDLE은 아직 오라클 11g라 테스트를 돌려볼 수 없다.
대신 오라클에서 교육 용도로 무료로 오라클 실행환경을 제공하는 Oracle Live SQL 에서 테스트 가능하다.
어찌저찌 12c에서 실행하면 잘 되는데, TABLE(l_tab1) 부분을 주목하자.
엄연히 패키지 내에 선언한 배열이고, 그 배열은 심지어 RECORD 형식이다.
보통 RECORD 형식은 PL/SQL 전용 문이기 때문에 SQL 쿼리문에서는 절대 안돌아가는 게 정석이다. 11g까지는.
하지만 패키지에 한해 RECORD 형식도 SELECT 문 같은 쿼리문이 지원되니 참 편하지 아니할 수가 없다.

하지만 이 기능도 한계가 있다. SQLPL/SQL 문은 컨텍스트가 틀리다.
당연히 데이터베이스에서 선언해야 쿼리가 돌아가는 특성상,
인라인 형식(익명 블록 내 DECLARE)에서 선언한 배열은 지원하지 않는다. 물론 거기서 선언한 RECORD 문은 말할 것도 없고.
패키지의 경우에서는 PACKAGE BODY 문에서 선언한 배열이나 RECORD 형식에서 선언하면 지원하지 않는다.
이들 모두 “cannot access rows from a non-nested table” 오류가 뜰 것이다.
보통 개발자들에게 TYPE 권한을 잘 안열고, 프로시저, 함수, 패키지에 한해 권한을 어느정도 오픈하는 환경이기 때문에, 12c가 구축된 환경에서는
쿼리를 짜는데 있어서 부담이 덜할 수 있는 좋은 기능이다.

뭐 MSSQL의 테이블 변수 기능에 흠뻑 젖은 개발자라면 여전히 불편한 건 매한가지다.

끗.

참고

composite / 2017년 11월 10일 / Piss Development
태그:, , , , ,

답글 남기기

Your email address will not be published / Required fields are marked *