Linux Shell脚本-实现同库不同用户查询包含指定字段的表并生成更新语句

#!/bin/bash
#1、查询用户包含bank_acc字段的表，生成sql语句写入input_file中
#2、查询用户包含trans_account字段的表，生成sql语句写入input_file中

# 输出文件
output_file="tg_sql_statements.sql"

# 清空输出文件
> "$output_file"

db_pub_link=" sqlplus -s pub/test@198.92.1.1:1521/orcl"
db_trans_link=" sqlplus -s trans/test@198.92.1.1:1521/orcl"

echo "查询公共库包含bank_acc字段表开始"
#查询用户包含bank_acc字段的表，生成sql语句写入input_file中
${db_pub_link} <<-! 2>&1 | tee -a $output_file
	set heading off;
	set wrap off;
	set pagesize 0;
	set linesize 5000;
	select 'create table  ' || lower(rpad(table_name||'_20250306', 45, ' ')) ||
	' as  select * from ' || lower(rpad(table_name, 35, ' ')) || ' where bank_acc like ''%-%'';'
	from user_tab_columns
	where column_name = 'BANK_ACC' and global_stats = 'YES' order by table_name;
	select 'update ' || lower(rpad(table_name, 35, ' ')) ||
	' set bank_acc = REPLACE(bank_acc, ''-'', '''')  where  bank_acc like ''%-%'';'
	from user_tab_columns
	where column_name = 'BANK_ACC' and global_stats = 'YES';
	exit
	!
echo "查询公共库包含bank_acc字段表结束"

echo "查询交易库包含bank_acc字段表开始"
#查询用户包含bank_acc字段的表，生成sql语句写入input_file中
${db_trans_link} <<-! 2>&1 | tee -a $output_file
	set heading off;
	set wrap off;
	set pagesize 0;
	set linesize 5000;
	select 'create table  ' || lower(rpad(table_name||'_20250306', 45, ' ')) ||
	' as  select * from ' || lower(rpad(table_name, 35, ' ')) || ' where  trans_account like ''%-%'';'
	from user_tab_columns
	where column_name = 'TRANS_ACCOUNT' and global_stats = 'YES' order by table_name;
	select 'update ' || lower(rpad(table_name, 35, ' ')) ||
	' set trans_account = REPLACE(trans_account, ''-'', '''')  where  trans_account like ''%-%'';'
	from user_tab_columns
	where column_name = 'TRANS_ACCOUNT' and global_stats = 'YES';
	exit
	!
echo "查询交易库包含bank_acc字段表结束"