一次汇总数据的shell

来源：互联网发布：jdk 8u20 windows x64 编辑：程序博客网时间：2024/06/03 23:42

要求自动以每天的日期为参数整理数据报表用到了shell 存储过程

#!/bin/bashTIMESTR='20110327'TM=`date +%s -d "$TIMESTR"`for (( i=1; i<=365; i++ ))do  TM=$(($TM - 1 * 24 * 3600))  TIMESTR=`date +%Y%m%d -d@"$TM"`  echo $TIMESTR  ./callday.sh $TIMESTR >>log.logdone

#!/bin/bashfor (( i=1 ; i<=46 ; i++ ))do  datg=`date -d "-$i months" +%Y%m`;  mond=${datg}"01";  echo $mond;  ./callmonth.sh $mond >>log.logdone

#!/bin/bash. $HOME/.bash_profileexport ORACLE_SID=bmzbbdbecho $1sqlplus esseapp/esseapp <<!exec org_busi_day_sum($1);exit!

create table KBK_ORG_DAY(  DAT      VARCHAR2(8) not null,  ORGCODE  VARCHAR2(10) not null,  BUSINO   VARCHAR2(6) not null,  BUSINAME VARCHAR2(20) not null,  NUM      NUMBER(10),  AMOUNT   NUMBER(10,2),  ADDATE   DATE)partition by range (dat)( partition  dat_201212 values less than ('20130101'), partition  dat_201301 values less than ('20130201'), partition  dat_201302 values less than ('20130301'), partition  dat_201303 values less than ('20130401'), partition  dat_201304 values less than ('20130501'), partition  dat_201305 values less than ('20130601'), partition  dat_201306 values less than ('20130701'), partition  dat_201307 values less than ('20130801'), partition  dat_201308 values less than ('20130901'), partition  dat_201309 values less than ('20131001'), partition  dat_201310 values less than ('20131101'), partition  dat_201311 values less than ('20131201'), partition  dat_201312 values less than ('20140101'))tablespace EAPP_DATA3  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );-- Create/Recreate primary, unique and foreign key constraints alter table KBK_ORG_DAY  add primary key (DAT, ORGCODE, BUSINO)  using index   tablespace EAPP_IDX1  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 64K    minextents 1    maxextents unlimited  );

create or replace procedure org_busi_moth_sum         (lastday in varchar2)         is         begin         delete from kbk_org_month where mt=substr(lastday,1,6);         insert into kbk_org_month          (select substr(lastday,1,6),orgcode,busino,businame, sum(num),   sum(to_number(amount))as amount,sysdate from kbk_org_day           where dat >=lastday and dat <= to_char(last_day(to_date(lastday,'yyyy-mm-dd') ),'yyyymmdd')         group by orgcode,busino,businame  );         commit;           end org_busi_moth_sum;     exec org_busi_day_sum('20130301')