使用sql*plus编辑sql文本(r4笔记第53天)
公众号：杨建荣的学习笔记 · 作者：r4笔记第53天 · 发布：2015-02-21 23:11:07 · 原文链接
工作中可能会经常实用工具来编辑sql 文本，实用sql*plus来编辑的机会比较少，但是这些也是硬功夫，一旦有需要手工编辑，其实发现也是很容易的。
关于编辑使用的命令如下，其实看起来一大堆，主要的命令还是增(input)删(del)改(change)查(list),按照这个思路来看就会容易很多，有些命令也是选择性的使用。
| Command | Abbreviation | Purpose |
|---|---|---|
|
|
adds text at the end of the current line |
|
|
changes old to new in the current line |
|
|
deletes text from the current line |
|
|
deletes all lines |
|
(none) | deletes the current line |
|
(none) | deletes line n |
|
(none) | deletes the current line |
|
(none) | deletes line n through the current line |
|
(none) | deletes the last line |
|
(none) | deletes a range of lines (m to n) |
|
(none) | deletes the current line through line n |
|
|
adds one or more lines |
|
|
adds a line consisting of text |
|
|
lists all lines in the SQL buffer |
|
|
lists line n |
|
|
lists the current line |
|
|
lists line n through the current line |
|
|
lists the last line |
|
|
lists a range of lines (m to n) |
|
|
lists the current line through line n |
最后使用一个实例来说明一下，我们有下面的sql语句。
select c from
(select * from
(select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
model return updated rows
dimension by (no)
measures (cc c, no n)
rules (
c[any] = substr(c[cv()],n[cv()],1)
));
我们需要做的就是把它修改成为一个不可运行的结构，然后把它再改回去。
需要修改成如下的样子，标黄的部分是需要删除的。
select c from
(select * from
(select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
model return updated rows
dimension by (no)
measures (cc c, no n)
rules (
c[any] = substr(c[cv()],n[cv()],1)
));
可以使用下面的命令来完成。
SQL> select c from
2 (select * from
3 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
4 model return updated rows
5 dimension by (no)
6 measures (cc c, no n)
7 rules (
8 c[any] = substr(c[cv()],n[cv()],1)
9 ));
C
——
o
r
a
c
l
e
6 rows selected.
SQL> del 1 –删除第1行,第2行变成了第1行
SQL> c/(sel/sel –把第1行的(sel变成sel
1* select * from
SQL> l –列出修改后的语句情况
1 select * from
2 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
3 model return updated rows
4 dimension by (no)
5 measures (cc c, no n)
6 rules (
7 c[any] = substr(c[cv()],n[cv()],1)
8* ))
SQL> del 3 –我们尝试删除第3行
SQL> l –列出修改后的语句情况
1 select * from
2 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
3 dimension by (no)
4 measures (cc c, no n)
5 rules (
6 c[any] = substr(c[cv()],n[cv()],1)
7* ))
SQL> l last –列出最后的一行语句
7* ))
SQL> c/))/) –把))替换为)
7* )
SQL> l –列出修改后的sql情况
1 select * from
2 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
3 dimension by (no)
4 measures (cc c, no n)
5 rules (
6 c[any] = substr(c[cv()],n[cv()],1)
7* )
SQL> / –这个时候运行，语句按照期望是不能运行的。
dimension by (no)
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
我们预期的结果达到了，然后我们需要把结果改回去，让它可运行。
SQL> 0 select c from –我们在第1行前增加一行
SQL> l –列出修改后的sql情况
1 select c from
2 select * from
3 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
4 dimension by (no)
5 measures (cc c, no n)
6 rules (
7 c[any] = substr(c[cv()],n[cv()],1)
8* )
SQL> l 1 2 –列出第1-2行，然后定位在第2行
1 select c from
2* select * from
SQL> c/sel/(sel –把第2行的sel修改为(sel
2* (select * from
SQL> l –列出修改后的sql情况
1 select c from
2 (select * from
3 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
4 dimension by (no)
5 measures (cc c, no n)
6 rules (
7 c[any] = substr(c[cv()],n[cv()],1)
8* )
SQL> c/)/)) –把最后1行的)修改为))
8* ))
SQL> l –把修改后的sql语句列出来
1 select c from
2 (select * from
3 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
4 dimension by (no)
5 measures (cc c, no n)
6 rules (
7 c[any] = substr(c[cv()],n[cv()],1)
8* ))
SQL> l 3 –列出第3行的sql 语句
3* (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))
SQL> append model return updated rows –在第3行后追加一行内容
3* (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))model return updated rows
SQL> l –列出修改后的sql语句情况
1 select c from
2 (select * from
3 (select ‘oracle’ cc, level no from dual connect by level <= length(‘oracle’))model return updated rows
4 dimension by (no)
5 measures (cc c, no n)
6 rules (
7 c[any] = substr(c[cv()],n[cv()],1)
8* ))
SQL> / –运行sql语句的结果
C
——
o
r
a
c
l
e
6 rows selected.
使用sql*plus所做的修改就这样完成了，其实很多操作还是可控的，修改的过程是一个交互式的过程，和vi操作略有不同，但是还是比较实用的。