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
)
 

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页