自動更新資料庫資料的shell

和以前自動ftp，load data的那個shell相比，沒有多大長進。只有兩點新東西

[@more@]

1）從資料庫檢索資料，賦值給shell變數（注意 -s 引數）

LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

            SET FEED OFF

            SET TERM OFF

            SET HEAD OFF

            SELECT count(*)

              FROM refresh_log

              WHERE user_name='$USERNAME'

              AND obj_name='$TABLE_NAME';

EOF`

2）截掉字串的最後一串。比如將 eden_flat_ord_cmpt_fr.sql 截為 eden_flat_ord_cmpt

>aaa='aa_bb_cc_dd_ee.sql'

>echo ${aaa%_*}

aa_bb_cc_dd

該shell位於~p482/script目錄下，呼叫~p482/sql目錄下的sql檔案更新表中的資料

shell指令碼：

#####################################################################

#

# Purpose: This script is to refresh tables, and log refresh

#              information both in table and OS file

#

# Usage: refresh_table FILE_NAME

#

# Global varialbles

# FILE_NAME: the file needed by refresh

# USERNAME: database login ID

# PASSWORD: password for database user

# DATABASE: service name of database

# TABLE_NAME: the table to be refreshed

# START_TIME: when refresh begins

# END_TIME: when refresh ends

# BEFORE_ROWS: rows before refresh

# AFTER_ROWS: rows after refresh

# REFRESH_SEQ: the refresh sequence number for the table

#LOG_FILE: the file to store log information

# LOG_ROWS: a mid_stage parameter

#

# by ******

# 2004-12-03

#

#####################################################################

 

#!/usr/bin/bash

 

rfsh_env()

{

START_TIME=`date "+%Y-%m-%d %H:%M:%S"`

LOG_FILE=refreshlog.log

USERNAME=******

PASSWORD=******

DATABASE=dbname

}

 

rfsh_refresh()

{

TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 3`

 

LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

            SET FEED OFF

            SET TERM OFF

            SET HEAD OFF

            SELECT count(*)

              FROM refresh_log

              WHERE user_name='$USERNAME'

              AND obj_name='$TABLE_NAME';

EOF`

 

if [ $LOG_ROWS -eq 0 ]; then

  REFRESH_SEQ=1

else

  REFRESH_SEQ=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

            SET FEED OFF

            SET TERM OFF

            SET HEAD OFF

            SELECT max(refresh_num+1)

              FROM refresh_log

              WHERE user_name='$USERNAME'

              AND obj_name='$TABLE_NAME';

EOF`

fi

 

BEFORE_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

        SET FEED OFF

        SET TERM OFF

        SET HEAD OFF

            select count(*) from $TABLE_NAME;

EOF`

 

START_TIME=`date "+%Y-%m-%d %H:%M:%S"`

 

sqlplus -s $USERNAME/$PASSWORD@$DATABASE>/dev/null<

  INSERT INTO refresh_log(user_name,obj_name,obj_type,refresh_num,starttime,rows_b4_refresh)

  VALUES('${USERNAME}','${TABLE_NAME}','T',${REFRESH_SEQ},sysdate,${BEFORE_ROWS} );

  COMMIT;

 

  spool /tmp/$TABLE_NAME.out

  @$FILE_NAME

  spool off

 

  UPDATE refresh_log

    SET endtime=sysdate

    WHERE obj_name='$TABLE_NAME'

    AND refresh_num=$REFRESH_SEQ;

  COMMIT;

 

  EXIT;

EOF

 

END_TIME=`date "+%Y-%m-%d %H:%M:%S"`

 

ERRORMSG=`cat /tmp/eden_flat_ord_cmpt.out|grep ORA-|grep -v ORA-00942|grep -v ORA-01418|head -n 1`

 

COUNT=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

            SET TERM OFF

            SET HEAD OFF

            SET FEED OFF

            SELECT count(*) FROM user_tables WHERE table_name=upper('$TABLE_NAME');

EOF`

 

if [ $COUNT -eq 1 ]; then

  REFRESH_STATUS='Y'

else

  REFRESH_STATUS='N'

fi

 

AFTER_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<

  SET FEED OFF

  SET TERM OFF

  SET HEAD OFF

  select count(*) from $TABLE_NAME;

EOF`

 

sqlplus -s $USERNAME/$PASSWORD@$DATABASE>/dev/null<

  UPDATE refresh_log

    SET status='$REFRESH_STATUS',

        total_time=(endtime-starttime)*3600*24,

            rows_after_refresh=$AFTER_ROWS,

            error='$ERRORMSG'

    WHERE obj_name='$TABLE_NAME'

    AND refresh_num=$REFRESH_SEQ;

  COMMIT;

 

  EXIT;

EOF

 

echo "owner: "$USERNAME>>$LOG_FILE

echo "table name: "$TABLE_NAME>>$LOG_FILE

echo "start time: "$START_TIME>>$LOG_FILE

echo "end time: "$END_TIME>>$LOG_FILE

echo "rows before refresh: "$BEFORE_ROWS>>$LOG_FILE

echo "rows after refresh: "$AFTER_ROWS>>$LOG_FILE

echo "refresh succeed(Y/N): "$REFRESH_STATUS>>$LOG_FILE

echo "error messages if any: "$ERRORMSG>>$LOG_FILE

echo " ">>$LOG_FILE

echo " ">>$LOG_FILE

}

 

 

#################################

## Main

#################################

 

if [ $# -lt 1 ]; then

  echo " "

  echo "Usage: refresh_table SQL_FILE_NAME"

  echo " "

  exit 1

fi

 

FILE_NAME=../sql/$1

 

if [ ! -e $FILE_NAME ]; then

  echo " "

  echo "File $FILE_NAME does NOT exist."

  echo "Please specify an existed SQL file."

  echo " "

  exit 1

fi

 

rfsh_env

 

rfsh_refresh $FILE_NAME