spool 2
来源:互联网 发布:cad软件培训班 编辑:程序博客网 时间:2024/05/17 22:47
1.spool的作用是什么?
spool的作用可以用一句话来描述:在sqlplus中用来保存或打印查询结果。
2.spool在oracle 9i、10g和11g下的语法及使用示例
oracle 9i spool语法
SPO[OL] [file_name[.ext]|OFF|OUT]
oracle 10g、11g spool语法
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF |OUT]
oracle 9i spool的主法比较简单,其实就相当于oracle10g中spool的replace选项,因此本文主要介绍oracle 10g的选项。
file_name[.txt]
保存查询结果集的的路径和文件名,如果没有指定后缀名,默认名一般为.lst或.lis。如果指定系统文件为/dev/null and/dev/stderr,则不会添加后缀名。
off
完成spool。
out
停止spool,并将文件输出到终端设备上,如打印机(可能有些操作系统不支持)。
我们从语法上可以看到,oracle在10g、11g中对spool增加了create、replace、append选项,
create
replace
append
sqlplus spool的create、replace、append选项示例
spool spool.ora create
spool spool.ora repalce
spool spool.ora append3.利用sqlplusspool将表导成csv文件
本sql将dba_sequence的所有数据导成csv文件。
viewplaincopy toclipboardprint?
- SET
HEADING OFF - SET
ECHO OFF - SET
FEEDBACK OFF - SET
PAGES 0 - SET
LINESIZE 32766 - SET
LONG 1999999 - SET
TRIMOUT ON - SET
TRIMSPOOL ON - SET
NEWPAGE NONE - SET
SQLBLANKLINES OFF - SET
TRIMS ON - SET
TIMING OFF - SET
SERVEROUTPUT ON - SET
VERIFY OFF - COLUMN
SQL_TEXT FORMAT A32766 WORD WRAPPED - SPOOL
data_to_csv.sql -
- SELECT
'SELECT ' '"''||'|| -
REPLACE(wmsys.wm_concat(COLUMN_NAME), ',', '||''","''||'||chr(10)) || -
'||''"'' SQL_TEXT FROM DBA_SEQUENCES;' SQL_TEXT -
FROM DBA_TAB_COLUMNS -
WHERE TABLE_NAME = 'DBA_SEQUENCES' -
order by column_id; - spool
off -
- spool
data_to_csv.csv - @data_to_csv.sql
- spool
off
运行如下:
17:10:26 dw@dw>@A.SQLSELECT '"'||SEQUENCE_OWNER||'","'||SEQUENCE_NAME||'","'||MIN_VALUE||'","'||MAX_VALUE||'","'||INCREMENT_BY||'","'||CYCLE_FLAG||'","'||ORDER_FLAG||'","'||CACHE_SIZE||'","'||LAST_NUMBER||'"' SQL_TEXT FROM DBA_SEQUENCES;"SYS","UGROUP_SEQUENCE","0","999999999999999999999999999","1","N","Y","10","1""SYS","OBJECT_GRANT","1","999999999999999999999999 999","1","N","Y","20","26432""SYS","SYSTEM_GRANT","1","999999999999999999999999 999","1","N","Y","20","1046""SYS","PROFNUM$","0","999999999999999999999999 999","1","N","N","0","2""SYS","AUDSES$","1","2000000000","1","Y","N","10000","581721""SYS","SNAPSHOT_ID$","1","2147483647","1","N","N","20","4""SYS","SNAPSITE_ID$","1","4294967295","1","N","N","20","1""SYS","JOBSEQ","1","999999999","1","Y","N","20","21""SYS","RGROUPSEQ","1","999999999","1","Y","N","20","1""SYS","ORA_TQ_BASE$","1","4294967","1","Y","N","0","83""SYS","PARTITION_NAME$","1","999999999999999999999999 999","1","N","N","20","21""SYS","CDC_SUBSCRIBE_SEQ$","1","999999999999999999999999 999","1","N","N","20","1""SYS","CDC_RSID_SEQ$","1","999999999999999999999999 999","1","N","Y","10000","10001""SYS","LOG$SEQUENCE","0","999999999999999999999999 999","1","N","Y","10","1""SYS","PSINDEX_SEQ$","1","18446744073709551615","1","N","N","1000","2100""SYS","AWSEQ$","1","4294967295","1","N","N","0","1000""SYS","STREAMS$_CAPTURE_INST","1","4294967295","1","Y","N","0","1".....
0 0
- spool 2
- spool
- SPOOL
- spool
- SPOOL
- spool
- spool Oracle
- Spool 用法
- SPOOL用法
- Oracle spool
- Oracle Spool
- sqlplus spool
- ORACLE SPOOL
- SPOOL用法
- ORACLE spool
- spool命令
- oracle spool
- Spool导出
- Unity动画播放方式区别
- 242. Valid Anagram
- 大数据究竟是什么?一句话让你认识并读懂大数据
- rdkafka 保存offset到本地文件
- 258. Add Digits
- spool 2
- Java EL表达式
- 292. Nim Game
- 数据结构中的各种排序---总结篇
- python的GUI编程--wxpython学习(一)--安装和原理步骤
- JAVA WEB 之 XML(1)
- char类型范围
- 自写字符串操作函数
- 326. Power of Three