ORACLE数据的导入导出操作

转载了网络上的一篇文章，是写的oracle数据的导入导出，很多人介绍的是exp和imp这种方式，但是好多后台的项目，比如数据仓库，ETL系统，都是生成的dat文件进行数据操作。

一、创建相关的表

CREATE TABLE dept (
  recno   varchar2(2),
  deptno   VARCHAR2(2),
  dname    VARCHAR2(20),
  loc      VARCHAR2(20),
  tdate   DATE);

CREATE TABLE emp (
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(10),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(8,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2),
  projno   NUMBER(4),
  loadseq  NUMBER(3));

CREATE TABLE proj (
  emp      NUMBER(4),
  projno   NUMBER(3));

CREATE TABLE funcdemo (
  last_name  VARCHAR2(20),
  first_name VARCHAR2(20));

CREATE TABLE decodemo (
  fld1    VARCHAR2(20),
  fld2    VARCHAR2(20));

CREATE TABLE denver_prj (
  projno  VARCHAR2(3),
  empno   NUMBER(5),
  projhrs NUMBER(2));

CREATE TABLE orlando_prj (
  projno  VARCHAR2(3),
  empno   NUMBER(5),
  projhrs NUMBER(2));

CREATE TABLE misc_prj (
  projno  VARCHAR2(3),
  empno   NUMBER(5),
  projhrs NUMBER(2));

CREATE TABLE po_tab OF XMLTYPE;

CREATE TABLE loadnums(
  col1 VARCHAR2(10),
  col2 NUMBER);


二、常用实例

1、例1：控制文件（control file）与数据文件（data file）合在一起，即：数据在控制文件里
1）控制文件（demo1.ctl）内容如下：
options (errors=100, silent=(feedback))
load data
infile *
truncate
into table dept
fields terminated by ','
optionally enclosed by '"'
(deptno, dname, loc)

begindata
12,research,"saratoga"
10,"accounting",cloveland
11,"art",salem
aa,finance,"boston"
21,"sales",rochester
42,"int'l","san francisco"
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo1.ctl
3）注意：（1）begindata关健字前后不能有空格，（2）infile后面直接跟上*

2、例2：控制文件（control file）与数据文件（data file）分开，但是数据文件中的数据的每个字段都是定长
1）控制文件（demo2.ctl）内容如下：
options(errors=10,silent=(header))
load data
infile 'demo2.txt'
truncate
into table emp
trailing nullcols
(empno    position(01:04)        integer external,
 ename    position(06:15)        char,
 job    position(17:25)        char,
 mgr    position(27:30)        integer external,
 sal    position(32:39)        decimal external,
 comm    position(41:48)        decimal external,
 deptno    position(50:51)        integer external)
2）数据文件（demo2.txt）内容如下：
7781 CLARK      MANAGER   7838 2572.50           10
7839XKING       PRESIDENT      5500.00           10
7934 MILLER     CLERK     7782  920.00           10
7566 JONES      MANAGER   7839 3123.75           20
7499 ALLEN      SALESMAN  7698 1600.00   300.01  30
7654 MARTIN     SALESMAN  7698 1312.50  1400.00  30
7658 CHAN       ANALYST   7566 3450.00           20
3）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo2.ctl
4）注意：position的应用

3、例3：控制文件（control file）与数据文件（data file）合在一起，即：数据在控制文件里，同时还使用到一些记录分隔符及特殊函数等
1）控制文件（demo3.ctl）内容如下：
load data
infile *
truncate
into table emp
fields terminated by ","
optionally enclosed by '"'
(
empno, 
ename, 
job, 
mgr, 
hiredate date "dd-month-yyyy",
sal, 
comm, 
deptno char terminated by ':',
projno, 
loadseq sequence(1,1)
)

begindata
9782,"clark",manager",7839, 09-june-2000, 2572.50,, 10:101
9839,"king","president", , 17-november-1999, 5500.00,,10:102
9934,"miller","clerk",7782, 23-january-2001, 920.00,, 10:102
9566,"jones","manager",7839, 02-april-2001, 3123.75,, 20:101
9499,"allen","salesman",7698, 20-february-2001, 1600.00, 300.00, 30:103
9654,"martin","salesman",7698, 28-september-2000, 1312.50, 1400.00, 30:103
9658, "chan", "analyst", 7566, 03-may-1999, 3450,, 20:101
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo3.ctl
3）注意：（1）sequence(begin,increment)，即：sequence(1,1)表示从1开始，每次增加1

4、例4：控制文件（control file）与数据文件（data file）分开，且使用continueif、discard等
1）控制文件（demo4.ctl）内容如下：
load data
infile 'demo4.dat'
discardfile 'demo4.dsc'
discardmax 999
replace
continueif this (1:1) = '*'
into table emp
(
empno        position(1:4)    integer external,
ename        position(6:15)    char,
job        position(17:25)    char,
mgr        position(27:30)    integer external,
sal        position(32:39)    decimal external,
comm        position(41:48)    decimal external,
deptno        position(50:51)    integer external,
hiredate    position(52:60)    integer external
)
2）数据文件（demo4.dat）内容如下：
*7781 CLARK      MA
XNAGER   7838 2572.50      -10  2512-NOV-95
*7839 KING       PR
XESIDENT      5500.00           2505-APR-93
*7934 MILLER     CL
XERK     7782  920.00           2508-MAY-90
*7566 JONES      MA
XNAGER   7839 3123.75           2517-JUL-95
*7499 ALLEN      SA
XLESMAN  7698 1600.00   300.00  25 3-JUN-94
*7654 MARTIN     SA
XLESMAN  7698 1312.50  1400.00  2521-DEC-85
*7658 CHAN       AN
XALYST   7566 3450.00           2516-FEB-94
*     CHEN       AN
XALYST   7566 3450.00           2516-FEB-94
*7658 CHIN     , AN
XALYST   7566 3450.00           2516-FEB-94
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo4.ctl
3）注意：discardfile与badfile的区别
（1）badfile：引起错误的记录被写入坏文件。
（2）discardfile：不能写入标准的记录写入丢弃文件
（3）continueif  this (1:1)='*'：表示如果每一行的第一个字符是*，那么表示新的记录的开始（此外还有：continueif next、continueif last）

5、例5：控制文件（control file）与数据文件（data file）分开，数据导入到多表中
1）控制文件（demo5.ctl）内容如下：
load data
infile 'demo5.dat'
badfile 'bad5.bad'
discardfile 'disc5.dsc'
replace

into table emp (
empno    position(1:4)    integer external,
ename    position(6:15)    char,
deptno    position(17:18)    char,
mgr    position(20:23)    integer external)

--1st project: proj has two columns, both not null
into table proj
when projno != '   ' (
emp    position(1:4)    integer external,
projno    position(25:27)    integer external)

-- 2nd project
into table proj
when projno != '   ' (
emp    position(1:4)    integer external,
projno    position(29:31)    integer external)

-- 3rd project
into table proj
when projno != '   ' (
emp    position(1:4)    integer external,
projno    position(33:35)    integer external)
2）数据文件（demo5.dat）内容如下：
1234 BAKER      10 9999 101 102 103
1234 JOKER      10 9999 777 888 999
2664 YOUNG      20 2893 425 abc 102
5321 OTOOLE     10 9999 321  55  40
2134 FARMER     20 4555 2A6 456
2414 LITTLE     20 5634 236 456  40
6542 LEE        10 4532 102 321  14
2849 EDDS       xx 4555     294  40
4532 PERKINS    10 9999  40
1244 HUNT       11 3452 665 133 456
123  DOOLITTLE  12 9940          132
1453 MACDONALD  25 5532     200
3）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo5.ctl
4）注意：when条件的使用（当有多个条件时，可以使用and操作符，但不能使用or操作符）

6、例6：控制文件（control file）与数据文件（data file）分开，使用：nullif XXX=blanks及direct=true
1）控制文件（demo6.ctl）内容如下：
load data
infile 'demo06.dat'
insert
into table emp
-- sorted indexes (emp_empno) 
(
empno    position(01:04)    integer external nullif empno=blanks,
ename    position(06:15)    char,
job    position(17:25) char,
mgr    position(27:30)    integer external nullif mgr=blanks,
sal    position(32:39)    decimal external nullif sal=blanks,
comm    position(41:48)    decimal external nullif comm=blanks,
deptno    position(50:51) integer external nullif deptno=blanks)
2）数据文件（demo6.dat）内容如下：
7781 clark      manager   7838 2572.50           10
7839 king       president      5500.00           10
7934 miller     clerk     7782  920.00           10
7566 jones      manager   7839 3123.75           20
7499 allen      salesman  7698 1600.00   300.00  30
7654 martin     salesman  7698 1312.50  1400.00  30
7658 chan       analyst   7566 3450.00           20
3）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo6.ctl direct=true
4）注意：nullif deptno=blanks，当取不到值时，就直接用空白来填充

7、例7：使用buit-in functions进行在数据导入时进行修改（UPPER、LOWER）
1）控制文件（demo7.ctl）内容如下：
load data
infile *
insert
into table funcdemo
(
 last_name     position(1:7)   char   "upper(:last_name)",
 first_name    position(8:15)   char   "lower(:first_name)"
)
begindata
poder  tanel
wilton jeremiah
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo7.ctl direct=true
3）注意：内置函数放在双引号内即可

8、例8：使用buit-in functions进行在数据导入时进行修改（DECODE）
1）控制文件（demo8.ctl）内容如下：
load data
infile *
truncate
into table decodemo
fields terminated by ','
optionally enclosed by '"'
(
 fld1, 
 fld2 "decode(:fld1, 'hello', 'goodbye', :fld1)"
)

begindata
hello,""
goodbye,""
this is a test,""
hello,""
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo8.ctl
3）注意：内置函数放在双引号内即可

9、例9：多个数据文件（要求：这两个数据文件的格式要一样的），导入到相应的不同的表中
1）控制文件（demo9.ctl）内容如下：
load data
infile 'demo9a.dat'
infile 'demo9b.dat'
append
into table denver_prj
when projno = '101'
 (projno  position(1:3) char,
  empno   position(4:8) integer external,
  projhrs position(9:10) integer external)

into table orlando_prj
when projno = '202'
 (projno  position(1:3) char,
  empno   position(4:8) integer external,
  projhrs position(9:10) integer external)

into table misc_prj
when projno != '101' and projno != '202'
 (projno  position(1:3) char,
  empno   position(4:8) integer external,
  projhrs position(9:10) integer external)
2）数据文件有多个
（1）demo9a.dat内容如下：
1011234515101543214010123456203032345610
（2）demo9b.dat内容如下：
202123451520223456104041234510
3）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo9.ctl
4）注意：when条件的使用（当有多个条件时，可以使用and操作符，但不能使用or操作符）

12、例12：使用constant、recnum、sysdate等
1）控制文件（demo12.ctl）内容如下：
options (errors=100, silent=(feedback))
load data
infile *
replace
into table dept
fields terminated by ','
optionally enclosed by '"'
(
  recno recnum, 
  deptno constant "xx",
  dname, 
  loc,
  tdate sysdate
)

begindata
research,"saratoga"
"accounting",cloveland
"art",salem
finance,"boston"
"sales",rochester
"int'l","san francisco"
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo12.ctl
3）注意：recnum表示使用行号，constant表示常量，sysdate是内置的日期函数

14、例14：处理带有“+”、“-”结尾的数字类型（to_number）
1）控制文件（demo14.ctl）内容如下：
load data
infile *
truncate
into table loadnums 
(
  col1 position(1:5),
  col2 position(7:16) "to_number(:col2,'99,999.99mi')"
)

begindata
abcde 1,234.99-
abcde 11,234.34+
abcde 45.23-
abcde 99,234.38-
abcde 23,234.23+
abcde 98,234.23+
2）运行sqlldr命令：sqlldr userid=hr/oracle@oracle11g control=demo14.ctl
3）注意：to_number(:col2,'99,999.99mi')表示将最后面的“+”、“-”在数据放库时，把它们放在最前面）

导出到TXT文件：
        1、用PL/SQL DEV打开CMD窗口。
        2、spool d:/output.txt;
        3、set heading off;   --去掉表头
        4、select * from usergroup;
        5、spool off;