你的位置:首页 > 操作系统

[操作系统]采用SHELL,通过SQL LOAD导入一定格式的txt文件至数据库中


1。 准备工作,window中可直接通过sqlload直接导入文件,linux下,需要有sqlload的相关软件。

2。 SQL脚本(MID_DFDZ.ctl)

  1. LOAD DATA
    INTO TABLE MID_T0_TRANS
    truncate
    FIELDS TERMINATED BY '|'
    (
    stldate,
    merno,
    termno,
    pan,
    transamt,
    refno,
    termssn,
    batno,
    transdate,
    workdate,
    fee
    )


3.shell脚本(getDFDZ.sh)

 

  #!/bin/bash

  if [ $# -lt 2 ]
  then
  echo "miss arguments"
  exit -1
  fi

  #parameters

  BATDATE=$1
  SEQ_NO=$2

  FILE_DFDZ=DFDZ_${BATDATE}
  #FTPADDR=172.16.3.8
  FTPADDR=144.131.254.186
  FTPUSER=dc
  FTPPSWD=dc2012
  #home/liushui/
  LOCAL_PATH="/100600/xqfdzwj/dfdz"
  #FTP_PATH="/weblogic/100600/xqfdzwj/dfdznew/"
  FTP_PATH="/dc/jy/dfdz/"
  DFDZPATH=/dc/jy/dfdz
  DFDZFILE=$DFDZPATH/"DFDZ_"$BATDATE.txt
  LOCFILE=/dc/jy/ctl
  ftp -in $FTPADDR << !
  user $FTPUSER $FTPPSWD
  bin
  lcd $FTP_PATH
  cd $LOCAL_PATH
  echo pwd
  get $FILE_DFDZ
  bye
  !

  DBUSER=dc
  DBPWD=dc_2015
  DBNAME=dcdb

  DFDZCTLFILE=$LOCFILE/MID_DFDZ.ctl

  DFDZBADFILE=$LOCFILE/${BATDATE}_dfdz.bad
  DFDZLOGFILE=$LOCFILE/${BATDATE}_dfdz.log

  sqlplus -s ${DBUSER}/${DBPWD}@${DBNAME}<<!
  ALTER TABLE TB_MAINTENANCE nologging
  !
  sqlldr userid=${DBUSER}/${DBPWD}@${DBNAME} control=$DFDZCTLFILE data=$DFDZFILE log=$DFDZLOGFILE bad=$DFDZBADFILE   errors=1000000 rows=10000
  RESULT=$?
  if [ $RESULT -ne 0 ]
  then
  SetCmdRslt $SEQ_NO 3
  exit -1
  fi

  RESULT=$?
  if [ $RESULT -ne 0 ]
  then
  exit -1
  fi
  exit 0


4。 txt格式文件(T0_H_20151009.txt)

  20151009|898340150399030|01086952|6214855491055386|1300.00|000789608053|000308|000001|133904|20151009|0.65
  20151009|898320559990126|55447203|6225768706558846|4085.00|000789614773|001363|000001|134011|20151009|0.00
  20151009|898620950130467|942E0166|6259654270166308|7930.00|000789615305|000218|000001|134011|20151009|3.97
  20151009|898654059982123|65412446|6217858300020175452|42097.00|000789614807|000069|000001|134013|20151009|21.05
  20151009|898430153980696|21183401|6222060012065499|97555.00|000789615795|000102|000001|134014|20151009|48.78
  20151009|898210245112423|15061518|4062522603299967|59900.00|000789615371|000833|000001|134016|20151009|29.95
  20151009|898411459982063|41145254|4512893437225106|16000.00|000789615837|000121|000003|134017|20151009|16.00
  20151009|898532550394078|TF407801|4581242413584282|22000.00|000789614459|000361|000001|134020|20151009|11.00