Vb教程 Vb.net教程 Vfp教程 C/C++教程 Vc/Vc++教程 Delphi教程 Java教程 Powerbuilder
  杀毒频道 | 短信频道 | 网络电视 | 论文中心 | 学上网 | 学软件 | 网页特效 | 电脑基础 | 论坛  
  NCRE | 软考 | CET | 职称英语 | 司法考试 | 报关员 | 公务员 | CATTI | CPA考试  
  Html教程 | Css教程 | Xml教程 | Asp教程 | Asp.net | Php教程 | Jsp教程 | Linux教程 | QQ技巧  
Photoshop Illustrator ImageReady Maya教程 3D Max教程 Lightscape Coredraw教程 Authorware Autocad教程 Freehand教程
Access教程 Mysql教程 Sql server Oracle教程 Word教程 Excel教程 Powerpoint Frontpage Asp.net源码 Php源代码
Flash教程 Fireworks Dreamweaver C#教程 outlook教程 系统安装 vbscript教程 Javascript Jsp源代码 Asp源代码
您的位置:首页 >> Oracle教程 >> 正文

sql*plus使用的一些技巧

文章来源:pconline 作者:SunnyXu

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

[返回]

编程语言 web开发 数据库 网络技术 操作系统 服务器 网页设计 图形设计 办公软件 常用软件 学电脑

Copyright© www.bianceng.cn Powered by 编程入门网 All Rights Reserved.
关于本站 | 版权声明 | 联系我们 | 友情链接 |
编程入门网 版权所有