nload.txt

Unix环境下用Shell编写的Oracle数据库数据批量导入导出程序

#!/usr/bin/ksh

#FieldTable start
FieldTable()
{
#	echo $1 , $2 , $USER
	sqlplus $USER<<! > /tmp/$1.desc
		desc $1
!

flag=0
iflag=0
sqlstr=""

sed -e'/^$/d' /tmp/$1.desc|while read line 
do
	head=`expr "$line" : "\(....\).*"`
	if [ $head = "SQL>" ]
	then 
		if [ $flag -eq 0 ]
		then
			flag=4
		else
			break
		fi	
	fi

	if [ $flag -gt 1 ]
	then
		iflag=1;
		flag=`expr $flag - 1`
	fi

	if [ $flag -eq 1 ]
	then
		if [ $iflag -ne 0 ]
		then 
			iflag=0
		else
			sqlstr=$sqlstr","
		fi
		
		head=`expr "$line" : "^[ ]*\([A-Z_][A-Z_0-9]*\).*"`
		if [ X$2 = X ]
		then
			sqlstr=$sqlstr$head
		else
			sqlstr=$sqlstr$2.$head
		fi
	fi
done

/usr/bin/rm /tmp/$1.desc
#echo $sqlstr

}
#FieldTable end


#SqlChange start
SqlChange()
{
#	echo "$1" + "$2"+ "$SQLSTATE"+"$allbit" 

	b=`expr "A$2" : "A\(.*\)\.\*"`
#	echo $b

	if [ X$b = X ]
	then
#		echo 888 $b
		nstr="\*"
		table=`expr "$SQLSTATE" : ".*[ 	]*from[ 	]*\(.*\)[ 	]*.*"`
	else
#		echo 999 $b
		nstr=$b"\.\*"
		tablenames=`expr "$SQLSTATE" : ".*[	]*from[ 	]*\(.*\)"`

#		echo hhhh $tablenames
		
		while true 
		do 
			tables=`expr "$tablenames" : "\(.*\)[,].*"`

#		echo iiii $tables

			if [ X"$tables" = X ]
			then
				tables=$tablenames
			fi
#			echo 111 $tables


			table=`expr "$tables" : "[ 	]*\(.*\)[ 	][ 	]*$b[ 	]*"`
#			echo 222 $table

			if [ X$table != X ]
			then
				break
			fi

			tablenames=`expr "$tablenames" : ".*[,]\(.*\)"`
#			echo 333 $tablenames

			if [ X"$tablenames" = X ]
			then 
				break
			fi
		done 
	fi


	if [ X$table = X ]
	then
		echo "invalid identifier in select statment , -904 "
		exit 2
	fi

#	echo $table

	FieldTable $table $b

#	echo "$2" $nstr 555 $sqlstr

	SQLSTATE=`echo "$SQLSTATE"|sed -e "s/$nstr/$sqlstr/"`

#	echo xxx "$SQLSTATE"
}

#SqlChange end

#main start 

USAGE="usage : $0 -u user/passwd {-t tablename|-e statement}  [-f path] [-p sepchar]"

OUTFILE="./myunload.txt"

uflag=0
tflag=0
aflag=0
eflag=0
fflag=0
SEPCHAR='|'
SQLSTATE=""

while getopts :u:t:e:f:p: i
do
	case $i in
		a )
			aflag=1;;
		u )
			uflag=`expr $uflag + 1`
			USER=$OPTARG;;
		e )
			eflag=`expr $eflag + 1`
			SQLSTATE="$OPTARG"
			;;
		t )
			tflag=`expr $tflag + 1`
			SQLSTATE="select * from "$OPTARG
			if [ $fflag -ne 1 ]
			then
				OUTFILE=${OPTARG}.txt
			fi
			;;
		f )
			fflag=`expr $fflag + 1`
			OUTFILE=$OPTARG;;
		p )
			SEPCHAR=$OPTARG;;
		? )
			echo $USAGE
			exit 2;;
	esac
done

if [ $uflag -ne 1 ]
then
	echo $USAGE
	exit 2
fi

if [ $tflag -eq 1 -a  $eflag -eq 1 ]
then
	echo $USAGE
	exit 2
fi

#echo "$SQLSTATE" , "$OUTFILE"
while true
do
allbit=`expr "$SQLSTATE" : ".*[, 	]\([a-z]*[.]*\*\)[ 	,].*"`
if [ X"$allbit" = X ]
then
#	echo xxxxxxxxxxxxx
	break
else
#	echo 777 "$allbit"
	SqlChange "$SQLSTATE" "$allbit"
fi
done

#selectstr=`expr "$SQLSTATE" : "\(.*\)[ 	]from[ 	].*"`

#echo "$SQLSTATE"\;|sed -e "s/$selectstr/`echo $selectstr|sed -e "s/,/\|\|\'$SEPCHAR\'\|\|/g"`/" >/tmp/$$.sql

sqlplus $USER<<!>/tmp/$$.txt
set pagesize 9999
set colsep $SEPCHAR
set head off
set heads off
set und off
set trim off
set trims off
set wrap on
set recsepchar '-'
$SQLSTATE;
!

#/usr/bin/rm /tmp/$$.txt

lflag=0
linestr=""

>$OUTFILE

sed -e'/^$/d' /tmp/$$.txt|while read line 
do
	if [ $lflag -eq 0 ]
	then
		head=`expr "$line" : "\(....\).*"`
		if [ $head = "SQL>" ]
		then
			lflag=1
		fi	
		continue
	else
		if [ X"$line" != X -a X"$line" = X`echo $line|awk /^--*$/` ]
		then
			echo $linestr$SEPCHAR
			linestr=""
		else
			if [ X"$linestr" = X ]
			then
				linestr=$line
			else
				linestr=$linestr$SEPCHAR$line
			fi
		fi
	fi
done | sed -e 's/ //g' >>$OUTFILE

#echo $OUTFILE
echo /tmp/$$.txt
echo `wc -l $OUTFILE |awk '{print $1}'` rows selected.
#/usr/bin/rm /tmp/$$.txt