Sql*plus中蕴藏着好多技巧如果掌握这些技巧对于在oracle数据库下进行快速开发与有效维护数据库都是有益的
．使用SQL*PLUS动态生成批量脚本
将spool与select命令结合起来使用可以生成一个脚本脚本中包含有可以批量执行某一任务的语句
例
生成一个脚本删除SCOTT用户下的所有的表
a 创建gen_drop_tablesql文件包含如下语句
SPOOLc:\drop_tablesql
SELECT DROP TABLE || table_name ||; FROM user_tables;
SPOOL OFF
b 以SCOTT用户登录数据库
SQLPLUS > @ …\gen_dorp_tablesql
c 在c盘根目录下会生成文件drop_tablesql文件包含删除所有表的语句如下所示:
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;
rows selected
SQL>SPOOL OFF
d 对生成的drop_tablesql文件进行编辑去掉不必要的部分只留下drop table …语句
e 在scott用户下运行dorp_tablesql文件删除scott用户下所有的表
SQLPLUS > @ c:\dorp_tablesql
在上面的操作中在生成的脚本文件中会有多余的字符如运行的sql语句标题或返回的行数需要我们编辑该脚本后再运行给实际的操作带来诸多不便懒惰是人的本性这促使我们用更简单的办法来实现上面的任务
a 创建gen_drop_tablesql文件包含如下语句
set echo off
set feedback off
set newpage none
set pagesize
set linesize
set verify off
set pagesize
set term off
set trims on
set linesize
set headingoff
set timing off
set verify off
set numwidth
SPOOLc:\drop_tablesql
SELECT DROP TABLE || table_name ||; FROM user_tables;
SPOOL OFF
b 以SCOTT用户登录数据库
SQLPLUS > @ …\gen_dorp_tablesql
c 在c盘根目录下会生成文件drop_tablesql文件包含删除所有表的语句如下所示:
DROP TABLE DEPT;
DROP TABLE EMP;
DROP TABLE PARENT;
DROP TABLE STAT_VENDER_TEMP;
DROP TABLE TABLE_FORUM;
d 在scott用户下运行dorp_tablesql文件删除scott用户下所有的表
SQLPLUS > @ c:\dorp_tablesql
．将一个表中的数据导出生成一个文本文件列与列之间以隔开
set echo off
set feedback off
set newpage none
set pagesize
set linesize
set verify off
set pagesize
set term off
set trims on
set linesize
set headingoff
set timing off
set verify off
set numwidth
SPOOLc:\drop_tablesql
select DEPTNO || || DNAME FROM DEPT;
SPOOL OFF
将上面的内容保存为一个文本文件后以scott登录执行该文件后显示结果
ACCOUNTING
RESEARCH
SALES
OPERATIONS
通过上面的两个例子我们可以将
set echo off
set feedback off
set newpage none
set pagesize
set linesize
set verify off
set pagesize
set term off
set trims on
set linesize
set headingoff
set timing off
set verify off
set numwidth
SPOOLc:\具体的文件名
你要运行的sql语句
SPOOL OFF
作为一个模版只要将必要的语句假如这个模版就可以了
在oracle的较新版本中还可以用set colsep命令来实现上面的功能
SQL> set colsep
SQL> select * from dept;
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALES CHICAGO
OPERATIONSBOSTON
aabb
．动态生成spool命令所需的文件名
在我们上面的例子中spool命令所需要的文件名都是固定的有时我们需要每天spool一次并且每次spool的文件名都不相同如文件名包含当天的日期该如何实现呢？
column dat new_value filename;
select to_char(sysdateyyyymmddhhmi) dat from dual;
spool c:\&&filenametxt
select * from dept;
spool off;
．如何从脚本文件中得到WINDOWS环境变量的值
在windos中
spool c:\temp\%ORACLE_SID%txt
select * from dept;
spool off
在上面的例子中通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值如果ORACLE_SID的值为orcl则生成的spool文件名为orcltxt
在UNIX中
spool c:\temp\$ORACLE_SIDtxt
select * from dept;
spool off
在上面的例子中通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值如果ORACLE_SID的值为orcl则生成的spool文件名为orcltxt
．如何指定缺省的编辑脚本的目录
在sql*plus中可以用save命令将上一条执行的sql语句保存到一个文件中但是如何设置该文件的缺省目录呢？
通过SQL> set editfile c:\temp\filesql 命令可以设置其缺省目录为c:\tmpe缺省文件名为filesql
．如何除去表中相同的行
找到相同的行
SELECT * FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE adeptno = bdeptno
AND adname = bdname Make sure all columns are compared
AND aloc = bloc);
注释
如果只找deptno列相同的行上面的查询可以改为
SELECT * FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE adeptno = bdeptno)
删除相同的行
DELETE FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID
FROM dept b
WHERE adeptno = bdeptno
AND adname = bdname Make sure all columns are compared
AND aloc = bloc);
注意上面并不删除列值为null的行
．如何向数据库中插入两个单引号()
Insert inot dept values(aabbab);
在插入时用两个表示一个
．如何设置sql*plus的搜寻路径这样在用@命令时就不用输入文件的全路径
设置SQLPATH环境变量
如
SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS
．@与@@的区别是什么？
@等于start命令用来运行一个sql脚本文件
@命令调用当前目录下的或指定全路径或可以通过SQLPATH环境变量搜寻到的脚本文件
@@用在脚本文件中用来指定用@@执行的文件与@@所在的文件在同一目录而不用指定全路径也不从SQLPATH环境变量指定的路径中寻找文件该命令一般用在嵌套脚本文件中
．&与&&的区别
&用来创建一个临时变量每当遇到这个临时变量时都会提示你输入一个值
&&用来创建一个持久变量就像用用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;
．引入copy的目的
Copy命令在两个数据库之间拷贝数据时特别有用特别是该命令可以在两个数据库之间传递long型字段的数据
缺点
在两个数据库之间传递数据时有可能丢失精度(lose precision)
．问什么在修改大量的行时我的脚本会变得很慢？
当通过PL/SQL块修改一个表中的许多行时你会创建在表上创建一个cursor但是只有在你关闭cursor时才会释放ROLLBACK SEGMENT这样当cursor仍然打开时修改过程会变慢这是因为数据库不得不搜寻大量的rollback segment以便于维护读一致性为了避免这样情况试着在表上加一个标志字段来描述该行是否已经被修改然后关闭该cursor然后再打开该cursor每次可以修改行
