시소당
오라클로 되어 있는 SQL문을 MS SQL Server 버전으로 변경해야 할 필요가 있거나 그 반대의 경우, 때에 따라 상당히 복잡한 작업이 들어가게 된다. 변경해야 할 오라클 SQL문이 ANSI 표준에 준하다면 그런대로 괜찮다.
우선 아래 내용대로 찾아 바꾼다. (텍스트에디터 이용)
| 오라클 |
MS SQL |
| || |
+ |
| FROM DUAL |
FROM절 삭제 |
| LENGTH |
LEN |
| MOD |
% |
| SYSDATE |
GETDATE() |
| NVL(a, b) |
ISNULL(a, b) |
| TO_CHAR(날짜, 'YYYY-MM-DD') |
CONVERT(VARCHAR(10), 날짜, 120) |
| TO_DATE(문자열) |
CONVERT(DATETIME, 문자열) |
여기까지는 간단하다. 문제는 이 다음, 커서(Cursor)나 MINUS, CONNECT BY 구문이 포함될 경우다.
1. Cursor
오라클 버전의 Cursor 사용법은 아래와 같다.
--------------------------------------------------------------------------
CURSOR user_list IS
(
SELECT user_id, user_name
FROM T_USR
)
FOR cur_user IN user_list
LOOP
INSERT INTO T_MBR VALUES(cur_user.user_id, cur_user.user_name);
END LOOP;
--------------------------------------------------------------------------
이를 MS SQL 버전으로 옮기면
--------------------------------------------------------------------------
DECLARE user_list CURSOR FOR
SELECT user_id, user_name
FROM T_USR
OPEN user_list
FETCH NEXT FROM user_listINTO @v_id, @v_name
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO T_MBR VALUES(@v_id, @v_name)
FETCH NEXT FROM rec INTO @v_id, @v_name
END
CLOSE user_list
DEALLOCATE user_list
--------------------------------------------------------------------------
2. sys_connect_by_path(), START WITH ~ CONNECT BY ~오라클에서 상당히 편리하게 사용되는 계층구조 쿼리 구문도 MS SQL에서는 다른 방식으로 구현해야 한다.
--------------------------------------------------------------------------
SELECT SUBSTR(user_name, 4) AS user_name
FROM (SELECT user_id
user_name as,
sys_connect_by_path (user_name, ' > ' ) AS user_name
FROM T_USR
START WITH manager_id = 0 CONNECT BY PRIOR user_id = manager_id
) A
--------------------------------------------------------------------------
이를 CTE 재귀호출 방식을 이용하여 MS SQL 버전으로 옮기면
--------------------------------------------------------------------------
WITH TBL_PARENT AS
( SELECT user_id, manager_id,
CONVERT(VARCHAR(100), CAST(user_name AS VARCHAR(100)) + ' > ') AS user_name
FROM T_USR
WHERE manager_id = 0
UNION ALL
SELECT user_id, manager_id,
CONVERT(VARCHAR(100), B.navi +
CAST(a.user_name AS VARCHAR(100)) + ' > ') AS user_name
FROM T_USR A
INNER JOIN TBL_PARENT B
ON A.manager_id = B.user_id
)
SELECT user_id, manager_id,
SUBSTRING(navi, 1, LEN(navi)-2) navi
FROM TBL_PARENT
--------------------------------------------------------------------------
3. MINUS
사실 앞서 설명한 커서나 계층쿼리를 컨버전하는 것보다 이 마이너스 연산이 더 복잡하다. 왜냐하면 커서와 계층구조 같이 일정한 패턴보다는 원하는 결과를 얻기 위해 약간 더 섬세한 조건절 조정이 필요한 경우가 있기 때문이다. 핵심은 오라클의 마이너스 연산은 두 개의 SELECT문 결과집합에서 완전히 동일한 레코드를 첫 번째 결과 집합에서 제외한다는 것이다.
--------------------------------------------------------------------------
SELECT user_id, user_name
FROM T_USR
WHERE dept_id = 'D001'
MINUS
SELECT user_id, user_name
FROM T_USR
WHERE manager_id = 100
--------------------------------------------------------------------------
이를 NOT EXISTS를 이용하여 MS SQL 구문으로 옮기면
--------------------------------------------------------------------------
SELECT user_id, user_name
FROM T_USR X
WHERE NOT EXISTS (
SELECT 1
FROM T_USR Y
WHERE ISNULL(X.user_id, '') = ISNULL(Y.user_id, '')
AND ISNULL(X.user_name, '') = ISNULL(Y.user_name, '')
)
AND dept_id = 'D001'
--------------------------------------------------------------------------
여기서 중요한 점은 NOT EXISTS 안에 있는 서브쿼리의 WHERE 절에서 바깥쪽 테이블에서 SELECT하는 모든 컬럼을 일일이 서브쿼리 테이블의 컬럼값과 비교해야 한다는 것이다. (오라클의 MINUS는 모든 컬럼이 동일한 레코드를 제외하기 때문) 그리고 NULL끼리는 비교가 불가하기 때문에 ISNULL 함수를 사용해 처리하였다. 2008 버전부터는 EXCEPT 연산을 MINUS 대신 사용 가능하다.
4. DECODE
CASE WHEN ~ ELSE ~ END 문과 결과는 동일하지만 사용법상의 단순함으로 인해 많이 사용되는 DECODE 함수는 말 그대로 CASE 문으로 변경해주면 된다.
--------------------------------------------------------------------------
SELECT DECODE(user_id, 'A', 'B', 'C')
FROM T_USR
--------------------------------------------------------------------------
MS SQL에서는 아래와 같이 쓸 수 있겠다. (동일하게 오라클에서도 사용 가능하다)
--------------------------------------------------------------------------
SELECT CASE WHEN user_id = 'A' THEN 'B'
ELSE 'C'
END
FROM T_USR
--------------------------------------------------------------------------