ppin.sh

本人编的第一个shell

#trap '' 1 2 3 5 7 9 15 13

#查看数据库中表是否存在
existstable()
{
sqlplus -s $username$PASS <<!|grep -v grep|grep -v EXISTS|grep exists|wc -l
select 'exists' from all_tables where table_name = upper('$tablename')
/
!
}

#取得用户输入的密码
getcon(){
stty -echo 
stty raw
conchar=`dd if=/dev/tty bs=1 count=1 2>/dev/null`
stty -raw
stty echo 
echo "$conchar"
}
getpass() {
     i=0;
     while
      [ "$p" = "
" -o "$i" -ge 16 ] && break
      p=`getcon`
      PASS=`echo $PASS$p`
     echo "*\c" > /dev/tty
     do
      i=`expr $i + 1`
     done

	echo "\n" > /dev/tty
	if [ $i -ge 16 ];
	then
		echo "WARNING:password is large lentgh!"
	fi
}


#取得数据包含列数量
getcolcnt()
{
  wc -l $filename|read colcnt shname
  if [ $colcnt -ne 0 ];
  then
	head -1 $filename|tr -s $terminated '\n'|wc -l|read colcnt
    #head -1 $filename|awk -v fs=$terminated '{print split($1,myarr,fs}'|read colcnt
	#head -1 $filename|tr -s $terminated '\n'|wc -l|read colcnt
    #head -1 $filename|wc -w|read colcnt shname
  fi
}

#创建临时数据库表
createtable()
{
echo "" > $sqlname
echo "create table $tablename" >> $sqlname
echo "(" >>$sqlname
i=1
while [ "$i" -lt "$colcnt" ]
do
      echo "a$i varchar2($colsize)," >> $sqlname
      i=`expr $i + 1`
done

echo "a$i varchar2($colsize)" >> $sqlname

echo ")" >> $sqlname

sqlplus -s $username$PASS <<!|grep -v grep |grep -v create 
@$sqlname
/
exit
!
}

#生成sqlload控制文件并执行
createfile()
{
echo "" > $ctrlname
echo "LOAD DATA" >> $ctrlname
echo "INFILE '$filename'" >> $ctrlname
echo "APPEND" >> $ctrlname
echo "INTO TABLE $tablename" >> $ctrlname
echo "fields terminated by '$terminated'">> $ctrlname
echo "(" >> $ctrlname
i=1
while [ "$i" -lt "$colcnt" ]
do
      echo "a$i char($colsize)," >> $ctrlname
      i=`expr $i + 1`
done

echo "a$i char($colsize)" >> $ctrlname

echo ")" >> $ctrlname

}


#------------------------------#
#--------主程序运行------------#
#------------------------------#
#校验输入参数的正确性
if [ $# -lt 3 ];
then
  echo "Usage:$0 username tablename filename [terminated] [colsize]" 
  exit 1
fi
username=$1
tablename=$2
filename=$3
terminated=$4
colsize=$5

if [ "$terminated" != "" ];
then
	:
else
	terminated="|"
fi

if [ "$colsize" != "" ];
then
	:
else
	colsize=200
fi


#判断文件是否存在
if [ ! -r $filename ]
then
 echo "WARNING: the FILE=$FILE is not exist !"
exit 1
fi

#判断文件是否有扩展名.dat

echo $filename|awk '{if(match($0,/.dat/)) print "1"}'|read extcnt

if [ "$extcnt" != "" ];
then
	:
else
	echo "WARNING:pls renanme $filename to $filename.dat" 
	exit 1
fi


#初始化环境变量
ctrlname=tmp.ldr
sqlname=tmp.sql
colcnt=0

#检查数据库密码是否已经输入,如果未输入提示输入密码
awk 'BEGIN {print match("'$username'",/\//)}'|read passcnt
if [ $passcnt -le 0 ];
then

#保存stty环境变量到savedstty
savedstty=`stty -g`
echo "Please input password:\c"
     getpass
#从savedstty中恢复stty环境变量
stty $savedstty

#检查密码是否有效
_LETTERS_ONLY=`echo $PASS|awk '{if ($0 ~ /[A-Za-z0-9]/) print "1"}'`

if [ "$_LETTERS_ONLY" != "" ];
then
    PASS=/$PASS
else
    echo "WARNING:password is not valid!"
    exit 1
fi

fi


#取得数据列数
getcolcnt

if [ "$colcnt" -le 0 ];
then
	echo "WARNING:datafile is null,don't load"
	exit 1
fi


#检查表是否存在
existstable|read tablecnt

if [ "$tablecnt" -ge 1 ];
then
	echo "WARNING:table have exists,pls change tablename!"
	exit 1
else
	createtable
fi

#创建控制文件
createfile

if [ ! -r $ctrlname ]
then
	echo "Error: the control file is not exist !"
	exit 1
fi

#执行sqlload导入
sqlldr userid=$username$PASS control=tmp.ldr > .tmp.log 2>&1 

if [ -r $ctrlname ]
then
	rm -f $ctrlname
fi

if [ -r $sqlname ]
then
	rm -f $sqlname
fi

echo "SQLLDR SUCCESS! PLS GOTO DB CHECK!"