sql*plus中怎么格式化输出

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:\drop_table.sql

SELECT 'DROP TABLE '|| table_name ||'' FROM user_tables

1．使用SQL*PLUS动态生成批量脚本

将spool与select命令结合起来使用，可以生成一个脚本，脚本中包含有可以批量执行某一任务的语句。

例1：

生成一个脚本，删除SCOTT用户下的所有的表：

a. 创建gen_drop_table.sql文件，包含如下语句：

SPOOL c:\drop_table.sql

SELECT 'DROP TABLE '|| table_name ||'' FROM user_tables

SPOOL OFF

b. 以SCOTT用户登录数据库

SQLPLUS >@ …..\gen_dorp_table.sql

c. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:

'DROPTABLE'||TABLE_NAME||''

--------------------------------------------------------------------------------

DROP TABLE DEPT

DROP TABLE EMP

DROP TABLE PARENT

DROP TABLE STAT_VENDER_TEMP

DROP TABLE TABLE_FORUM

5 rows selected.

SQL>SPOOL OFF

d. 对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下drop table …..语句

e. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。

SQLPLUS >@ c:\dorp_table.sql

在上面的 *** 作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的 *** 作带来诸多不便。懒惰是人的本性，这促使我们用更简单的办法来实现上面的任务。

a. 创建gen_drop_table.sql文件，包含如下语句：

set echo off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:\drop_table.sql

SELECT 'DROP TABLE '|| table_name ||'' FROM user_tables

SPOOL OFF

b. 以SCOTT用户登录数据库

SQLPLUS >@ …..\gen_dorp_table.sql

c. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:

DROP TABLE DEPT

DROP TABLE EMP

DROP TABLE PARENT

DROP TABLE STAT_VENDER_TEMP

DROP TABLE TABLE_FORUM

d. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。

SQLPLUS >@ c:\dorp_table.sql

2．将一个表中的数据导出生成一个文本文件，列与列之间以”,”隔开

set echo off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:\drop_table.sql

select DEPTNO || ',' || DNAME FROM DEPT

SPOOL OFF

将上面的内容保存为一个文本文件后，以scott登录，执行该文件后显示结果：

10,ACCOUNTING

20,RESEARCH

30,SALES

40,OPERATIONS

通过上面的两个例子，我们可以将：

set echo off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:\具体的文件名

你要运行的sql语句

SPOOL OFF

作为一个模版，只要将必要的语句假如这个模版就可以了。

在oracle的较新版本中，还可以用set colsep命令来实现上面的功能：

SQL>set colsep ,

SQL>select * from dept

10,ACCOUNTING ,NEW YORK

20,RESEARCH ,DALLAS

30,SALES ,CHICAGO

40,OPERATIONS ,BOSTON

35,aa ,bb

3．动态生成spool命令所需的文件名

在我们上面的例子中，spool命令所需要的文件名都是固定的。有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？

column dat1 new_value filename

select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual

spool c:\&&filename..txt

select * from dept

spool off

4．如何从脚本文件中得到WINDOWS环境变量的值：

在windos中：

spool c:\temp\%ORACLE_SID%.txt

select * from dept

...

spool off

在上面的例子中，通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt

在UNIX中：

spool c:\temp\$ORACLE_SID.txt

select * from dept

...

spool off

在上面的例子中，通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt

5．如何指定缺省的编辑脚本的目录

在sql*plus中，可以用save命令，将上一条执行的sql语句保存到一个文件中，但是如何设置该文件的缺省目录呢？

通过SQL>set editfile c:\temp\file.sql 命令，可以设置其缺省目录为c:\tmpe，缺省文件名为file.sql。

6．如何除去表中相同的行

找到相同的行：

SELECT * FROM dept a

WHERE ROWID <>(SELECT MAX(ROWID)

FROM dept b

WHERE a.deptno = b.deptno

AND a.dname = b.dname -- Make sure all columns are compared

AND a.loc = b.loc)

注释：

如果只找deptno列相同的行，上面的查询可以改为：

SELECT * FROM dept a

WHERE ROWID <>(SELECT MAX(ROWID)

FROM dept b

WHERE a.deptno = b.deptno)

删除相同的行：

DELETE FROM dept a

WHERE ROWID <>(SELECT MAX(ROWID

FROM dept b

WHERE a.deptno = b.deptno

AND a.dname = b.dname -- Make sure all columns are compared

AND a.loc = b.loc)

注意：上面并不删除列值为null的行。

7．如何向数据库中插入两个单引号(’’)

Insert inot dept values(35,’aa’’’’bb’,’a’’b’)

在插入时，用两个’表示一个’。

8．如何设置sql*plus的搜寻路径，这样在用@命令时，就不用输入文件的全路径。

设置SQLPATH环境变量。

如：

SQLPATH = C:\ORANT\DBSC:\APPS\SCRIPTSC:\MYSCRIPTS

9．@与@@的区别是什么？

@等于start命令，用来运行一个sql脚本文件。

@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。

@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。

10．&与&&的区别

&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。

&&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。

如，将下面三行语句存为一个脚本文件，运行该脚本文件，会提示三次，让输入deptnoval的值：

select count(*) from emp where deptno = &deptnoval

select count(*) from emp where deptno = &deptnoval

select count(*) from emp where deptno = &deptnoval

将下面三行语句存为一个脚本文件，运行该脚本文件，则只会提示一次，让输入deptnoval的值：

select count(*) from emp where deptno = &deptnoval

select count(*) from emp where deptno = &deptnoval

select count(*) from emp where deptno = &deptnoval

11．引入copy的目的

Copy命令在两个数据库之间拷贝数据时特别有用，特别是该命令可以在两个数据库之间传递long型字段的数据。

缺点：

在两个数据库之间传递数据时，有可能丢失精度(lose precision)。

12．问什么在修改大量的行时，我的脚本会变得很慢？

当通过PL/SQL块修改一个表中的许多行时，你会创建在表上创建一个cursor，但是只有在你关闭cursor时，才会释放ROLLBACK SEGMENT，这样，当cursor仍然打开时，修改过程会变慢，这是因为数据库不得不搜寻大量的rollback segment以便于维护读一致性。为了避免这样情况，试着在表上加一个标志字段来描述该行是否已经被修改，然后关闭该cursor，然后再打开该 cursor。每次可以修改5000行.

SPOOL OFF

需要写一个简单的脚本，将所有数据文件、控制文件、在线日志文件等输出到一个文件中，一口气写完之后，发现输出结果中每行之间都有一空行，非常不爽，研究了一下 sqlplus 的设置参数之后解决了问题，把几个常用的参数在这里做一个小结，方便以后查询。

set echo on/off 是否显示脚本中的需要执行的命令

set feedback on/off 是否显示 select 结果之后返回多少行的提示

set linesize n 设置一行最多显示多少字符，之前就是因为 n 设得过大，导致行与行之间有空白行

set termout on/off 在执行脚本时是否在屏幕上输出结果，如果 spool 到文件可以将其关闭

set heading on/off 是否显示查询结果的列名，如果设置为 off，将用空白行代替，如果要去除该空白行，可以用 set pagesize 0

set pagesize n设置每页的行数，将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting information

set trimspool on/off 在 spool 到文件时是否去除输出结果中行末尾的空白字符，之前的隔行可以用该参数去掉，和该参数对应的是 trimout，后者用于屏幕输出

set trimout on/off 是否去掉屏幕上输出结果行末尾的空白字符

SQL>select * from aa

BB CC

---------- ----------

1 999999.99

SQL>col bb format ''''99.9''''

SQL>select * from aa

BB CC

----- ----------

1.0 999999.99

SQL>col bb head ''''99.9''''

SQL>select * from aa

99.9 CC

----- ----------

1.0 999999.99

SQL>col bb head ''''学号''''

SQL>select * from aa

学号 CC

----- ----------

1.0 999999.99

SQL>col bb format ''''99''''

SQL>select * from aa

学号 CC

---- ----------

1 999999.99

SQL>col bb head ''''学|号''''

SQL>select * from aa

学

号 CC

--- ----------

1 999999.99

SQL>select count(bb),sum(cc) from aa

COUNT(BB)SUM(CC)

---------- ----------

8192 8191999918

SQL>col sum(cc) format ''''9999999999.99''''

SQL>select count(bb),sum(cc) from aa

COUNT(BB)SUM(CC)

---------- --------------

8192 8191999918.08

SQL>col sum(cc) format ''''9999999999.99'''' head ''''总分''''

SQL>col count(bb) format ''''999999'''' head ''''人数''''

SQL>select count(bb),sum(cc) from aa

人数 总分

------- --------------

8192 8191999918.08

to_date('日期类型字符串','要转化的日期类型')函数进行日期格式转换

sql：select

to_date('1990-12-12

12:12:32','yyyy-MM-dd

hh24:mi:ss')

from

dual

解释：此语句的输出结果就是

“1990-12-12

12:12:32”（日期类型）。后面的日期类型格式可以根据实际需要修改

如改为：select

to_date('1990-12-12

12:12:32','yyyy-MM-dd

)

from

dual输出结果就是“1990-12-12”.