【shell】Oracle和MySQL加工数据脚本模板

实现思路与步骤

1. 在Oracle完成底层数据加工
2. 数据导出生成txt文件
3. 将文件导入MySQL
4. 在MySQL上进行简单处理

#!/bin/sh
source ~/.bashrc
source ~/.bash_profile

#数据账期由参数控制 acct_date1如202111200937;
acct_date1=$1
acct_date=${acct_date1:0:8}
start_date=`date -d "$acct_date -30 days" +%Y%m%d%H%M`
end_date0=$acct_date"2330"
part_mi=$(date -d ${start_date:0:8} +%m)
lastmonth_date=`date -d "$acct_date 1 month ago" +%Y%m%d`

export_dir=/data/ossfs
export_file1=user_d_${acct_date1}.txt

echo "开始执行-----"
echo `date +%Y%m%d%H%M%S` $acct_date
echo "1.在Oracle完成底层数据加工"
expsql11="
truncate table user_d_1;
commit;

insert into user_d_1
select * from table1 where name='张三'
commit;
"

sqlplus -S 数据库账户名/密码@132.**.**.**:15**/dtcent<<EOF
set trimspool on
set linesize 2000
set pagesize 1000
set newpage none
set heading off
set term off
set numwidth 11
set feedback off;
$expsql11
quit 
EOF

hoursec=${acct_date1:8:4}
echo $hoursec
echo $hoursec = 2330
if [[ $hoursec = 2330 ]];
then
echo "特殊处理过程-----"
fi

echo "数据导出生成txt文件-----"

expsql2="
select * from user_d_1;
"
sqlplus -S 用户名/密码@132.**.**.**:15**/dtcent<<EOF
set trimspool on
set linesize 2000
set pagesize 1000
set newpage none
set heading off
set term off
set numwidth 11
set feedback off;
spool $export_dir/$export_file1
$expsql2
spool off
spool $export_dir/$export_filen
$expsqln
spool off
quit 
EOF

echo "生成ctl文件-----"
sqlldr 用户名/密码@132.**.**.**:15**/dtcent control=/data/ossfs/user_d_1_tmp.ctl

echo "将ctl文件数据导入tmp表中-----"
cat > /data/ossfs/user_d_1_tmp.ctl << EOF
OPTIONS (skip=0)
load data
infile '$export_dir/$export_file1'
append into table oracle用户.user_d_1
fields terminated by ","        
(P_DATE,
CITY_CODE
)
EOF

echo `date +%Y%m%d%H%M%S` `cat $export_dir/$export_file1 | wc -l` records exported sucessfullly user_d_1

echo "3.将文件导入MySQL"
cn1=`cat $export_dir/$export_file1 | wc -l`
cn2=`cat $export_dir/$export_file2 | wc -l`

echo user_d_1 $cn1
echo user_d_2 $cn2

if [ $cn1 -gt 1000 -a $cn2 -gt 100 ];
then
/usr/sbin/mysql -h **.***.**.*** -u prodomdb -用户名 -P 3306 -e "delete from user_d_tmp where p_date=$acct_date1 ;"
/usr/sbin/mysql -h **.***.**.*** -u prodomdb -用户名 -P 3306 -e "load data local infile '$export_dir/$export_file1' into table user_d_tmp fields terminated by ',';"

mv $export_dir/$export_file1 $export_dir/data_his/

echo "4.在MySQL上进行简单处理"
/usr/sbin/mysql -h **.***.**.*** -u prodomdb -用户名 -P 3306 -e "update user_d_tmp set name = '李四' where p_date=$acct_date1 ;"