티스토리 뷰

오라클 개발자를 위한 큐브리드 SQL 가이드

소개: 오라클에 익숙한 개발자들이 큐브리드에 적응 할 수 있도록 작성된 문서 입니다.

2009-01-29 고객지원팀 권성준 version 1.0 CUBRID

2009-03-31컨설팅팀 우광명 version 1.1 CUBRID

적용 대상: CUBRID7.3 이상 , 오라클 8i 이상

목 차

개요 1

샘플 데이터 준비 2

pattern 1. ROWNUM pseudocolumn(의사 컬럼) 및 Top-N query 4

pattern 2. Implicit (묵시적인) and Explicit(명시적인) Data Conversion 6

pattern 3. 날짜 타입의 차이점 및 사용법 7

pattern 4. 함수의 사용법 9

pattern 5. Cubrid와 Oracle의 문법적 차이. 9

pattern 6. Cubrid와 Oracle의 Expression의 차이 10

pattern 7. 오라클만 지원하는 기능 10

pattern 8. 큐브리드만 지원하는 기능 11


개요

본 문서는 오라클을 사용해 본적이 있는 개발자가 큐브리드에 쉽게 적응 할 수 있도록 도와 줄 수 있게끔 간략한 차이점을 문서로 만든 것입니다.

현재 문서의 상태는 모든 것을 비교 하고 있지는 않습니다. 앞으로 계속 자주 사용되는 패턴이 발생되면 추가적으로 업데이트 할 것입니다.


샘플 데이터 준비

샘플 데이터는 두 DB모두 동일한 구조를 사용 하였다.

주의할 사항은 오라클의 DATE타입은 큐브리드의 TIMESTAMp 로 변환 할 수 있다.


 

   큐브리드  오라클
 DB 구조  create table board_data (
bbs_id integer ,
view_cnt integer ,
title varchar(10
0),
contentsvarchar(4000),
reg_user_idvarchar(50),
reg_dateTIMESTAMp,
upd_date
TIMESTAMp
);

ALTERTABLEboard_dataADD
pRIMARYKEY(bbs_id);
 createtableboard_data(
bbs_idinteger,
view_cntinteger,
titlevarchar(100),
contentsvarchar2(4000),
reg_user_idvarchar(50),
reg_dateDATE,
upd_dateDATE
);

ALTERTABLEboard_dataADD
pRIMARYKEY(bbs_id);
 샘플 데이터  insert into board_data values ( 3, 0, '타이틀3','내용3','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );

insert into board_data values ( 2, 0, '타이틀2','내용2','quark', to_timestamp ( '20090330','YYYYMMDD') , to_ti
mestamp ( '20090330','YYYYMMDD') );

insert into board_data values ( 4, 0, '타이틀4','내용4','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );

commit;

insert into board_data values ( 1, 0, '타이틀1','내용1','quark', to_ti
mestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );


insert into board_data values ( 10, 0, '타이틀10','내용10','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );

insert into board_data values ( 11, 0, '타이틀11','내용11','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );

commit;

insert into board_data values ( 9, 0, '타이틀9','내용9','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYY
YMMDD') );

insert into board_data values ( 8, 0, '타이틀8','내용8','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );

insert into board_data values ( 7, 0, '타이틀7','내용7','quark', to_timestamp ( '20090330','YYYYMMDD') ,
to_timestamp ( '20090330','YYYYMMDD') );

insert into board_data values ( 6, 0, '타이틀6','내용6','quark', to_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );
commit;

insert into board_data values ( 5, 0, '타이틀5','내용5','quark', t
o_timestamp ( '20090330','YYYYMMDD') , to_timestamp ( '20090330','YYYYMMDD') );
commit;
 insert into board_data values ( 3, 0, '타이틀3','내용3','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

insert into board_data values ( 2, 0, '타이틀2','내용2','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

insert into board_data values ( 4, 0, '타이틀4','내용4','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

commit;

insert into board_data values ( 1, 0, '타이틀1','내용1','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );


insert into board_data values ( 10, 0, '타이틀10','내용10','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

insert into board_data values ( 11, 0, '타이틀11','내용11','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

commit;

insert into board_data values ( 9, 0, '타이틀9','내용9','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '200
90330','YYYYMMDD') );

insert into board_data values ( 8, 0, '타이틀8','내용8','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );

insert into board_data values ( 7, 0, '타이틀7','내용7','quark', to_date ( '20090330','YYYYMMDD') , to_d
ate ( '20090330','YYYYMMDD') );

insert into board_data values ( 6, 0, '타이틀6','내용6','quark', to_date ( '20090330','YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );
commit;

insert into board_data values ( 5, 0, '타이틀5','내용5','quark', to_date ( '20090330'
,'YYYYMMDD') , to_date ( '20090330','YYYYMMDD') );
commit;


pattern 1. ROWNUM pseudocolumn(의사 컬럼) 및 Top-N query

웹 개발 시 가장 많이 사용하는 SQL패턴이 페이징 및 목록을 구성하는 SQL일 것이다 첫 번째 패턴으로 오라클에서 가장 자주 사용하는 일반적인 SQL을 샘플로 하였다.


· 큐브리드 정의

SELECT 문에 ORDER BY 절이 포함된 경우 WHERE절에 명시된 ROWNUM 의사 컬럼의 값은 ORDER BY 절 처리를 위한 정렬 과정 전에 생성된다. SELECT 문에 GROUp BY 절이 포함된 경우에는 HAVING 절에 명시된 GROUpBY_NUM() 함수의 값은 질의 결과가 그룹화된 이후에 계산된다. ORDER BY 절에 의한 정렬 과정이 완료된 이후에 결과 튜플의 일련 번호를 얻어내기 위해서는 ORDER BY 절에 ORDERBY_NUM() 함수를 사용해야 한다.

·오라클 정의

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.


간략히 말하면 큐브리드는 scan 하는 시점에 ordering을 하고 오라클은 결과셋을 만든 다음 ordering을 한다.


예제1.

 

 큐브리  오라클  비고
 select a.*, rownum
from (
select bbs_id, title, contents, reg_user_id , rownum rnum
from board_data
order by bbs_id desc
) a
where rownum between 1 and 5;
 select a.*, rownum
from (
select bbs_id, title, contents, reg_user_id , rownum rnum
from board_data
order by bbs_id desc
) a
where rownum between 1 and 5;
 사용X
데이터scan에 따라서 다른 결과를 낳음
 select b.*, rownum
from ( select rownum rnum , a.*
from (select bbs_id, title, contents, reg_user_id
from board_data
order by bbs_id desc
) a
) b
where rnum between 1 and 5
 select b.*, rownum
from ( select rownum rnum , a.*
from (select bbs_id, title, contents, reg_user_id
from board_data
order by bbs_id desc
) a
) b
where rnum between 1 and 5
 사용X
데이터scan에 따라서 다른 결과를 낳음
 select bbs_id, title, reg_date
from board_data
order by bbs_id desc for orderby_num() between 1 and 5
 select b.*, rownum
from ( select rownum rnum , a.*
from (select bbs_id, title, contents, reg_user_id
from board_data
order by bbs_id desc
) a
) b
where rnum between 1 and 5
 사용 O
큐브리드 권고 사항



pattern 2. Implicit (묵시적인) and Explicit(명시적인) Data Conversion


오라클의 경우 SQL 안에서 묵시적인 cast 연산을 지원 한다..

하지만 큐브리드의 경우 오라클과 같이 명시적으로 혹은 묵시적으로 SQL안에서 Data conversion을 하지 않는다

오라클에서 지원하는 Data Conversion은 다음의 URL에서 확인 가능

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements002.htm#i55214


Create Table Temp_table { Int_column Integer };


예제1.

Insert into Temp_table values(123); Cubrid, Oracle 둘 다 사용가능

Insert into Temp_table values(123); Oracle 에서만 사용가능

Insert into Temp_table values(123); Cubrid 는 사용불가 Data Conversion을 명시적으로 해주어야함


예제2.

Select nvl(max(Int_column), 0) from Temp_table; Cubrid, Oracle 둘 다 사용가능

Select nvl(max(Int_column, ‘0’ ) from Temp_table; Oracle 에서만 사용가능

Select nvl(max(Int_column, ‘0’ ) from Temp_table; Cubrid 는 사용불가 Data Conversion을 명시적으로 해주어야함


pattern 3. 날짜 타입의 차이점 및 사용법

오라클에서 의미 하는 날짜 관련 type과 큐브리드에서 의미 하는 날짜 관련 type은 많이 다르다.

다음의 표에서 유사점과 차이점을 비교 하도록 하겠다

 

   큐브리드  오라클  지원여부  비고
 지원 하는 날짜-시간 타입  DATE  DATE  O  오라클 DATE와 큐브리드 DATE 타입은 다르다. 오라클의 경우 시분초 정보도 같이 표시 된다.
 TIME    O  오라클에서 지원하지 않는다.
 TIMESTAMp  DATE  O  두가지 타입의 의미는 같지만 지원하는 범위는 다르다.
큐브리드의 경우 GMT로 1970년 1월 1일 0시0분0초부터 2038년 1월 19일 03시14분07초까지라는 점이다.
   TIMESTAMp  X  큐브리드에서 지원 하지 않는다.
   INTERVAL YEAR, DAY  X  큐브리드에서 지원 하지 않는다.
 지원 하는 연산  SELECT SYSTIMESTAMp3600*24 FROM DB_ROOT;
 SELECT SYSDATE -1 FROM DUAL;
 날짜와 관련된 연산을 하는경우 오라클은 일단위 큐브리드는 초단위 연산을 한다.
 지원 하는 Data Conversion  큐브리드에서는 명시적으로 cast function을 사용하여 타입변환을 해주어야 한다.  오라클의 경우 묵시적인 형변환을 해준다.  



예제1.

Select sysdate -1 from dual; Oracle

Select systimestamp3600*24 from db_root ; Cubrid



Create Table Temp_Table{ Date_column Date, Time_column Time, Timestamp_column Timestamp}

예제2

Select * from Temp_Table where months_between(sysdate, Timestamp_column); Oracle

Select * from Temp_Table where months_between(cast(systimestamp as date), cast(Timestamp_cokumn as date)); Cubrid

months_between 함수는 date type을 parameter로 가진다. Oracle은 입력 데이터의 type을 자동으로 DATE type으로 변환시켜주지만, Cubrid는 cast를 통해 변환을 시켜주어야 한다.







pattern 4. 함수의 사용법

예제 1.

select sum(count(access_id)) from game_access group by access_id Oracle

select sum(cnt) from (select count(access_id) as cnt from game_access group by access_id) a Cubrid

Cubrid는 Oracle처럼 집계함수를 중복하여 사용할 수 없다. 중복하여 사용하고 싶다면, from에 sub-query로 나눠서 사용해야 한다.


pattern 5. Cubrid와 Oracle의 문법적 차이.

예제1.

Delete from Temp_Table; Cubrid, Oracle

Delete Temp_Table; Oracle

Oracle은 Delete와 Delete from을 모두 사용할 수 있지만, Cubrid는 Delete만 사용할 수 있다.


예제 2

Select * from Temp_Table order by dbms_random.value; Oracle

Select * from Temp_Table order by random(); Cubrid

Oracle의 dbms_random.value 함수를 대신하여, Cubrid에서는 random()함수를 제공하고 있다.


예제 3.

Select count(column1) from Temp_Table order by column2; Oracle 만 사용가능

Select count(column1) from Temp_Table; Cubrid, Oracle


pattern 6. Cubrid와 Oracle의 Expression의 차이

예제 1.

Select * from Temp_Table where column != 123; Oracle

Select * from Temp_Table where column <> 123; Cubrid

Oracle의 Not Equal 표현인 != 대신에 Cubrid에서는 <>제공한다.

예제 2.

Insert into Temp_Table values (123, ‘’ ); Oracle

Insert into Temp_Table values(123, null); Cubrid, Oracle

DATE type에서는 ‘’와 null이 동일하지 않다.

예제 3.

Select column as month from Temp_Table; Oracle

Select column asmonthfrom Temp_Table Cubrid

Cubrid와 Oracle은 예약어의 종류가 다르다. 또한, Cubrid는 테이블 명을 예약어로 사용하고 싶을 때에는 더블 쿼트 “” 를 꼭 사용하여야 한다.

pattern 7. 오라클만 지원하는 기능

   큐브리드  오라클  비고
 Analytic function  지원X    기능에 따라서 구현 가능
 WITH clause  지원X    
 TEMp TABLE  지원X    
 procedure/Function/package  큐브리드의 경우 Java Stored procedure를 지원함  pL/SQL
SQLJ
 


예제 1. Analytic Function rank() 사용

select rank() over (order by score desc) as cnt, userid, score, datetime

FROM game_rank WHERE ROWNUM <= 150 -> 오라클만 사용가능


SELECT rownum as cnt , userid, score, datetime FROM (

select userid, score, datetime FROM game_rank order by score desc

) V WHERE rownum <= 150 -> 큐브리드 , 오라클 모두 사용가능


pattern 8. 큐브리드만 지원하는 기능

인터넷 서비스에 최적화된 큐브리드는 클릭 카운터라는 기능을 제공 한다.


INCR 함수는 SELECT 절에 포함되어 인자로 주어진 컬럼의 값을 1 증가시켜 주는 기능을 합니다. DECR 함수는 해당 컬럼의 값을 1 감소시킨다.

.

예제 1.

SELECT content, INCR(view_cnt) FROM board_data WHERE bbs_id = 1; CUBRID


SELECT content, view_cnt FROM board_data WHERE bbs_id = 1;

UpDATE board_data set

View_cnt = view_cnt +1

WHERE bbs_id = 1 ; ORACLE



All rights reserved.


이 글은 스프링노트에서 작성되었습니다.