oralce 批量导入导出

amwfnhz

在使用ORACLE时,有时需要在AIX下按指定的分隔符导入/导入某张表,SHELL实现如下:

导出程序:
#! /bin/ksh
# Create Date: 20090623
# Author: hzhuang
# QQ:179240033
# Description: 把数据表按指定的分隔符导出为目标文件
if [ $# -lt 1 -o $# -gt 4 ]
then
echo "用法:unload.sh -T表名  -F[分隔符] -A[目标地址]"
exit 1
fi
  #取出所有参数,并把参数放到指定的变量中去
  until [ $# -eq 0 ]
  do
FLG=`echo $1|awk '{print substr($1,1,2)}'`
   if [ $FLG == "-T" ]; then
  TABNAME=`echo $1 | awk '{print substr($1,3)}'`
elif [ $FLG == '-F' ]; then
  FLAG=`echo $1 | awk '{print substr($1,3)}'`
elif [ $FLG == '-A' ]; then
  ADD=`echo $1 | awk '{print substr($1,3)}'`
else
  echo "用法:unload.sh -T表名  -F[分隔符] -A[目标地址]"
  exit 1
fi
shift
  done
if [ -z "$FLAG" ]
then
  FLAG="|"
fi

upp=`echo $TABNAME|awk '{print toupper($1)}'`

#取出系统控制表
sqlplus -S ${DB_USER}/${DB_PASSWORD} << ! >/dev/null 2>&1
set echo on
set feedback off
set heading off
set verify off
set trimspool off
set linesize 512
        spool ${HOME}/tmp/unload.tmp
select column_name from user_tab_columns where table_name = '$upp' order by column_id;
!
#删除空行
sed '/^$/d' ${HOME}/tmp/unload.tmp > ${HOME}/tmp/unloadtmp.tmp
rm ${HOME}/tmp/unload.tmp 1>/dev/null 2>&1
sed 's/ //g' ${HOME}/tmp/unloadtmp.tmp > ${HOME}/tmp/unload.tmp
rm ${HOME}/tmp/unloadtmp.tmp 1>/dev/null 2>&1
#删除第一行和最后一行
#sed '1d' ${HOME}/tmp/unloadtmp.tmp > ${HOME}/tmp/unload.tmp
#rm ${HOME}/tmp/unloadtmp.tmp 1>/dev/null 2>&1
#sed '$d' ${HOME}/tmp/unload.tmp > ${HOME}/tmp/unloadtmp.tmp

sqlstr=`awk -v tbl=$TABNAME -v FLG=$FLAG '
BEGIN{
  sqlstr="select "
}
{
  sqlstr=sqlstr  $1 "||"  "'\''" FLG "'\''" "||"
}
END{
  LEN = length(sqlstr);
  FLEN=length(FLG);
  sqlstr=substr(sqlstr,1,LEN-2);
  sqlstr = sqlstr " FROM "tbl ";"
  print sqlstr;
}' < ${HOME}/tmp/unload.tmp`
rm ${HOME}/tmp/unload.tmp 1>/dev/null 2>&1
if [ $? -ne 0 ]
then
echo "组织数据结构出错"
exit 1
fi
echo $sqlstr > ${HOME}/tmp/unload.sql


sqlplus -S ${DB_USER}/${DB_PASSWORD} << ! >/dev/null 2>&1
set echo on;
set feedback off;
set heading off;
set verify off;
set trimspool off;
set linesize 1024;
    spool ${HOME}/tmp/${upp}.txt;
@${HOME}/tmp/unload.sql;
!
if [ $? -ne 0 ]
then
echo "导出表$upp数据出错"
exit 1
fi
#删除空行
sed '/^$/d' ${HOME}/tmp/${upp}.txt > ${HOME}/tmp/${upp}txt.txt
#删除头为SQL的行
sed '/^SQL/d' ${HOME}/tmp/${upp}txt.txt    > ${HOME}/tmp/${upp}.txt
#删除行首为空字符的
sed '/^ /d' ${HOME}/tmp/${upp}.txt > ${HOME}/tmp/${upp}txt.txt
sed 's/ *$//g' ${HOME}/tmp/${upp}txt.txt > ${HOME}/tmp/${upp}.txt

rm ${HOME}/tmp/${upp}txt.txt 1>/dev/null 2>&1
rm ${HOME}/tmp/unload.sql 1>/dev/null 2>&1
if [ ! -z "${ADD}" ]
then
mv ${HOME}/tmp/${upp}.txt ${ADD}
fi
exit 0


导入程序:

#! /bin/ksh
# Create Date:  20090827
# Author:hzhuang
# QQ:179240033
# Description:  按指定的分隔符导入数据文件到表中
if [ $# -eq 0 - a $# -gt 3 ]
then
echo "用法:load.sh  -T[表名] -F[分隔符] -f文件名 "
echo "注意:如果分隔符是|线,必须要转义,默认为TABLE分隔"
exit 1
fi

#取出参数
str=$*
l1=`echo $str|awk '{print $1}'`
l2=`echo $str|awk '{print $2}'`
l3=`echo $str|awk '{print $3}'`

if [ `echo $l1 | grep "^-T"` ]
then
tablename=`echo $l1|awk '{print substr($1,3)}'`
fi
if [ `echo $l1 | grep "^-F"` ]
then
ifsname=`echo $l1|awk '{print substr($1,3)}'`
fi
if [ `echo $l1 | grep "^-f"` ]
then
filename=`echo $l1|awk '{print substr($1,3)}'`
fi

if [ `echo $l2 | grep "^-T"` ]
then
tablename=`echo $l2|awk '{print substr($1,3)}'`
fi
if [ `echo $l2 | grep "^-F"` ]
then
ifsname=`echo $l2|awk '{print substr($1,3)}'`
fi
if [ `echo $l2 | grep "^-f"` ]
then
filename=`echo $l2|awk '{print substr($1,3)}'`
fi

if [ `echo $l3 | grep "^-T"` ]
then
tablename=`echo $l3|awk '{print substr($1,3)}'`
fi
if [ `echo $l3 | grep "^-F"` ]
then
ifsname=`echo $l3|awk '{print substr($1,3)}'`
fi
if [ `echo $l3 | grep "^-f"` ]
then
filename=`echo $l3|awk '{print substr($1,3)}'`
fi
if [ -z "$tablename" -a -z "$ifsname" -a -z "$filename" ]
then
echo "ERROR:"
echo "用法:load.sh  -T[表名] -F[分隔符] -f文件名 "
  echo "注意:如果分隔符是|线,必须要转义,默认为TABLE分隔"
exit 1
fi
if [ -z "$ifsname" ]
then
ifsname=" "
fi
if [ -z "$filename" ]
then
filename="${tablename}.txt"
if [ ! -f $filename ]
then
  echo "文件[$filename]不存在"
  exit 1
fi
fi

tablename=`echo $tablename|awk '{print toupper($1)}'`
echo $tablenameupp
#取出系统控制表
sqlplus ${DB_USER}/${DB_PASSWORD} << ! >/dev/null 2>&1
        set echo on
        set feedback off
        set heading off
        set verify off
        set trimspool off
        set linesize 80
        spool ${HOME}/tmp/$tablename.tmp
        select column_name from user_tab_columns where table_name = '$tablename' order by column_id;
!

#删除空行
sed '/^$/d' ${HOME}/tmp/$tablename.tmp > ${HOME}/tmp/$tablenametmp.tmp
rm ${HOME}/tmp/$tablename.tmp 1>/dev/null 2>&1
#删除第一行和最后一行
sed '1d' ${HOME}/tmp/$tablenametmp.tmp > ${HOME}/tmp/$tablename.tmp
rm ${HOME}/tmp/$tablenametmp.tmp 1>/dev/null 2>&1
sed '$d' ${HOME}/tmp/$tablename.tmp > ${HOME}/tmp/$tablenametmp.tmp
rm ${HOME}/tmp/$tablename.tmp 1>/dev/null 2>&1
mv ${HOME}/tmp/$tablenametmp.tmp ${HOME}/tmp/$tablename.tmp
#建立表控制文件
echo "OPTIONS (rows=1000)" > $HOME/tmp/$tablename.ctl
echo "LOAD DATA" >> $HOME/tmp/$tablename.ctl
echo "INFILE '$filename'" >> $HOME/tmp/$tablename.ctl
echo "truncate INTO table $tablename"  >> $HOME/tmp/$tablename.ctl
echo "FIELDS TERMINATED BY '$ifsname'" >> $HOME/tmp/$tablename.ctl
echo "trailing nullcols" >> $HOME/tmp/$tablename.ctl
echo "(" >> $HOME/tmp/$tablename.ctl
awk '{print $1","}' $HOME/tmp/$tablename.tmp >> $HOME/tmp/$tablename.ctl
sed '$ s/,//g' $HOME/tmp/$tablename.ctl > $HOME/tmp/$tablename.ctl.tmp
mv $HOME/tmp/$tablename.ctl.tmp $HOME/tmp/$tablename.ctl 1>/dev/null 2>&1
echo ")" >> $HOME/tmp/$tablename.ctl

rm $HOME/tmp/$tablename.tmp 1>/dev/null 2>&1

sqlldr ${DB_USER}/${DB_PASSWORD} control=$HOME/tmp/$tablename.ctl errors=50  rows=1000 bad=$tablename.bad log=$tablename.log
if [ $? -ne 0 ]
then
        echo "load数据时出错,出错码为[$?].请检查"
        exit 1
fi
rm $HOME/tmp/$tablename.ctl 1>/dev/null 2>&1
exit 0


导入程序中在开始判断输入参数时是才学习SHELL时使用的方法,后来使用也正常,也懒得改了,导出程序中是经过修改的,程序在使用过程中都一直正常,只是在处理大数据量的表时,有点慢,希望高人来修改修改.如果修改了再发我一份,我QQ:179240033   MAIL:179240033@QQ.COM

renxiao2003

复杂，我以为是使用exp导出用imp导入呢。

amwfnhz

回复 2# renxiao2003


    EXP/IMP导入导出是二进制的,这个程序是把数据导成文本文件,方便在不同的数据库中间进行数据传送.

allendream

用sqlplus的spool能实现这种文本文件的导出，而且很简单

allendream

用sqlplus的spool能实现这种文本文件的导出，而且很简单