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

来源：互联网发布：小黄鸭淘客软件合法吗编辑：程序博客网时间：2024/05/18 00:15

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
)

0 0