#!/bin/sh
export ORACLE_HOME=/oracle/products/10.2/db
export ORA_NLS33=/oracle/products/10.2/db/ocommon/nls/admin/data
export ORACLE_BASE=/oracle/products
export ORACLE_TERM=xterm
export SHELL=/bin/bash
if [ $# -gt 4 ] || [ $# -lt 3 ]
then
echo "-------------------------------------------------------------------------------------"
echo "程序名:load_tool.sh"
echo -e "功能:把数据文件导入到oracle数据库/n"
echo -e "程序参数: <地市> <表名> <导入的文件> <分隔符(可选择)>/n"
echo "<分隔符(可选择)> 的说明:若不输入指定的分隔符,程序自行判断文件分隔符"
echo "而且只会从以下分隔符中判断:"
echo -e "& && 空格 | || , ; # ## @ @@ ~ / /n"
echo -e "<表名> 的值说明:输入的表名没建立,则程序根据数据文件自行建表."
echo "如下例子:"
echo "(1)把文件1.txt导入到深圳库里,分隔符是|,让程序自行建表,表名是tp_1_bak:"
echo "./load_tool.sh sz tp_1_bak 1.txt /"|/""
echo "(2)把文件1.txt导入到深圳库的tp_test表里,不输入指定的分隔符"
echo "./load_tool.sh sz tp_test 1.txt "
echo "(3)把文件1.txt导入到深圳库的tp_test表里,指定分隔符是,"
echo "./load_tool.sh sz tp_test 1.txt ,"
echo "--------------------------------------------------------------------------------------"
exit
fi;
###############定义变量并赋值###################
#exec_user=$1
#exec_pass=$2
exec_city=$1
exec_table=$2
exec_text=$3
exec_str=$4
is_load=0;
is_createtable=0
load_mode=APPEND
current_date=`date +"%Y%m%d%H%M%S"`
##########根据第2个参数<表名>判断是否建表#########
if [ "$exec_table" = "1" ]
then
is_createtable=1;
fi
############创建获取文件分隔符的函数###############
text_split()
{
split_text=$1 #保存导入的文件
split_str=0 #单个分隔符变量,并初始化
i=0 #循环变量,并初始化
is_stop=0 #是否退出循环变量,并初始化
count_split=0
text_row=$2 #保存文件的总行数
splitstr_arr=( '|' '||' " " '&' '&&' ',' ';' '#' '##' '@' '@@' '/' '//' )
while [ $i -lt ${#splitstr_arr[@]} ] && [ "$is_stop" = "0" ]
do
split_str=${splitstr_arr[$i]}
#当文件行数大于200行小于10000行,不相同分隔符的行数小于文件总行数的5%,就选择为分隔符
if [ $text_row -lt 10000 ] && [ $text_row -gt 200 ]
then
diff_row=`echo "$text_row*0.05"|bc`
diff_row=`echo $diff_row |awk '{printf "%.0f/n",$1}'`
diff_row1=`grep -v -c "$split_str" $split_text`
if [ $diff_row1 -lt $diff_row ]
then
is_stop=1
echo $split_str
break;
fi
if [ $diff_row1 -eq $text_row ]
then
count_split=`expr $count_split + 1`
fi
#当文件小于200行,不相同的分隔符行数小于10行,就选择为分隔符
elif [ $text_row -lt 199 ]
then
diff_row=10
diff_row1=`grep -v -c "$split_str" $split_text `
if [ $diff_row1 -lt $diff_row ]
then
is_stop=1
echo $split_str
break;
fi
if [ $diff_row1 -eq $text_row ]
then
count_split=`expr $count_split + 1`
fi
#当文件大于10000行,选择前1000行,不相同的分隔符小于20行,就选择为分隔符
elif [ $text_row -gt 10001 ]
then
diff_row1=`head -1000 $split_text | grep -v -c "$split_str"`
if [ $diff_row1 -lt 20 ]
then
is_stop=1
echo $split_str
break;
fi
if [ $diff_row1 -eq 1000 ]
then
count_split=`expr $count_split + 1`
fi
fi
i=`expr $i + 1`
done
#当上述所有空格符都不符合文件的中的分隔符,就默认为只有一个字段
if [ ${#splitstr_arr[@]} -eq $count_split ]
then
echo "null_split"
#存在多种分隔符
elif [ $is_stop = "0" ] && [ ${#splitstr_arr[@]} -ne $count_split ]
then
echo "no"
fi
}
########创建数据库连接并执行脚本文件的函数########
oracle_con()
{
con_user=$1
con_pass=$2
con_constr=$3
con_file=$4
con_passsign=/
con_strsign=@
sqlplus $con_user$con_passsign$con_pass$con_strsign$con_constr << !
start $con_file
exit;
!
}
#############创建获取数据库连接串的函数###########
get_orastr()
{
city=$1
if [ "$city" = "fs" ] || [ "$city" = "zj" ] || [ "$city" = "mm" ] || /
[ "$city" = "yj" ] || [ "$city" = "sg" ]
then
echo "fsboss"
elif [ "$city" = "st" ] || [ "$city" = "zq" ] || [ "$city" = "jy" ] || [ "$city" = "hz" ]
then
echo "stboss"
elif [ "$city" = "dg" ] || [ "$city" = "sw" ] || [ "$city" = "hy" ] || [ "$city" = "cz" ]
then
echo "dgboss"
elif [ "$city" = "gz" ]
then
echo "gzboss"
elif [ "$city" = "zs" ] || [ "$city" = "zh" ] || [ "$city" = "jm" ] || [ "$city" = "mz" ] || /
[ "$city" = "yf" ] || [ "$city" = "qy" ]
then
echo "zsdb2"
elif [ "$city" = "sz" ]
then
echo "szdb1"
else
echo "no"
exit;
fi
}
#####判断数据库账号和密码是否正在正确的函数#########
is_right_ora()
{
is_ora_value=`sqlplus -S $1/$2@$3 << EOF
set heading off
set pagesize 0
set verify off
set echo off
select 1 from dual
exit
END`
if [ `echo "$is_ora_value" | grep "Invalid option"| wc -l` -gt 0 ]
then
echo "n"
else
echo "y"
fi
}
###############根据输入的地市获取连接串############
exec_ora_sign=`get_orastr $exec_city`
if [ "$exec_ora_sign" = "no" ]
then
echo -e "/n输入的地市不存在!/n"
exit;
fi
###############判断输入的文件是否存在##############
if [ ! -s $exec_text ]
then
echo -e "/n输入的文件$exec_text 不存在!/n"
exit;
fi
###########判断用户的账号和密码是否正确#############
echo -n -e "/n数据库账号($exec_city所属的`echo $exec_ora_sign|awk '{print substr($0,1,2)}'`库):"
read -t 60 exec_user
if [ -z $exec_user ]
then
echo -e "/n输入的账号为空或超时,退出程序!/n"
exit;
fi
echo -n "数据库密码:"
read -s -t 60 exec_pass
if [ -z $exec_pass ]
then
echo -e "/n输入的账号为空或超时,退出程序!/n"
exit;
fi
is_usr_pass=`is_right_ora $exec_user $exec_pass $exec_ora_sign`
if [ "$is_usr_pass" = "n" ]
then
echo -e "/n输入的账号或密码有误!/n"
exit;
fi
###############去掉文件空行####################
sed '/^$/d' $exec_text > $exec_user$exec_text.bak
mv -f $exec_user$exec_text.bak $exec_text
##############统计输入的文档数据行数############
count_row=` wc -l $exec_text | awk -F " " '{print $1}'`
echo -e "/n`date +"%Y-%m-%d %H:%M:%S"` 文件$exec_text的数据(不包括空行)共有$count_row行!"
###############获取文件分隔符##################
if [ -z $exec_str ]
then
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 正在获取文件数据的分隔符..."
exec_str=`text_split $exec_text $count_row`
if [ "$exec_str" = "null_split" ]
then
exec_str="|"
echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text只有一个字段.."
elif [ "$exec_str" = "no" ]
then
echo "`date +"%Y-%m-%d %H:%M:%S"` 请查看数据文件$exec_text,存在多种分隔符,请指定分隔符!"
grep -v "$exec_str" $exec_text |head -3
grep "$exec_str" $exec_text |head -3
exit;
elif [ `echo $exec_str | awk '{print length($0)}'` -eq 0 ]
then
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 获取文件数据的分隔符是:空格符"
exec_str=" "
fi
fi
################导出输入的表的字段#############
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 正在导出表$exec_table的字段..."
echo "
spool desc_table_$current_date.txt
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set heading off
set echo off
set termout off
set timing off
set time off
set trimspool off
set feedback off
set newpage none
" > desc_table_$current_date.sql;
echo "select (case when r1 <> 1 then COLUMN_NAME||',' else COLUMN_NAME||')' end ) COLUMN_NAME From (
select COLUMN_NAME,row_number()over(order by column_id desc ) r1
from user_tab_columns where table_name = upper('$exec_table')
order by column_id );
spool off;" >> desc_table_$current_date.sql;
oracle_con $exec_user $exec_pass $exec_ora_sign desc_table_$current_date.sql > /dev/null
#################判断输入的表是否存在#############
if [ ! -s desc_table_$current_date.txt ]
then
echo -e "`date +"%Y-%m-%d %H:%M:%S"` `echo $exec_ora_sign|awk '{print substr($0,1,2)}'`库不存在/"$exec_table/"表!"
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 默认创建表,表名:$exec_table,请留意!"
echo "`date +"%Y-%m-%d %H:%M:%S"` 正在创建表..."
file_num=`awk -F "$exec_str" 'BEGIN {max_nf=0} {if (max_nf < NF) max_nf=NF} END { print max_nf}' $exec_text`
echo "create table $exec_table (" > create_table_$current_date.sql
echo "(" > create_table_ctl_$current_date_$current_date.txt
for ((i=1;i<=$file_num;i=i+1))
do
file_max=`awk -F "$exec_str" 'BEGIN {max=0 } {max=length($'$i')> max?length($'$i'):max} END {print max}' $exec_text`
file_max=`expr $file_max + 5`
if [ $i -ne $file_num ]
then
echo "file$i varchar($file_max) default null," >> create_table_$current_date.sql
echo "file$i," >> create_table_ctl_$current_date_$current_date.txt
else
echo "file$i varchar($file_max) default null);" >> create_table_$current_date.sql
echo "file$i)" >> create_table_ctl_$current_date_$current_date.txt
fi
done
is_table=`oracle_con $exec_user $exec_pass $exec_ora_sign create_table_$current_date.sql`
if [ `echo $is_table | grep "invalid" | wc -l | awk -F " " '{print $1}'` != "0" ]
then
echo -e "/n输入的表名“$exec_table”不符合ORACLE建表的表名称规范,请重命名!/n"
unset splitstr_arr
rm -f create_table_$current_date.txt
rm -f create_table_ctl_$current_date.txt
rm -f desc_table_$current_date.txt
rm -f desc_table_$current_date.sql
rm -f create_table_$current_date.sql
exit;
fi
echo "
load data
infile '$exec_text'
$load_mode INTO TABLE $exec_table
FIELDS TERMINATED BY '$exec_str'
trailing nullcols " > loadtool_$current_date.ctl
cat create_table_ctl_$current_date.txt >> loadtool_$current_date.ctl;
else
#################导数模式######################
echo -e "/n--------------------------导入模式选择----------------------------------"
echo "1)insert 数据装载时要求表为空 2)append 保留原记录,添加新记录"
echo "3)replace 把旧记录替换成新记录 4)truncate 快速删除原记录,添加新记录"
echo "------------------------------------------------------------------------"
echo -n "(按回车键,则取用模式是append保留原记录,添加新记录):"
b_read_time=`date +%s`
read -t 60 load_mode
a_read_time=`date +%s`
diff_time=$((a_read_time-b_read_time))
if [ $diff_time -gt 59 ]
then
echo -e "/n60秒超时没响应,退出程序!/n"
exit;
fi
while [ ! -z $load_mode ]
do
if [ "$load_mode" = "1" ]
then
load_mode="INSERT"
break;
elif [ "$load_mode" = "2" ]
then
load_mode="APPEND"
break;
elif [ "$load_mode" = "3" ]
then
load_mode="REPLACE"
break;
elif [ "$load_mode" = "4" ]
then
load_mode="TRUNCATE"
break;
else
echo -n "请重新选择:"
b_read_time=`date +%s`
read -t 60 load_mode
a_read_time=`date +%s`
diff_time=$((a_read_time-b_read_time))
if [ $diff_time -gt 59 ]
then
echo -e "/n60秒超时没响应,退出程序!/n"
exit;
elif [ -z $load_mode ]
then
load_mode=APPEND
break;
fi
fi
done
if [ -z $load_mode ]
then
load_mode=APPEND;
fi
#################创建控制文件####################
echo "
load data
infile '$exec_text'
$load_mode INTO TABLE $exec_table
FIELDS TERMINATED BY '$exec_str'
trailing nullcols
( " > loadtool_$current_date.ctl
cat desc_table_$current_date.txt >> loadtool_$current_date.ctl;
fi
###############把文件的数据导入到数据库##############
echo "`date +"%Y-%m-%d %H:%M:%S"` 正在把文件$exec_text导入到$exec_city地市..."
sqlldr $exec_user/$exec_pass@$exec_ora_sign control=loadtool_$current_date.ctl,direct=y,log=loadtool_$current_date.log,bad=loadtool_bad_$current_date.bad > /dev/null
###############判断导数是否全部成功##################
if [ -s loadtool_bad_$current_date.bad ] && [ `sed '/^$/d' loadtool_bad_$current_date.bad | wc -l | awk -F " " '{print $1}'` -gt 1 ]
then
echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text没有全部成功导入到$exec_city地市!"
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 详细情况请检查loadtool_$current_date.log和loadtool_bad_$current_date.bad!"
else
echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text全部成功导入到$exec_city!"
fi
#################删除生成的相关文件###################
rm -f loadtool_$current_date.ctl
unset splitstr_arr
rm -f create_table_$current_date.txt
rm -f create_table_ctl_$current_date.txt
rm -f desc_table_$current_date.txt
rm -f desc_table_$current_date.sql
rm -f create_table_$current_date.sql
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 取用导数模式是:$load_mode"
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 运行完毕!/n"
sql导数脚本
最新推荐文章于 2023-04-03 10:43:45 发布