DU——X 而如果设为off,则显示如下:SQL> set ver off;SQL> select * from dual where 1=&var;Enter value for var: 1
/ D5 m5 N/ B! ^) h6 @6 j DU——X使用以&和&&开头的替代变量的前提是set define on;(不过这是缺省状态)。
9 @+ m( x7 f3 a6 E& B3 P n 2. set feedback on/off回显本次sql命令处理的记录条数,缺省为on SQL> select * from test where id = 1;NAME ID
- j% b/ Q" z0 f2 v% \$ r: Z---------- ----------
& e( S* S7 p+ z8 R* z, } a 1 b 1已选择2行。
! G8 V6 Y, Q! W& b7 c) m SQL> set feedback off;SQL> / NAME ID
6 n) q. a4 Z' p# _1 Y0 J* p; l---------- ----------
" f; Y$ W( V( J a 1 b 1 SQL> set feedback on;SQL> / NAME ID
5 t- j5 }. N/ `- ?& ~6 d+ N) Q---------- ----------5 }$ C3 c" V8 ]6 R, n
a 1 b 1已选择2行。2 m; T5 n" p6 P" q l, N
3. set timing on SQL> set timing on SQL> select * from v$version;7 R$ ]" f0 Z K/ x% V4 O
BANNER, ]7 O j1 r4 W' w. L2 ]* P
----------------------------------------------------------------# i. I& Z$ d* M3 R6 F# `
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production已用时间: 00: 00: 00.008 B0 B$ K+ A) f E% ?
14.修改sql buffer中的当前行中,第一个出现的字符串C[HANGE] /old_value/new_value SQL> l 1* select * from dept SQL> c/dept/emp 1* select * from emp
& y8 C6 H0 f! ^/ q! { 15.编辑sql buffer中的sql语句SQL>EDI[T]
) a5 f* J' G5 F+ G3 J: M 16.显示sql buffer中的sql语句,list n显示sql buffer中的第n行,并使第n行成为当前行L[IST] [n]
; J& m) D- t4 v+ E- V. x* Q 17.在sql buffer的当前行下面加一行或多行I[NPUT]# x, O8 x- n- ^! @/ ?/ |
18.将指定的文本加到sql buffer的当前行后面A[PPEND]
8 c2 F0 f+ R+ Y4 V SQL> select deptno,dname from scott.dept;DEPTNO DNAME
2 h0 ]" u# ?# F1 j---------- --------------) |3 y1 D1 E1 G7 v
10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS SQL> l 1* select deptno,dname from scott.deptloc, loc SQL> a , dmm 1* select deptno,dname from scott.deptloc, loc, dmm
) s$ }! X5 ~, [5 {) a" k8 B 19.将sql buffer中的sql语句保存到一个文件中SAVE file_name0 g) ]1 b+ [, r) O' c
20.将一个文件中的sql语句导入到sql buffer中GET file_name
1 a" K& J g& I+ K C: t" k 21.再次执行刚才已经执行的sql语句RUN or /" j6 V# T& p, |6 [. K
22.执行一个存储过程EXECUTE procedure_name
) K4 `! D- {' k, n 23.在sql*plus中连接到指定的数据库CONNECT user_name/passwd@db_alias
; U& C" X5 I# P/ }+ G6 ~& z 24.设置每个报表的顶部标题TTITLE
# {, |2 ^$ M, ~$ h {" I5 [0 ^1 f# K 25.设置每个报表的尾部标题BTITLE: X1 ]0 v; J1 ]6 v% T/ J
26.写一个注释REMARK [text]
% l" L3 H1 N- W9 V" J. o1 n% P1 f 27.将指定的信息或一个空行输出到屏幕上PROMPT [text]3 R# l2 g* Z( W2 c# w" j$ N' M3 j
28.将执行的过程暂停,等待用户响应后继续执行PAUSE [text] Sql>PAUSE Adjust paper and press RETURN to continue.6 A" @8 _) c% R. b
29.将一个数据库中的一些数据拷贝到另外一个数据库(如将一个表的数据拷贝到另一个数据库)$ K) ?5 {# Q( r3 R0 `% @) g" [" ]4 \
COPY {FROM database | TO database } {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ……)] USING query sql>COPY FROM SCOTT/TIGER@A TO DAVE/ADMIN@B create emp_temp USING SELECT * FROM EMP
2 Y% y( j2 l0 M) d4 z2 Q 30.不退出sql*plus,在sql*plus中执行一个操作系统命令:HOST Sql> host hostname该命令在windows下可能被支持。% T1 _1 }9 y3 J! N9 W
31.在sql*plus中,切换到操作系统命令提示符下,运行操作系统命令后,可以再次切换回Linux 下是:!# b' a4 C% Y% q+ R: m! f! f
Windows 是:$ sql>!
8 I; ]# x% X" T6 w3 [7 @- D $hostname $exit sql>该命令在windows下不被支持。
V+ o3 R+ v' t9 c" G* P+ X, ? 32.显示sql*plus命令的帮助HELP Sql>help index; K( ~ R/ b& y3 q2 I7 Y. _" a
33.显示sql*plus系统变量的值或sql*plus环境变量的值SQL> help show Shows the value of a SQL*Plus system variable, or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login.
% ~$ t: O, {" v5 R$ n SHO[W] option, K' B+ @4 ^: }# z+ {9 k k: n6 x' j
where option represents one of the following terms or clauses:system_variable ALL BTI[TLE] ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name] LNO PARAMETERS [parameter_name] PNO RECYC[LEBIN] [original_name] REL[EASE] REPF[OOTER] REPH[EADER] SGA SPOO[L] SQLCODE TT[ITLE] USER
# P! L8 k4 Z8 y& n4 g SHOW SPOOL is not available in iSQL*Plus
9 f/ b- |; u, U" x1 Y2 _/ G 13.1 . 显示当前环境变量的值:Show all
& v! z# L" Q! Z$ P A3 s) }( M L 13.2 . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息Show error当创建一个函数、存储过程等出错时,变可以用该命令查看在那个地方出错及相应的出错信息,进行修改后再次进行编译。
% o/ Q2 S8 y- a( o6 b# E 13.3 . 显示初始化参数的值:show PARAMETERS [parameter_name]
5 f" K( b7 T2 g 13.4 . 显示数据库的版本:show REL[EASE], h2 ]* k9 c4 X: G- |& U9 r6 j. j/ J
13.5 . 显示SGA的大小show SGA
, ]1 d2 }& F# ~/ _5 G 13.6. 显示当前的用户名show user
3 v: H7 b# k2 C/ c 14.使用SQL*PLUS动态生成批量脚本将spool与select命令结合起来使用,可以生成一个脚本,脚本中包含有可以批量执行某一任务的语句。( E6 J2 J) B9 J0 d6 M
例1:生成一个脚本,删除SCOTT用户下的所有的表:a. 创建gen_drop_table.sql文件,包含如下语句:SPOOL d:/drop_table.sql SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;SPOOL OFF
$ h8 C9 A& n/ G! E1 j- Q b. 以SCOTT用户登录数据库:SQLPLUS > @ d:/drop_table.sql c. 在c盘根目录下会生成文件drop_table.sql文件,包含删除所有表的语句,如下所示:SQL>SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;'DROPTABLE'||TABLE_NAME||';') l* N7 O. d; ~- @
-----------------------------
: F7 q" z' d7 U1 |) h0 `/ ^ 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.
0 W3 E, V" Q' f) h4 g" z6 k 在上面的操作中,在生成的脚本文件中会有多余的字符,如运行的SQL语句,标题,或返回的行数,需要我们编辑该脚本后再运行,给实际的操作带来诸多不便。懒惰是人的本性,这促使我们用更简单的办法来实现上面的任务。3 x/ R4 r, G7 Y
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;
* n( ?; Q$ D9 e4 U d. 在scott用户下运行dorp_t:able.sql文件,删除scott用户下所有的表。SQLPLUS > @ c:dorp_table.sql8 _! _; L$ \ h* D+ j
例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.作为一个模版,只要将必要的语句假如这个模版就可以了。& \0 M: B, C5 d: c0 T& L
在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
' j, Q( ~+ w6 u7 }" r* \& t( B3 h 例3.动态生成spool命令所需的文件名。! g. t7 ~" o/ @7 W4 q) X5 i
在我们上面的例子中,spool命令所需要的文件名都是固定的。有时我们需要每天spool一次,并且每次spool的文件名都不相同,如文件名包含当天的日期,该如何实现呢?
! Q7 h0 Q) R7 G ` column dat1 new_value filename;select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;spool c:&&filename……txt select * from dept;spool off;. K d1 f4 p; ]. u$ }: r# e
15.如何从脚本文件中得到WINDOWS环境变量的值:在windos中:spool c:/temp%ORACLE_SID%.txt select * from dept;……2 f! U7 c- ~& ?, l0 w6 H& O
spool off在上面的例子中,通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值,如果ORACLE_SID的值为orcl,则生成的spool文件名为:orcl.txt.在UNIX中:spool temp$ORACLE_SID.txt select * from dept;……
* w" E7 a6 H! I5 E% d' Z spool off在上面的例子中,通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值,如果ORACLE_SID的值为orcl,则生成的spool文件名为:orcl.txt.
4 I" W" M6 f+ A# T' u 16.如何指定缺省的编辑脚本的目录:在sql*plus中,可以用save命令,将上一条执行的sql语句保存到一个文件中,但是如何设置该文件的缺省目录呢?; N3 @/ g E: i+ R( x
通过SQL> set editfile c:/temp/file.sql 命令,可以设置其缺省目录为c:/tmp,缺省文件名为file.sql. s& E) Y& N! [4 [
17.如何除去表中相同的行:找到相同的行:SELECT * FROM dept a WHERE ROWID (SELECT MAX(ROWID)
0 h7 S; @) o0 g( ^& v 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)7 X8 S3 J1 t( i: g
FROM dept b WHERE a.deptno = b.deptno)
4 @2 M) ^* H: |; \( F 删除相同的行: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的行。' ~2 g. n0 R8 E6 d/ _! Z
18.如何向数据库中插入两个单引号(‘’):Insert inot dept values(35,‘aa’‘’‘bb’,‘a’‘b’);。: R! k+ T4 z% K. v- ~$ H x
在插入时,用两个‘表示一个’。
6 s! R$ [ _3 U- P! b/ ^ 19.如何设置sql*plus的搜寻路径,这样在用@命令时,就不用输入文件的全路径。% f4 p+ c* d+ h/ M, q
设置SQLPATH环境变量。
; a+ t+ h5 I; B! e% S: v2 \1 r 如:SQLPATH = C:/ORANTDBS;C:/MYSCRIPTS( N( t L8 q: l. ~( \6 y! |
20.@与@@的区别是什么?
# J" W |& ?% @( n# b+ s& ]' E @等于start命令,用来运行一个SQL脚本文件。0 Q4 e, I) X7 O- [) Z( [0 S6 c; c
@命令调用当前目录下的,或指定全路径,或可以通过SQLPATH环境变量搜寻到的脚本文件。
3 I4 D7 _/ p" I% ?1 c% U$ Q# ?. q! N @@用在脚本文件中,用来指定用@@执行的文件与@@所在的文件在同一目录,而不用指定全路径,也不从SQLPATH环境变量指定的路径中寻找文件,该命令一般用在嵌套脚本文件中。# @) o8 Y; \' b: e
21.&与&&的区别&用来创建一个临时变量,每当遇到这个临时变量时,都会提示你输入一个值。/ O s5 ]4 j7 `7 O9 ]# U
&&用来创建一个持久变量,就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&&命令引用这个变量时,不会每次遇到该变量就提示用户键入值,而只是在第一次遇到时提示一次。
( z9 I8 S9 n3 \& a$ k% ?- x 如,将下面三行语句存为一个脚本文件,运行该脚本文件,会提示三次,让输入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; |