Oracle通过sqlplus spool导入出数据.docx

《Oracle通过sqlplus spool导入出数据.docx》由会员分享，可在线阅读，更多相关《Oracle通过sqlplus spool导入出数据.docx（9页珍藏版）》请在三一办公上搜索。

1、Oracle通过sqlplus spool导入出数据之前用过spool导出oracle数据为文本，可惜一直也没有整理下，今天再次用到，网上找了相关文档，也算作个记录。 第一部分，第二部分，第三部分 第一部分 第一步 :这是我的导出数据的脚本call.sql conn scott/tiger set echo off set term off set line 1000 pages 0 set feedback off set heading off set trimspool on spool /temp/test/ldr_test.csv select a.empno|,|a.ename|,

2、|to_char(a.hiredate,yyyy-mm-dd hh24:mi:ss)|,|a.sal from test a; spool off set trimspool off set heading on set feedback on set term on set echo on exit 注释:call.sql脚本执行方法 (1)sqlplus /nolog 先进入sqlplus命令模式 (2)start call.sql 在sqlplus命令模式下执行 第二步 :导入数据的脚本add_test.ctl LOAD DATA INFILE ldr_test.csv TRUNCATE

3、 INTO TABLE test FIELDS TERMINATED BY, OPTIONALLY ENCLOSED BY (EMPNO,ENAME,HIREDATE date yyyy-mm-dd hh24:mi:ss,SAL) 注释: 在第一步导出数据后,执行add_test.sql脚本命令为: sqlplus scott/tiger control=add_test.ctl 至此用sqlplus导入/出数据完成了，如果有些参数不明白，请看一下第二三部分。 哦.忘了说test测试表的结构了，create table test as select empno,ename,hiredate,s

4、al from emp; 第二部分 spool本身其实没有啥难的 ，就是set参数的个数太太多啦！下面就是我网上Copy的，当然有一些是自己加上去的 SQLset colsep ; /-域输出分隔符 SQLset newp none /设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的 SQLset echo off; /显示start启动的脚本中的每个sql命令，缺省为on SQL set echo on /设置运行命令是是否显示语句 SQL set feedback on; /设置显示“已选择XX行

5、” SQLset feedback off; /回显本次sql命令处理的记录条数，缺省为on即去掉最后的 已经选择10000行 SQLset heading off; /输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据 SQLset headsep off /标题分隔符 SQLset pagesize 0; /输出每页行数，缺省为24,为了避免分页，可设定为0。 SQLset linesize 80; /输出一行字符个数，缺省为80。 SQLset numwidth 12; /输出number类型域长度，缺省为10 SQLset termout/term off

6、; /显示脚本中的命令的执行结果，缺省为on SQLset trimout on; /去除标准输出每行的拖尾空格，缺省为off SQLset trimspool on; /去除重定向输出每行的拖尾空格，缺省为off SQLset serveroutput on; /设置允许显示输出类似dbms_output SQL set timing on; /设置显示“已用时间：XXXX” SQL set autotrace on-; /设置允许对执行的sql进行分析 SQLset verify off /可以关闭和打开提示确认信息old 1和new 1的显示. 第三部分 这个是纯Copy的set命令全家

7、福 ，呵呵，很专业，如果看不习惯的话可以全部改成小写 使用set命令的语法如下: SET 系统变量 值 其中系统变量及其可选值如下: ARRAYSIZE 20(默认值)|n AUTOCOMMIT OFF(默认值)|ON|IMMEDIATE BLOCKTERMINATOR .(默认值)|C CMDSEP ;|C|OFF(默认值)|ON COMPATIBILITY V5|V6|V7|NATIVE(默认值) CONCAT .(默认值)|C|OFF|ON(默认值) COPYCOMMIT 0(默认值)|n CRT crt DEFINE &|C|OFF|ON(默认值) ECHO OFF|ON EMBEDD

8、ED OFF(默认值)|ON ESCAPE (默认值)|C|OFF(默认值)|ON FEEDBACK 6(默认值)|n|OFF|ON FLUSH OFF|ON(默认值) HEADING OFF|ON(默认值) HEADSEP |(默认值)|C|OFF|ON(默认值) LINESIZE 80(默认值)|n LONG 80(默认值)|n LONGCHUNKSIZE 80(默认值)|n MAXDATA n NEWPAGE 1(默认值)|n NULL text NUMFORMAT 格式 NUMWIDTH 10(默认值)|n PAGESIZE 14(默认值)|n PAUSE OFF(默认值)|ON|te

9、xt RECSEP WRAPPED(默认值)|EACH|OFF RECSEPCHAR |C SCAN OFF|ON(默认值) SERVEROUTPUT OFF|ON SIZE n SHOWMODE OFF(默认值)|ON SPACE 1(默认值)|n SQLCASE MIXED(默认值)|LOWER|UPPER SQLCONTINUE ;(默认值)|文本 SQLNUMBER OFF|ON(默认值) SQLPERFIX #(默认值)|C SQLPROMPT SQL;(默认值)|文本 SQLTERMINATOR ;(默认值)|C|OFF|ON(默认值) SUFFIX SQL(默认值)|文本 TAB

10、 OFF|ON(默认值) TERMOUT OFF|ON(默认值) TIME OFF(默认值)|ON TIMING OFF(默认值)|ON TRIMOUT OFF|ON(默认值) UNDERLINE -(默认值)|C|OFF|ON(默认值) VERIFY OFF|ON(默认值) WRAP OFF|ON(默认值) 系统变量说明: ARRAYSIZE 20(默认值)|n 置一批的行数,是SQL*PLUS一次从数据库获取的行数,有效值为1至5000. 大的值可提高查询和子查询的有效性,可获取许多行,但也需要更多的内存.当超过1000时,其效果不大. AUTOCOMMIT OFF(默认值)|ON|IMM

11、EDIATE 控制ORACLE对数据库的修改的提交. 置ON时,在ORACLE执行每个SQL命令或PL/SQL块后对数据库提交修改;置OFF时则制止自动提交,需要手工地提交修改,例如用SQL的 COMMIT命令. IMMEDIATE功能同ON. BLOCKTERMINATOR .(默认值)|C 置非字母数字字符,用于结束PL/SQL块.要执行块时,必须发出RUN命令或/命令. CMDSEP ;|C|OFF(默认值)|ON 置非字母数字字符,用于分隔在一行中输入的多个SQL/PLUS命令.ON或OFF控制在一行中是否能输入多个命令. ON时将自动地将命令分隔符设为分号(;).其中C表示所置字符.

12、 COMPATIBILITY V5|V6|V7|NATIVE(默认值) 指定当前所链接的ORACLE版本.如果当前ORACLE的版本为5,则置COMPATIBILITY为V5; 为版本6时置成V6; 为版本7时置成V7. 如果希望由数据库决定该设置,在置成NATIVE. CONCAT .(默认值)|C|OFF|ON(默认值)设置结束一替换变量引用的字符.在中止替换变量引用字符之后可跟所有字符,作为体会组成部分,否则 SQL*PLUS将解释为替换变量名的一部分.当CONCAT开关为ON时,SQL*PLUS可重置CONCAT的值为点(.). COPYCOMMIT 0(默认值)|n 控制COPY命令

13、提交对数据库修改的批数.每次拷贝n批后,将提交到目标数据库.有效值为0到5000. 可用变量ARRAYSIZE设置一批的大小.如果置COPYCOMMIT为0,则仅在COPY操作结束时执行一次提交. CRT crt 改变SQL*PLUS RUNFORM命令使用的缺省CRT文件.如果置CRT不包含什么,则crt仅包含.如果在一个Form的系统调用期间,要使用NEW.CRT(缺省CRT是OLD.CRT),可按下列形式调用Form: SQL;RUNFORM -C NEW form名 或者 SQL;SET CRT NEW SQL;RUNFORM form名 第二中方法存储CRT选择,以致在下次运行RUN

14、FORM命令(是在同一次SQL*PLUS交互中)时,不需要指定. DEFINE &|C|OFF|ON(默认值) 设置在替换变量时所使用的字符.ON或OFF控制SQL*PLUS是否扫描替换变量的命令及用他们的值代替. DEFINE的ON或OFF的设置控制SCAN变量的设置. ECHO OFF|ON 控制START命令是否列出命令文件中的每一命令.为ON时,列出命令;为OFF时,制止列清单. EMBEDDED OFF(默认值)|ON 控制每一报表在一页中开始的地方. 为OFF时,迫使每一报表是在新页的顶部开始;为ON时,运行一报表在一页的任何位置开始. ESCAPE (默认值)|C|OFF(默认值

15、)|ON 定义作为Escape字符的字符.为OFF时,使Escape字符不起作用.为ON时,使Escape字符起作用. FEEDBACK 6(默认值)|n|OFF|ON 显示由查询返回的记录数.ON和OFF置显示为开或关.置FEEDBACK为ON时,等价于置n为1. 如果置FEEDBACK为0,等价于将它置成OFF. FLUSH OFF|ON(默认值) 控制输出送至用户的显示设备.为OFF时,运行操作系统做缓冲区输出;为ON时,不允许缓冲. 仅当非交互方式运行命令文件时使用OFF,这样可减少程序I/O总是,从而改进性能. HEADING OFF|ON(默认值) 控制报表中列标题的打印.为ON时

16、,在报表中打印列标题;为OFF时禁止打印列标题. HEADSEP |(默认值)|C|OFF|ON(默认值) 定义标题分隔字符.可在COLUMN命令中使用标题分隔符,将列标题分成多行.ON和OFF将标题分隔置成开或关.当标题分隔为关(OFF)时,SQL*PLUS打印标题分隔符像任何字符一样. LINESIZE 80(默认值)|n 置SQL*PLUS在一行中显示的字符总数,它还控制在TTITLE和BTITLE中对准中心的文本和右对齐文本. 可定义LINESIZE为1至最大值,其最大值依赖于操作系统. LONG 80(默认值)|n 为显示和拷贝LONG类型值的最大宽度的设置. 对于ORACLE7,

17、n的最大值为2G字节;对于版本6,最大值为32767. LONGCHUNKSIZE 80(默认值)|n 为SQL*PLUS检索LONG类型值的增量大小.由于内存的限制,可按增量检索,该变量仅应用于ORACLE7. MAXDATA n 置SQL*PLUS可处理的最大行宽字符数,其缺省值和最大值在不同操作系统中是可变的. NEWPAGE 1(默认值)|n 置每一页的头和顶部标题之间要打印的空行数.如果为0, 在页之间送一换号符,并在许多终端上清屏. NULL text 设置表示空值(null)的文本,如果NULL没有文本,则显示空格(缺省时). 使用COLUMN命令中的NULL子句可控制NULL变

18、量对该列的设置. NUMFORMAT 格式 设置显示数值的缺省格式,该格式是数值格式. NUMWIDTH 10(默认值)|n 对显示数值设置缺省宽度. PAGESIZE 14(默认值)|n 置从顶部标题至页结束之间的行数.在11英寸长的纸上打印报表,其值为54,上下各留一英寸(NEWPAGE值为6). PAUSE OFF(默认值)|ON|text 在显示报表时,控制终端滚动.在每一暂停时,必须按RETURN键.ON将引起SQL*PLUS在每一报表输出页开始时暂停.所指定的文本是每一次 SQL*PLUS暂停时显示的文本.如果要键入多个词,必须用单引号将文本括起来. RECSEP WRAPPED(默认值)|EACH|OFF RECSEPCHAR |C 指定显示或打印记录分行符的条件.一个记录分行符,是由RECSEPCHAR指定的字符组成的单行.空格为RECSEPCHAR的默认字符. RECSEP 告诉SQL*PLUS在哪儿做记录分隔.例如将RECSEP置成WRAPPED,在每一缠绕行之后,打印记录分行符.如果将RECSEP置成 EACH,SQL*PLUS在每一行后打印一记录分行符.如果将RECSEP置成OFF, SQL*PLUS不打印分行符. 来源：