如何通过Unix脚本/PLSQL将TXT文件数据导入SQL表

以下提供两种常用实现方式：

方案1：Unix脚本结合Oracle SQL*Loader导入 #

SQL*Loader是Oracle原生自带的批量导入工具，Unix环境安装Oracle客户端后即可直接使用，适合大数据量导入场景。

1. 新建SQL*Loader控制文件load.ctl，内容如下：

load data
-- 替换为你的实际文件路径
infile '$home/dir/subdir/input.txt'
-- 数据导入模式：append为追加，truncate为清空目标表后导入，按需修改
append into table 你的目标表名
fields terminated by ','
-- 处理行末尾空字段、中间空字段自动映射为NULL
trailing nullcols
(
col1,
col2,
col3 "trim(:col3)",
col4,
col5,
col6
)

2. 新建Unix执行脚本load.sh，内容如下：

#!/bin/bash
# 替换为你实际的Oracle环境变量路径
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

# 替换为实际数据库连接信息
DB_USER="数据库用户名"
DB_PASS="数据库密码"
DB_INSTANCE="数据库实例名"

# 执行导入
sqlldr userid=${DB_USER}/${DB_PASS}@${DB_INSTANCE} control=./load.ctl log=./load.log bad=./load.bad

# 输出结果
if [ $? -eq 0 ];then
    echo "导入完成，执行日志见load.log"
else
    echo "导入失败，错误数据见load.bad，执行日志见load.log"
fi

3. 执行导入：

# 给脚本加执行权限
chmod +x load.sh
# 运行脚本
./load.sh

方案2：PLSQL导入 #

该方案不需要额外客户端，只要你有PLSQL执行权限、且Oracle服务器可以访问到目标txt文件即可使用，适合需要做复杂数据校验转换的场景。

1. 用DBA权限创建目录对象并授权：

-- 替换为txt文件所在的服务器实际目录路径
CREATE OR REPLACE DIRECTORY INPUT_DIR AS '/home/your_user/dir/subdir';
-- 给执行导入的用户授予目录读权限
GRANT READ ON DIRECTORY INPUT_DIR TO 你的数据库用户名;

2. 执行PLSQL导入块：

DECLARE
    v_file UTL_FILE.FILE_TYPE;
    v_line VARCHAR2(1000);
    -- 字段变量，可根据实际表字段类型调整
    v_col1 VARCHAR2(10);
    v_col2 VARCHAR2(10);
    v_col3 VARCHAR2(20);
    v_col4 NUMBER;
    v_col5 NUMBER;
    v_col6 VARCHAR2(2);
    v_p1 NUMBER;
    v_p2 NUMBER;
    v_p3 NUMBER;
    v_p4 NUMBER;
    v_p5 NUMBER;
BEGIN
    -- 打开目标文件
    v_file := UTL_FILE.FOPEN('INPUT_DIR', 'input.txt', 'R');
    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_file, v_line);
            -- 拆分逗号分隔的字段
            v_p1 := INSTR(v_line, ',', 1, 1);
            v_p2 := INSTR(v_line, ',', 1, 2);
            v_p3 := INSTR(v_line, ',', 1, 3);
            v_p4 := INSTR(v_line, ',', 1, 4);
            v_p5 := INSTR(v_line, ',', 1, 5);
            
            v_col1 := TRIM(SUBSTR(v_line, 1, v_p1 - 1));
            v_col2 := TRIM(SUBSTR(v_line, v_p1 + 1, v_p2 - v_p1 - 1));
            v_col3 := TRIM(SUBSTR(v_line, v_p2 + 1, v_p3 - v_p2 - 1));
            v_col4 := TO_NUMBER(TRIM(SUBSTR(v_line, v_p3 + 1, v_p4 - v_p3 - 1)));
            v_col5 := TO_NUMBER(TRIM(SUBSTR(v_line, v_p4 + 1, v_p5 - v_p4 - 1)));
            v_col6 := TRIM(SUBSTR(v_line, v_p5 + 1));
            
            -- 插入目标表，空字符串自动转NULL
            INSERT INTO 你的目标表名(col1, col2, col3, col4, col5, col6)
            VALUES(v_col1, v_col2, NULLIF(v_col3,''), v_col4, v_col5, v_col6);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT;
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('行处理失败：'||v_line||'，错误：'||SQLERRM);
        END;
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('导入完成');
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        ROLLBACK;
        RAISE;
END;
/

注意事项 #

• 执行导入前需提前确认目标表已创建，字段类型、长度和文件数据匹配
• 数据量较大时优先选择SQL*Loader方案，性能远高于逐行插入的PLSQL方案

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