sql*plus使用的一些技巧

原创 2005年04月28日 14:42:00

阅读本文档之前,建议先看” http://www.cnoug.org/viewthread.php?tid=16867“一文,

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 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;
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行.

SQL*PLUS使用技巧

在ORACLE的维护过程中,目前ORACLE有很多易用的客户端工具,如PL/SQL、DEVELOPER、TOAD,但作为数据库管理员也需要经 常和SQLPLUS 打交道,一些常用的操作往往需要在SQL...

sql*plus 中输出格式控制技巧

原文地址 http://blog.yesky.com/38/eric1945/1663038.shtml sql*plus是我们常用的工具,大家也许对此工具感到一个困惑的地方就是...

SQL*PLUS使用(一)

学习资料:                    1:WIKI                    2: Oracle document                    3:  sql-...
  • topwqp
  • topwqp
  • 2013年05月26日 23:01
  • 3428

使用SQL*Plus操作数据库,当出现Error Message时,如何在简体中文和英文之间切换?

背景: Oracle DBA登录后台数据库进行管理操作时,一般都是使用SQL*Plus客户端工具进行,如果操作出现ora类型的Error Message时,有的人想看简体中文错误提示,有的人想看纯英...
  • chhuma
  • chhuma
  • 2011年10月07日 10:09
  • 1421

使用SQL*Plus

两种启动SQL*Plus方法: 第一种:在运行里面输入sqlplus命令便可启动,然后输入用户,例如:scott/tiger,便连上了; 第二种:在oracle开发工具里面单击sqlplus工具,...

SQL*PLUS命令的使用大全(详细)

SQL*PLUS命令的使用大全(详细)    Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中,可以运行sql*plus命令与sql*plus语句。    我们...
  • wgz721
  • wgz721
  • 2011年05月05日 20:58
  • 283

Oracle基础(一):oracle实例与sql*plus命令使用

一、oracle 数据库的三层结构 1、客户端-Client:专门用于访问数据库。如sqlplus、pl/sql dev等 2、数据库服务器-oracle dbms(database manageme...

Oracle SQL plus环境设置与使用

目录 glogin.sql配置替代变量08、定制SQL.PLUS环境spool用法 glogin.sql配置 配置文件位置:$ORACLE_HOME/sqlplus/admin/gl...

[Oracle]SQL*PLUS命令的使用大全

一、DataBase   保存数据,以表的形式表现数据   二、SQL   Structur query language    结构化查询语言,是操作关系型数据库中的对象。     ...

Oracle杂谈二 SQL*PLUS命令的使用大全

Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中,可以运行sql*plus命令与sql*plus语句。        我们通常所说的DML、DDL、DCL语...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql*plus使用的一些技巧
举报原因:
原因补充:

(最多只允许输入30个字)