* Oracle START WITH ~ CONNECT BY 절 MSSQL로 처리하기.

 

# Oracle

SELECT   LEVEL, CATE_ID, LTRIM (SYS_CONNECT_BY_PATH (CATE_NAME, ' > '), ' > ') || ' > ' AS CATE_NAME

      FROM TBL_CATEGORY

START WITH CATE_ID = 1

CONNECT BY PRIOR CATE_ID = PARENT_CATE_ID

 

 

 

# SQL Server

WITH CTE_TABLE

AS

(

       SELECT 1 AS LEVEL, A.CATE_ID, PARENT_CATE_ID, CATE_NAME, CONVERT(VARCHAR(100), CAST(A.CATE_NAME as VARCHAR(100)) + ' > ') AS CATE_NAME

       FROM dbo.TBL_CATEGORY A

       WHERE A.CATE_ID = 1

 

       UNION ALL

      

       SELECT LEVEL + 1, M.CATE_ID, M.PARENT_CATE_ID, M.CATE_NAME, CONVERT(VARCHAR(100), N.CATE_NAME + ' > ' + CAST(M.CATE_NAME AS VARCHAR(100)) + ' > ' ) AS CATE_NAME

       FROM dbo.TBL_CATEGORY M JOIN CTE_TABLE N ON M.PARENT_CATE_ID = N.CATE_ID

)

SELECT * FROM CTE_TABLE

;

저작자표시 비영리

* Oracle Multi In 절 MSSQL로 처리하기.

 

# Oracle

SELECT A, B, C, D

FROM TABLE_A

WHERE (A, B) IN

(

       SELECT BB.A, CC.B

       FROM TABLE_B BB, TABLE_C CC

       WHERE BB.A = CC.A

)

 

 

# SQL Server

SELECT A, B, C, D

FROM TABLE_A AA JOIN

(

       SELECT BB.A, CC.B

       FROM TABLE_B BB JOIN TABLE_C CC ON BB.A = CC.A

)

AAA ON  AA.A = AAA.A AND AA.B = AAA.B

 

저작자표시 비영리
http://www.sqler.com/319831

안녕하세요?

 

일전에 제가 엑셈에 교육받으러 갔을 때 받은 자료를 기재해서 올립니다.

 

1.     Oracle

-       Lock Compatibility

Requested mode

Existing granted mode

N

SS

SX

S

SSX

X

Null (N)

Yes

Yes

Yes

Yes

Yes

No

Sub-Shared (SS)

Yes

Yes

Yes

No

No

No

Sub-Exclusive (SX)

Yes

Yes

No

No

No

No

Shared (S)

Yes

No

No

Yes

No

No

Shared Sub-Exclusive (SSX)

Yes

No

No

No

No

No

Exclusive (X)

Yes

No

No

No

No

No

 

2.     SQL Server

-       Lock Type (Lock Resource)

Lock Resource

Description

RID

Heap내의 특정 행 보호

KEY

Serializable Transaction에서 Key Range 및 Index 행 보호

PAGE

8-kilobyte(KB) Page 보호

EXTENT

연속적인 8개의 page 보호

HOBT

Heap or B-tree 보호

TABLE

모든 데이터와 인덱스를 포함한 전체 테이블 보호

FILE

Database file 보호

APPLICATION

Application-specified resource 보호

METADATA

Metadata 보호

ALLOCATION UNIT

Allocation unit 보호

DATABASE

Database 보호

 

-       Lock Mode

Mode

Description

Exclusive Lock (X)

- INSERT, UPDATE, DELETE와 같이 데이터를 변경하는 작업에

대해 사용

Intent Lock

- Lock Escalation을 위해 Table/Page에 사용

1)     Intent Shared (IS)

2)     Intent Exclusive (IX)

3)     Shared with Intent Exclusive (SIX)

4)     Intent Update (IU)

5)     Shared Intent Update (SIU)

6)     Update Intent Exclusive (UIX)

Schema Lock

- Table의 Schema에 관련된 작업 수행 시에 사용

 (Schema Modification (Sch-M), Schema Stability (Sch-S))

Bulk Update Lock (BU)

- Table에 Bulk Copy 혹은 TABLOCK Hint 사용 시 발생

Key-Range Lock

- Serializable Transaction에 의해 읽혀진 Key 구간에 대해

다른 Transaction의 Insert 방지

 

-       Intent Lock Compatibility

Requested mode

Existing granted mode

IS

S

U

IX

SIX

X

Intent Shared (IS)

Yes

Yes

Yes

Yes

Yes

No

Shared (S)

Yes

Yes

Yes

No

No

No

Update (U)

Yes

Yes

No

No

No

No

Intent Exclusive (IX)

Yes

No

No

Yes

No

No

Shared with Intent Exclusive (SIX)

Yes

No

No

No

No

No

Exclusive (X)

No

No

No

No

No

No

 

 

사실상 Oracle 관련해서 들은 내용은 크게 기억이 안 나네요. -_-

저작자표시 비영리
http://www.sqler.com/316811

안녕하세요?

지난 번에 이어 마지막으로 세 번째 Function 비교 시간입니다.

MSSQL 2005, Oracle 9i 기준입니다.

 

20.   지금(Right Now) 가져오기

구분

MSSQL

Oracle

함수

GETDATE()

SYSDATE

용례

SELECT GETDATE()

SELECT SYSDATE FROM DUAL;

결과

2010-11-07 11:50:08.700

2010/11/07 11:50:09

 

è  MSSQL 2008에서는 “SYSDATETIME()” 을 통해서 더 상세하게 사용이 가능합니다.(DATETIME2)

SELECT SYSDATETIME()

-- 결과

2010-11-07 11:52:25.9900000

 

 

21.   일자 더하기 / 빼기

구분

MSSQL

Oracle

함수

DATEADD

+ / -

용례

SELECT GETDATE();

SELECT DATEADD(d,1,GETDATE());

SELECT DATEADD(hh,5,DATEADD(d,1,GETDATE()));

SELECT DATEADD(d,-1,GETDATE());

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE + 1 FROM DUAL;

SELECT SYSDATE + 1.5 FROM DUAL;

결과

2010-11-07 11:57:38.140

2010-11-08 11:57:38.140

2010-11-08 16:57:38.140

2010-11-06 11:57:38.140

2010/11/07 11:58:09

2010/11/08 11:58:09

2010/11/08 23:58:09
2010/11/06 11:58:09

è  위 결과를 보시면 아시겠지만, Oracle에서는 소수점 단위로 일자계산이 가능합니다. MSSQL에서는 일자에 소수점을 사용해도 인식이 불가능합니다.

 

 

22.   일자 차이 계산

구분

MSSQL

Oracle

함수

DATEDIFF

+ / -

용례

SELECT DATEDIFF(dd,'2010/10/07',GETDATE())

SELECT SYSDATE - TO_DATE('2010/10/07') FROM DUAL;

결과

31

31.39103009259259259259259259259259259259

è  위 결과에도 나타나듯이, MSSQL에서는 일자로 지정하면 딱 떨어지지만, Oracle에서는 특별히 지정하지 않는 한 소수점까지 나타냅니다.

 

 

23.   해당 월의 마지막 날 가져오기

구분

MSSQL

Oracle

함수

지원하지 않음

LAST_DAY

용례

-

SELECT LAST_DAY(to_date('2010/11/15', 'yyyy/mm/dd')) FROM DUAL;

결과

-

2010/11/30 00:00:00

è  MSSQL에서는 아래와 같이 처리할 수 있습니다.

SELECT DATEADD(d,-1,CONVERT(DATETIME,CONVERT(CHAR(6),DATEADD(m,1,'2010-11-15'),112) + '01'))

-- 설명 : 해당일에 1개월을 더한 뒤, 그 달의 1일에서 하루를 뺀 날을 가져옵니다.

-- 결과

2010-11-30 00:00:00.000

 

 

24.   Time Zone에 의한 시간 변환

구분

MSSQL

Oracle

함수

지원하지 않음

NEW_TIME

용례

-

SELECT NEW_TIME (TO_DATE ('2010/11/07 13:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL;

결과

-

2010/11/07 10:45:00

è  AST : 대서양 표준시(캐나다 동부, 푸에르토리고, 버진아일랜드 등, 그리니치 표준시보다 4시간 늦음)
MST : 산악 표준시(로키 산맥에 가까운 미국과 캐나다 일부 지역의 동계 표준시, 그리니치 표준시보다 7시간 늦음)

è  Oracle Zone 표

Value

Description

AST

Atlantic Standard Time

ADT

Atlantic Daylight Time

BST

Bering Standard Time

BDT

Bering Daylight Time

CST

Central Standard Time

CDT

Central Daylight Time

EST

Eastern Standard Time

EDT

Eastern Daylight Time

GMT

Greenwich Mean Time

HST

Alaska-Hawaii Standard Time

HDT

Alaska-Hawaii Daylight Time

MST

Mountain Standard Time

MDT

Mountain Daylight Time

NST

Newfoundland Standard Time

PST

Pacific Standard Time

PDT

Pacific Daylight Time

YST

Yukon Standard Time

YDT

Yukon Daylight Time

 

è  MSSQL에 DATETIMEOFFSET이라는 것이 있지만, 이것은 표준시간대 인식일 뿐 구조는 다릅니다.

 

 

25.   해당일자 다음에 오는 해당 요일 반환

구분

MSSQL

Oracle

함수

지원하지 않음

NEXT_DAY

용례

-

SELECT NEXT_DAY('2010/11/07', '월요일') FROM DUAL;

결과

-

2010/11/08 00:00:00

è  보시다시피 2010년 11월 7일 이후에 처음 오는 월요일을 반환합니다.
하지만, 주의할 사항은 국가설정에 따라 일자설정이 다릅니다.
미국으로 되어 있으면 SELECT NEXT_DAY('10-Nov-07', 'MONDAY') FROM DUAL; 으로 하셔야 합니다.

 

 

26.   지정한 날짜의 특정 부분을 나타내는 문자열을 반환합니다.

구분

MSSQL

Oracle

함수

DATENAME, DATEPART …

TO_CHAR

용례

SELECT DATENAME(day, '2010-11-07')

SELECT DATENAME(month, '2010-11-07')

SELECT DATENAME(year, '2010-11-07')

 

SELECT DATEPART(day,'2010-11-07')

SELECT DAY('2010-11-07')

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;

결과

7

11

2010

 

7

7

07

11

2010

è  MSSQL DATENAME의 경우 2005 이전 버전에 맞춰져 있습니다. 그 이상의 버전에서는 용례 아랫부분을 참조하시면 됩니다.
Oracle의 경우엔 TO_CHAR를 만능으로 써서 할 수 있습니다.

다만 실제로 숫자처럼 쓰시려면 TO_NUMBER를 통해서 숫자형으로 변환해 주셔야 합니다.

 

 

27.   문자형을 날짜형으로 변환

구분

MSSQL

Oracle

함수

CONVERT / CAST

TO_DATE

용례

SELECT CONVERT(DATETIME,'2010-11-07')

SELECT CONVERT(DATETIME,'2010-11-07 12:20:23')

SELECT CONVERT(DATETIME,'20101107')

SELECT CONVERT(DATETIME,'20101107 12:20:23')

 

SELECT CAST('20101107 12:20:23' AS DATETIME)

SELECT TO_DATE('2010-11-07') FROM DUAL;

SELECT TO_DATE('2010/11/07 12:20:23', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

SELECT TO_DATE('20101107','YYYYMMDD') FROM DUAL;

SELECT TO_DATE('11/07/2010','MM/DD/YYYY') FROM DUAL;

결과

2010-11-07 00:00:00.000

2010-11-07 12:20:23.000

2010-11-07 00:00:00.000

2010-11-07 12:20:23.000

 

2010-11-07 12:20:23.000

2010/11/07 00:00:00

2010/11/07 12:20:23

2010/11/07 00:00:00

2010/11/07 00:00:00

è  Oracle의 경우엔 Format_Mask를 지정해 주시는 것이 관례입니다.

 

 

28.   날짜형을 문자형으로 변환

구분

MSSQL

Oracle

함수

CONVERT / CAST

TO_CHAR

용례

SELECT CONVERT(CHAR(8),GETDATE(),112)

SELECT CONVERT(CHAR(10),GETDATE(),120)

SELECT CONVERT(CHAR(20),GETDATE(),120)

SELECT CONVERT(CHAR(20),GETDATE())

 

SELECT CAST(GETDATE() AS CHAR(20))

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;

결과

20101107

2010-11-07

2010-11-07 17:41:21

11  7 2010  5:42PM

 

11  7 2010  5:41PM

2010-11-07 17:43:53

20101107

11/07/2010

è  문자형과 날짜형 변환은 위처럼 사용이 가능한데요,

MSSQL에서의 자세한 사항은 강산아님의 아티클

(http://www.sqler.com/?mid=bColumn&page=4&document_srl=265068)

을 참조해 주세요.

 

일단, Oracle 과 MSSQL 함수 비교는 여기서 마칠까 합니다.

 

물론, MSSQL 2008과 Oracle 10g/11g 로 올라가면서 추가되거나 변경된 것들도 많은데요, 제가 조금 더 공부해서 알려드리도록 하겠습니다.

 

이상 [쓸만한게없네]였습니다.

저작자표시 비영리

http://www.sqler.com/270809

안녕하세요?

지난 번에 이어 두 번째 Function 비교 시간입니다.

또다른 9가지 나갑니다. ^^.

 

11.   문자열 변환

구분

MSSQL

Oracle

함수

REPLACE

REPLACE

용례

SELECT REPLACE('1234567','123','321')

SELECT REPLACE('1234567','123','321') FROM DUAL

결과

3214567

3214567

è  MSSQL의 STUFF라는 함수가 있는데요. 이것은 엑셀의 REPLACE와 같습니다.

SELECT STUFF('13579',2,3,'222')

--> 결과

12229

 

 

12.   음절의 첫 글자만 대문자로 변환(Pascal Case)

구분

MSSQL

Oracle

함수

지원하지 않음

INITCAP

용례

-

SELECT INITCAP('we are the world') FROM DUAL;

결과

-

We Are The World

 

 

 

11.   문자열에서 특정 문자만 쏙쏙 골라서 다른 문자로 변환, 정확하게 이야기하면 지정한 문자와 맞으면 특정 문자로 처리함, 또는 특정 지정문자가 없는 경우 해당 문자를 삭제함.   

구분

MSSQL

Oracle

함수

지원하지 않음

TRANSLATE

용례

-

SELECT TRANSLATE('NothingToUse','o','!')  FROM DUAL;

SELECT TRANSLATE('BFG123', '12345BCDEFG', '123XXXXXXXX') FROM DUAL;

결과

-

N!thingT!Use

XXX123

è  아래 SAMPLE을 참조하세요.

create table translate_test

(

    mem_id varchar(20)

)

;

 

INSERT INTO translate_test (mem_id) values ('ABCD1234');

INSERT INTO translate_test (mem_id) values ('abcdef');

INSERT INTO translate_test (mem_id) values ('585472');

 

COMMIT;

 

select mem_id, translate(mem_id, '0123456789' || mem_id, '0123456789')

from translate_test;

 

-- 결과

ABCD1234         1234

abcdef  

585472  585472

 

 

14.   나열한 인수 중 가장 큰 값 / 작은 값을 반환

구분

MSSQL

Oracle

함수

지원하지 않음

GREATEST / LEAST

용례

-

SELECT GREATEST('Z9', '나의','콜','A341', '999','123')  FROM DUAL;

SELECT LEAST('Z9', '나의','콜','A341', '999','123')  FROM DUAL;

결과

-

콜 / 123

è  비교하는 것은, 문자순위(A보다는 Z가 크다. 한글은 자모순서에 의함), 숫자순위(0보다는 9가 크가), 글자수단위(같은 문자로 시작하더라도 결국 글자수가 많은 것이 크다.)

 

 

15.   길이 가져오기, 또는 BYTE 단위 길이 가져오기

구분

MSSQL

Oracle

함수

LEN, DATALENGTH

LENGTH

용례

SELECT LEN('NothingToUse')

SELECT DATALENGTH('NothingToUse')

SELECT LEN('지원하지 않음')

SELECT DATALENGTH('지원하지 않음')

SELECT LENGTH('NothingToUse') FROM DUAL;

SELECT LENGTH('지원하지 않음') FROM DUAL;

결과

12 / 12 / 7 / 13

12 / 7

è  위에서 보시면 알겠지만, BYTE 단위로 길이를 확인하시려면 LEN 이 아닌 DATALENGTH 를 사용하셔야 합니다.

 

 

16.   NULL 일 경우 대체값 표시.

구분

MSSQL

Oracle

함수

ISNULL

NVL

용례

SELECT ISNULL(QTY1,100) FROM A_TEMP

SELECT NVL(QTY1,100) FROM A_TEMP;

결과

100 (값이 NULL일 경우)

100 (값이 NULL 일 경우)

 

 

17.   숫자형을 문자형으로 변환

구분

MSSQL

Oracle

함수

STR, CONVERT, CAST

TO_CHAR

용례

SELECT 123 + 456

SELECT STR(123) + STR(456)

SELECT STR(123,3,0) + STR(456,3,0)

SELECT TO_CHAR(123) || TO_CHAR(456) FROM DUAL;

SELECT 123 || 456 FROM DUAL;

결과

579

123       456

123456

123456

123456

è  MSSQL의 STR는 기본 자릿수가 10자리입니다.

è  Oracle에서는 위에서 보시다시피 숫자형태를 Concat하더라도 자동적으로 문자로 나옵니다.

è  물론 MSSQL에서는 CONVERT나 CAST를 훨씬 더 많이 사용합니다.

SELECT CONVERT(VARCHAR(3),123) + CONVERT(VARCHAR(3),456)

--> 결과

123456

 

 

18.   문자형을 숫자형으로 변환

구분

MSSQL

Oracle

함수

CONVERT, CAST

TO_NUMBER

용례

SELECT '123' + '456'

SELECT CONVERT(INT,'123') + CONVERT(INT,'456')

SELECT CAST('123' AS INT) + CAST('456' AS INT)

SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL;

결과

123456 / 579 / 579

579

 

 

19.   조건처리 구문

구분

MSSQL

Oracle

함수

CASE

CASE (DECODE)

è  MSSQL CASE문은 여러 개의 조건 중 맞는 결과를 표시하는 것이고, Oracle의 DECODE는 참/거짓에 따라 결과를 표시합니다.

1)     MSSQL CASE SAMPLE

-- 1. 테이블생 성

CREATE TABLE CASE_STUDY

(PKEY INT PRIMARY KEY,

DATA1 VARCHAR(20),

DATA2 VARCHAR(30)

);

 

-- 2. 자료 입력

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');

 

-- 3. 데이터 출력

SELECT

       PKEY,

       CASE

             WHEN PKEY = 1 THEN DATA1

             WHEN PKEY = 2 THEN DATA2

             WHEN PKEY = 3 THEN DATA1

             ELSE DATA2

       END AS DATA

FROM

       CASE_STUDY

;

 

-- 4. 결과

1      A      -- 1일 때 DATA1값

2      나     -- 2일 때 DATA2값

3      C      -- 3일 때 DATA1값

4      라     -- 아니면 DATA2값

5      마     -- 아니면 DATA2값

 

2)     같은 자료를 Oracle DECODE를 사용할 경우

 

-- 1. 테이블생 성

CREATE TABLE CASE_STUDY

(

  PKEY NUMBER(9),

  DATA1 VARCHAR2(20),

  DATA2 VARCHAR2(30)

);

 

-- 2. 자료 입력

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');

 

-- 3. 데이터 출력

SELECT

       PKEY,

    DECODE(PKEY, 1, DATA1,

        DECODE(PKEY, 2, DATA2,

          DECODE(PKEY, 3, DATA1,

          DATA2)

         )

       ) AS DATA

FROM

       CASE_STUDY

   

 -- 또는

 SELECT

       PKEY,

    DECODE(PKEY, 1, DATA1,

          2, DATA2,

          3, DATA1,

          DATA2) AS DATA

FROM

       CASE_STUDY

   

 

-- 4. 결과

1      A      -- 1일 때 DATA1값

2      나     -- 2일 때 DATA2값

3      C      -- 3일 때 DATA1값

4      라     -- 아니면 DATA2값

5      마     -- 아니면 DATA2값

 

è  Oracle 8.1.7부터는 MSSQL과 거의 동일한 CASE문을 제공합니다.

위 구문을 MSSQL 구문과 동일하게 하셔도 결과는 같습니다.

 

 

오늘은 여기까지이고요, 조만간 또 3탄 올리겠습니다.

 

이상 [쓸만한게없네]였습니다.

저작자표시 비영리

+ Recent posts

티스토리툴바