SQLPLUS 命令

定制：sql提示符信息

1、显示SQLPLUS帮助，命令如下：HELP INDEX

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
  /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
  ARCHIVE LOG   EDIT         REMARK                   STORE
  ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
  CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
  CLEAR         HOST         RUN                      WHENEVER OSERROR
  COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
  COMPUTE       LIST         SET
  CONNECT       PASSWORD     SHOW

2、查看相关命令介绍以及语法，命令如下：HELP start

3、@等于start命令，运行sql脚本文件，可以调用当前目录下或指定路径的文件

  

 SQL>start    file_path/file_name
    SQL>@ file_path/file_name
    可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可

4、@@ { url| file_name[.ext] } [arg ...] 

执行位于指定脚本中的SQL*Plus语句。这个命令和@（“at”符号）命令功能差不多。在执行嵌套的命令文件时它很有用，因为它会在与调用它的命令文件相同的路径或url中查找指定的命令文件。

5、重新运行上一次运行的sql语句

   SQL>/

6、编写交互式命令accept

SYS@orcl> accept a prompt 'Please enter values:'
 Please enter values:40
 SYS@orcl> select * from scott.dept where deptno=&a;
 old   1: select * from scott.dept where deptno=&a
 new   1: select * from scott.dept where deptno=40

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

7、a[ppend]  将指定的文本加到sql buffer的当前行后面   

SYS@orcl> del
 SYS@orcl> list;
 SP2-0223: No lines in SQL buffer.
 SYS@orcl> append select count(*) from scott.emp;
SYS@orcl> list;
   1* select count(*) from scott.emp
SYS@orcl> /

  COUNT(*)
----------
        14

8、启动或停止自动归档联机重做日志，手工（显示地）归档指定的重做日志，或者显示重做日志文件的信息

ARCHIVE LOG {LIST|STOP}|{START|NEXT|ALL|integer}[TO destination]

SYS@orcl> archive log list;
 Database log mode              No Archive Mode
 Automatic archival             Disabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     5
 Current log sequence           7

9、屏蔽掉一个列中显示的相同的值    BREAK ON break_column

  

SYS@orcl> break on deptno
   SYS@orcl> select deptno,ename,sal from scott.emp;

  显示对BREAK的设置
  SQL> break

  删除BREAK的设置
  SQL> clear breaks

10、BTI[TLE] [printspec [text|variable] ...]|[ON|OFF] 设置格式化页脚

在每个报表页的底部放置一个标题并对其格式化，或者列出当前BTITLE定义。使用以下子句之一替换printspec。
COL n
 LE[FT]
 BOLD
 S[KIP] [n]
 CE[NTER]
 FORMAT text
 TAB n
 R[IGHT]

SYS@orcl> btitle col 5 deptno
 SYS@orcl> select deptno,ename,sal from scott.emp;
 SYS@orcl> btitle off

11、C[HANGE] sepchar old [sepchar [new [sepchar]]]

更改在SQL缓冲区当前行中第一次出现的指定文本。可使用任何非文字数字字符（例如“/”或“!”）作为sepchar。CHANGE和第一个sepchar之间的空格可省略。
修改sql buffer中的当前行中，第一个出现的字符串  C[HANGE] /old_value/new_value
SYS@orcl> change /scott/dd
   1* select * from dd.dept
从行中删除文本
SYS@orcl> change /dd.
   1* select * from dept

12、重置当前值 CL[EAR] option ...

option 可以是：

     BRE[AKS]
      BUFF[ER]
      SCR[EEN]

从SQL缓冲区删除所有行
SYS@orcl> list
   1* select * from dept
 SYS@orcl> clear buffer

清屏
SYS@orcl> clear screen

13、COL命令：格式化列的显示形式

    1). 改变缺省的列标题

 

      COLUMN column_name HEADING column_heading

        SYS@orcl> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

SYS@orcl> col loc heading location
 SYS@orcl> select * from scott.dept;

    DEPTNO DNAME          location
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

   2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上

   

    如：COLUMN ENAME HEADING ’Employee|Name’
      SYS@orcl> column dname heading 'a|name'
       SYS@orcl> select * from scott.dept;

           a
     DEPTNO name           location
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

    3). 改变列的显示长度

  

     如：col 列名 format a40

    4). 设置列标题的对齐方式 JUS[TIFY] {L[EFT]|C[ENTRE]|R[IGHT]}

       如：col 列名 justify center
       SYS@orcl> column dname justify right
       SYS@orcl> select * from scott.dept;                        a
     DEPTNO           name location
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO

   5). 不让一个列显示在屏幕上 NOPRI[NT]|PRI[NT]

       如：col 列名 noprint

       SYS@orcl> column deptno noprint
       SYS@orcl> select * from scott.dept;

             a
           name location
-------------- -------------
ACCOUNTING     NEW YORK
 RESEARCH       DALLAS
 SALES          CHICAGO

       

    6). 格式化NUMBER类型列的显示（9999.99，999 999.0，$999 999.00, 0000.00,aXX,yyyy-mm-dd hh24:mi:ss)

       如：COLUMN 列名 FORMAT $99,990

    7). 显示列值时，如果列值为NULL值，用text值代替NULL值

       如：COL 列名 NUL[L] text

SYS@orcl> create table test (id number,name char(3));

Table created.

SYS@orcl> insert into test(id) values(1);

1 row created.

SYS@orcl> commit;

Commit complete.

SYS@orcl> col name null wl
SYS@orcl> select  *from test;

        ID NAM
---------- ---
         1 wl

   8). 将所有列的显示属性设为缺省值

        SQL> CLEAR COLUMNS

14、如果已经使用了break命令将报告的行分为了集，可以对每个子集执行计算。compute

SYS@orcl> break on department_id
SYS@orcl> compute sum of salary on department_id
SYS@orcl> select department_id,salary from hr.emp_details_view;

15、连接到数据库connect

SYS@orcl> connect system/oracle

16、定义用户变量 DEFINE

SYSTEM@orcl> define a=40
SYSTEM@orcl> select * from scott.dept where deptno=&a;
old   1: select * from scott.dept where deptno=&a
new   1: select * from scott.dept where deptno=40

删除变量
SYSTEM@orcl> undefine a

17、删除当前行

    del
    del n
    del m n

SYSTEM@orcl> select *from scott.dept;
SYSTEM@orcl> list
   1* select *from scott.dept
SYSTEM@orcl> input select * from scott.emp;
SYSTEM@orcl> list
   1  select *from scott.dept
   2* select * from scott.emp
SYSTEM@orcl> del 1
SYSTEM@orcl> list
   1* select * from scott.emp

18、显示一个表的结构  DESCRIBE

    SQL> desc table_name

19、断开连接 disconnect

SYSTEM@orcl> DISCONNECT

20、对当前的输入进行编辑

    SQL>edit

21、执行存储过程 EXECUTE

22、退出sqlplus使用EXIT

23、将文件的sql语句导入到sql buffer中 get file_name

SYS@orcl> get afiedt.buf

24、不退出sql*plus，在sql*plus中执行一个操作系统命令：

    HOST

25、在sql buffer的当前行下面加一行或多行   i[nput]

SYS@orcl> list
   1* select * from scott.emp
SYS@orcl> input select *from dual;
SYS@orcl> list
   1  select * from scott.emp
   2* select *from dual

26、显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行

    l[ist][n]

27、将执行的过程暂停，等待用户响应后继续执行

    PAUSE [text]
SYS@orcl> pause abc
abc 
     输入回车
SYS@orcl>

28、显示绑定变量 print

SYS@orcl> variable aa number
SYS@orcl> begin
   2  :aa:=3;
   3  end;
   4  /

PL/SQL procedure successfully completed.

SYS@orcl> print aa

        AA
----------
          3

29、将指定的信息或一个空行输出到屏幕上

    PROMPT [text]

SYS@orcl> prompt wl
wl

30、退出quit

31、再次执行刚才已经执行过的sql语句

    run
    /

32、将sql buffer中的sql语句保存到一个文件中

    save file_name

33、关闭数据库  SHUTDOWN

34、将显示的内容输出到指定文件

    SQL> SPOOL file_name
    在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。

   关闭spool输出
    SQL> SPOOL OFF
    只有关闭spool输出，才会在输出文件中看到输出的内容。

35、开启数据库   STARTUP

36、在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：

    !

37、设置每个报表的顶部标题

    ttitle

SYS@orcl> ttitle off

38、显示sql*plus系统变量的值或sql*plus环境变量的值

   1).显示当前环境变量的值：

      Show all

   2).显示初始化参数的值：

      show PARAMETERS [parameter_name]

   3).显示数据库的版本：

      show REL[EASE]

   4).显示SGA的大小

      show SGA

   5).显示当前的用户名

      show user

39、Set命令，通过SET变量，可以为SQL*PLUS建立一个特殊的环境。

arraysize

从数据库一次获取的行数，默认值20，最大值5000，值越大，内存需要越多，可以提高查询的有效性。当超过1000时，效果不大。

autocommit

off值，禁止自动提交，需要手工提交修改，此值为默认值，on值，执行SQL命令提交修改，immediate和on相同

SYS@orcl> show autocommit
SYS@orcl> set autocommit on

linesize

一行显示的字符数，默认值是80

例：
SET LIN[ESIZE] {80|n}
如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示

feedback

是否显示SQL语句查询或修改的行数，即显示查询返回的记录数，on为开，off为关

SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数

例子：
show feedback
select * from scott.dept;
set feedback off
select * from scott.dept;

serveroutput

存储过程是否显示输出，off为禁止，on显示输出

例子：
show serveroutput

SYS@orcl> begin
dbms_output.put_line('a');
end;
/
a

SYS@orcl> set serveroutput off

pagesize

设置一页有多少行数，如果设为0，则所有的输出内容为一页并且不显示列标题

例：
SET PAGES[IZE] {24|n}
     

trimout

每行尾部是否允许带空格，on 去除重定向(spool)输出每行尾部的空格，off 允许显示尾部的空格

SYS@orcl> show trimout
SYS@orcl> select * from scott.dept;
SYS@orcl> set trimout off
SYS@orcl> select * from scott.dept;

heading

报表中列标题是否打印，即是否显示每行的列名，on报表中打印列标题，off禁止打印列标题

SYS@orcl> show heading
SYS@orcl> select * from scott.dept;

SYS@orcl> set  heading off
SYS@orcl> select * from scott.dept;

verify

是否显示替代变量被替代前后的语句，on显示（默认值），off禁止列清单

SYS@orcl> select * from scott.dept where deptno=&a;
Enter value for a: 40
old   1: select * from scott.dept where deptno=&a
new   1: select * from scott.dept where deptno=40

        40 OPERATIONS     BOSTON
SYS@orcl> set verify off
SYS@orcl> select * from scott.dept where deptno=&a;
Enter value for a: 40

        40 OPERATIONS     BOSTON

echo

是否重复显示所要执行的SQL语句，指start命令是否列出文件中的每行命令，on列出命令，off禁止列清单

SET ECHO {ON|OFF}

SYS@orcl> show echo
echo OFF
SYS@orcl> @/home/oracle/a.sql

        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON
SYS@orcl> set echo on
SYS@orcl> @/home/oracle/a.sql
SYS@orcl> select * from scott.dept;

        10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

time

显示日期，on显示当前时间，off禁止显示

例：
显示时间
set time on|off

timing

显示执行时间，on显示sql命令或pl/sql块的运行时间，off禁止

例：
set TIMING ON

wrap

是否截断数据项，off截断数据项，on允许数据项换到下一行，当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。

例：
SET WRA[P] ON

sqlprompt

设置sqlplus的命令提示符

例：
set sqlprompt "SQL> "
set sqlprompt "SQL*plus>>"

newpage

每一页的头和顶部标题之间要打印的空行数，0表示页之间换行符
设置页与页之间的分隔
       SQL> SET NEWP[AGE] {1|n|NONE}
       当set newpage n 时，会在页和页之间隔着n个空行。
       当set newpage none 时，会在页和页之间没有任何间隔

showmode

在执行set命令时，是否列出新值和老值

sqlcase {mixed(默认值)|lower|upper} 
在命令执行前 将sql命令和pl/sql块的大小写进行转换

numformat 数值格式 

设置显示数值的缺省格式
   
SYS@orcl> set numformat 99,999.00

numwidth {10(默认值)|n}

显示数值设置缺省宽度

underline {-(默认值)|c|off|on(默认值)} 

列标题是否加下划线

SYS@orcl> show underline
underline "-" (hex 2d)
SYS@orcl> select * from scott.dept;
   2 
SYS@orcl>
SYS@orcl> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
      10.00 ACCOUNTING     NEW YORK
      20.00 RESEARCH       DALLAS
      30.00 SALES          CHICAGO
      40.00 OPERATIONS     BOSTON
SYS@orcl> set underline off
SYS@orcl> select * from scott.dept;

    DEPTNO DNAME          LOC
      10.00 ACCOUNTING     NEW YORK
      20.00 RESEARCH       DALLAS
      30.00 SALES          CHICAGO
      40.00 OPERATIONS     BOSTON

tab {off|on(默认值)} 

输出中如何格式化空白空间，on用tab字符，off用空格

sqlnumber {off|on(默认值)}
当执行sql或pl/sql时，设置第二行和后面行的提示，on提示行号，off提示设置为sqlprompt的值

define

on扫描替换变量的命令及用他们的值代替
off不扫描替换变量的命令，不用他们的值代替

scan {off|on(默认值)}

是否对存在的替换变量和值扫描，off禁止替换变量和值的处理，on允许处理

space {1(默认值)|n} 

设置输出列之间空格的数目，最大值为10

pause {off(默认值)|on}

显示报表时，控制滚动，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页

例：
set pause on

sqlterminator

sql命令结束的字符，off不识别终止符，用空行结束sql命令，on终止符为分号

SYS@orcl> show sqlterminator
 sqlterminator ";" (hex 3b)
 SYS@orcl> set sqlterminator ":"
 SYS@orcl> select * from scott.dept:

    DEPTNO DNAME          LOC
 ---------- -------------- -------------
         10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON