select * into new_table

from old_table


oracle(8i)은?



create table new_table as select * from old_table


오라클로 되어 있는 SQL문을 MS SQL Server 버전으로 변경해야 할 필요가 있거나 그 반대의 경우, 때에 따라 상당히 복잡한 작업이 들어가게 된다. 변경해야 할 오라클 SQL문이 ANSI 표준에 준하다면 그런대로 괜찮다.


우선 아래 내용대로 찾아 바꾼다. (텍스트에디터 이용)

오라클MS SQL 
||+ 
FROM DUALFROM절 삭제
LENGTHLEN
MOD% 
SYSDATEGETDATE() 
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
--------------------------------------------------------------------------  

출처 : http://mysaga.tistory.com/209


CREATE TABLESPACE TS_BTS
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EFUSIONI\TS_BTS.DBF' SIZE 100M;


CREATE USER BTS IDENTIFIED BY BTS DEFAULT TABLESPACE ts_bts;

GRANT UNLIMITED TABLESPACE to bts;

GRANT CONNECT, RESOURCE, CREATE VIEW, DBA TO bts;


 

글쓴이 : 김홍선



13자리로 주어지는 주민번호를 가지고 나이를 계산해 보자.
물론 가정은 아래와 같은 것들이 되겠다.

- 우리나라 나이 계산방식과 같이 태어나자마자 1살로 계산한다.
- 2000년 미만은 주민번호 7번째자리가 남녀에 따라 1이나 2이고,
2000년 이상은 주민번호 7번째자리가 3이나 4이다.
- 1900년 미만과 내년이상은 고려하지 않는다.


주민번호 13자리가 변수 :jumin 으로 주어진다면,
아래와 같은 공식이 나오겠다.
(implicit type casting 을 크게 고려하지 않았다.)



SELECT TO_CHAR (SYSDATE, 'yyyy')
- 1899
- 100 * (CEIL (SUBSTR (jumin, 7, 1) / 2) - 1)
- SUBSTR (jumin, 1, 2) age
FROM (SELECT :jumin jumin
FROM DUAL)

 

출처 : http://www.soqool.com/

CREATE TABLE plan_table (
        STATEMENT_ID       VARCHAR2(30),
        plan_id            NUMBER,
        TIMESTAMP          DATE,
        remarks            VARCHAR2(4000),
        operation          VARCHAR2(30),
        options            VARCHAR2(255),
        object_node        VARCHAR2(128),
        object_owner       VARCHAR2(30),
        object_name        VARCHAR2(30),
        object_alias       VARCHAR2(65),
        object_instance    NUMERIC,
        object_type        VARCHAR2(30),
        optimizer          VARCHAR2(255),
        search_columns     NUMBER,
        ID                 NUMERIC,
        parent_id          NUMERIC,
        DEPTH              NUMERIC,
        POSITION           NUMERIC,
        COST               NUMERIC,
        CARDINALITY        NUMERIC,
        BYTES              NUMERIC,
        other_tag          VARCHAR2(255),
        partition_start    VARCHAR2(255),
        partition_stop     VARCHAR2(255),
        partition_id       NUMERIC,
        other              LONG,
        distribution       VARCHAR2(30),
        cpu_cost           NUMERIC,
        io_cost            NUMERIC,
        temp_space         NUMERIC,
        access_predicates  VARCHAR2(4000),
        filter_predicates  VARCHAR2(4000),
        projection         VARCHAR2(4000),
        TIME               NUMERIC,
        qblock_name        VARCHAR2(30)
);

오라클 데이터 베이스를 쓰면서 데이터 타입을 DATE 형식으로 썼을때

SYSDATE가 아닌 전달받은 날짜로 입력시 형식이 맞지 않으면 직면하는 에러이다..

잊어먹지 않기 위해 메모를 남겨둠...

insert into t_date(regist_date) values (to_date(?, 'yyyy-mm-dd hh:mi:ss));

insert into t_date(regist_date) values (to_date(?, 'yyyy-mm-dd hh24:mi:ss));

SQL*Plus 사용시 import 할때

  요넘이 항상 문제이다..

실제로 &n 이라는 문자가 매겨변수를 입력받는 문자이다 보니 매겨변수를 계속 넣으라고 한다면

설정을 이렇게 변경해 보길 바란다..

SQL > set scan off

물런 반대는

SQL > set scan on

이 되겠지? ㅋㅋ

다음 포스팅은 SQL*Plus 의 setting 들을 함 써볼까 한다.

+ Recent posts

티스토리툴바