oracle -> mssql 변경

 

운영중인 시스템의 DataBase를 변경하는 일이 발생!

변경하는 과정을 정리하면 의미가 있을 것 같다.

 

1. mssql 설치 및 DataBase 생성 이후

캐릭터셋을 확인하자.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

DEFAULT_CHARACTER_SET_NAME 값이 iso1로 표시된다.

이러면 한글이 입력되지 않거나 깨질 수 있으니 변경하자

ALTER DATABASE 데이터베이스명 COLLATE Korean_Wansung_CI_AS

 

만약 권한에러가 발생하면 DBA에게 요청

 

2. 마이그레이션 툴은 "ESF Database Migration Toolkit"을 사용

변환툴 : https://www.dbsofts.com/

ESF Database Migration Toolkit

가격이 생각보다 저렴(322 usd)하고 무엇보다 구매 전 바로 다운로드 받아 사용이 가능함.

변환툴에서 oracle 컬럼 타입을 아래와 같이 변경

varchar -> nvarchr : 다국어지원이 필요하므로.

int -> bigint : 오라클의 int와 mssql 의 int 사이즈 차이가 존재하여 (오라클이 더 많이 들어감) bigint로 변경

Table, View만 이관 가능하다고 하나, 실제로 돌려보면 View가 일반 테이블로 생성된다.

결론은 Table과 데이터 정도만 이관하는데 만족해야 할 것 같다.

 

3. sequence mssql 문법에 맞게 변경하여 생성

거의 동일하며 mssql 에서는 시퀀스 타입 지정을 추가 해줘야 함.

-- 오라클에서 시퀀스 검색(현재 계정의 모든 시퀀스 조회
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG FROM USER_SEQUENCES;

 

-- 오라클 시퀀스 생성 문법
CREATE SEQUENCE
NO_SEQ -- 시퀀스 명
START WITH 1 -- 시작값
INCREMENT BY 1 -- 증가값
MAXVALUE 10000 -- 최대값
MINVALUE 1 -- 최소값
CACHE 20  -- 캐쉬값 (잦은 인덱스 채번 시 부하를 줄이기 위함이며, 잦은 채번이 아닐경우 없는게 좋음)
NOCYCLE  -- CYCLE : 최대값 초과 시 다시 시작, NOCYCLE : 다시 시작하지 않음
;

 

-- mssql 시퀀스 생성 문법

CREATE SEQUENCE
NO_SEQ -- 시퀀스 명
AS DECIMAL(18,0) -- 시퀀스 타입
START WITH 1 -- 시작값
INCREMENT BY 1 -- 증가값
MAXVALUE 10000 -- 최대값
MINVALUE 1 -- 최소값
CACHE 20 : 캐쉬값 (잦은 인덱스 채번 시 부하를 줄이기 위함이며, 잦은 채번이 아닐경우 없는게 좋음)
NOCYCLE -- CYCLE : 최대값 초과 시 다시 시작, NOCYCLE : 다시 시작하지 않음
;

 

4. Function,  Procedures가 있다면 mssql 문법에 맞게 변경

오라클 Function, Procedure 목록 조회

-- 오라클 Function 목록 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';

-- 오라클 Procedure 목록 조회
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';

Function

-- 오라클 Function 문법
CREATE OR REPLACE FUNCTION "펑션명" (
  IN_ARGS IN VARCHAR2(10)  -- 인자값1
  , IN_ARGS2 IN VARCHAR2(10)  -- 인자값2
)
  RETURN VARCHAR2(100)  -- 리턴데이터타입
IS

  OUT_TMP_VAL VARCHAR2(100) := '';  -- 내부에서 사용할 변수 선언

BEGIN

  -- 업무 쿼리 작성
  SELECT TMP_VAL INTO OUT_TMP_VAL
  FROM TMP_TABLE
  WHERE AA = IN_ARGS
      AND BB = IN_ARGS2;

RETURN OUT_TMP_VAL;

-- 예외 시 리턴값 처리
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '';
  WHEN OTHERS THEN
    RETURN '';

END;

 

-- mssql Function 문법
-- 생성 시 CREATE , 수정 시 ALTER
CREATE FUNCTION "펑션명" (
  @IN_ARGS VARCHAR(10)  -- 인자값1
  , @IN_ARGS2 VARCHAR(10)  -- 인자값2
)
  RETURN VARCHAR(100)
AS

BEGIN

  DECLARE @OUT_TMP_VAL VARCHAR (100) = '';  -- 내부에서 사용할 변수 선언

  --업무쿼리 작성
  SELECT @OUT_TMP_VAL = TMP_VAL 
  FROM TMP_TABLE
  WHERE AA = IN_ARGS
      AND BB = IN_ARGS2;

RETURN @OUT_TMP_VAL;

END;

 

Procedure

-- 오라클 문법
CREATE OR REPLACE PROCEDURE "프로시저명" (
  IN_ARGS IN VARCHAR2 -- 인자값1
  , IN_ARGS2 IN VARCHAR2 -- 인자값2
  , RESULT_STATUS OUT VARCHAR2 -- 리턴데이터
)
IS
  V_TMP_VAL VARCHAR2; -- 내부에서 사용할 변수 선언
  V_TMP_VAL2 VARCHAR2;  -- 내부에서 사용할 변수 선언2
  ERROR_MSG VARCHAR2(2000);  -- ERROR MSG 선언 (필요시)

BEGIN
  FOR R1 IN (
    -- FOR문을 위한 SELECT 업무쿼리 작성
    SELECT 
      A.COL1
      , A.COL2
    FROM TMP_TABLE A;
    WHERE A.COL3 = IN_ARGS
       AND A.COL4 = IN_ARGS2;
  )
  LOOP  -- SELECT 한 내용을 FOR문 돌려...
    -- 업무 쿼리 작성
    INSERT INTO INSERT_TABLE (COL1, COL2) VALUES
    (R1.COL1, R1.COL2);
    -- 또는 필요 시 업데이트

  END LOOP
  RESULT_STATUS := 'S'  -- 필요시 리턴 메세지 입력
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RESULT_STATUS := 'F';  -- 필요시 익셉션 발생 시 리턴 메세지 입력
    -- 필요 시 별도 에러 로그 테이블에 입력도 가능
    INSERT INTO ERR_LOG(ERR_ID, DOC_NO) VALUES (SYS_GUID(), IN_ARGS);

END;

 

-- mssql 문법
CREATE [ALTER] PROCEDURE "프로시저명" (
  @IN_ARGS IN VARCHAR -- 인자값1
  , @IN_ARGS2 IN VARCHAR -- 인자값2
  , @RESULT_STATUS OUT VARCHAR -- 리턴데이터
)
AS
  DECLARE @V_TMP_VAL VARCHAR; -- 내부에서 사용할 변수 선언
  DECLARE @V_TMP_VAL2 VARCHAR;  -- 내부에서 사용할 변수 선언2
  DECLARE @ERROR_MSG VARCHAR(2000);  -- ERROR MSG 선언 (필요시)

BEGIN
  DECLARE R1 CURSOR FOR
    -- FOR문을 위한 SELECT 업무쿼리 작성
    SELECT 
      A.COL1
      , A.COL2
    FROM TMP_TABLE A;
    WHERE A.COL3 = IN_ARGS
       AND A.COL4 = IN_ARGS2;
  
  OPEN R1
  FETCH NEXT FROM R1
    INTO @IN_ARGS, @IN_ARGS2
  WHILE @@FETCH_STATUS = 0
  BEGIN TRAN
    -- SELECT 한 내용을 FOR문 돌려...
    -- 업무 쿼리 작성
    INSERT INTO INSERT_TABLE (COL1, COL2) VALUES
    (R1.COL1, R1.COL2);
    -- 또는 필요 시 업데이트
  CLOSE R1
  DEALLOCATE R1
  COMMIT TRAN
  SET @RESULT_STATUS = 'S'  -- 필요시 리턴 메세지 입력
  
BEGIN CATCH
  ROLLBACK TRAN
  SET @RESULT_STATUS = 'F'  -- 필요시 익셉션 발생 시 리턴 메세지 입력
    -- 필요 시 별도 에러 로그 테이블에 입력도 가능
    INSERT INTO ERR_LOG(ERR_ID, DOC_NO) VALUES (SYS_GUID(), IN_ARGS);
  END CATCH

END

 

5. 업무 쿼리를 mssql 문법에 맞게 변경

구분	oracle	mssql
시스템 날짜	SYSDATE	GETDATE() 타임존 필요시 SYSDATETIMEOFFSET()
조건표현식	DECODE	CASE ~ WHEN ~THEN ~ END
문자열 연결	||	+
NULL일 경우 값 채우기	NVL	ISNULL
차집합	MINUS	EXCEPT
교집합	INTERSECT	NOT EXIST
문자열 길이	LENGTH	LEN
문자열 자르기	SUB	SUBSTRING
나눗셈	MOD(M,N)	M % N
올림	CEIL	CEILIN
버림	TRUNC(M,N)	ROUND(M,N,1)
프로시저 실행	BEGIN	EXCEPT
숫자나 날짜를 문자열로 변환	TO_CHAR	CONVERT
조인(+)	+	OUTER JOIN
공백제거	TRIM(STR)	LTRIM(RTRIM(STR))
여러행을 하나의 컬럼으로 가져오기	LISTAGG	STUFF, FOR XML
데이터 1건만 가져오기	ROWNUM = 1	TOP 1
유니크 랜덤 값 가져오기	RANDOMUUID()	NEWID()
	FROM DUAL	FROM DUAL은 쓸수 없으며, 꼭 필요한 경우 2가지 방법 1. DUAL 테이블 생성 2. SELECT 'A' FROM (SELECT 1 AS DUAL) TMP
시퀀스 값 가져오기	SELECT SEQUENCE_NM.NEXTVAL FROM DUAL;	SELECT NEXT VALUE FOR [SEQUENCE] AS VAL;
문자열 위치값 가져오기	INSTR	CHARINDEX
줄바꿈 문자열 등 CHAR 값	CHR	CHAR
입력, 수정 동시 쿼리	MERGE INTO	오라클과 동일하나 마지막에 ;(세미콜론)으로 끝나야 하며, INSERT 문에 alias는 제거 필요 함.
업데이트 문 Alias 사용 시	UPDATE TB T SET T.COL = SYSDATE WHERE T.ID = '001';	UPDATE T SET T.COL = GETDATE() FROM TB T WHERE T.ID = '001'
테이블 복사	CREATE TABLE 복사할테이블 AS SELECT * FROM 원본테이블	SELECT * INTO 복사할테이블 FROM 원본테이블
동시성 제어 SELECT 시 TABLE 잠금	SELECT COL FROM 테이블명 FOR UPDATE	SELECT COL FROM 테이블명 with(updlock)