ORACLE数据导出工具

最近应公司要求导出数据为csv格式文件供业务人员存档查看，同时还需要按照指定分隔符导出其他文本格式，待数据迁移使用，就是根据指定的sql，按照指定的分隔符和文本格式导出数据。所使用的数据是oralce，由于生产环境又没有下载安装像sqluldr2之类的数据库导出工具。想着自己利用自带的sqlplus客户端写个通用的oracle导数脚本，查询资料，发现网上各种博客里描写的spool一键导出，都是花拳绣腿，用起来是真不好使，数据错位，分隔符混乱，断行显示各种问题都有，特此记录下自己的这个导出工具。

shell脚本

--help查看使用说明

#!/bin/bash
#################################################################################################################################
#   name: sql export tool for oracle   
#   desc: export use MACKUP/SPOOL with sqlplus client
#   Author          Date            version
#   Epoch-Elysian   20231129        1.0
#
#################################################################################################################################
TYPE="csv" SEPERATOR="" FILE_PATH="" SPOOL_CONF="" EXPORT_SQL="" CLIENT_LANG="AMERICAN_AMERICA.AL32UTF8" HEADER="" USE_MACKUP="Y"
function usage {
cat <<EOF

Usage : sh $0 [options] -f <filepath> <sqltext>

examples : sh $0 -f /home/test.csv "select * from table;"
           sh $0 -f /home/test.data -t text -s "|" --head="id;name;age" "select * from table;"

Options 
-t, --type [ csv , text ]          导出文本类型： csv 和普通文本类型，不指定默认为［ csv ]
-f, --file                         导出文件路径
-s, --seperator                    设置字段分隔符，不指定默认情况下： csv [,] text [ TAB ]

--head                             添加头部，使用［;］分割，例如：--head="id;name;age"
--lang                             设置 sqlplus 客户端会话编码，默认值：[ AMERICAN_AMERICA .AL32UTF8]
--disable-mackup                   默认使用 MACKUP 生成文本格式，导出时，特殊情况下可能会出现异常，可添加此参数禁用 MACKUP 
                                   此时程序会自动解析sql，为sql字段拼接分隔符后导出，禁用后具有局限性，对执行的sql要求如下：
                                   1）不支持 select *，必须明确指定字段名
                                   2）不支持字段别名，拼接分隔符时会导致 sql 语法错误 

-- help 
EOF
}

function init_options {
    #指定 options 操作符
    ARGS=$(getopt -o t:s:f: -l type:,seperator:,file:,head:,lang:,disable-mackup,help -n "$0" -- "$@")
    # shellcheck disable=SC2181
    if [ $? -ne 0 ]; then
        usage
        exit 1
    fi
    #重新排列 getopts 拿到的参数值
    eval set -- "$ARGS"
    #循环遍历options参数列表
    while true; do
        case "$1" in
        -t | --type)
            TYPE=$2
            shift
            ;;
        -f | --file)
            FILE_PATH=$2
            shift
            ;;
        -s | --seperator)
            SEPERATOR=$2
            shift
            ;;
        --head)
            HEADER=$2
            shift
            ;;
        --lang)
            CLIENT_LANG=$2
            shift
            ;;
        --disable-mackup)
            USE_MACKUP="N"
            ;;
        --help)
            usage
            exit 0
            ;;
        --)
            shift
            break
            ;;
        esac
        shift
    done
    #初始化分隔符
    if [ "$TYPE" = "csv" ] && [ -z "$SEPERATOR" ]; then
        SEPERATOR=","
    fi
    if [ "$TYPE" = "text" ] && [ -z "$SEPERATOR" ]; then
        SEPERATOR=$'\t'
    fi

    if [ $# -ne 1 ]; then
        echo "参数输入错误"
        usage
        exit 1
    fi
    #导数sql
    EXPORT_SQL=$1
}

function use_mackup_check {
    if [ "$USE_MACKUP" = "Y" ]; then
        echo "export file use MACKUP , please ensure that sqlplus version is greater than 12.0.0.0.0, or use --disable-mackup to disable MACKUP "
        local version
        version=$(sqlplus -V | cut -d " " -f2-)
        echo "current sqlplus version:$version"
    fi
}

#spoo1导数配置
function init_spool_conf {
local spool_conf_csv spool_conf_mackup_csv spool_conf_mackup_txt spool_conf_txt
spool_conf_csv=$(
cat <<EOF
SET linesize 20480
SET long 1024000
SET longchunksize 20480
SET termout off 
SET newp none 
SET trims on 
SET trimout on 
SET echo off 
SET pagesize 0
SET heading off 
SET feedback off 
SET trimspool on 
SET term off 
EOF
)

spool_conf_mackup_csv=$(
cat <<EOF
SET long 1024000
SET longchunksize 20480
SET heading off 
SET feedback off 
SET MARKUP CSV ON DELIMITER '$SEPERATOR' QUOTE ON 
EOF
)

spool_conf_mackup_txt=$(
cat <<EOF
SET long 1024000
SET longchunksize 20480
SET heading off 
SET feedback off 
SET MARKUP CSV ON DELIMITER '$SEPERATOR' QUOTE OFF 
EOF
)

spool_conf_txt=$(
cat <<EOF
SET linesize 20480
SET long 1024000
SET longchunksize 20480
SET termout off
SET newp none
SET trims on
SET trimout on
SET echo off
SET pagesize 0
SET head off
SET heading off
SET feedback off
SET trimspool on
SET term off
EOF
)
case $1 in
csv)
    SPOOL_CONF=$([ "$2" == "Y" ] && echo "$spool_conf_mackup_csv" || echo "$spool_conf_csv")
    ;;
text)
    SPOOL_CONF=$([ "$2" == "Y" ] && echo "$spool_conf_mackup_txt" || echo "$spool_conf_txt")
    ;;
*)
    echo " not support export - type for $1"
    exit 1
    ;;
esac
}

function parse_and_process_sql {
    local sql_text="$1"
    local end_char=$'\n'
    # mack up 判断
    if [ "$4" = "Y" ]; then
        # if ! echo "$ sql _ text "| grep - e '.*;[[: space :]]*]'; then
        # fi
        # sql _ text +=";"
        #多条sql按;分割处理
        local OLD_IFS=$IFS
        IFS=";"
        #一次性读取多行纪录存入数组中
        local -a -p sql_list
        read -rd '' -a sql_list <<<"$sql_text"
        IFS=$OLD_IFS
        sql_text=""
        for sql in "${sql_list[@]}"; do
            if [ -z "$(echo "$sql" | tr -d '[:space:]')" ]; then
                continue
            fi
            sql_text+="$sql;$end_char"
        done
        EXPORT_SQL=$sql_text
        return
    fi
    #分隔符处理
    local type="$2" seperator="$3" split_char=""
    case $type in
    csv)
        split_char="||'\"$seperator\"'||"
        ;;
    text)
        split_char="||'$seperator'||"
        ;;
    *)
        echo "not support export-type for $type"
        exit 1
        ;;
    esac

    #多条sql按;分割处理
    local OLD_IFS=$IFS
    IFS=";"
    local -a -p sql_list
    #一次性读取多行纪录存入数组中
    read -rd '' -a sql_list <<<"$sql_text"
    IFS=$OLD_IFS
    sql_text=""
    for sql in "${sql_list[@]}"; do
        if [ -z "$(echo "$sql" | tr -d '[:space:]')" ]; then
            continue
        fi
        local fields
        #获取sql字段
        fields=$(echo "$sql" | awk -v RS="" 'BEGIN{IGNORECASE=1} {start=index($0,"SELECT");end=index($0,"FROM");print substr($0,start+6,end-start-6)}')
        if [ -z "$(echo "$fields" | tr -d '[:space:]')" ]; then
            echo "sql解析失败...检查sql是否正确或者符合--disable-mackup相关要求"
            exit 1
        elif [ "$(echo "$fields" | tr -d '[:space:]')" = "*" ]; then
            echo "export on disable mackup,not support [SELECT * FROM TABLE]" >&2
            exit 1
        fi
        #替换字段中的分隔符
        fields=$(echo "$fields" | awk -v FS="" -v OFS="" -v concat_str="$split_char" '{c=0;for(i=1;i<=NF;i++){ if($i=="(")c++;if(c<=0 && $i==",")$i=concat_str;if($i==")")c--; } print $0}')
        #csv 时,首尾拼拨
        if [ "$type" = "csv" ]; then
            fields="'\"'||$fields||'\"'"
        fi
        #重新组装sql
        sql_text+=$(echo "$sql" | awk -v fields="$fields" -v RS="" 'BEGIN{IGNORECASE=1} {start=index($0,"SELECT")+5;end=index($0,"FROM");head=substr($0,1,start);tail=substr($0,end);printf "%s %s %s",head,fields,tail}')
        sql_text+=";$end_char"
    done
    #重新组装sql
    EXPORT_SQL=$sql_text
}

function add_header {
    local header=$1 file=$2
    if [ -z "$header" ]; then
        return
    fi
    header=${header//;/$SEPERATOR}
    echo "start add header to export file ......"
    if sed -i "1i $header" "$file"; then
        echo "add header >> success"
    else
        echo "add header >> failed"
        exit 1
    fi
}

#连接测试
function check_conn {
local RESULT
RESULT=$(
sqlplus -S "${DB_USER}/${DB_PWD}@${DB_NAME}" <<EOF
SET heading off;
SET feedback off;
SELECT 1 FROM DUAL;
exit 
EOF
)

# shellcheck disable=SC2181
if [ $? -ne 0 ]; then
    echo "connect to oracle server error ..."
    exit 1
elif [ -n "$RESULT" ]; then
    echo "connected to oracle server >> success"
fi
}

function main {
init_options "$@"
#设置终端编码和数据库一致,避免乱码
export NLS_LANG=$CLIENT_LANG
if [ -z "$FILE_PATH" ]; then
    echo "请使用-f | --file 指定输出文件路径"
    exit 1
fi
if [ -z "$(echo "$EXPORT_SQL" | tr -d '[:space:]')" ]; then
    echo "参数输入错误： sqltext "
    exit 1
fi
read -rp "请输入用户名:" DB_USER
read -rp "请输入库名:" DB_NAME
read -rp "请输入密码:" -s DB_PWD
#换行
echo
use_mackup_check
init_spool_conf "$TYPE" "$USE_MACKUP"
parse_and_process_sql "$EXPORT_SQL" "$TYPE" "$SEPERATOR" "$USE_MACKUP"
#检查连接
check_conn
echo "start to export sql to $FILE_PATH...export type is $TYPE"
if [ "$USE_MACKUP" = "N" ]; then
    echo "export on disable mackup , sql text after process is :"
    echo "$EXPORT_SQL"
fi
#导出sql执行
sqlplus -S "${DB_USER}/${DB_PWD}@${DB_NAME}" <<EOF 1>/dev/null
WHENEVER SQLERROR EXIT 1;
$SPOOL_CONF 
spool $FILE_PATH 
$EXPORT_SQL 
spool off ;
exit
EOF

# shellcheck disable=SC2181
if [ $? -ne 0 ]; then
    echo "export sql execute error >> error , please check export sql"
    exit 1
else
    echo "export sql >> success "
fi
#添加头部
add_header "$HEADER" "$FILE_PATH"
}
main "$@"

脚本特殊处理记录：

• shell脚本getopt使用：init_options
• shell脚本字符串按分隔符转数组正确姿势

        #多条sql按;分割处理
        local OLD_IFS=$IFS
        IFS=";"
        #一次性读取多行纪录存入数组中
        local -a -p sql_list
        read -rd '' -a sql_list <<<"$sql_text"
        IFS=$OLD_IFS
        sql_text=""
        for sql in "${sql_list[@]}"; do
            if [ -z "$(echo "$sql" | tr -d '[:space:]')" ]; then
                continue
            fi
            sql_text+="$sql;$end_char"
        done

• ?awk替换不在括号中的逗号

fields=$(echo "$fields" | awk -v FS="" -v OFS="" -v concat_str="$split_char" '{c=0;for(i=1;i<=NF;i++){ if($i=="(")c++;if(c<=0 && $i==",")$i=concat_str;if($i==")")c--; } print $0}')

• shell脚本处理sqlplus执行sql的异常：WHENEVER SQLERROR EXIT SQLCODE

sqlplus -S "${DB_USER}/${DB_PWD}@${DB_NAME}" <<EOF 1>/dev/null
WHENEVER SQLERROR EXIT 1;
$SPOOL_CONF 
spool $FILE_PATH 
$EXPORT_SQL 
spool off ;
exit
EOF

?

?