土狼的博客

--统计表建立

---------------------------------------------------------
create table TBL_CONVERT_PROC_STAT
(
  SCRIPT_NAME         VARCHAR2(255) not null,
  OPER_USER           VARCHAR2(15),
  LAST_TOTAL_COST     NUMBER,
  CUR_PROC_BEGIN_DATE DATE,
  CUR_PROC_END_DATE   DATE,
  CUR_TOTAL_COST      NUMBER,
  SCRIPT_GRP          NUMBER(3)
)
/

 --具体操作SHELL

alter table TBL_CONVERT_PROC_STAT
  add constraint I_TBL_CONVERT_PROC_STAT primary key (SCRIPT_NAME)
/

---------------------------------------------------------

#filename: run_sql.sh

if [ $ # -ne 5 ] ; then
  echo "用法: `basename $ 0` 脚本组ID 数据库TNS 用户名 密码 SQL脚本文件名"
  echo "范例: `basename $ 0` 1 gzcs1     ZG     ZG   test.sql"
  exit 1
fi

SCRIPT_GRP=$ 1
SERVICE=$ 2
USERNAME=$ 3
PASSWORD=$ 4
FILE=$ 5

echo `date '+%Y-%m-%d'`" "`date '+%T'` "---> 脚本<" $ FILE" >开始... ..."

sqlplus -S $ {USERNAME}/$ {PASSWORD}@$ {SERVICE} <<END_SUITE
prompt --======================================;
Declare
 script_count number;
begin
  select count(*) into script_count from TBL_CONVERT_PROC_STAT
  where script_name = substr('$ FILE',(Instr('$ FILE', '/', -1) +1),length('$ FILE'));
  if (script_count = 0) then
    Insert /*+ NOLOGGING*/Into TBL_CONVERT_PROC_STAT(script_name,oper_user,cur_proc_begin_date,script_grp)
    Values(substr('$ FILE',(Instr('$ FILE', '/', -1) +1),length('$ FILE')),'YOURNAME',Sysdate,'$ SCRIPT_GRP');
  else
    update /*+ NOLOGGING*/ TBL_CONVERT_PROC_STAT
    set last_total_cost = cur_total_cost, cur_proc_begin_date = sysdate, cur_proc_end_date = '',
        script_grp='$ SCRIPT_GRP'
    where script_name = substr('$ FILE',(Instr('$ FILE', '/', -1) +1),length('$ FILE'));
  end if;
  Commit; 
end;
/
prompt --======================================;
alter session set sort_area_size=204800;
@$ FILE
prompt --======================================;
update /*+ NOLOGGING*/ TBL_CONVERT_PROC_STAT
set cur_proc_end_date = sysdate, cur_total_cost = (sysdate - cur_proc_begin_date)*86400,
script_grp='$ SCRIPT_GRP'
where script_name = substr('$ FILE',(Instr('$ FILE', '/', -1) +1),length('$ FILE'));
Commit;
prompt --======================================;
END_SUITE

echo `date '+%Y-%m-%d'`" "`date '+%T'` "---> 脚本<" $ FILE" >处理结束!"

---------------------------------------------------------