SQLPLUS命令的使用简单样例(原创)

1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
2. 对当前的输入进行编辑
SQL>edit
3. 重新运行上一次运行的sql语句
SQL>/
4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
   在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5. 关闭spool输出
SQL> SPOOL OFF
   只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构
SQL> desc table_name
7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
     DEPTNO DNAME                        LOC
---------- ---------------------------- ---------
         10 ACCOUNTING                   NEW YORK
sql>col  LOC heading location
sql>select * from dept;
    DEPTNO DNAME                        location
--------- ---------------------------- -----------
        10 ACCOUNTING                   NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
SQL> select * from bitmap_t;

        id NAME
---------- ----------
         2 A
         3 A
         4 A
         1 A
         1 B
SQL> col id heading 'id|_id';
SQL> select * from bitmap_t;

        id
       _id NAME
---------- ----------
         2 A
         3 A
         4 A
         1 A
         1 B
3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
      EMPNO ENAME      JOB        
---------- ----------     ---------
       7369 SMITH      CLERK      
       7499 ALLEN      SALESMAN   
7521 WARD       SALESMAN   
Sql> col ename format a40
      EMPNO ENAME                                    JOB
----------   ----------------------------------------         ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
       7521 WARD                                    SALESMAN
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
      EMPNO           ENAME                   JOB
----------   ----------------------------------------       ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
7521 WARD                                     SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
      EMPNO           ENAME
----------     ----------------------------------------
       7369 SMITH
       7499 ALLEN
7521 WARD
6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name        Salary    Commission
---------- ---------- --------- ----------
30          ALLEN        $1,600    300
7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
        COL1
--------------------
HOW ARE YOU?
  
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?
  
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
  
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A
9). 显示列的当前的显示属性值
SQL> COLUMN column_name
10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS
8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
  WHERE SAL < 2500
  ORDER BY DEPTNO;
DEPTNO      ENAME         SAL
---------- ----------- ---------
10           CLARK        2450
MILLER      1300
20            SMITH       800
ADAMS       1100  
9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n
SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100  
10. 显示对BREAK的设置
SQL> BREAK
11. 删除6、7的设置
SQL> CLEAR BREAKS
12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}
3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {n|ON|OFF}
默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功，命令行会返回类似：Table created的反馈，又比如执行一个pl/sql成功的时候，命令行会返回：PL/SQL procedure successfully completed 。当载脚本中运行很多语句的时候，一般将feedback设为off。
set feedback n:当一条sql语句返回n或者大于n行记录的时候，就会有feedback,比如：set feedback 5,当返回记录等于或多于5行的时候会有类似8 rows selected.的反馈.如果返回行少于5则没有此反馈。
4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
   如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。
7).显示时，用text值代替NULL值
SQL> SET NULL text
8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF}  
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL> SET WRA[P] {ON|OFF}
当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL> SET TERM[OUT] {ON|OFF}
当设置为set termout off的时候就会忽略sql语句的输出如果这条sql是在脚本中运行的话。也就是说如果命令不在脚本中运行，即使设置为set termout off，仍然会返回sql的结果。在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。
12).将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMSPOOL[OUT] {ON|OFF} 
13)显示每个sql语句花费的执行时间
set TIMING  {ON|OFF}
14．修改sql buffer中的当前行中，第一个出现的字符串
C[HANGE] /old_value/new_value
SQL> l
   1* select * from dept
SQL> c/dept/emp
   1* select * from emp
15．编辑sql buffer中的sql语句
EDI[T]
16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行
L[IST] [n]
17．在sql buffer的当前行下面加一行或多行
I[NPUT]
18．将指定的文本加到sql buffer的当前行后面
A[PPEND]
SQL> select deptno,
   2  dname
   3  from dept;
     DEPTNO DNAME
---------- --------------
         10 ACCOUNTING
         20 RESEARCH
         30 SALES
         40 OPERATIONS
  
SQL> L 2
   2* dname
SQL> a ,loc
   2* dname,loc
SQL> L
   1  select deptno,
   2  dname,loc
   3* from dept
SQL> /
     DEPTNO DNAME          LOC
---------- -------------- -------------
         10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON
19．将sql buffer中的sql语句保存到一个文件中
SAVE file_name
20．将一个文件中的sql语句导入到sql buffer中
GET file_name
21．再次执行刚才已经执行的sql语句
RUN
or
/
22．执行一个存储过程
EXECUTE procedure_name
23．在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@db_alias
24．设置每个报表的顶部标题
TTITLE
25．设置每个报表的尾部标题
BTITLE
26．写一个注释
REMARK [text]
27．将指定的信息或一个空行输出到屏幕上
PROMPT [text]
28．将执行的过程暂停，等待用户响应后继续执行
PAUSE [text]
Sql>PAUSE Adjust paper and press RETURN to continue.
29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST  
create emp_temp
USING SELECT * FROM EMP
30．不退出sql*plus，在sql*plus中执行一个操作系统命令：
HOST
Sql> host hostname
该命令在windows下可能被支持。
31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：
!
  
sql>!
$hostname
$exit
sql>
该命令在windows下不被支持。
32．显示sql*plus命令的帮助
HELP
如何安装帮助文件：
Sql>@ ?sqlplusadminhelphlpbld.sql ?sqlplusadminhelphelpus.sql
Sql>help index
33．显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W] option
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} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
1) . 显示当前环境变量的值：
Show all
2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。
3) . 显示初始化参数的值：
show PARAMETERS [parameter_name]
4) . 显示数据库的版本：
show REL[EASE]
5) . 显示SGA的大小
show SGA
6). 显示当前的用户名
show user 

SET DEFINE OFF
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
SET FLUSH OFF
SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data. The gain may be marginal.
Use OFF only when you run a script that does not require user interaction and whose output you do not need to see until the script finishes running.
SET FLUSH is not supported in iSQL*Plus
SET LONGCHUNKSIZE
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.
Experiment with different sizes if LONGS or LOBs are being fetched.
SET SQLPROMPT
Sets the SQL*Plus command prompt.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
SET SQLPROMPT is not supported in iSQL*Plus
SET TAB
Determines how SQL*Plus formats white space in terminal output.
Setting TAB ON causes multiple spaces to be compressed in terminal output. Unless this significantly reduces the written data, the processing required may marginally outweigh any benefit.
SET TAB is not supported in iSQL*Plus

SET APPINFO OFF
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF prevents administrators monitoring the performance and resource usage of scripts.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.

如果需要用SQLPLUS脚本快速导出大量数据的脚本
那么你需要注意以下几点：
一行数据太长也不要换行；
不要在导出文件中出现SQL语句；
不要在窗口中显示查询结果，直接将结果保存到导出文件中，以提高执行速度；
不要在导出文件中出现列的标题行；
不要在导出文件中出现插入分页行；
去掉行尾空格；
不要在导出文件的最后显示：查询得到多少行。
Examda提示方法：
一定要将以下内容保存为sql脚本，然后用@xx.sql去调用该脚本。否则set term off无效，查询结果将被显示在窗口中，大大降低执行速度。
而且要用column命令设置太宽的列的格式。
set linesize 1000
set echo off
set term off
set heading off
set pagesize 0
set trimspool on
set feedback off
col COL1 FORMAT A20
col COL_VERYLONG FORMAT A400
spool ...
select ... from ... where ... order by ...;
spool off

 

参考至:http://www.programfan.com/article/showarticle.asp?id=2915

             http://www.examda.com/ncre/three/db/fudao/20081013/091133450.html
             http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch8.htm
             http://blog.sina.com.cn/s/blog_8c4853c301017om0.html

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]