记一次自动导出CVS配置始末

最新推荐文章于 2020-02-11 22:34:53 发布

箫季樣

最新推荐文章于 2020-02-11 22:34:53 发布

阅读量403

点赞数

文章标签： shell cvs

本文链接：https://blog.csdn.net/u010551713/article/details/73548259

版权

一开始的shell如下：

#!/bin/bash
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
$ORACLE_HOME/bin/sqlplus -S username/pwd  > /dev/null 2>&1  <<EOF
set echo off feedback off trimspool on linesize 3000;
set term off verify off feedback off pagesize 0;
spool /home/oracle/expdate/xcmx$(date +%Y%m%d).csv;
select site_code|| ',' ||
       '="'||ewbs_list_no || '"'|| ',' ||
       '="'||bill_code || '"'|| ',' ||
       out_piece|| ',' ||
       count(pallet_code) over(partition by site_code, ewbs_list_no, bill_code)|| ',' ||
       '="'||pallet_code || '"'|| ',' ||
       vol|| ',' ||
       weight|| ',' ||
       pieces|| ',' ||
       pallet_user_name|| ',' ||
       pallet_user_code 
  from (select site_code,
               ewbs_list_no,
               bill_code,
               out_piece,
               pallet_code,
               pallet_user_name,
               pallet_user_code,
               sum(son_vol) vol,
               sum(son_weight) weight,
               count(sub_bill_code) pieces
          from t_bas_pallet
         where create_time >=to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and create_time < to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
           and pallet_code is not null
           and is_deleted = 0
       and type in (1, 2)
         group by site_code,
                  ewbs_list_no,
                  bill_code,
                  out_piece,
                  pallet_code,
                  pallet_user_name,
                  pallet_user_code)
 order by site_code, ewbs_list_no, bill_code;
spool off;

spool /home/oracle/expdate/fjzymx$(date +%Y%m%d).csv;
select site_code|| ',' ||
       '="'||pallet_code || '"'|| ',' ||
       pallet_user_name|| ',' ||
       pallet_user_code|| ',' ||
       '="'||pallet_time || '"'|| ',' ||
       sort_man_name|| ',' ||
       sort_man_code|| ',' ||
       '="'||sort_time || '"'|| ',' ||
       trunc((sort_time-pallet_time)*24*60,2)||'分'  from(
select site_code,
       pallet_code,
       pallet_user_name,
       pallet_user_code,
       pallet_time,
       sort_man_name,
       sort_man_code,
       sort_time,
       row_number() over(partition by pallet_code order by pallet_time desc) rn
  from t_bas_sort_job
where create_time >=
       to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and create_time <
       to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
       and type in (1,2)
   and is_deleted = 0) where rn=1  order by site_code,pallet_user_code;
spool off;

spool /home/oracle/expdate/xcjt$(date +%Y%m%d).csv;
select site_code|| ',' ||
       pallet_user_code|| ',' ||
       pallet_user_name|| ',' ||
       count(pallet_code)|| ',' ||
       sum(directs)
  from (select site_code,
               pallet_user_code,
               pallet_user_name,
               pallet_code,
               count(distinct(next_site_name))  directs
          from fdm.t_bas_pallet
         where create_time >= add_months(trunc(sysdate, 'mm'),-1)
           and create_time < trunc(sysdate, 'mm') + 1 / 2
           and pallet_code is not null
           and is_deleted = 0
           and type in (1, 2)
           group by site_code,
               pallet_user_code,
               pallet_user_name,
               pallet_code)
 group by site_code, pallet_user_code, pallet_user_name order by site_code,pallet_user_code;
quit
EOF
rm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csv

--文件需要可执行权限

使用crontab自动执行发现只有最后的删除部分是正常执行的了的，但是SQL导出部份并没有执行，但是手动执行一切正常，该输出的文件都输出了，该删除的动作也做了。就很奇怪。在调试几次后发现每次自动执行的时间都非常块（正常手动执行需要5分钟左右），于是就在想是不是SQL段根本就没有执行或者说执行报错。

于是问公司的老司机，老司机给了我他的一段导出数据shell，如下：

#!/bin/ksh

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=nedb1
export ORACLE_BASE=/u01
export ORACLE_HOME=/u01/oracle/product/11.2.0.4/db
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.
export DIAG=/u01/diag/rdbms/nedb/nedb1/trace

LOGF=/home/oracle/exp_data/spool_`date +"%Y%m%d%H"`.log

sqlplus -s username/pwd <<EOF>>$LOGF
set linesize 3000;
set heading off;
set feedback off;
set term off;
set pagesize 0;
set trimspool on; 
spool /home/oracle/exp_data/anedbmay.csv
SELECT T.Ewbs_List_No/* as 交接单号*/|| ',' ||
       n.task_no /*as 任务号*/|| ',' ||
       d.ewb_no /*as 运单号*/|| ',' ||
       (select s2.site_name from hs_basic_site s2 where s2.site_id = T.SITE_ID) /*as 出库网点*/|| ',' ||
       (select s3.site_name from hs_basic_site s3 where s3.site_id = T.NEXT_SITE_ID) /*as 下一网点*/|| ',' ||
       (select s4.site_name from hs_basic_site s4 where s4.site_id = ewb.send_site_id) /*as 寄件网点*/|| ',' ||
       (select s5.site_name from hs_basic_site s5 where s5.site_id = s.first_center_site_id)/* as 寄件网点所属分拨*/|| ',' ||
       ewb.calc_weight /*as 结算重量*/|| ',' ||
       d.out_weight /*as 出站交接重量*/|| ',' ||
       d.in_weight /*as 进站交接重量*/ 
  FROM HS_EWBS_LIST T
  left join hs_ewbs_list_detail d on d.ewbs_list_no = t.ewbs_list_no
  left join hs_ewbs_list_no n on n.ewbs_list_no = t.ewbs_list_no
  left join hs_opt_ewb ewb on ewb.ewb_no = d.ewb_no
  left join hs_basic_site s on s.site_id = ewb.send_site_id
 WHERE n.task_no is not null and n.task_no <> '0'
  and t.created_time between to_date('2016-05-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and to_date('2016-06-02 23:59:59','yyyy-mm-dd hh24:mi:ss');
spool off
exit
EOF

经过对比，豁然顿悟。原来是shell的环境变量没有声明，导致sh执行的时候不认识sqlplus命令，于是修改shell，如下：

#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=anepridb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
sqlplus -S username/pwd  > /dev/null 2>&1  <<EOF
set echo off feedback off trimspool on linesize 3000;
set term off verify off feedback off pagesize 0;
spool /home/oracle/expdate/xcmx$(date +%Y%m%d).csv;
select site_code|| ',' ||
       '="'||ewbs_list_no || '"'|| ',' ||
       '="'||bill_code || '"'|| ',' ||
       out_piece|| ',' ||
       count(pallet_code) over(partition by site_code, ewbs_list_no, bill_code)|| ',' ||
       '="'||pallet_code || '"'|| ',' ||
       vol|| ',' ||
       weight|| ',' ||
       pieces|| ',' ||
       pallet_user_name|| ',' ||
       pallet_user_code 
  from (select site_code,
               ewbs_list_no,
               bill_code,
               out_piece,
               pallet_code,
               pallet_user_name,
               pallet_user_code,
               sum(son_vol) vol,
               sum(son_weight) weight,
               count(sub_bill_code) pieces
          from t_bas_pallet
         where create_time >=to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and create_time < to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
           and pallet_code is not null
           and is_deleted = 0
       and type in (1, 2)
         group by site_code,
                  ewbs_list_no,
                  bill_code,
                  out_piece,
                  pallet_code,
                  pallet_user_name,
                  pallet_user_code)
 order by site_code, ewbs_list_no, bill_code;
spool off;

spool /home/oracle/expdate/fjzymx$(date +%Y%m%d).csv;
select site_code|| ',' ||
       '="'||pallet_code || '"'|| ',' ||
       pallet_user_name|| ',' ||
       pallet_user_code|| ',' ||
       '="'||pallet_time || '"'|| ',' ||
       sort_man_name|| ',' ||
       sort_man_code|| ',' ||
       '="'||sort_time || '"'|| ',' ||
       trunc((sort_time-pallet_time)*24*60,2)||'分'  from(
select site_code,
       pallet_code,
       pallet_user_name,
       pallet_user_code,
       pallet_time,
       sort_man_name,
       sort_man_code,
       sort_time,
       row_number() over(partition by pallet_code order by pallet_time desc) rn
  from t_bas_sort_job
where create_time >=
       to_date(to_char(sysdate-1,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and create_time <
       to_date(to_char(sysdate,'yyyy-mm-dd')|| ' 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
       and type in (1,2)
   and is_deleted = 0) where rn=1  order by site_code,pallet_user_code;
spool off;

spool /home/oracle/expdate/zrjx$(date +%Y%m%d).csv;
select siteCode || ',' || sortUserName || ',' || sortUserCode || ',' ||sortTime || ',' || commissionNumber
  from (select temp2.siteCode,
               temp2.sortUserName,
               temp2.sortUserCode,
               temp2.sortTime,
               temp2.commissionNumber + NVL(TBPU.DIFFERENCE, 0) commissionNumber
          from (select tbpc.site_code         siteCode,
                       tbpc.SORT_USER_NAME    sortUserName,
                       tbpc.SORT_USER_CODE    sortUserCode,
                       tbpc.SORT_TIME         sortTime,
                       tbpc.COMMISSION_NUMBER commissionNumber
                  from t_bas_perf_count tbpc
                 where tbpc.SORT_TIME BETWEEN
                       TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd'),
                               'yyyy-mm-dd') AND
                       TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd'),
                               'yyyy-mm-dd')) temp2
          left join (select SITE_CODE,
                           SORT_USER_NAME,
                           SORT_USER_CODE,
                           trunc(sysdate - 1, 'dd') sort_time,
                           sum(MODIFICATION_NUMBER - COMMISSION_NUMBER) difference
                      from t_bas_performance
                     where SORT_TIME BETWEEN
                           TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd') ||
                                   ' 00:00:00',
                                   'yyyy-mm-dd hh24:mi:ss') AND
                           TO_DATE(to_char(sysdate - 1, 'yyyy-mm-dd') ||
                                   ' 23:59:59',
                                   'yyyy-mm-dd hh24:mi:ss')
                       and modification_flag = 2
                     group by site_code,
                              sort_user_code,
                              sort_user_name,
                              to_date(to_char(SORT_TIME, 'yyyy-MM-dd'),
                                      'yyyy-MM-dd')) tbpu
            on temp2.SITECODE = tbpu.site_code
           and temp2.sortUserCode = tbpu.SORT_USER_CODE
           and temp2.sortTime = tbpu.sort_time
         order by temp2.siteCode, temp2.sortUserCode) zrjx;
spool off;

spool /home/oracle/expdate/xcjt$(date +%Y%m%d).csv;
select site_code|| ',' ||
       pallet_user_code|| ',' ||
       pallet_user_name|| ',' ||
       count(pallet_code)|| ',' ||
       sum(directs)
  from (select site_code,
               pallet_user_code,
               pallet_user_name,
               pallet_code,
               count(distinct(next_site_name))  directs
          from fdm.t_bas_pallet
         where create_time >= add_months(trunc(sysdate, 'mm'),-1)
           and create_time < trunc(sysdate, 'mm') + 1 / 2
           and pallet_code is not null
           and is_deleted = 0
           and type in (1, 2)
           group by site_code,
               pallet_user_code,
               pallet_user_name,
               pallet_code)
 group by site_code, pallet_user_code, pallet_user_name order by site_code,pallet_user_code;
spool off;
quit
EOF
rm /home/oracle/expdate/xcmx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/fjzymx$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/xcjt$(date +%Y%m%d --date="-7 day").csv
rm /home/oracle/expdate/zrjx$(date +%Y%m%d --date="-7 day").csv

再次测试，一切正常了。
参考：http://blog.itpub.net/519536/viewspace-621602