2008/06/19 09:16

Oracle -> MSSQL 컨버젼 작업

2008/06/19 09:16 in Database

1. NVL --> ISNULL,
decode --> CASE WHEN ~~ THEN ~~ ELSE ~~ END
2. from dual 제거..
3. oracle MINUS 연산을 MSSQL로 변경
-- Oracle
SELECT c1
FROM 테이블1
WHERE 조건1
MINUS
SELECT c1
FROM 테이블2
WHERE 조건2
-- MS/SQL
SELECT A.c1
FROM
(
SELECT c1
FROM 테이블1
WHERE 조건1
) A
WHERE
NOT EXISTS
(
SELECT 'a'
FROM
(
SELECT c1
FROM 테이블2
WHERE 조건2
) B
WHERE A.c1 = B.c1
)

5. MSSQL의 경우 테이블의 인라뷰는 반드시 alias를 지정해주어야 한다.

6. 모든 SELECT 쿼리에 WITH(NOLOCK)처리

: SELECT * FROM t_account WITH (NOLOCK)

7. 다국어 지원을 위해서는..

1) 문자열데이터 타입은 NCHAR, NVARCHAR,NTEXT 사용..

또한, delete_flag NCHAR(1) DEFAULT N'Y',

2) 데이터 입력시

INSERT INTO t_node (node_id, node_name)
VALUES (N'NODE0000000001', N'한글도메인');

8. START WITH, CONNECT BY 구문 변환
------------ MSSQL 2000의 경우
직접 만든 Function을 사용한다
1) 특정노드의 자식 노드 가져오기
SELECT *
FROM t_node
WHERE delete_flag = 'N'
START WITH node_id = #nodeType:CHAR#
CONNECT BY PRIOR node_id = parent_id
==>
SELECT *
FROM fn_get_child_node(#nodeType:CHAR#)
WHERE delete_flag = 'N'

2) 특정노드의 부모 노드 가져오기
SELECT *
FROM t_node

WHERE delete_flag = 'N'
START WITH node_id = #nodeType:CHAR#
CONNECT BY node_id = PRIOR parent_id
==>
SELECT *
FROM fn_get_parent_node(#nodeType:CHAR#)
WHERE delete_flag = 'N'

3) 주어진 노드 목록에 대한 모든 자식 노드 가져오기
SELECT *
FROM t_node
WHERE delete_flag = 'N'
START WITH node_id IN
<iterate property="nodeIdList" open=" (" close=") " conjunction=",">
#nodeIdList[]#
</iterate>
CONNECT BY PRIOR node_id = parent_id
==>
SELECT *
FROM fn_get_child_node_by_inlist(
<iterate property="nodeIdList" conjunction="+">
#nodeIdList[]#
</iterate>
)
WHERE delete_flag = 'N'

4) 주어진 노드 목록에 대한 모든 부모 노드 가져오기
SELECT *
FROM t_node
WHERE delete_flag = 'N'
START WITH node_id IN
<iterate property="nodeIdList" open=" (" close=") " conjunction=",">
#nodeIdList[]#
</iterate>
CONNECT BY node_id = PRIOR parent_id
==>
SELECT *
FROM fn_get_parent_node_by_inlist(
<iterate property="nodeIdList" conjunction="+">
#nodeIdList[]#
</iterate>
)
WHERE delete_flag = 'N'

-------- MSSQL 2005 구문

SELECT node_id
FROM t_node
WHERE delete_flag='N'
START WITH node_id='NODE0000000000'
CONNECT BY PRIOR node_id = parent_id

===>

WITH CTE (node_id, node_name, depth)
AS
(
SELECT node_id, node_name, depth
FROM t_node
WHERE delete_flag='N'
AND node_id='NODE0000000000'
UNION ALL
SELECT N.node_id, N.node_name, N.depth
FROM t_node N JOIN CTE C ON N.parent_id = C.node_id
)
SELECT node_id
FROM CTE

9. Oracle rownum을 이용해 startNo, endNo로 부분범위 처리를 했던 쿼리....

즉 페이지 네비게이션 쿼리에 대한 처리
각 쿼리마다 order by 조건이 틀리지만 큰 틀로는 아래와 같이 처리하면 됩니다.

내림차순 ==>

오라클 > updated_date의 내림차순(DESC)으로 했을 경우

SELECT *
FROM
(
SELECT /*+ ORDERED INDEX_DESC(C IDX_NOTICE_UPDATED_DATE) */
ROWNUM rnum,
C.notice_id, N.node_name as domain_name, C.urgent_flag,
C.title, C.from_valid_date, C.to_valid_date, C.updated_by, C.updated_date
FROM t_notice C, t_node N
WHERE C.delete_flag = 'N'
AND C.domain_id = N.node_id
AND ROWNUM <= #endNo#
)
WHERE rnum >= #startNo#

MSSQL > updated_date의 내림차순(DESC)으로 했을 경우

SELECT TOP #rowsPerPage# 
C.notice_id, N.node_name as domain_name, C.urgent_flag,
C.title, C.from_valid_date, C.to_valid_date, C.updated_by, C.updated_date
FROM t_notice C WITH(NOLOCK), t_node N WITH(NOLOCK)
WHERE C.delete_flag = N'N'
AND C.domain_id = N.node_id
AND C.updated_date+C.notice_id <
(


SELECT ISNULL(MIN(NTS.updated_date+NTS.notice_id),
(SELECT TOP 1 updated_date + 'NOTI9999999999' FROM t_notice WITH(NOLOCK) ORDER BY updated_date desc) )
FROM
(
SELECT TOP (#startNo#-1) C1.notice_id, C1.updated_date
FROM t_notice C1 WITH(NOLOCK), t_node N1 WITH(NOLOCK)
WHERE C1.delete_flag = N'N'
AND C1.domain_id = N1.node_id
ORDER BY C1.updated_date desc
) NTS
)
ORDER BY C.updated_date desc

==> 오름차순

오라클 > updated_date의 오름차순(ASC)으로 했을 경우

SELECT *
FROM
(
SELECT /*+ ORDERED INDEX(C IDX_NOTICE_UPDATED_DATE) */
ROWNUM rnum,
C.notice_id, N.node_name as domain_name, C.urgent_flag,
C.title, C.from_valid_date, C.to_valid_date, C.updated_by, C.updated_date
FROM t_notice C, t_node N
WHERE C.delete_flag = 'N'
AND C.domain_id = N.node_id
AND ROWNUM <= #endNo#
)
WHERE rnum >= #startNo#

MSSQL > updated_date의 오름차순(ASC)으로 했을 경우

SELECT TOP #rowsPerPage#
C.notice_id, N.node_name as domain_name, C.urgent_flag,
C.title, C.from_valid_date, C.to_valid_date, C.updated_by, C.updated_date
FROM t_notice C WITH(NOLOCK), t_node N WITH(NOLOCK)
WHERE C.delete_flag = N'N'
AND C.domain_id = N.node_id
AND C.updated_date+C.notice_id >
(


SELECT ISNULL(MAX(NTS.updated_date+NTS.notice_id), CONVERT(VARBINARY, '') )
FROM
(
SELECT TOP (#startNo#-1) C1.notice_id, C1.updated_date
FROM t_notice C1 WITH(NOLOCK), t_node N1 WITH(NOLOCK)
WHERE C1.delete_flag = N'N'
AND C1.domain_id = N1.node_id
ORDER BY C1.updated_date
) NTS
)
ORDER BY C.updated_date

Posted by java/ajax

Trackback 0 Comment 0