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

1.用触发器和序列实现

CREATESEQUENCEu.seq_questionno
STARTWITH0
MAXVALUE999999999999999999999999999
MINVALUE0
NOCYCLE
NOCACHE
NOORDER;

CREATEORREPLACETRIGGERu.tr1
BEFOREINSERT
ONu.t1
REFERENCINGOLDASOLDNEWASNEW
FOREACHROW
BEGIN
SELECTc1.NEXTVAL
INTO:NEW.c1
FROMDUAL;
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
)