2014년 11월 27일 목요일

SQL*LOADER SAMPLE[오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

　 BULLETIN CATEGORY BULLETIN TOPIC : UTILITY　　　　　　　　　　　 
: SQL*LOADER SAMPLE 

-------------------------------------------------------------------------------- 
　SQL*Loader는 외부 화일의 데이타를 ORACLE　 데이타베이스의 테이블에 넣기 위한　　　 
유틸리티이다.　 SQL*Loader를 사용하려면 외부 데이타 화일과 콘트롤화일이 필요하다.　　 콘트롤화일이라고 하는 것은 로드하는 데이타의 정보를 저장한 화일인데 예를 들면 다음과 같다.　　 
　　 load data　　　　　　　　　콘트롤화일의 앞에는 반드시 이 말이 필요함　　 
　　 infile sample.dat　　　　　외부 데이타 화일을 지정　　 
　　 replace　　　　　　　　　　테이블에 데이타 넣는 방법 지정　　 
　　 into table TABLE_NAME　　　데이타를 로드하는 테이블을 지정　　 
　　 fields terminated by ','　 데이타 필드의 종결문자 지정　　 
　　 (a integer external,　　　 테이블의 열 및 외부데이타 화일의 데이타 형을 지정　　 
　 　 b char)　　 

　　　 참고로 Replace 외에 다음의 옵션이 가능하다.　　　 

replace　　 테이블의 기존 행을 모두 삭제(delete)하고 Insert(7.0에서는 truncate 함)　　　 
append　　　 새로운 행을 기존의 데이타에 추가　　　 
insert　　　 비어 있는 테이블에 넣을 때　　　 
truncate　 테이블의 기존 데이타를 모두 truncate 하고 인서트(7.0에는 없음) 
　 SQL*Loader를 실행하면 아래의 화일이 생성된다.　　　 
로드 작업의 결과와 에러 등을 기록한 로그화일(확장자는 log)　　　 
에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)　　　 
사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일) 
　 이것은 discardfile 옵션으로 별도로 지정해야 생성된다.　　 
　 실행 방법은 다음과 같다.　　 

$ sqlload scott/tiger control=sample.ctl data=sample.dat　　　 

1. 임의의 컬럼에 문자열(값)을 입력한 경우　　 

　[ 테이블 구조 ]　　 
create table cons_test　　 
(a number,　　 
　b number,　　 
　c number,　　 
　d varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile cons.dat　　 
replace　　 
into table cons_test　　 
fields terminated by ','　　 
(a integer external,　　 
　b integer external,　　 
　c CONSTANT '100',　　 
　d char)　　 

[ 외부 데이타 화일 ]　　 
1,2,DATA　　 
2,4,DATA2　　 

[ 검색결과 ]　　 

SQL> select * from cons_test;　　 

　 A　　　　　　 B　　　　　　 C D　　 
　------- ----- ------ ------------　　 
　 1　　　　　　 2　　　　 100 DATA　　 
　 2　　　　　　 4　　　　 100 DATA2　　 

2. 로드한 때의 날짜를 넣고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table sysdatetb　　 
(a number,　　 
　b date,　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile sysdate2.dat　　 
replace　　 
into table sysdatetb　　 
fields terminated by ','　　 
(a integer external,　　 
　b sysdate,　　 
　c char(10))　　 

[ 외부 데이타 화일 ]　　 
111,STRINGS　　 
222,STRINGS2　　 

[ 검색결과 ]　　 

SQL> select * from sysdatetb;　　 

　　　　 A B　　　　 C　　 
------- --------- -------------　　 
　　 111 13-MAY-94 STRING　　 
　　222 13-MAY-94 STRING2　　 

　 SYSDATE는 Conventional Path의 경우는　 실행시에 삽입된 각각의 레코드 배열마다,　　　 
Direct Path의 경우는 로드된 각각의 레코드의 블럭마다 사용된다. 새로운 값으로 변경된다.　　　 

3. SEQUENCE를 임의의 수에서 임의의 수만큼 높여서 붙이고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table seqtb　　 
(a varchar(10),　　 
　b number,　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile seq.dat　　 
replace　　 
into table seqtb　　 
fields terminated by ','　　 
(a char,　　 
　b sequence(100,5),　　 
　c char)　　 

[ 외부 데이타 화일 ]　　 
1,a　　 
2,b　　 
3,c　　 
[ 검색결과 ]　　 

SQL> select * from seqtb;　　 

A　　　　　　　 B　 C　　 
----- ---- ----------------　　 
1　　　　　　 100　 a　　 
2　　　　　　 105　 b　　 
3　　　　　　 110　 c　　 

4. 논리 레코드를 구성하는 물리 레코드가 여러줄로 구성된 경우　　 
　　 (물리 레코드의 1바이트째로 판단되는 경우)　　 

[ 테이블 구조 ]　　 
create table conti_test　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile conti.dat　　 
replace　　 
continueif this　　 
(1) = '%'　　 
into table conti_test　　 
fields terminated by ','　　 
(a char,　　 
　b char,　　 
　c char)　　 

[ 외부 데이타 화일 ]　　 
%1,　　 
%2,　　 
3　　 
%A,B　　 
,C　　 
%a,b　　 
%c　　 
%d　　 
,ef　　 

[ 검색결과 ]　　 
SQL> select * from conti_test;　　 

A　　　　　 B　　　　 C　　 
---- ------ -----------　　 
1　　　　　 2　　　　 3　　 
A　　　　　 B　　　　 C　　 
a　　　　　 bcd　　　 ef　　 

　　 이 예의 경우 1바이트 째가 계속 행의 체크를 위해서 사용되기 때문에, 실제의 데이타를 1바이트 째부터 시작해서는 안된다. 위의 경우, 레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결된다.　　　 

5. 외부데이타 화일의 물리 레코드가 복수 레코드로 구성된 경우　　 
　　　 (구성하는 물리 레코드 수가 모두 일정한 경우)　　 

[ 테이블 구조 ]　　 
create table con_test　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile conti.dat　　 
replace　　 
concatenate 2　　 
into table con_test　　 
fields terminated by ','　　 
(a char,　　 
　b char,　　 
　c char)　　 

[ 외부 데이타 화일 ]　　 
1,2,　　 
3　　 
a,b,　　 
c　　 
A,　　 
B,C　　 

[ 검색결과 ]　　 

SQL> select * from con_test;　　 

A　　 B　　　C　　　 
----- ------ -------　　 
1　　 2　　　3　　 
a　　 b　　　c　　 
A　　 B　　　C　　 

6. 포지션 지정시 char형 데이타 전후의 블랭크도 로드하고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table pretb　　 
(a varchar(10),　　 
　b varchar(10), c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile pre.dat　　 
replace　　 
preserve blanks　　 
into table pretb　　 
(a position(01:05) char,　　 
　b position(06:10) char,　　 
　c position(11:20) char)　　 

[ 외부 데이타 화일 ****　　 
12 4 67890 ab def hi　　 
2　　 67890 ab def hi　　 

[ 검색결과 ]　　 

SQL> select * from pretb;　　 

A　　 B　　　　　　　　 C　　 
----- ------- --------------　　 
12 4　67890　　 ab def hi　　 
2　　 67890　　 ab def hi　　 
　　　 
[ 결과확인 ]　　 

SQL> select length(a), length(c) from pretb;　　 

LENGTH(A) LENGTH(C)　　 
--------- ----------　　 
　　　　　　 5　　　　 10　　 
　　　　　　 5　　　　 10　　 

7. 데이타가 없는 경우 NULL 데이타를 넣고자 할 때　　 

[ 테이블 구조 ]　　 
create table tratb　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile tra.dat　　 
replace　　 
into table tratb　　 
fields termintated by ','　　 
trailing nullcols　　 
(a char,　　 
　b char,　　 
　c char)　　 

[ 외부 데이타 화일 ]　　 
1,aa,　　 
2,bb,FF　　 
3,cc,　　 

[ 검색결과 ]　　 

SQL> select * from tratbl　　 

A　　 B　　　 C　　 
----- ----- ---------　　 
1　　 aa　　 
2　　 bb 　　FF　　 
3　　 cc　　 

　trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 된다.　　 

8. CHAR형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table nulltb　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile null.dat　　 
replace　　 
into table nulltb　　 
fields terminated by ','　　 
(a char,　　 
　b char,　　 
　c char(10) nullif c = blanks)　　 

[ 외부 데이타 화일 ]　　 
aa,bb, ,　　 
11,22, ,　　 
99,88,AA　　 
00,00,BB　　 

[ 검색결과 ]　　 

SQL> select * from nulltb;　　 

A　　 B　　　 C　　 
----- ------ -------　　 
aa　　bb　　 
11　　22　　　　　　　　 
99　　88 　　 AA　　　　　　　 
00　　00　 　 BB　　 

9. POSITION 지정시 BLANK를 그대로 로드하고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table nulltb2　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c date)　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile null3.dat　　 
replace　　 
preserve blanks　　 
into table nulltb2　　 
(a position(1:2) char,　　 
　b position(3:4) char nullif b = blanks,　　 
　c position(5:13) date "YY/MM/DD")　　 

[ 외부 데이타 화일 ]　　 
998892/11/11　　 
　　94/12/12　　 

[ 검색결과 ]　　 

SQL>select * from nulltb2;　　 

A　　 B　　 C　　 
----- ----- ---------------　　 
99　　88　　92/11/11　　 
　　　　　　94/12/12　　 

SQL> select length(a), length(b) from nulltb2;　　 

LENGTH(A) LENGTH(B)　　 
--------- ----------　　 
　　　　　　 2　　　　 2　　 
　　　　　　 2　　 

　position 지정으로 블랭크를 그대로 입력하기를 원하는 경우 preserve blanks를 지정한다.　　 

10. BLANK가 들어가 있을 때 0을 입력하고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table def2　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c number)　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile def2.dat　　 
replace　　 
into table def2　　 
fields terminated by ','　　 
(a char,　　 
　b char,　　 
　c integer external defaultif c = blanks)　　 

[ 외부 데이타 화일 ]　　 
11,11,123　　 
22,22, ,　　 
33,33, ,　　 
44,44, ,　　 

[ 검색결과 ]　　 

SQL> select * from deft;　　 

A　　　　 B　　　　 C　　 
----- -------- -------　　 
11　　　 11　　　 123　　 
22　　　 22　　　　 0　　 
33　　　 33　　　　 0　　 
44　　　 44　　　　 0　　 

11. 데이타가 NULL일 때 NULL이라고 하는 문자열을 넣고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table ifnulltb　　 
(a varchar(10),　　 
　b varchar(10),　　 
　c varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile ifnull.dat　　 
replace　　 
into table ifnulltb　　 
fields terminated by ','　　 
(a char,　　 
　b char "nvl(:b,'NULL')",　　 
　c char)　　 

[ 외부 데이타 화일 ]　　 
1,2,3,　　 
A,,B　　 
a,b,c　　 

[ 검색결과 ]　　 

SQL> select * from ifnulltb;　　 

A　　　 B　　　　　C　　 
----- ------ --------------　　 
1　　　 2　　　　　3　　 
A　　　 NULL　　　 B　　 
a　　　 b　　　　　 c　　 

　 NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를 경유하지 않기 때문에　　　 
사용할 수 없다.　　　 

12. 어떤 열을 모두 대문자(소문자)로 변환하여 로드하고 싶은 경우　　 

[ 테이블 구조 ]　　 
create table uptb　　 
(a varchar(10),　　 
　b varchar(10))　　 

[ 콘트롤 화일 ]　　 
load data　　 
infile upper.dat　　 
replace　　 
into table uptb　　 
fields terminated by ','　　 
(a char "lower(:a)",　　 
　b char "upper(:b)")　　 

[ 외부 데이타 화일 ]　　 
aBcDeFg,AbCdEf　　 
ccDD11,ffGG22　　 

[ 검색결과 ]　　 

SQL>select * from uptb;　　 

A　　　　　 B　　　　　　　　 
------- -------------　　 
abcdefg　　 ABCDEF　　 
cdd11　　　 FFGG22　　　 

　　



 [100%환급,개발자전문]빅데이터/SQL/자바/스프링/안드로이드/닷…오라클자바…12-272641
 [채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육…오라클자바…12-111909
53 [평일100%환급7건]Spring,자바&JSP,안드로이드,웹퍼블리싱,C#닷… 오라클자바…03-151723
52 [주말]C#,ASP.NET마스터 오라클자바…01-311847
51 [기업100%환급,평일주간]SQL기초에서스키마오브젝트,PLSQL,힌트… 오라클자바…01-312684
50 [평일주간야간,주말]C기본&자료구조,알고리즘 오라클자바…01-311437
49 [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정-… 오라클자바…01-191759
48 [평일야간,주말]안드로이드개발자과정(Android기초실무) 오라클자바…01-111645
47 [평일야간,주말주간야간]JAVA,Network&JSP&Spring,MyBatis,Hiber… 오라클자바…01-032163
46 [100%환급,개발자전문]빅데이터/SQL/자바/스프링/안드로이드/닷… 오라클자바…12-272641
45 [평일주간]NoSQL,MongoDB,빅데이터기초과정 오라클자바…12-191868
44 [평일주간야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX… 오라클자바…12-141838
43 [채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육… 오라클자바…12-111909
42 [평일주간]빅데이터하둡기초과정(BigData Hadoop) 오라클자바…12-091499
41 [평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 오라클자바…12-011730
40 [기업100%환급]오라클&자바웹스프링신입과정3주(SQL,JAVA,JSP,Se… 오라클자바…12-011907
39 [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) 오라클자바…12-011373
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)