#!/bin/bash
创新互联建站坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站设计、成都做网站、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的云冈网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
#先写一个简单的脚本，后续使用脚本调用存储过程
#by：亚信-张颜
export ORACLE_HOME=/opt/oracle/app/oracle_base/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
#DATE_STR保存了从数据库中返回的三个值：1小时之前的年月，12小时前的时间串，以及当前时间串
DATE_STR=(`sqlplus -s 用户名/密码 < set heading off set feedback off set pagesize 0 set verify off set echo off select to_char(TRUNC(SYSDATE - 1 / 24, 'HH'), 'YYYYMM'),TO_CHAR(TRUNC(SYSDATE - 1 / 2, 'HH'), 'YYYYMMDDHH24MISS'), TO_CHAR(TRUNC(SYSDATE, 'HH'), 'YYYYMMDDHH24MISS') from dual; exit; eof`) echo "`date` 开始校验程控退订数据......" echo "`date` 开始提取程控功能营业送开通表退订数据" #通过监控营业送开通工单历史表，查询程控功能退订的数据，当前设定为12个小时运行1次 # for REGION_ID in {891..897} do sqlplus -s 用户名/密码 < set heading off set feedback off set pagesize 0 set verify off set echo off insert into zyan_jiankongchengkong select bill_id, 'MBELL', create_date, sysdate from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]} where action_id = 5 and OLD_PS_PARAM like '%MBELL=1%' and (PS_PARAM is null or PS_PARAM not like '%MBELL=1%') and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS') and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS'); commit; insert into zyan_jiankongchengkong select bill_id, 'CFNRCF', create_date, sysdate from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]} where action_id = 5 and OLD_PS_PARAM like '%CFNRCF=1%' and (PS_PARAM is null or PS_PARAM not like '%CFNRCF=1%') and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS') and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS'); commit; exit; eof done #校验程控功能订购在退订功能时是否存在订购关系，如果存在则表示是异常，需要进一步核查: echo "`date` 开始校验程控退订记录营业侧订购关系:" for REGION_ID in {891..897} do sqlplus -s 用户名/密码 < set heading off set feedback off set pagesize 0 set verify off set echo off delete from zyan_jiankongchengkong a where create_date <> (select max(create_date) from zyan_jiankongchengkong b where a.bill_id = b.bill_id and a.func_name = b.func_name) and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS'); commit; delete from zyan_jiankongchengkong a where not exists (select 1 from so.ins_user_${REGION_ID} b, so.ins_prod_${REGION_ID} c, so.ins_off_ins_user_${REGION_ID} f where a.bill_id = b.bill_id and c.prod_id in (121030512001, 121030512002) and b.user_id = c.user_id and c.user_id = f.user_id and c.offer_inst_id = f.offer_inst_id and b.region_id=f.region_id and f.expire_date > sysdate and c.expire_date > sysdate and c.effective_date < a.create_date and c.create_date and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id) and a.func_name = 'MBELL' and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS'); commit; delete from zyan_jiankongchengkong a where not exists (select 1 from so.ins_user_${REGION_ID} b, so.ins_prod_${REGION_ID} c, so.ins_off_ins_user_${REGION_ID} f where a.bill_id = b.bill_id and c.prod_id in (121030505101) and b.user_id = c.user_id and c.user_id = f.user_id and c.offer_inst_id = f.offer_inst_id and b.region_id=f.region_id and f.expire_date > sysdate and c.expire_date > sysdate and c.effective_date < a.create_date and c.create_date and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id) and a.func_name = 'CFNRCF' and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS'); commit; exit; eof done echo "`date` 本次校验完成！" echo ""
网站栏目：shell脚本:数据库业务监控
路径分享：http://bjjierui.cn/article/pjseoi.html
