Oracle sql loader 导数据时添加序号的三种方法

1.用触发器和序列实现

CREATE  SEQUENCE u.seq_questionno
START  WITH   0
MAXVALUE  999999999999999999999999999
MINVALUE  0
NOCYCLE
NOCACHE
NOORDER;

CREATE   OR   REPLACE   TRIGGER  u.tr1
   BEFORE  INSERT
    ON  u.t1
   REFERENCING OLD  AS  OLD NEW  AS  NEW
    FOR  EACH ROW
BEGIN
    SELECT  c1.NEXTVAL
      INTO  :NEW.c1
      FROM  DUAL;
END ;

 

控制文件:

LOAD DATA 
INFILE 'G:a.txt' 
BADFILE 'G:a.bad'
DISCARDFILE 'G:a.dsc'

INTO TABLE "T1"
APPEND
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
( 
    C2,
    C3, 
    C4,
    C5
    -- C1
)

2.直接在控制文件中加入序列

LOAD DATA 
INFILE 'G:a.txt' 
BADFILE 'G:a.bad'
DISCARDFILE 'G:a.dsc'

INTO TABLE "T1"
APPEND
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
( 
    C2,
    C3, 
    C4,
    C5,
    C1 "SEQ_QUESTIONNO.nextval"
)

3.不用序列,在控制文件中使用 RECNUM

LOAD DATA 
INFILE 'G:a.txt' 
BADFILE 'G:a.bad'
DISCARDFILE 'G:a.dsc'

INTO TABLE "T1"
APPEND
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(
    C1 RECNUM,
    C2,
    C3, 
    C4,
    C5
)