#!/bin/sh  
DD=`date +%Y%m%d%H`
export NLS_LANG=AMERICAN_AMERICA.UTF8  
export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1  
export PATH=$ORACLE_HOME/bin:$PATH  
export ORACLE_SID=devdb  
export LOGFILE=/home/oracle/script/tf/devdb.log
if [ $# != 1 ]; then  
echo Usage: ./1.sh arg1  
echo Examples:  
echo 1. To run s_org_ext_x.sql, enter:  
echo 'oad.sh s_org_ext >s_org_ext_x.out'  
exit 0  
fi
cd /home/oracle/script/tf
sqlplus "/as sysdba" << EOF  
set time on  
set echo on  
set linesize 150  
col sum(prize_num) format 99999999  
col PRIZE_UNIT format a30  
col PHONE format a20  
col award_time format a30  
spool ${LOGFILE}  
@$1  
spool off  
exit  
EOF
#EMAIL  
. /home/oracle/.muttrc  
cat ${LOGFILE} | mutt -s "devdb_again" mailaddr1 mailaddr2 mailaddr3
award.sql  

统计所有奖品中奖数量----------  
SELECT SUM(prize_num),prize_unit  
FROM devdb.tbl_award  
GROUP BY prize_unit;

--统计所有实物中奖----------  
SELECT ar.sky_id ,ar.phone , ar.lottery_prize_id ,ar.prize_num , ar.prize_unit , ar.award_time  
FROM devdb.tbl_award ar , devdb.tbl_prize lp  
WHERE ar.lottery_prize_id = lp.lottery_prize_id  
AND lp.prize_type = 1 ;