通过定时任务在数据库中生成不同资源的CSV文件接口

需求：两个服务器之间，A服务器上存在ORACLE数据库，需要把此数据库中表中的不同资源通过定时任务生成CSV文件，同步到另一台服务器B中。

 

分析：首先在A服务器上建立目录,如：/wjjk_oracle/export_script/space_res用于存放空间资源脚本，在此目录下分别新增一个xxx.sql脚本（用于在A服务器上生成csv文件），新增一个xxx.sh脚本（用于取到该csv文件并上传到B服务器上）。

另外A服务器上建立目录,如：：/wjjk_oracle/export_files/space_res_files 用于存放生成的csv文件

具体代码如下：

bts.sql：

conn &1/&2
set head off 
set headsep off 
set newp none 
set linesize 1000
set pagesize 10000 
set sqlblanklines OFF 
set trimspool ON 
set termout off 
set feedback off 
set verify off
column dat1 new_value filename;
select to_char(sysdate,'yyyymmdd') dat1 from dual;
spool /wjjk_oracle/export_files/datang_to_dw_files/space_res/&&filename._bts.csv
prompt OMC中网元名称,中文名称,所属BSC,移动通信标识,传输类型,基站应急类型,VIP基站,所属机房,蜂窝类型,基站编号,设备型号,故障受理单位,代维公司,网元状态,设备供应商,软件版本信息,开通时间;
select a.name||','||
       a.name||','||      
       (SELECT de.name
          FROM device de
         where de.device_id = b.belong_net_cell
           and de.status <> 'D')||','||
       ''||','||
        ''||','||
       ''||','||
       ''||','||
       (SELECT rm.name
          FROM room rm
         where rm.room_id = a.room_id
           and rm.status = 'U')||','||
       b.T_HONEYCOMB_TYPE||','||
       a.code||','||
       (select ff.name
          from facility_model ff
         WHERE ff.facility_model_id = a.facility_model_id)||','||
       ''||','||
       ''||','||
       decode(a.status, 'U','现网','F','空载','S','停用','')||','||
       (select f.name
          from factory f
         WHERE f.factory_id = a.factory_id
           and rownum = 1)||','||
       a.soft_version||','||
       a.using_date 
  FROM device a, base_station b
 WHERE a.res_spec_id = 201701
   and a.device_id = b.base_station_id
   and a.status <> 'D';
spool off
exit

nodeb.sql:

conn &1/&2
set head off 
set headsep off 
set newp none 
set linesize 1000
set pagesize 10000 
set sqlblanklines OFF 
set trimspool ON 
set termout off 
set feedback off 
set verify off
column dat1 new_value filename;
select to_char(sysdate,'yyyymmdd') dat1 from dual;
spool /wjjk_oracle/export_files/datang_to_dw_files/space_res/&&filename._nodeb.csv
prompt OMC中网元名称,中文名称,所属RNC,移动通信标识,传输类型,频段,基站应急类型,VIP基站,所属机房,蜂窝类型,基站编号,设备型号,故障受理单位,代维公司,网元状态,设备供应商,软件版本信息,开通时间;
select a.name||','||
       a.name||','||      
       (SELECT de.name
          FROM device de
         where de.device_id = b.belong_net_cell
           and de.status <> 'D')||','||
       ''||','||
        ''||','||
        b.spectrum||','||
       ''||','||
       ''||','||
       (SELECT rm.name
          FROM room rm
         where rm.room_id = a.room_id
           and rm.status = 'U')||','||
       b.T_HONEYCOMB_TYPE||','||
       a.code||','||
       (select ff.name
          from facility_model ff
         WHERE ff.facility_model_id = a.facility_model_id)||','||
       ''||','||
       ''||','||
       decode(a.status, 'U','现网','F','空载','S','停用','')||','||
       (select f.name
          from factory f
         WHERE f.factory_id = a.factory_id
           and rownum = 1)||','||
       a.soft_version||','||
       a.using_date 
  FROM device a, base_station b
 WHERE a.res_spec_id = 201702
   and a.device_id = b.base_station_id
   and a.status <> 'D';
spool off
exit

最后在建立一个bts.sh:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_SID=rms1
ouser=rmssc
opassword=rms4sc
rip=133.63.202.XX 
ruser=fxxxxx
rpassword=xxx
#BTS START
cd /wjjk_oracle/export_script/datang_to_dw/space_res
nohup $ORACLE_HOME/bin/sqlplus rms/rms4sc @bts.sql $ouser $opassword
curdate="`date +%Y%m%d`"
filename="$curdate""_bts.csv"
cd /wjjk_oracle/export_files/datang_to_dw_files/space_res
echo $filename
ftp -v -n $rip << EOF
user $ruser $rpassword
binary
hash
#$strrpath
cd datang_to_dw/space_res
prompt
mput $filename
bye
EOF
#BTS end

#NODEB START
cd /wjjk_oracle/export_script/datang_to_dw/space_res
nohup $ORACLE_HOME/bin/sqlplus rms/rms4sc @nodeb.sql $ouser $opassword
curdate="`date +%Y%m%d`"
filename="$curdate""_nodeb.csv"
cd /wjjk_oracle/export_files/datang_to_dw_files/space_res
echo $filename
ftp -v -n $rip << EOF
user $ruser $rpassword
binary
hash
#$strrpath
cd datang_to_dw/space_res
prompt
mput $filename
bye
EOF
#NODEB END

最后建立一个定时任务，crontab -e。

00 16 * * * /wjjk_oracle/export_script/datang_to_dw/space_res/bts.sh >> /wjjk_oracle/export_script/datang_to_dw/space_res/bts.log 2>&1