如何在CTL模板中获取Oracle序列当前值以关联外键？

问题分析 #

你之前的尝试失败，核心原因是序列的CURRVAL是会话级别的：如果表A和B/C的加载不在同一个SQL*Loader会话中，CURRVAL无法获取到A表生成的主键值；另外直接在CTL中写独立的SELECT语句不符合语法规范。

以下是几种可行的解决方案：

方案1：单CTL文件按顺序加载（同一会话复用CURRVAL） #

将三个CSV的加载逻辑放在同一个CTL文件中，确保先加载表A，再加载B、C，这样同一会话内可以直接用CURRVAL获取序列当前值。

示例CTL代码：

LOAD DATA
-- 指定三个CSV文件
INFILE 'a_data.csv'
INFILE 'b_data.csv'
INFILE 'c_data.csv'
APPEND

-- 先加载表A，生成主键
INTO TABLE SCHEMEA.TABLE_A
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    TAB_REC_ID "SCHEMEA.SEQ_TAB_REC_ID.NEXTVAL",
    -- 表A的其他列，比如COL1, COL2...
    COL1,
    COL2
)

-- 加载表B，关联A的主键
INTO TABLE SCHEMEA.TABLE_B
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    -- 表B的其他列
    B_COL1,
    B_COL2,
    -- 引用同会话的序列当前值作为外键
    TAB_REC_ID "SCHEMEA.SEQ_TAB_REC_ID.CURRVAL"
)

-- 加载表C，关联A的主键
INTO TABLE SCHEMEA.TABLE_C
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    -- 表C的其他列
    C_COL1,
    C_COL2,
    TAB_REC_ID "SCHEMEA.SEQ_TAB_REC_ID.CURRVAL"
)

方案2：分CTL加载，通过外部变量传递序列值 #

如果必须分开三个CTL文件加载，可通过脚本先加载表A，再获取序列CURRVAL并传递给B/C的加载命令。

步骤1：编写表A的CTL（load_a.ctl） #

LOAD DATA
INFILE 'a_data.csv'
APPEND
INTO TABLE SCHEMEA.TABLE_A
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    TAB_REC_ID "SCHEMEA.SEQ_TAB_REC_ID.NEXTVAL",
    COL1,
    COL2
)

步骤2：编写Shell脚本执行加载并传递变量 #

# 加载表A
sqlldr userid=your_user/your_pwd@your_db control=load_a.ctl

# 获取序列当前值（同一会话执行）
SEQ_VAL=$(sqlplus -s your_user/your_pwd@your_db <<EOF
SET FEEDBACK OFF
SELECT SCHEMEA.SEQ_TAB_REC_ID.CURRVAL FROM DUAL;
EXIT;
EOF
)

# 加载表B，传递序列值
sqlldr userid=your_user/your_pwd@your_db control=load_b.ctl SEQ_VAL=$SEQ_VAL

# 加载表C，传递序列值
sqlldr userid=your_user/your_pwd@your_db control=load_c.ctl SEQ_VAL=$SEQ_VAL

步骤3：表B的CTL（load_b.ctl） #

LOAD DATA
INFILE 'b_data.csv'
APPEND
INTO TABLE SCHEMEA.TABLE_B
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    B_COL1,
    B_COL2,
    -- 引用外部传入的变量作为外键
    TAB_REC_ID ":SEQ_VAL"
)

表C的CTL与表B类似，替换对应表名即可。

方案3：临时表+存储过程批量处理 #

如果数据需要复杂关联校验，可先将CSV加载到临时表，再通过存储过程统一处理序列关联。

步骤1：创建临时表 #

CREATE TABLE SCHEMEA.STG_A AS SELECT * FROM SCHEMEA.TABLE_A WHERE 1=0;
CREATE TABLE SCHEMEA.STG_B AS SELECT * FROM SCHEMEA.TABLE_B WHERE 1=0;
CREATE TABLE SCHEMEA.STG_C AS SELECT * FROM SCHEMEA.TABLE_C WHERE 1=0;

步骤2：用SQL*Loader将CSV加载到临时表 #

编写三个CTL分别加载STG_A、STG_B、STG_C，无需处理序列。

步骤3：创建存储过程处理关联插入 #

CREATE OR REPLACE PROCEDURE SCHEMEA.LOAD_TAB_DATA
AS
    v_rec_id NUMBER;
BEGIN
    -- 遍历临时表A的记录，生成主键并插入正式表
    FOR a_rec IN (SELECT * FROM SCHEMEA.STG_A) LOOP
        SELECT SCHEMEA.SEQ_TAB_REC_ID.NEXTVAL INTO v_rec_id FROM DUAL;
        
        -- 插入表A
        INSERT INTO SCHEMEA.TABLE_A (TAB_REC_ID, COL1, COL2)
        VALUES (v_rec_id, a_rec.COL1, a_rec.COL2);
        
        -- 插入表B（根据实际关联条件匹配，比如用业务字段关联）
        INSERT INTO SCHEMEA.TABLE_B (TAB_REC_ID, B_COL1, B_COL2)
        SELECT v_rec_id, b_rec.B_COL1, b_rec.B_COL2
        FROM SCHEMEA.STG_B b_rec
        WHERE b_rec.BUSINESS_KEY = a_rec.BUSINESS_KEY; -- 替换为实际关联字段
        
        -- 插入表C
        INSERT INTO SCHEMEA.TABLE_C (TAB_REC_ID, C_COL1, C_COL2)
        SELECT v_rec_id, c_rec.C_COL1, c_rec.C_COL2
        FROM SCHEMEA.STG_C c_rec
        WHERE c_rec.BUSINESS_KEY = a_rec.BUSINESS_KEY;
    END LOOP;
    
    COMMIT;
    -- 清空临时表（可选）
    TRUNCATE TABLE SCHEMEA.STG_A;
    TRUNCATE TABLE SCHEMEA.STG_B;
    TRUNCATE TABLE SCHEMEA.STG_C;
END;
/

步骤4：执行存储过程 #

EXEC SCHEMEA.LOAD_TAB_DATA;

内容的提问来源于stack exchange，提问作者Sajna Sheeja