Shell脚本实现批量更新客户号

clientno.sh脚本

#!/bin/bash

# 设置Oracle数据库的连接信息
username="user1"
password="passwd1"
hostname="198.97.1.2"
port="1521"
sid="orcl"

# 构建连接字符串
connection_string="${username}/${password}@//${hostname}:${port}/${sid}"

# 执行SQL查询
echo "开始创建临时表并导入数据"
sqlplus -s ${connection_string} <<EOF
	create table clientnotmp(row_id varchar2(50),new_client_no varchar2(50),old_client_no varchar2(50));
	create table tabletmp(table_name varchar2(50),item varchar2(50));
	insert into tabletmp  select table_name,'CLIENT_NO'
	from user_tab_columns
	where column_name ='CLIENT_NO' and global_stats = 'YES';
	commit;
	insert into tabletmp  select table_name,'TRANS_ACCOUNT'
	from user_tab_columns
	where column_name ='TRANS_ACCOUNT' and global_stats = 'YES';
	commit;
exit;
EOF
echo "创建临时表并导入数据结束"

echo "解析客户号变更文件开始"
# 文件名为clientno.txt
filename1="clientno.txt"

# 定义分隔符为逗号
IFS='|'

# 读取文件中的每一行
# 读取客户号变更文件并创建临时表导入 clientno.txt文件格式：序号|新客户号|旧客户号
while read -r field1 field2 field3 
do
    # 打印读取的字段
	# echo $field1,$field2,$field3
	# 拼接sql语句写到 clientno_1.txt文件中
    echo "insert into clientnotmp values ('"$field1"','"$field2"','"$field3"');" >> clientno_1.sql
done < $filename1
echo "commit;" >>clientno_1.sql

echo "解析客户号变更文件结束"

echo "导入客户号映射表开始"
#文件解析后导入
SQL_PATH=/home/oracle/clientno_1.sql
sqlplus -s ${connection_string} <<EOF
	@${SQL_PATH}
exit;
EOF
echo "导入客户号映射表结束"

echo "更新表拼接sql开始"
# 开始拼接sql
sqlplus -s ${connection_string} <<EOF
	create table temptable as select * from clientnotmp cross join tabletmp;
	spool /home/oracle/last.sql
	select 'update '||t1.table_name||' set client_no='''||t1.new_client_no||' where client_no='''|| t1.old_client_no||';'
	from temptable t1 where item='CLIENT_NO';
	select 'update '||t1.table_name||' set trans_account='''||t1.new_client_no||' where trans_account='''|| t1.old_client_no||';'
	from temptable t1 where item='TRANS_ACCOUNT';
	spool off
exit;
EOF
echo "更新表拼接sql结束"

clientno.txt文件内容：

1|666666|999999
2|888888|777777