오라클 관련 SQL

	오라클에서 쓰는 잃어버리기 쉬운 TIPs
1. 오라클에서 사용하는 필요한 문법s. 2. MSSQL도 함께 모았어요..   (여러분의 댓글이 있으면, 본 블러그를 찾아오시는 모든분에게 좋은정보가 추가됩니다)

 

1. 오라클과 MSSQL 확인.

A) 오라클 문법

1) INSTR, INSTRB 의 다른점 (한글을 1 Byte처리할 것인지? 2 Bytes처리 할 것인지?)

=> 'D' 가 위치한 결과를 나타내는 samples

a) SELECT INSTR('ABC123오라클문DEF자르기', 'D') AA FROM DUAL   ;    

결과: 11

b) SELECT INSTRB'ABC123오라클문DEF자르기', 'D') AA FROM DUAL   ;

결과: 15

2) SUBSTR, SUBSTRB 의 다른점 (한글을 1 Byte처리할 것인지? 2 Bytes처리 할 것인지?)

=> 자르기 문법

a) SELECT SUBSTR('ABC123오라클문DEF자르기', 6,10) AA FROM DUAL   ;    

결과: 3오라클문DEF자르

b) SELECT SUBSTRB('ABC123오라클문DEF자르기', 6,10) AA FROM DUAL   ;   

결과: 3오라클문D 

3) 월 구하기

a) SELECT TO_CHAR(TO_DATE('20110101', 'YYYYMMDD'), 'MONTH', 'NLS_DATE_LANGUAGE=KOREAN') FROM DUAL ;

결과: 1월

b) SELECT TO_CHAR(TO_DATE('20110101', 'YYYYMMDD'), 'MONTH',

'NLS_DATE_LANGUAGE=AMERICAN')  FROM DUAL; 

결과: JANUARY

4) DB의 NLS_CHARACTERSET 설정값 보기

a) SELECT * FROM NLS_DATABASE_PARAMETERS;

5) Oracle 오류번호 보기

a) ORA-01461 : LONG 값은 LONG 열에만 바인딩할 수 있습니다

확인: 위 내용과는 무관하게, 해당 컬럼의 length(길이) Over시 나타나는 오류.

 

99) 참고Page: https://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm

 

B) MSSQL 문법

1) '20041231' 을 날짜 형식으로

a) SELECT CONVERT(DATETIME, '20041231', 112)

b) SELECT CONVERT(DATETIME, '2004/12/31', 111)

c) SELECT CONVERT(DATETIME, '2004.12.31', 102)

결과: 2004-12-31 00:00:00.000

d) SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '20041231', 112), 111)

결과: 2004/12/31

e) SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, '20041231', 112), 102)

결과 : 2004.12.31

2) CASE 문법 (오라클)

a) SELECT  TITLE_ID
     ,  CASE WHEN TYPE = 'business' THEN 'B'
             WHEN TYPE = 'mod_cook' THEN 'M'
             ELSE 'E'
        END AS TYPE
  FROM  TITLES

b) SELECT SUBSTR('ABC123오라클문DEF자르기', 6,10) AA FROM DUAL   ;    

결과: 3오라클문DEF자르

c) SELECT SUBSTRB('ABC123오라클문DEF자르기', 6,10) AA FROM DUAL   ;   

결과: 3오라클문D

3) 날자쿼리할때 유용한 문법 (MSSQL)

a) select * from TB_SMS_SEND
    where DATENAME(year, SEND_DT) = '2018'
       and DATENAME(month, SEND_DT) = '01'
       and DATENAME(day, SEND_DT) = '10' ;

 

C) ORACLE / MSSQL 문법

[날자형->문자형 변환]
Oracle:
 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
 SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL;
 SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
MSSQL :
 SELECT CONVERT(CHAR(8),GETDATE(),112) -- 20180806

 SELECT CONVERT(CHAR(10),GETDATE(),120) -- 2018-08-06
 SELECT CONVERT(CHAR(20),GETDATE(),120) -- 2018-08-06 09:11:14
 SELECT CONVERT(CHAR(20),GETDATE()) --08  6 2018  9:10AM  

 SELECT CAST(GETDATE() AS CHAR(20)) --08  6 2018  9:09AM 
--==========================================

[문자형->날자형 변환]
Oracle:
 SELECT TO_CHAR(TO_DATE('2010/11/07'), 'DD') FROM DUAL;
 SELECT TO_CHAR(TO_DATE('2010/11/07'), 'MM') FROM DUAL;
 SELECT TO_CHAR(TO_DATE('2010/11/07'), 'YYYY') FROM DUAL;
MSSQL :
 SELECT DATENAME(day, '2018-08-06') -- 7
 SELECT DATENAME(month, '2018-08-06') -- 8
 SELECT DATENAME(year, '2018-08-06') -- 2018
 SELECT DATEPART(day,'2018-08-06') -- 6
 SELECT DAY('2010-11-07') -- 6

[응용 for MSSQL]

SELECT GETDATE() AS 기준일자
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 - 7*0, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_월요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 - 7*1, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 일주전_월요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 - 7*2, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 이주전_월요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 - 7*3, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 삼주전_월요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 0, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_월요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 1, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_화요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 2, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_수요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 3, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_목요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 4, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_금요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 5, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_토요일
 , DATEADD( DAY, ( 8 - DATEPART( WEEKDAY, GETDATE() ) ) % 7 - 6 + 6, DATEADD( DAY, DATEDIFF( DAY, 0, GETDATE() ), 0 ) ) AS 금주_일요일 ;

==> 결과..

 

 

끝까지 읽어 주셔서 대단히 감사합니다.

보신글이 도움이 되셨다면, 덧글하나 남겨 주시면 정말 많은 힘과 용기가 됩니다.

 

또한, 본 블러그 찾아오시는 모든 분들에게 좋은정보를 제공할 수 있는 좋은 정보 공유지원역할을 하시는 것 입니다.

 

궁금한 점은 내용 남겨 주시기 바랍니다..

이상으로 블로그를 방문해 주신 모든 분들께 감사드리며, 본 포스팅을 마치도록 하겠습니다.

좋은 정보를 공유해요~~