시소당

ORACLE : DB2 대치 되는 PL함수 및 유용한 정보

http://www.ibmdb2.net/board/board.php?bnum=4&num=1650&act=read&cate=&page=1&search=title&s_word=함수

TYPE ORACLE DB2

------------------------------------------------------------------------------------

DUMMY DUAL SYSIBM.SYSDUMMY

EX)

ORACLE > SELECT SYSDATE FROM DUAL;

DB2 > SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1

------------------------------------------------------------------------------------

NULL NVL(XX,'333') COALESCE(XX,'333')

ROWNUM ROWNUM > 1 ..WHERE XX > '333' FETCH FIRST 1 ROW ONLY

날짜타입 SYSDATE CURRENT DATE : 2004-06-11

CURRENT TIMESTAMP : 2004-06-11 :10:12 23:20

-------------------------------------------------------------------------------------

숫자변환 TO_NUMBER 정수형 : INTEGER(XX), DOUBLE(XX),FLOAT(XX)

문자변환 TO_CHAR 문자형 : CHAR(XX)

날짜변환 TO_DATE 1.TO_DATE 있지만 쓸려면 절라 삽질해야함.. 포기

2.오라클의 TO_DATE(XX,'YYYY-MM-DD')

지원안함..

3.DATE함수

DATE('2004') ->에러 지원안함

DATE('200406') ->에러 지원안함

DATE('20040611') ->에러 지원안함

DATE('2004'||'-'||'06'||'-'||'11') ->날짜 리턴

DATE('2004-06'||'-'||'11') ->날짜 리턴

DATE('2004-06-11') ->날짜 리턴

날짜계산 ADDMONTH 1.DB2에는 오라클의 ADDMONTH함수 비슷꾸리무한

게 없음.. ㅠ.ㅠ

2.CURRENT DATE + 1 years ->년도 계산

CURRNET DATE + 1 months ->월계산

CURRENT DATE + 1 days ->일자계산

CURRENT TIMESTAMP + 윗것과 같음

CURRENT TIME + 윗것과 같음

3.두날짜사이에 계산일수 구하기

DATE('2004-02-03') - DATE('2004-06-11')

조건절 DECODE DB2 조건절은 CASE .. WHEN .. THEN .. END

만 지원함..

------------------------------------------------------------------------

another................

1. rownum

select * from (select deptname, rownumber() over() rn from org) a where rn<3
DEPTNAME RN
-------------- --------------------
Head Office 1
New England 2
2 record(s) selected.

select deptname from org fetch first 2 rows only
DEPTNAME
--------------
Head Office
New England
2 record(s) selected.


2. date

select hex(current date) from sysibm.sysdummy1
1
--------
20070216
1 record(s) selected.

select '^^' from sysibm.sysdummy1 where '20070216' between hex(current date) and hex(current date -5 days)
1
--
0 record(s) selected.

SUBSTR(CHAR(HEX(CURRENT TIMESTAMP)),1,14)


3. nvl

SQL SELECT 절에서 collesce(col1,널대체값) 또는 value(col1,널대체값)


4. instr

userid >> test:1234
test:123
select substr(userid, LOCATE( ':', userid )+1) from test_table;
1234
123

5.rownum

db2 "select * from (select a.*,rownumber() over() rn from staff a fetch first 1 rows only )a where rn=0"


6. sequence

"일련번호" DECIMAL(15,0) NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +999999999999999
NO CYCLE
CACHE 20
NO ORDER ) )

그리고 db에서 제공하는 sequence를 사용하는 방법도 있습니다.