Oracle Admin 강좌 (Oracle9i)
SQL*LOADER에서 SEQUENCE함수와 DECODE함수 사용하는 방법 9 7 40,187

by 김정식 SQL*LOADER DECODE TRAILING NULLCOLS CONTROL FILE SQLLDR SQL Loader [2003.11.09]


  Unique한 DATA을 Load하고자 할때 쓰이는 오라클 시퀀스와, SEQUENCE() 함수와 DECODE 함수의 사용 예제입니다.

  함수를 사용하는 경우는 CONVENTIONAL PATH LOAD인 경우에만 가능하며 DIRECT PATH LOAD인 경우는 적용되지 않음을 주의해야 합니다.

 

테스트 테이블 생성

 
SQL> CREATE  TABLE load_test
      (id     NUMBER,
       data1    VARCHAR2(30),
       data2    VARCHAR2(30));
    

 

Oracle Sequence를 Load하기

 
-- SQL*Loader에 사용할 시퀀스를 생성 합니다.
SQL> CREATE SEQUENCE load_test_seq
     START WITH 1
     INCREMENT BY 1;


-- 각 데이터의 레코드의 필드를 ','로 구분을 했습니다   
-- control file 파일의 내용
============== load_test.ctl 시작 ==============
LOAD DATA
INFILE *
INTO TABLE load_test
FIELDS TERMINATED BY ','
-- null or non_existent, TRAILING NULLCOLS를 생략하면 Load가 되지 않습니다.
TRAILING NULLCOLS
-- 컬럼 옆에 시퀀스명을 지정합니다.
(data1, data2, id "load_test_seq.nextval")
BEGINDATA       -- 데이터 시작을 알림
RESEARCH,SARATOGA
ACCOUNTING,CLEVELAND
ART,SALEM
FINANCE,BOSTON
SALES,PHILA
SALES,ROCHESTER
"INT'L","SAN FRAN"
============== load_test.ctl 끝 ==============

--  SQL*Loader을 아래와 같이 실행을 합니다.
C:\> sqlldr scott/tiger load_test.ctl

-- 위의 Control File은 SQL*Loader에서 내부적으로 아래와 같은 Insert문을 만듭니다.
INSERT INTO load_db_seq_delimiter (data1,data2,id)
VALUES (:data1,data2,"load_test_seq.nextval");

-- 위의 방법은 DIRECT PATH에서는 사용하지 못합니다.   
    

 

SEQUENCE 함수를 사용하여 일련번호 Load하기

  아래는 SEQUENCE함수의 옵션 입니다.

  SEQUENCE(n,increment) : 지정한 n 값부터 시작하여 increment 값만큼 증가 합니다.

  SEQUENCE(COUNT,increment) : table에 이미 존재하는 로우들을 count한 수에서 시작하여 increment 값만큼 증가 합니다.

  SEQUENCE(MAX, increment) : 해당 컬럼의 maximum 값에서 시작하여 increment 값만큼 증가 합니다.

 
-- Control File생성
-- 위에서 생성한 load_test테이블을 가지고 테스트 하겠습니다. 
============== load_test.ctl 시작 ==============
LOAD DATA
INFILE *
-- 기존 데이터에 새로운 데이터를 추가합니다.
APPEND
INTO TABLE load_test
FIELDS TERMINATED BY ','
-- SEQUENCE함수를 사용해서 MAX부터 1씩증가 시킵니다.
(id SEQUENCE(MAX,1), data1, data2)
BEGINDATA
INCHON,SPCLINE
SEOUL,SPCROOM
============== load_test.ctl 끝 ==============
 
 
-- SQL*Loader을 실행하고 데이터를 확인해 보세요..
C:\> sqlldr scott/tiger load_test.ctl
    

 

Decode 함수 사용 예제

 

 
-- data1 column의 데이터가 'HELLO'이면 'GOODBYE'를
-- 그렇지 않으면 data1 column값을 로드하는 예제 입니다.
 
 
-- Control File생성
-- 위에서 생성한 load_test테이블을 가지고 테스트 하겠습니다.  
============== load_test.ctl 시작 ==============
LOAD DATA
INFILE *
APPEND               -- 기존 데이터에 새로운 데이터를 추가합니다.
INTO TABLE load_test
FIELDS TERMINATED BY ','
-- SEQUENCE함수를 사용해서 MAX부터 1씩증가 시킵니다.
-- DECODE함수를 사용해서 데이터를 컨트롤 합니다.
(id SEQUENCE(MAX,1), data1, data2 "decode(:data1, 'HELLO', 'GOODBYE', :data1)")
BEGINDATA
HELLO,""
HI,""
HEY,""
HELLO,""
============== load_test.ctl 끝 ==============
 
-- SQL*Loader을 실행하고 데이터를 확인해 보세요..
 
C:\> sqlldr scott/tiger load_test.ctl
    

- 강좌 URL : http://www.gurubee.net/lecture/1844

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김정식 [2005.03.10 16:46:53]
DATE형 컬럼에 LOAD할 경우에는
컬럼명 옆에 DATE 그리고 포맷을 적어준다.
HIREDATE DATE "DD-MON-YYYY",
CREDATE DATE "RRRR-MM-DD"

by 배은희 [2006.01.17 16:38:48]
nullcols 이게 몰까요?

by 김정식 [2010.04.22 18:48:18]
TRAILING NULLCOLS 는 정의한 컬럼의 데이터가 DATA파일에 존재하지 않는다는 것을 명시적으로 기술한 것입니다.
위 예에서는 id 컬럼의 데이터가 DATA파일에 없고, 시퀀스 값으로 사용하겠다고 기술하고 있습니다.

by 김정식 [2010.11.01 17:08:09]
TIMESTAMP 컬럼도 DATE형과 동일하다
REGDATE TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3"

by 김정식 [2013.09.23 12:09:43]

by 김정식 [2013.09.23 12:10:34]
CSV 파일 double quote 로 감싸여져 있는 경우

OPTIONALLY ENCLOSED BY  '"'

by 안떠니 [2014.11.26 08:10:53]
C:\> sqlldr scott/tiger load_test.ctl

 

디비를 지정하지 않고 실행하네요... 생략하는건가요?

ctl 파일 안에도 그냥 load_test 달랑...스키마 지정 안하나요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입