SQLPlus常用编辑功能

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

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

sqlplus的常用功能列表如下：
[img]http://images.cnblogs.com/cnblogs_com/killkill/WindowsLiveWriter/SQLPlus_DAB5/image_2.png[/img]


（一）列出缓冲区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