关于导出
原理
通过sqlplus的的报表功能生成文本文件，但产生的文本文件因分析原因存在空行现象（只有回车没有其它），可以通过第三方方式处理一下生成标准文本
A 学习过程
1 建表
SQL>CREATE TABLE TESTINOUT(ID NUMBER(10), USERNAME VARCHAR2(10), DATE1 DATE, MONEY NUMBER(6, 2));
2 添加数据
SQL>insert into TESTINOUT(id,username,date1,money) values(1,'will',to_date('29-12-2001','dd-mm-yyyy'),33.33)
SQL>insert into TESTINOUT(id,username,date1,money) values(2,'ddxxkk',to_date('29-12-2002','dd-mm-yyyy'),443.33)
SQL>insert into TESTINOUT(id,username,date1,money)
values(1234567890,'thisistest',to_date('29-12-2002','dd-mm-yyyy'),4443.33)
3 设定列间距为'（没有空格）
SQL> set colsep '
4
SQL>column id format a10 truncate
SQL>column money format a6 truncate
SQL> select to_char(id,'0000000009S') as
id,username,to_char(date1,'yyyymmdd') as
date1,to_char(money*100,'000000s') as money from testinout;
ID USERNAME DATE1 MONEY
----------------------------------
0000000001test 20011229003333
0000000002ddxxkk 20021229055500
1234567890thisistest20021229444333
B
测试过程
1编写脚本
testinout.sql的内容如下
set colsep '
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
column money format a6 truncate
column id format a10 truncate
spool d:\iii
select to_char(id,'0000000009S') as
id,username,to_char(date1,'yyyymmdd') as
date1,to_char(money*100,'000000s') as money from testinout;
spool off
exit;
2 执行脚本
sqlplus user1/user1@my8i @d:\testinout.sql
或
sqlplus user1/user1 @d:\testinout.sql>d:\t
3 d:\iii.lst的内容如下
0000000001test 20011229003333
0000000002ddxxkk 20021229055500
1234567890thisistest20021229444333
1234567890thisi汉字t200
