sql*plus使用的一些技巧


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
将上面的内容保存为一个文本文件后，以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

[1] [2]  下一页