spool导数处理数据格式&空格问题

最近使用spool导数，出现导出的数据科学计数法、小数位数丢失、字段间补了很多空格，网上搜索了一圈采取的措施：

1.解决数据格式问题

使用to_char函数,例如to_char(num,‘9999.99’)

2.解决字段间空格问题

导出的查询sql字段间使用||加分隔符的方式连接起来，如

select COLUMN_NAME||'~'||DATA_TYPE||'~'||DATA_LENGTH||'~'||DATA_PRECISION||'~'||DATA_SCALE from user_tab_columns

实际使用的过程中，发现导出number型字段的时候，字段间还是自动补了空格，这个时候，可以在to_char函数外嵌套replace函数，去空格，例如：

select COLUMN_NAME||'~'||DATA_TYPE||'~'||replace(to_char(DATA_LENGTH,'99999'),' ','')||'~'||DATA_PRECISION||'~'||DATA_SCALE from user_tab_columns

如果表字段比较少，需要处理的表也少的话，我们可以直接把导出的sql写好；表字段超多，需要处理的表也多的话，再手写就比较麻烦了，下面介绍一个我自己写的一个只要获取到表名，就能按照格式使用约定的分隔符导出的例子。

1.首先我们通过user_tab_columns表，查询表的字段名、字段类型、字段长度等信息，这边要注意，表名要大写

value1=`sqlplus -S "${orauser}/${orapasswd}@${sid}" << !
  set heading off 
  set feedback off
  set pagesize 0
  set verify off
  set echo off
  set linesize 2000
  set sqlblanklines on
  select COLUMN_NAME||'~'||DATA_TYPE||'~'||DATA_LENGTH||'~'||DATA_PRECISION||'~'||DATA_SCALE||'^' from user_tab_columns where table_name = ${table_name} order by column_id;
  exit;
  !`

2.我们循环解析查询到的字段名称、字段类型，并按照不同的类型进行不同处理，我这边的做法是：
如果是字符串类型，就直接用拼接符连接，如果是int型也直接拼接，如果是number带小数型，用to_char限制格式，然后再用replace去空格，再拼接；
要注意一下，最后一个字段需要单独处理

#将返回字符串进行数组切分,分隔符'^'
  OLD_IFS="$IFS"
  IFS="^"
  arr1=($value1)
  IFS="$OLD_IFS"
  
  #通过spool进行导数操作
  echo "开始导出数据">>${log_file}
  echo set echo off newpage 0 space 0 pagesize 0 line 5000 feed off head off trimspool on trimout on >>tem001.sql
  #导出路径及文件名
  echo SPOOL /app/detail/${exp_file_name}>>tem001.sql
  #导出sql拼接
  sql="select "
  echo $sql>>tem001.sql  
  for((y=0;y<${#arr1[@]};y++));do
   OLD_IFS="$IFS"
   IFS="~"
   arr2=(${arr1[${y}]})
   IFS="$OLD_IFS"
   
   #用于判断是否是最后一个字段，是的话，不用再拼接【||chr(29)||】
   int=`expr ${#arr1[@]} - 1`   
   if [ ${y} != ${int} ];then
    if [ ${arr2[1]} = 'NUMBER' ];then
     if [ ${arr2[3]} > 0 ];then
      f='0.'
      for((j=0;j<${arr2[3]}-1;j++));do
       f="9"$f
      done
      for((j=0;j<${arr2[4]}+0;j++));do
       f=$f"9"
      done
      sql1='replace(to_char('${arr2[0]}",'"${f}"'),' ','')||chr(29)||"
     else
      sql1=${arr2[0]}"||chr(29)||"
     fi
    else
     sql1=${arr2[0]}"||chr(29)||"
    fi
   else
    if [ ${arr2[1]} = 'NUMBER' ];then
     if [ ${arr2[3]} > 0 ];then
      f='0.'
      for((j=0;j<${arr2[3]}-1;j++));do
       f="9"$f
      done
      for((j=0;j<${arr2[4]}+0;j++));do
       f=$f"9"
      done
      sql1='replace(to_char('${arr2[0]}",'"${f}"'),' ','')"
     else
      sql1=${arr2[0]}
     fi
    else
     sql1=${arr2[0]}
    fi
   fi
   echo $sql1>>tem001.sql
  done
  #from 表名 条件语句
  sql2="' from "${table_name}" '"${where}"';'"
  
  eval echo $sql2>>tem001.sql
  echo SPOOL OFF>>tem001.sql
  echo quit>>tem001.sql
  #执行导出
  sqlplus ${orauser}/${orapasswd}@${sid} @tem001.sql>>${log_file}

注：在实际操作中发现，发现因为拼接的原因，sql太长，导致报SP2-0027:输入太长(>2499个字符)的错误，拼接过程可以将sql：

select a||'~'||b||'~'||c||'~'||d from table_name

写成：

select 
a||'~'||
b||'~'||
c||'~'||
d
from table_name

可以避免SP2-0027错误。

写脚本过程中的小记录，如果有不对的地方或者可以优化的欢迎留言指正，谢谢~