oracle - 如何从文本文件将数据加载到 oracle 表并使用 shell 脚本检查记录是否已存在？

以最难的方式解决问题

$ cat test.dat
id , jobname , started, ended , time
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10

在甲骨文中

SQL>  create table t ( id number, jobname varchar2(1) , started number, ended number, elapsed number ) ;

Table created.

SQL> insert into t values ( 1 , 'A' , 1130 , 1200 , 30 ) ;

1 row created.

SQL> insert into t values ( 2, 'B' , 1120 , 1130 , 10 ) ;

1 row created.

SQL> select * from t ;

        ID J    STARTED      ENDED    ELAPSED
---------- - ---------- ---------- ----------
         1 A       1130       1200         30
         2 B       1120       1130         10

在这种情况下，我们的程序需要更新 ID 为 2 的记录并插入 ID 为 3 的记录。这只是一个基本的 shell 程序，但到目前为止，这是处理这个问题的最糟糕的方法。使用 oracle sql 加载程序驱动程序的外部表会更好、更容易和更快。

代码

#/bin/bash 

records=$(cat /home/ftpcpl/test.dat | tail -n +2 | wc -l)
echo "Number of records in file: $records" 

record_exists ()
{

record_id=$1

counter=`sqlplus -S "/ as sysdba" << eof
whenever sqlerror exit 2;
set echo off verify off head off feed off
select count(*) from t where t.id = ${record_id} ;
eof`

if [[ $? -eq 2 ]]; then exit 2; fi 

export counter=$(echo ${counter} | tr -d '\n')

}

UpdateRecord ()
{

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
update t set jobname = '${jb}' , 
   started =  ${st}  , 
   ended   =  ${en}  , 
   elapsed =  ${ti}  
where id = ${id} ;
commit;
eof

if [[ $? -eq 2 ]]; then exit 2; fi 

}

InsertRecord ()
{

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
insert into t values ( ${id} , '${jb}' , ${st}  , ${en}  , ${ti} );
commit;
eof

if [[ $? -eq 2 ]]; then exit 2; fi 

}

logfile=test.log 

tail -n +2 /home/ftpcpl/test.dat | 
while read -r line
do
  echo $line    
  export id=$(echo $line | awk -F ',' '{print $1}' | xargs echo -n )
  export jb=$(echo $line | awk -F ',' '{print $2}' | xargs echo -n )
  export st=$(echo $line | awk -F ',' '{print $3}' | xargs echo -n )
  export en=$(echo $line | awk -F ',' '{print $4}' | xargs echo -n )
  export ti=$(echo $line | awk -F ',' '{print $5}' | xargs echo -n )
 
  record_exists ${id}
  
  if [[ ${counter} -eq 1 ]];
  then
    UpdateRecord 
  else 
    InsertRecord 
  fi 
done

演示程序

./test.sh
Number of records in file: 3
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10
[ftpcpl@scglvdoracd0006 ~]$ cat test.log

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:34 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>   2    3    4    5
1 row updated.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:36 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>   2    3    4    5
1 row updated.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:38 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>
1 row created.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

最终记录

SQL> select * from t ;

        ID J    STARTED      ENDED    ELAPSED
---------- - ---------- ---------- ----------
         1 A       1130       1200         30
         2 A       1120       1130         10
         3 C       1120       1130         10

oracle - 如何从文本文件将数据加载到 oracle 表并使用 shell 脚本检查记录是否已存在？

问题描述

解决方案

推荐阅读