如何通过shell脚本将文本文件数据加载到Oracle表并实现存在更新不存在插入

首先默认你判断记录是否存在的唯一标识为id字段，如果你是按id+jobname等多字段组合判断存在性，修改下文MERGE语句的ON条件即可。以下提供两种适用不同数据量的实现方式：

方案1：小数据量（万行以内），轻量快速实现 #

无需额外建表，直接通过shell预处理CSV后拼接MERGE语句执行即可：

• 第一步：预处理CSV文件，去掉表头、清理多余空格、给字符串类型字段加单引号：

# 替换your_data.csv为你的实际CSV文件名
awk -F',' 'NR>1 {gsub(/^[ \t]+|[ \t]+$/, "", $0); gsub(/[ \t]*,[ \t]*/, ",", $0); print $0}' your_data.csv | awk -F',' '{print $1",'\''"$2"'\'',"$3","$4","$5}' > temp_data.tmp

• 第二步：在你的shell脚本中调用sqlplus执行MERGE（Oracle原生Upsert语法，天然满足存在更新、不存在插入的需求）：

# 替换ORACLE_USER、ORACLE_PWD、ORACLE_SID为你实际的数据库连接信息
sqlplus -s ${ORACLE_USER}/${ORACLE_PWD}@${ORACLE_SID} <<EOF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
MERGE INTO 你的目标表名 t
USING (
  `awk -F',' '{print "SELECT "$1" id, '"'"'"$2"'"'"' jobname, "$3" started, "$4" ended, "$5" run_time FROM DUAL " (NR>1?"UNION ALL ":"")}' temp_data.tmp`
) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.jobname = s.jobname, t.started = s.started, t.ended = s.ended, t.time = s.run_time
WHEN NOT MATCHED THEN
  INSERT (id, jobname, started, ended, time)
  VALUES (s.id, s.jobname, s.started, s.ended, s.run_time);
COMMIT;
EXIT;
EOF

方案2：大数据量（10万行以上），高性能实现 #

通过Oracle外部表直接映射CSV文件，不需要提前加载数据到库，MERGE执行效率更高：

• 第一步：先在Oracle侧创建目录对象（需DBA权限，指向CSV文件所在的服务器路径），再创建映射CSV的外部表：

-- 替换/path/to/csv/folder为你CSV文件存放的实际路径，替换你的Oracle用户名
CREATE OR REPLACE DIRECTORY CSV_DIR AS '/path/to/csv/folder';
GRANT READ, WRITE ON DIRECTORY CSV_DIR TO 你的Oracle用户名;

-- 创建外部表，字段和CSV列一一对应
CREATE TABLE job_csv_ext (
  id NUMBER,
  jobname VARCHAR2(100),
  started NUMBER,
  ended NUMBER,
  run_time NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY CSV_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1 -- 跳过CSV第一行表头
    FIELDS TERMINATED BY ','
    TRIM ALL SPACES -- 自动清理字段前后的空格
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('your_data.csv') -- 替换为你的实际CSV文件名
)
REJECT LIMIT UNLIMITED;

• 第二步：直接调用MERGE语句执行数据同步，你可以把下方SQL放到shell脚本的sqlplus执行块中即可：

MERGE INTO 你的目标表名 t
USING job_csv_ext s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.jobname = s.jobname, t.started = s.started, t.ended = s.ended, t.time = s.run_time
WHEN NOT MATCHED THEN
  INSERT (id, jobname, started, ended, time)
  VALUES (s.id, s.jobname, s.started, s.ended, s.run_time);
COMMIT;

注意：如果你的CSV存在字段包含逗号、特殊字符、空值等场景，需要对应调整外部表的解析参数或者预处理逻辑，避免字段解析错位。

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