Linux Shell脚本-实现查询指定用户的全库表包含某些字段并且将有数据的导出

#!/bin/bash
#1、查询用户包含bank_acc字段的表，生成sql语句写入input_file中
#2、查询input_file中每个包含bank_acc表，哪些是有数据的

#输入文件
input_file="fx_sql_statements.sql"

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

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

db_link = sqlplus -s test/test@198.1.2.2:1521/orcl

echo "查询包含bank_acc字段表开始"
#查询用户包含bank_acc字段的表，生成sql语句写入input_file中
${db_link} <<-! 2>&1 | tee -a $input_file
	SELECT 'select '''||table_name||''' from ' ||owner||'.'||table_name ||' where '||COLUMN_NAME||' like ''%-%'';' FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE 'BANK_ACC' and owner in ('USER1','USER2','USER3') 
	and (table_name not like '%202%') and ( table_name not like '%SJQY%') and ( table_name not like '%_OLD') order by table_name;
	exit
	!
echo "查询包含bank_acc字段表结束"

echo "查询数据结果开始"

#逐行读取输入文件
while IFS= read -r sql
do
    # 执行SQL语句并检查是否有输出
    result=$(${db_link} <<EOF
    SET HEADING OFF
    SET FEEDBACK OFF
    SET PAGESIZE 0
    $sql
    EXIT
EOF
    )

    # 如果查询结果不为空，则标记并写入输出文件
    if [ -n "$result" ]; then
        echo "-- HAS DATA: $sql" >> "$output_file"
    else
        echo "-- NO DATA: $sql" >> "$output_file"
    fi

done < "$input_file"

echo "查询数据结果结束"
echo "SQL statements have been processed and marked in $output_file"