SQLPlus常用编辑功能

相信使用过Linux下Oracle的各位都知道，方向键、退格键会出现乱码的情况，解决方案非常多，有改配置文件，还有安装rlwrap的，这些就不再列举。

但是，实际上谁愿意你在服务器上面随便装个wlwrap呢，即使你再怎么解释，管理员也是将信将疑的。既然如此，那就设法使用sqlplus本身的功能代替方向键和退格键的编辑功能吧。

sqlplus的常用功能列表如下：



（一）列出缓冲区list

SQL> help list

LIST
----

Lists one or more lines of the most recently executed SQL command
or PL/SQL block which is stored in the SQL buffer. Enter LIST with
no clauses to list all lines. In SQL*Plus command-line you can also
use ";" to list all the lines in the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.

L[IST] [n | n m | n  * | n LAST | * | * n | * LAST | LAST]
以下是示例代码

SQL> select
  2  table_name , tablespace_name
  3  from
  4  tabs
  5  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TESTTABLE                      DCB_DATA
BLUEBALL                       DCB_DATA
PHASES                         DCB_DATA
VOLUME                         DCB_DATA
REDBALL                        DCB_DATA

SQL> l
  1  select
  2  table_name , tablespace_name
  3  from
  4* tabs
SQL> l 1 3
  1  select
  2  table_name , tablespace_name
  3* from
SQL> l 1 last
  1  select
  2  table_name , tablespace_name
  3  from
  4* tabs
SQL> list 2
  2* table_name , tablespace_name
SQL> list * last
  2  table_name , tablespace_name
  3  from
  4* tabs
符号 * 的含义是当前的焦点在哪里。



（二）替换第n行命令

将第n行的命令替换掉，可以这样写

示例代码如下

n commandText
SQL> list
  1  select
  2  table_name , tablespace_name
  3  from
  4* tabs
SQL> 2 table_name
SQL> list
  1  select
  2  table_name
  3  from
  4* tabs
SQL> /

TABLE_NAME
------------------------------
TESTTABLE
BLUEBALL
PHASES
VOLUME
REDBALL


（三）替换字符串

将当前行的字符串替换掉

示例代码如下

SQL> select
  2  segment_nam,
  3  bytes/1048576 MB
  4  from
  5  user_segment
  6  /
user_segment
*
ERROR at line 5:
ORA-00942: table or view does not exist


SQL> list 5
  5* user_segment
SQL> change /user_segment/user_segments/
  5* user_segments
SQL> list
  1  select
  2  segment_nam,
  3  bytes/1048576 MB
  4  from
  5* user_segments
SQL> /
segment_nam,
*
ERROR at line 2:
ORA-00904: "SEGMENT_NAM": invalid identifier


SQL> list 2
  2* segment_nam,
SQL> change /segment_nam/segment_name/
  2* segment_name,
SQL> /

SEGMENT_NAME                           MB
------------------------------ ----------
TESTTABLE                               2
PK_VOLUME_VID                           1
VOLUME                                  1
MPK_REDBALL                             1
REDBALL                                 1
MPK_BLUEBALL                            1
BLUEBALL                                1
PHASES                                  1

8 rows selected.

SQL> list
  1  select
  2  segment_name,
  3  bytes/1048576 MB
  4  from
  5* user_segments
SQL> list 3
  3* bytes/1048576 MB
SQL> change /bytes//1048576/bytes//1024
  3* /1048576 MB
SQL> list
  1  select
  2  segment_name,
  3  /1048576 MB
  4  from
  5* user_segments
SQL> list 3
  3* /1048576 MB
SQL> change /1048576/bytes//1024 KB/
  3* /bytes MB
SQL> change /bytes MB / bytes//1024 KB/
SP2-0023: String not found.
SQL> change /bytes MB/ bytes//1024 KB/
  3* / bytes
SQL>
SQL>
SQL> select
  2  segment_name,
  3  bytes/1048576 MB
  4  from
  5  user_segments
  6  /

SEGMENT_NAME                           MB
------------------------------ ----------
TESTTABLE                               2
PK_VOLUME_VID                           1
VOLUME                                  1
MPK_REDBALL                             1
REDBALL                                 1
MPK_BLUEBALL                            1
BLUEBALL                                1
PHASES                                  1

8 rows selected.


（四）在行后面追加文本

示例代码如下

SQL> select table_name
  2  from
  3  tabs
  4  /

TABLE_NAME
------------------------------
TESTTABLE
BLUEBALL
PHASES
VOLUME
REDBALL

SQL> 1
  1* select table_name
SQL> append ,tablespace_name
  1* select table_name,tablespace_name
SQL> list
  1  select table_name,tablespace_name
  2  from
  3* tabs
SQL> /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TESTTABLE                      DCB_DATA
BLUEBALL                       DCB_DATA
PHASES                         DCB_DATA
VOLUME                         DCB_DATA
REDBALL                        DCB_DATA