Sql*plus技巧之SPOOL应用_Oracle教程

Sql*plus中蕴藏着好多技巧，假如把握这些技巧，对于在Oracle数据库下进行快速开发与有效维护数据库都是有益的。下面就介绍一二：　　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文件，包含删除所有表的语句，如下所示:

SQL> SELECT ''''DROP TABLE '''' table_name '''';'''' FROM user_tables;

''''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
　　将上面的内容保存为一个文本文件后，的XM}`S!;网(J以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:tempfile.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\DBS;C:\APPS\SCRIPTS;C:\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; 　　将下面三行语句存为一个脚本文件，:RY7供0管k网[运行该脚本文件，则只会提示一次，让输入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行。