Oracle에서 MS-SQL로 포팅

Oracle에서 MS-SQL로 포팅

작성자: Ospace(ospace114@empal.com)
작성일: 2007.11.07

아래 내용은 주로 프로시저(procedure)에 대한 내용이다.

먼저 두 SQL간에 프로시저 형식을 보자

-- Oracle
CREATE OR REPLACE PROCEDURE  myStoredProcedure (
        AGE  NUMBER    IN,
        AGEO NUMBER    OUT)
IS
BEGIN
...
END

-- MS-SQL
CREATE PROCEDURE  myStoredProcedure
        @age  int,
        @ageo int         OUTPUT
AS
BEGIN
...
END

변경사항

• CREATE OR REPLATE --> CREATE

프로시저 처음 선언 부분이 조금 차이가 있다. 둘다 비슷한 성격이다.

• <매개변수> <입출력형태> <데이터형> --> @<매개변수> <데이터형> <리턴여부>

매개변수명은ms-sql에서는 앞에 반드시 @을 사용해야한다. 지역변수도 마찬가지이다.

리턴여부는 Orace에서는 입출력에 대해서 in, out, inout으로 지정한다. ms-sql에서는 출력만 output으로 설정한다. 특별히 out하지 않아도 SELECT에 의해 내부적으로 값을 가지고 있기 때문에 생략도 가능하나 이경우 여러 개가 있을 경우 각 내부적으로 갖고 있는 값이 무엇인지 잘 알고 있어야 한다.

테이터 형은 서로 같은 부합되는 데이터형을 사용하면 된다.

ex) VARCHAR2 --> varchar(10)

테이터 형중에 커서(cursor)값이 있다. Oracle에서는 해당 값을 저장하는 출력변수를 반드시 지정해야되나,

Ms-sql에서는 그냥 SELECT문을 실행하면 된다. 그러면 해당 쿼리 결과가 내부적으로 저장된다. 그러므로 ms-sql에서 커서 선언하는 부분은 프로시저 파라미터이든 지역변수이든 모두 제거해주면 된다.

ex) o_users Types.cussortype --> 제거

OPEN o_users FOR --> 제거

Ms-sql에서도 꼭 사용하고 싶다면 ms-sql에서도 커서가 있다.

1. 먼저 커서를 선언하고 값을 가져온다.

DECLARE @Jumin varchar(255)
DECLARE @crsJumin CURSOR FOR
SELECT user_soc_no1 + '-' + user_soc_no2 as soc_no FROM tb_usr

2. 선언한 커서 변수를 명시적으로 연다.

OPEN @crsJumin

3. FETCH를 이용해서 커서에 있는 케코드를 추출한다. 결과는 @Jumin에 저장된다.

FETCH NEXT FROM @crsJumin INTO @Jumin

4. 모든 값을 출력한다.

WHILE @@FETCH_STATUS = 0 BEGIN
   PRINT @Jumin
   FETCH NEXT FROM @crsJumin INTO @Jumin
END

5. 사용한 커서를 닫는다.

CLOSE @crsJumin

6. 커서에 할당된 자원을 해제한다.

DEALLOCATE @curJumin

AS --> IS

그냥 바꿔준다.

END; --> END

Oracle의 세미콜론(;)를 모두 제거한다. 이는 END에만 해당되는 것은 아니다.

제어문을 변경하자

다른 설명이 필요없다. 두가지를 비교하면 확실히 알 수 있다.

-- Oracle
IF @num < 50 THEN
....
ELSIF @num < 100 THEN
...
ELSE
...
END IF;
-- Ms-sql
IF (@num < 50) BEGIN
...
END
ELSE IF(@num < 100) BEGIN
....
END
ELSE BEGIN
...
END

반복문을 변경하자.

반복문에서 FOR문은 Oracle과 ms-sql 많이 다르다. Oracle에서는 FOR문이 있지만 Ms-sql에서는 없다. 이를 구현하려면 WHILE문으로 구현해야 한다. 그리고 Oracle에서 그냥 변수("i")를 바로 사용할 수 있지만 Ms-sql에서는 지역변수("@i")를 선언해서 사용해야 한다. 조금 번거롭다.

-- Oracle
FOR i IN 1..i_num LOOP
...
END LOOP;
-- Ms-sql
WHILE(@i < @i_num) BEGIN
...
SET @i = @i +1
END

다음은 다른곳에서 변경한 예이다. 그러나 이변환은 완벽하지는 않다. 루프를 돌면서 추출되는 위치가 조금 애매하다. 다시 확인이 필요하다.

-- oracle
FOR i IN 1.. @i_num LOOP
   SET @v_row_o = trim(SUBSTRING(@v_cols_o, INSTR(@v_cols_o, ',', 1, i)+1, (INSTR(@v_cols_o, ',', 1, i+1) - INSTR(@v_cols_o, ',', 1, i))-1))
-- ms-sql
WHILE( @index1 < LEN(@str1) ) BEGIN
   set @index2 = CHARINDEX(',', @v_cols_o, @index1)
   IF(@index2 = 0) BEGIN
                BREAK
   END

   SET @v_row_o = SUBSTRING(@v_cols_o, @index1, @index2 - @index1)
   SET @v_row_o = LTRIM(@v_row_o)
   SET @v_row_o = RTRIM(@v_row_o)
   set @index1 = @index2 + 1
END

연산자를 변경하자

JOIN 연산자

일반적으로 JOIN 연산자는 두개의 결과에 대해 일치하는 값을 표시한다. 만약 일치하는 값이 없다면 결과는 나타나지 않을 것이다. 공통된 값이 없어도 테이터 값이 표시하게 할때 외부 조인을 사용하게 된다.

Oracle

Out Join (외부 조인)에 대해서 보자. 이를 (+)형태로 사용한다.

일반적인 Join은 추출된 두개의 컬럼을 조인하는 경우 공통된 값이 없다면 일부 데이터를 리턴하지 않은다.

그래서 만약 만족시킬 값이 없는 즉에 (+) 연산자를 사용한다. (+)연사자가 있는 측이 데이터가 없다면 null이라고 추가해서 표시한다는 의미이다. 예를 들어 다음과 같다.

아래는 a와 b의 uid값이 일치하는 값만 표시하게 된다.

...
WHERE a.uid = b.uid

UID  UID

---- ----

 1  1

 2  2

아래는 왼쪽 a의 컬럼을 b 컬럼 만큼 확장한다.?

...
WHERE a.uid(+) = b.uid

UID  UID

---- ----

 1  1

 2  2

 3

-- Ms-sql

다음은 city열을 기준으로 authors와 publishers 테이블을 조인하는 경우이다.

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
  ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

결과:

au_fname  au_lname  pub_name 

-------------------- ------------------------------ -----------------

Johnson  White  NULL

Akiko  Yokomoto  NULL

Abraham  Bennet  Algodata Infosystems

Cheryl  Carson  Algodata Infosystems

LEFT OUTER JOIN은 publishers 테이블의 city열에 일치하는 값이 있는지 관계 없이 authors 테이블의 행이 결과에 포함된다. 나열된 저자 대부분이 일치하는 데이터가 없으므로 pub_name에 null값이 포함된다.

위의 말은 LEFT에 있는 테이블을 기존으로 오른쪽 테이블을 확장시킨다.

그럼 간단한 변환 예제를 보자.

-- Oracle
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a, publishers p
WHERE a.city = p.city(+)
-- Ms-sql
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
  ON a.city = p.city

문자열 처리를 변경해보자

문자열 처리는 내부 프로시저를 호출해서 사용하기 때문에 Oracle과 Ms-sql에 따라서 명령어 형태와 사용방법이 틀리다. 때에 다라서는 내용을 많이 수정할 수 있다. 앞의 예제 코드를 참조하라.

INSTR --> CHARINDEX

둘간에는 입력되는 인자 수와 순서에 주의해야 한다. 때에따라서 이 차이점으로 인해 많은 변경이 필요하다.

-- oracle

INSTR('ABCABCABC', 'BC', 3, 2)

이는 'ABCABCABC'문자열에서 3번째 위치부터("BC")부터 시작해서 2번째 검색된 위치를 반환

결과: 8

-- ms-sql

CHARINDEX('BC', 'ABCABCABC', 3)

이는 'ABCABCABC'문자열에서 3번째 위치에서 찾기 시작해서 'BC'문자열이 시작되는 위치를 반환한다.

결과: 5

SUBSTR --> SUBSTRING

-- oracle

SUBSTR(

-- ms-sql

SUBSTRING('ABCDEF', 1, 2)

이는 "ABCDEF"문자열에서 1번째 위치에서 시작해서 총 2글자를 추출한다.

결과: AB

함수를 변경하자

TO_NUMBER

-- Oracle

TO_NUMBER('1234')

-- Ms-sql

CAST('1234' as int)

NLV

-- Oracle

NVL2(expr, expr1, expr2)

expr값이 null이 아니면 expr1을 반환하고 null이면 expr2를 반환한다.

NVL(expr, expr1)

expr값이 null이 아니면 expr값을 반환하고 null이면 expr1값을 반환한다.

-- Ms-sql

ISNULL(expr, expr1)

이것은 NVL와 같음

COMMIT

Oracle에서는 알아서 해주지만 Ms-sql에서 다음 명령어를 적당한 위치에 넣는다. 적당한 위치란 데이터베이스가 변경되기 시작한 부분을 말한다. 그리고 COMMIT를 그대로 사용한다.

Begin Tran

만약 처리중 에러가 발생하여 되돌아가려면 다음 명령을 입력하면 된다.

ROLLBACK TRAN

그리고 에러가 없다면 다음과 같은 명령을 수행하면 된다.

COMMIT TRAN

예외처리를 해보자.

-- oracle

EXCEPTION
WHEN NO_DATA_FOUND THEN
            SET @o_status ='9'
            SET @o_msg='처리된 건수가 0건 입니다.'
WHEN OTHERS THEN
            SET @o_status ='9'
            SET @o_msg='처리도중 에러가 발생하였습니다. 상세내용 : '+DBMS_UTILITY.FORMAT_ERROR_STACK
            ROLLBACK

-- ms-sql

IF (@@ERROR > 0)
BEGIN
            ROLLBACK TRAN
            SET @o_status = '9'
            SET @o_msg= '[ error_code= db_'+cast(@@error as varchar)+']'
END

사용자 예외처리는 어떻게?

TIP & Tech

프로시저 업데이트 오류

프로시저 수정하고 업데이트하는 에러가 발생함. 이는 기존 선언된 프로시저가 있기 때문에 발생한다.

해결하려면 기본 등록된 프로시저를 제거하고 다시 등록해줘야하는데, 간단하게 해결할 수 있는 방법이 있다.

USE 데이터베이스명

IF EXISTS (SELECT name FROM sysobjects
        WHERE name = '프로시저이름' AND type = 'P')
  DROP PROCEDURE 프로시저이름
GO
CREATE PROCEDURE 프로시저이름
AS
....
GO

구분 식별자 설정하기

SET QUOTED_IDENTIFIER OFF
SET QUOTED_IDENTIFIER ON

구분 식별자를 큰 따옴표("")를 사용할지 말지를 설정한다. 큰 따옴표를 사용할 예를 보면,

SELECT * FROM "Blank in Table Name"

이는 Microsoft OLE DB Provider for SQL Server와 SQL Server ODBC를 연결시에는 기본으로 ON이지만 그외는 OFF이다. 상황에 따라서 변경될 수 있으므로 큰따옴표를 사용하지 않던가, 항상 ON이 되도록 설정하여야 한다.

SET ANSI_NULLS ON

ON이 되면 모든 Nll값과 비교는 NULL이 된다. OFF가되면 유니코드가 아닌 값과 Null값이 비교하면 두값이 모두 Null이되면 True가 된다. 이 값은 기본값은 OFF이다.

계산된 열 또는 인덱스된 뷰에서 인덱스를 만들거나 변경할대에도 SET ANSI_NULLS을 항상 ON으로 해야한다. 이 옵션의 상태는 IsAnsiNullsEnbaled 속성을 검사하여 알 수 있다.

SET NOCOUNT ON

INSERT와 UPDATE, 그리고 DELETE 문은 결과를 리턴하는데 이는 수정될 열 수만 가지는 결과다. 이런 값의 리턴을 취소하려면 SET NOCOUNT ON을 배치나 프로시저안에 포함해주면 된다.