(Oracle管理)ORACLE数据库sqlplus使用技巧

Sql*plus中蕴藏着好多技巧，如果掌握这些技巧，对于在oracle数据库下进行快速开发与有效维护数据库都是有益的。

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

例1：生成一个脚本，删除SCOTT用户下的所有的表：a. 创建gen_drop_table.sql文件，包含如下语句：SPOOL c:\drop_table.sqlSELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;SPOOL OFFb. 以SCOTT用户登录数据库SQLPLUS > @ …..\gen_dorp_table.sqlc. 在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 STA T_VENDER_TEMP;DROP TABLE TABLE_FORUM;5 rows selected.SQL>SPOOL OFFd. 对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下drop table …..语句e. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。

SQLPLUS > @ c:\dorp_table.sql在上面的操作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的操作带来诸多不便。

懒惰是人的本性，这促使我们用更简单的办法来实现上面的任务。

a. 创建gen_drop_table.sql文件，包含如下语句：set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading offset timing offset verify offset numwidth 38SPOOL c:\drop_table.sqlSELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;SPOOL OFFb. 以SCOTT用户登录数据库SQLPLUS > @ …..\gen_dorp_table.sqlc. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示: DROP TABLE DEPT;DROP TABLE EMP;DROP TABLE PARENT;DROP TABLE STA T_VENDER_TEMP;DROP TABLE TABLE_FORUM;d. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。

SQLPLUS > @ c:\dorp_table.sql2．将一个表中的数据导出生成一个文本文件，列与列之间以”,”隔开set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading offset timing offset verify offset numwidth 38SPOOL c:\drop_table.sqlselect DEPTNO || ',' || DNAME FROM DEPT;SPOOL OFF将上面的内容保存为一个文本文件后，以scott登录，执行该文件后显示结果：10,ACCOUNTING20,RESEARCH30,SALES40,OPERATIONS通过上面的两个例子，我们可以将：set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading offset timing offset verify offset numwidth 38SPOOL c:\具体的文件名你要运行的sql语句SPOOL OFF作为一个模版，只要将必要的语句加入这个模版就可以了。

在oracle的较新版本中，还可以用set colsep命令来实现上面的功能：SQL> set colsep ,SQL> select * from dept;10,ACCOUNTING,NEW YORK20,RESEARCH,DALLAS30,SALES,CHICAGO40,OPERATIONS,BOSTON35,aa,bb3．动态生成spool命令所需的文件名在我们上面的例子中，spool命令所需要的文件名都是固定的。

有时我们需要每天spool 一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？column dat1 new_value filename;select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;spool c:\&&filename..txtselect * from dept;spool off;4．如何从脚本文件中得到WINDOWS环境变量的值：在windos中：spool c:\temp\%ORACLE_SID%.txtselect * from dept;spool off在上面的例子中，通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt在UNIX中：spool c:\temp\$ORACLE_SID.txtselect * from dept;...spool off在上面的例子中，通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt5．如何指定缺省的编辑脚本的目录在sql*plus中，可以用save命令，将上一条执行的sql语句保存到一个文件中，但是如何设置该文件的缺省目录呢？通过SQL> set editfile c:\temp\file.sql 命令，可以设置其缺省目录为c:\tmpe，缺省文件名为file.sql。

6．如何除去表中相同的行找到相同的行：SELECT * FROM dept aWHERE ROWID <> (SELECT MAX(ROWID)FROM dept bWHERE a.deptno = b.deptnoAND a.dname = b.dname-- Make sure all columns are comparedAND a.loc = b.loc);注释：如果只找deptno列相同的行，上面的查询可以改为：SELECT * FROM dept aWHERE ROWID <> (SELECT MAX(ROWID)FROM dept bWHERE a.deptno = b.deptno)删除相同的行：DELETE FROM dept aWHERE ROWID <> (SELECT MAX(ROWIDFROM dept bWHERE a.deptno = b.deptnoAND a.dname = b.dname -- Make sure all columns are comparedAND 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:\MYSCRIPTS9．@与@@的区别是什么？@等于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型字段的数据。