오라클 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 / 0 Comments

SQL별 숫자 극한값 구하기.

주로 ORDER BY 에서 최우선순위를 주거나 아예 밀 때 요긴할 SQL 구문이니
잘 쓰도록.

MySQL(MariaDB)

출처: StackOverflow – In SQL how do I get the maximum value for an integer?
MIN은 현재 방법 없다고 한다. 그냥 타입에 알맞는 상수 써라.

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 as max_int_unsigned
,      ~0 >> 40 as max_mediumint_unsigned
,      ~0 >> 48 as max_smallint_unsigned
,      ~0 >> 56 as max_tinyint_unsigned
,      ~0 >> 1  as max_bigint_signed
,      ~0 >> 33 as max_int_signed
,      ~0 >> 41 as max_mediumint_signed
,      ~0 >> 49 as max_smallint_signed
,      ~0 >> 57 as max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

MSSQL

방법 없다. 아래 링크가서 타입에 알맞은 상수 갖다 써라. 아니면 아래 상수를 스칼라 함수 꾸며 써도 된다. 아니면 “닷넷 어셈블리” 간단히 만들어서 써도 된다.
MSDN – int, bigint, smallint, and tinyint (Transact-SQL)

MSSQL 2012 이상

Maximum Limit Value For Integer Data Type in SQL Server 2012

Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range'  from sys.types Where name = 'BIGInt' 
Select Power(cast(2 as varchar),(32) -1) as 'int max range'  from sys.types Where name = 'Int' 
Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range'  from sys.types Where name = 'SMALLInt' 

ORACLE

역시 방법 없다. 오라클은 여타 SQL과는 달리 INTEGER가 NUMERIC(11)의 ALIAS다.
일단 알려진 방법은 아래 함수가 있다. INTEGER MAX 구하는 함수이다.
Is there a way to get the PL/SQL maximum pls_integer?

CREATE FUNCTION MAX_PLS_INTEGER_SIZE RETURN PLS_INTEGER AS 
  p PLS_INTEGER;
  b NUMBER;
BEGIN
  b := 0;
  WHILE TRUE LOOP
    BEGIN
      p := POWER(2, b)-1;

      b := b + 1;
      EXCEPTION WHEN OTHERS THEN
        EXIT;
    END; 
  END LOOP;

  RETURN p;
end;
\
SELECT MAX_PLS_INTEGER_SIZE FROM DUAL;

PostgreSQL

얘도 오라클과 마찬가지로 NUMERIC이 모든 숫자 타입을 담당한다.
상수 값은 공식 문서 참조.
다행히도 간단한 방법이 있는데, pg_column_size라는 함수가 있다.
여기에 원하는 타입과 타입에 맞는 바이트 길이를(예: int는 4) 대입하여 처리하면 된다.
Postgres maximum value for BIGINT

select  (2^(8*pg_column_size(1::bigint)-2))::bigint << 1 as min_bigint_value;
select  -(((2^(8*pg_column_size(1::bigint)-2))::bigint << 1)+1) as max_bigint_value;

일단 많이 쓰는 SQL 기반으로 작성하였다. 추가할 거 있다면 서로 공유하면 웃음꽃이 피어날 것이다.

composite / 2016년 5월 11일 / Piss Development / 0 Comments

SQL을 갖고놀자! SQLFiddle!

자바스크립트 테스트라던가 갖고논다면 아마 JSFiddle 이나 JSBin 을 많이 이용하실 겁니다.

또한 자바나 C# 등의 서버단 언어를 갖고 놀거나 테스트하기 위해 IDEOne 사이트도 이용하실 겁니다.

그렇다면, DB를 갖고 노는 사이트는 있습니까? 있습니다!

바로 SQLFiddle 입니다!

http://sqlfiddle.com/

여기서는 여러분이 DB의 CRUD를 테스트해주는 재대로된 환경을 제공합니다!

<

p style=”text-align: center; clear: none; float: none;”>

광고는 애교로 봐줍시다. 돈들여서 AWS 사고 서버 들이고 호스팅 하고 하는데.. 가치가 있다면 Donate도 할수도 있죠.

어쨌든, 이녀석은 왼쪽에는 스키마 DDL을 작성하여, 옆에서 CRUD 작업을 할 수 있는 환경을 제공합니다.

제공하는 DB 종류는 MySQL을 비롯하여 오라클 11g, MSSQL 2008 및 2012, PostgreSQL, SQLite 등을 제공합니다!

만약 당신의 업무 중에 중요한 데이터를 변경할 일이 생기셨습니까?

그러면 먼저 왼쪽 편집기에 CREATE TABLE 로 테이블을 만들고, INSERT 문으로 대표적인 몇 개의 데이터를 넣으세요!

그런 다음, 오른쪽 편집기에서 CRUD 작업을 실시하세요.

많은 작업을 하기에는 무리기 때문에 샘플로 몇개 뽑아서 테스트하기에 적합합니다.

만약 새로운 질의문 테스트를 원하신다면, 주저 말고 여기서 작업하세요.

여기서는 실행 계획(Execution Plan)과 오류도 자세하게 볼 수 있습니다.

이제 DB 관리 및 개발에 대한 부담. 여기서 확 줄이세요. 여러분에 컴터에 무겁게 DB깔 필요도 없이 어떤 CRUD 작업은 여기에 맡기세요.

대세는 클라우드라는게 어떤건지 이제 감이 오십니까? 이것이 바로 아이디어이고 혁명입니다.

composite / 2012년 12월 12일 / 미분류 / 0 Comments