Oracle的sqlplus指定格式导出数据

最新推荐文章于 2022-05-31 11:50:42 发布

SimpleSimpleSimples

最新推荐文章于 2022-05-31 11:50:42 发布

阅读量701

点赞数 1

分类专栏： Oracle数据库

本文链接：https://blog.youkuaiyun.com/SimpleSimpleSimples/article/details/82319583

版权

Oracle数据库专栏收录该内容

6 篇文章

订阅专栏

本文介绍 SQL Plus 的基本使用方法及常见参数设置，并提供了一个实用的 Shell 脚本示例用于导出数据库中的指定表数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.sqlplus连接

Last login: Sun Sep 2 01:38:10 2018 from 10.45.64.151
[bdp@host69 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 2 21:27:13 2018

Enter user-name: smart_prod
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2.sqlplus常见参数查看

SQL> show all;
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1102000100
repfooter OFF and is NULL
repheader OFF and is NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 11.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SMART_PROD"
verify ON
wrap : lines will be wrapped
errorlogging is OFF
SQL>

常用参数解析：

SQL>set colsep' ';　　　　 //-域输出分隔符

SQL>set echo off;　　　　 //显示start启动的脚本中的每个sql命令，缺省为on

SQL> set echo on //设置运行命令是是否显示语句

SQL> set feedback on; //设置显示“已选择XX行”

SQL>set feedback off;　 //回显本次sql命令处理的记录条数，缺省为on

SQL>set heading off;　　 //输出域标题，缺省为on

SQL>set pagesize 0;　　 //输出每页行数，缺省为24,为了避免分页，可设定为0。

SQL>set linesize 80;　　 //输出一行字符个数，缺省为80

SQL>set numwidth 12;　 //输出number类型域长度，缺省为10

SQL>set termout off;　　 //显示脚本中的命令的执行结果，缺省为on

SQL>set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off

SQL>set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off

SQL>set serveroutput on; //设置允许显示输出类似dbms_output

SQL> set timing on; //设置显示“已用时间：XXXX”

SQL> set autotrace on-; //设置允许对执行的sql进行分析

SQL> set wrap off; //设置超过最大行数时不换行，跟linesize 大小有关

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; //日期格式化

3.导出脚本示例

#!/bin/bash
export NLS_LANG="AMERICAN_AMERICA.UTF8"
#source /home/weihu/.bash_profile
export ORACLE_SID=orcl
export ORACLE_HOME=/home/oracle/app/11.2.0.4/db_1
export PATH=$ORACLE_HOME/bin:$PATH
#服务器域名
hostname="136.3.218.225"; 
#数据库名
dbname="startest"; 
#用户名
user="star";
#密码
password="star";
#端口号
prot="1521";
#生成文件所在目录
fileDir="/home/oracle/file/";
#导出标签生成文件名，注意date后面空格不省略
fileName="injection_label_all_`date +%Y%m%d%H%M`.txt";
labelValueFileName="injection_label_value_all_`date +%Y%m%d%H%M`.txt";
injectionLabelGrp="injection_label_grp_all_`date +%Y%m%d%H%M`.txt";
injectionLabelGrpMbr="injection_label_grp_mbr_all_`date +%Y%m%d%H%M`.txt"

#全路径=生成文件所在目录+文件名
fullPathFileName="${fileDir}${fileName}";
labelValueFullPathFileName="${fileDir}${labelValueFileName}";
injectionLabelGrpFullPath="${fileDir}${injectionLabelGrp}";
injectionLabelGrpMbrFullPath="${fileDir}${injectionLabelGrpMbr}";

#表名
tableName="injection_label";

#目标主机信息
ftpIp="136.3.218.6";
ftpUser="bss";
ftpPassword="bss@123";
ftpDir="/home/bss/smart/injectionLabel/";

#若文件存在，则删除文件
if [ -f ${fullPathFileName} ]; then
    rm ${fullPathFileName}
fi
 
#创建文件
TIME1=$(date "+%Y-%m-%d %H:%M:%S") 
echo -e "\n\n========================"${TIME1}" start handle!\n"
echo -e '-h'${hostname} '-P'${prot} '-u'${user} '-p'${password} '-D'${dbname} '-e"'${sqlstr}'"', 
#从数据库promotion中读取cateWhiteList表信息--skip-column-names:去掉表头信息
#mysql -h${hostname} -P${prot} -u${user} -p${password} -S /mysql/mysql5.6/data/mysql.sock << EOF
# mysql -h${hostname} -D${dbname} -u${user} -p${password} -P${prot} -S${mysqlSo
#/home/oracle/app/11.2.0.4/db_1/bin/sqlplus -s ${user}/${password} <<EOF
sqlplus -s ${user}/${password} <<EOF
set pages 0
set term off
set termout off
set wrap off
set linesize 4000
set echo off
set space 0 
clear columns
SET trimspool on
set heading off
set feedback off
set trimout on
set trimspool on
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
spool "${fullPathFileName}"
SELECT
    'injectionLabelId'||'|'||
    'injectionLabelCode'||'|'||
    'injectionLabelName'||'|'||
    'injectionLabelDesc'||'|'||
    'labelManageType'||'|'||
    'labelType'||'|'||
    'labelValueType'||'|'||
    'labelDataType'||'|'||
    'relevantFlag'||'|'||
    'statusCd'||'|'||
    'createStaff'||'|'||
    'updateStaff'||'|'||
    'createDate'||'|'||
    'statusDate'||'|'||
    'updateDate'||'|'||
    'remark'||'|'||
    'tableCode'||'|'||
    'orderId' from dual union all 
SELECT
to_char(
    decode(NVL(INJECTION_LABEL_ID,-999),-999,'\N',INJECTION_LABEL_ID)||'|'||
    NVL(INJECTION_LABEL_CODE,'\N')||'|'||
    NVL(INJECTION_LABEL_NAME,'\N')||'|'||
    NVL(INJECTION_LABEL_DESC,'\N')||'|'||
    '\N'||'|'||
    NVL(LABEL_TYPE,'\N')||'|'||
    NVL(LABEL_VALUE_TYPE,'\N')||'|'||
    NVL(LABEL_DATA_TYPE,'\N')||'|'||
    '\N'||'|'||
    NVL(STATUS_CD,'\N')||'|'||
    decode(NVL(CREATE_STAFF,-999),-999,'\N',CREATE_STAFF)||'|'||
    decode(NVL(UPDATE_STAFF,-999),-999,'\N',UPDATE_STAFF)||'|'||
    NVL(TO_CHAR(CREATE_DATE),'\N')||'|'||
    NVL(TO_CHAR(STATUS_DATE),'\N')||'|'||
    NVL(TO_CHAR(UPDATE_DATE),'\N')||'|'||
    NVL(REMARK,'\N')||'|'||
    NVL(TABLE_CODE,'\N')||'|'||
    '\N'
    )
FROM
    injection_label;
spool off
spool "${labelValueFullPathFileName}"
 SELECT
    'labelValueId'||'|'||
    'injectionLabelId'||'|'||
    'labelValue'||'|'||
    'valueName'||'|'||
    'valueDesc'||'|'||
    'statusCd'||'|'||
    'createStaff'||'|'||
    'updateStaff'||'|'||
    'createDate'||'|'||
    'statusDate'||'|'||
    'updateDate'||'|'||
    'remark' from dual union all
SELECT
to_char(
    decode(NVL(LABEL_VALUE_ID,-999),-999,'\N',LABEL_VALUE_ID)||'|'||
    decode(NVL(INJECTION_LABEL_ID,-999),-999,'\N',INJECTION_LABEL_ID)||'|'||
    NVL(LABEL_VALUE,'\N')||'|'||
    NVL(VALUE_NAME,'\N')||'|'||
    NVL(VALUE_DESC,'\N')||'|'||
    NVL(STATUS_CD,'\N')||'|'||
    decode(NVL(CREATE_STAFF,-999),-999,'\N',CREATE_STAFF)||'|'||
    decode(NVL(UPDATE_STAFF,-999),-999,'\N',UPDATE_STAFF)||'|'||
    NVL(TO_CHAR(CREATE_DATE),'\N')||'|'||
    NVL(TO_CHAR(STATUS_DATE),'\N')||'|'||
    NVL(TO_CHAR(UPDATE_DATE),'\N')||'|'||
    NVL(REMARK,'\N')
    )
FROM
    injection_label_value;
spool off
spool "${injectionLabelGrpFullPath}"
SELECT
    'grpId'||'|'||
    'grpNbr'||'|'||
    'grpName'||'|'||
    'grpDesc'||'|'||
    'parentGrpId'||'|'||
    'pathCode'||'|'||
    'statusCd'||'|'||
    'createStaff'||'|'||
    'updateStaff'||'|'||
    'createDate'||'|'||
    'statusDate'||'|'||
    'updateDate'||'|'||
    'remark'||'|'||
    'actionType'
 from dual union all 
SELECT
to_char(
    decode(NVL(GRP_ID,-999),-999,'\N',GRP_ID)||'|'||
    '\N'||'|'|| 
    NVL(GRP_NAME,'\N')||'|'||
    NVL(GRP_DESC,'\N')||'|'||
    decode(NVL(PARENT_GRP_ID,-999),-999,'\N',PARENT_GRP_ID)||'|'||
    NVL(PATH_CODE,'\N')||'|'||
    NVL(STATUS_CD,'\N')||'|'||
    decode(NVL(CREATE_STAFF,-999),-999,'\N',CREATE_STAFF)||'|'||
    decode(NVL(UPDATE_STAFF,-999),-999,'\N',UPDATE_STAFF)||'|'||
    NVL(TO_CHAR(CREATE_DATE),'\N')||'|'||
    NVL(TO_CHAR(STATUS_DATE),'\N')||'|'||
    NVL(TO_CHAR(UPDATE_DATE),'\N')||'|'||
    NVL(REMARK,'\N')||'|'||
    '\N' 
    )
FROM
    INJECTION_LABEL_GRP;
spool off
spool "${injectionLabelGrpMbrFullPath}"
SELECT
    'grpMbrId'||'|'||
    'grpId'||'|'||
    'injectionLabelId'||'|'||
    'statusCd'||'|'||
    'createStaff'||'|'||
    'updateStaff'||'|'||
    'createDate'||'|'||
    'statusDate'||'|'||
    'updateDate'||'|'||
    'updateStaff'||'|'||
    'actionType'
 from dual  union all 
SELECT
to_char(
    decode(NVL(GRP_MBR_ID,-999),-999,'\N',GRP_MBR_ID)||'|'||
    decode(NVL(GRP_ID,-999),-999,'\N',GRP_ID)||'|'||
    decode(NVL(INJECTION_LABEL_ID,-999),-999,'\N',INJECTION_LABEL_ID)||'|'||
    NVL(STATUS_CD,'\N')||'|'||
    decode(NVL(CREATE_STAFF,-999),-999,'\N',CREATE_STAFF)||'|'||
    decode(NVL(UPDATE_STAFF,-999),-999,'\N',UPDATE_STAFF)||'|'||
    NVL(TO_CHAR(CREATE_DATE),'\N')||'|'||
    NVL(TO_CHAR(STATUS_DATE),'\N')||'|'||
    NVL(TO_CHAR(UPDATE_DATE),'\N')||'|'||
    NVL(REMARK,'\N')||'|'||
    '\N'  
    )
FROM
    INJECTION_LABEL_GRP_MBR;
spool off
EOF
#mysql -h${hostname} -P${prot} -u${user} -p${password} -D${dbname} -e"${sqlstr}"
# if [ $? -ne 0 ]; then
#     echo "读取表失败"
#     exit 1
# fi

#推送文件
ftp -v -n<<!
open $ftpIp
user $ftpUser $ftpPassword
binary
cd $ftpDir
lcd ${fileDir}
prompt
mput ${fileName} ${labelValueFileName}  ${injectionLabelGrp} ${injectionLabelGrpMbr}
bye
!

echo "推送标签全量文件成功"
#rm  ${fullPathFileName}  ${labelValueFullPathFileName}
##echo "清空本地文件成功"

该脚本以竖线分割，注意导出的空格设置，可以用oracle的replace方法去掉。

制表符 chr(9)
换行符 chr(10)
回车符 chr(13)