오라클 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

2015년 6월 29일자 NoSQL 오픈소스 임베디드 DB 리스트

이번엔 약속대로 자바 기반의 임베디드 NoSQL DB를 소개한다.
Java 1.6에 내장된 Apache Derby DB(Java DB)는 소개에서 제외한다. SI/SM 개발자들 이거 자바 내장인거 알기나 하는지 내 알바 아니지만.
사족으로 역시 자바가 정말 다양한 방식의 NoSQL DB가 있다. 닷넷에서 아쉬우면 IKVM 써서 쓰는 방법이 있긴 하지만…

1. MapDB

MapDB is embedded database engine. It provides java collections backed by disk or memory database store. MapDB has excellent performance comparable to java.util.HashMap and other collections, but is not limited by GC. It is also very flexible engine with many storage backend, cache algorithms, and so on. And finally MapDB is pure-java single 400K JAR and only depends on JRE 6+ or Android 2.1+.

순수 자바로 만들어진 컬렉션 기반의 키값 DB. 메모리 및 파일 가능.
400kb의 가벼움과 안드로이드 지원으로 특히 안드로이드 개발자에게 사랑받고 있는 DB인 데다가
Apache License 2.0 이라 기업에서도 부담없이 사용 가능.

2. OrientDB

OrientDB is a 2nd Generation Distributed Graph Database with the flexibility of Documents in one product with an Open Source commercial friendly license (Apache 2 license). First generation Graph Databases lack the features that Big Data demands: multi-master replication, sharding and more flexibility for modern complex use cases.

순수 자바로 만들어진 Graph/Document DB. 서버/임베디드 모두 가능. NoSQL 계의 MySQL 같은 존재임.
Graph DB는 NoSQL 중에 Relational Schema를 지원하기 때문에 기존 SQL DB와 유사하게 사용 가능한 장점이 있다.
리플리케이션 및 셰딩이 지원되기 때문에 스케일링이 가능하면서도 Apache License 2.0으로 기업에서도 무료로 사용 가능.
자바를 사용하는 유명 기업에서 채용되기도 하였으며, 유료인 Enterprise Edition은 엔터프라이즈에 필요한 기능을 사용 가능. (개발/테스트 목적으로 무료로 사용가능)

3. Neo4j

Neo4j is used by thousands of organizations, including 50+ of the Global 2000, in mission-critical production applications. Developed by the inventors of the modern graph database, Neo4j is the only graph database on…

순수 자바로 만들어진 Graph DB로 OrientDB와 쌍벽을 이루고 있으나 우세. eBay나 National Geographic 등 세계 유명 기업에서 사랑받는 DB.
Graph DB는 NoSQL 중에 Relational Schema를 지원하기 때문에 기존 SQL DB와 유사하게 사용 가능한 장점이 있다.
리플리케이션 및 셰딩이 지원되기 때문에 스케일링이 가능한데 라이선스가 GPLv3 라 기업의 경우 내부시스템은 무료로 쓸 수 있으나 재배포 판매 시 소스 공개하던가 사던가 해야 함.
유료인 Enterprise Edition은 엔터프라이즈에 필요한 기능을 사용 가능. (30일 체험 사용 가능)

4. Titan

Titan is a scalable graph database optimized for storing and querying graphs containing hundreds of billions of vertices and edges distributed across a multi-machine cluster. Titan is a transactional database that can support thousands of concurrent users executing complex graph traversals in real time.

빅데이터를 위해 태어난 자바 기반의 Graph DB. 다른 NoSQL DB와 연동하는 기능이 특징.
Graph DB는 NoSQL 중에 Relational Schema를 지원하기 때문에 기존 SQL DB와 유사하게 사용 가능한 장점이 있다.
빅데이터 위주의 스케일링에서 강점이 있으며, Apache License 2.0이기 때문에 기업에서도 부담없이 사용 가능.

5. iBoxDB

iBoxDB is a fast transactional table style document NoSQL Application Database, easily store, process objects and documents, traditional table with unstructured data, zero configuration, pure JAVA and .NET engines, no dependencies.

iBoxDB has a well designed interface with great performance and capability for agile development, you can focus on the applications. It can embed into an application and deploy on mobiles, desktops, servers, then help designers to persist objects and documents thread-safely with transaction support.

.NET 과 Java 둘 다 품은 (그것도 IKVM도 아니고 독립적으로 각각 엔진 개발) 특이한 키값 기반 NoSQL DB.
.NET은 2 이상, Java는 6 이상이면 가능. 라이선스는 걍 무료. 오픈소스는 아님 (자바는 오픈되었으나 닷넷만 클로즈드)

6. JasDB

JasDB was designed with easy of use and minimal configuration in mind. JasDB can be installed and configured in almost no time at all. Use JasDB as an embedded DB inside your JVM or you can use it to store your unstructured data in JSON format. If you want to scale JasDB, simply run and use it through the REST webservice.

순수 자바로 만들어진 Document DB. REST 내장으로 서버로도 사용 가능. JSON 형식으로 저장하는 것이 특징.
안드로이드도 지원하며, MIT X11 License로 기업에서도 부담없이 사용 가능

… 자바로 만든 NoSQL DB 드럽게 많아서 더이상 나열 못하겠다.
NoSQL DB를 전문적으로 모은 사이트가 있으니 어느 언어 개발하던 상관없이 부족함을 채우고 싶다면 여기로 가서
http://nosql-database.org/
Key-Value, Document, Graph, Column 등 다양한 DB 종류 구분이 가능하다. 하지만 지원되는 언어는 나도 모르니 해당 공식 사이트 가서 확인하라.

composite / 2015년 6월 29일 / Piss Development / 0 Comments

.NET 에서 사용 가능한 Embedded DB 모음

오픈소스 DB 찾느라 해맸다. 그것도 임베디드 가능한 놈으로.


VistaDB은 완전 상용이니 쓸 맛이 안났고, SQLCE 4는 쿼리 제한이 너무 많아 사용이 꺼렸다.


그래서 결국 찾았다. 이제 오늘 그것을 알려 주겠다. 닷넷 개발자들이어. 다시 일어서서 기술을 찬양하라!


 


1. RavenDB


순수 .NET 100%로 만든 문서 기반(Document) DB. 임베디드


라이센스 : GPL (상업적 이용이 어려운 아쉬움이 있음. RavenDB 포함 안하거나 재배포 안하는 상용이라면 상관없지만.)


사이트 : http://ravendb.net/


이미 외국에서 유명세 타고있는 임베디드 DB. DB 암호화 및 압축 또한 지원된다. (근데 내장 암호화는 비용 발생)


아, 물론 이걸로 서버 돌려서 쓸 수 있도록 API도 제공하며, 그냥 임베디드로 써도 된다. 서버 돌려서 쓰면 다른 언어에서도 쓸 수 있다.


메모리 DB도 지원하여 테스트하기에 적당.


 


2. BerkeleyDB


C로 만들었으며 Java 및 .NET 등 일부 언어 API 지원. 키값(Key-Value) 형식의 DB


라이센스 : Sleepycat 라이센스 (오라클은 자사에서 공개한 오픈소스 솔루션들을 절대 상용으로 쓰는건 용납하지 않음.)


사이트 : http://www.oracle.com/technetwork/products/berkeleydb/overview/index.html


굳이 말 안해도 되겠다. 자바나 C에서 유명한 녀석이니까. 닷넷 API도 제공한다.


이거 쓰고싶으면 먼저 컴파일해야 하는 귀차니즘이 있다. 그다음은 잘 쓰면 된다.


 


3. ScimoreDB


C++로 만들고 .NET/JAVA API 제공. SQL DB


라이센스 : 프리웨어(?) (비상업이던 상업적이든 쓰는건 상관없으나 소스는 따로 판매)


사이트 : http://www.scimore.com/


SQLCE의 대안은 일단 되긴 된다. 페이징 쿼리 제공하고 MSSQL의 재귀쿼리 등을 제공하니..


일단 이녀석 SQL 기반이 MSSQL 에 쓰는 T-SQL 기반이다.


근데.. 텍스트 취합은 + 연산자 쓰면 안되고 MySQL 처럼 CONCAT 함수 써야 한다. 등등 조금 불편한 점이 있긴 있지만,


SQLCE보단 낫긴 해도 MSSQL의 대안이라고 생각한다면 큰 오산.


 


4. FireBirdDB


C++로 만들고 유명한 여러 언어 API 드라이버 제공. 물론 .NET도 포함. SQL DB다.


라이센스 : IPL/IDPL (생소하지만 한마디로 표현하자면… 맘대로 쓰셈.)


사이트 : http://www.firebirdsql.org/


지금 실무 소규모 환경용에 쓰고 있는 녀석이다. P/SQL 기반이라 오라클DB 좀 만져봤다면 어렵지 않게 접근할 수 있다.


원래 이녀석은 서버-클라이언트 기반 SQL Server-Client 인데, 임베디드도 제공한다. 쪼끔 제한사항이 있긴 해도 지장은 없다.


임베디드 DB 치고는 참 다양한기능을 제공한다. 프로시저까지도 제공하니. 단, 사용자 정의 함수는 오라클의 자바나, MSSQL의 어셈블리같은 개념인데, C++이나 델파이 API로 제공하니 조금 다가가기는 어려울 것이다. 그러니 프로시저 사용을 생활하하는게 좋다.


여태까지 소개한 DB는 한글 문서나 커뮤니티가 없으나, 다행히도 이거는 한국 커뮤니티인 볼랜드포럼에서 도움을 받을 수 있다.


게다가 구글 검색으로 한글 블로그에서 소개된 firebird와 .net 연동 글을 볼 수 있다.


 


결론 : Firebird DB를 씁시다. 문서 기반 NoSQL DB는 Raven DB도 좋습니다.


주의 : C/C++ 에서 만든 DB는 서버에서 비트수 따지니 개발환경 구성시 비트수는 잘 따질 것.

composite / 2013년 5월 20일 / 미분류 / 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