使用sql*plus编辑sql文本(r4笔记第53天)
使用sql*plus编辑sql文本(r4笔记第53天)
工作中可能会经常实用工具来编辑sql 文本，实用sql*plus来编辑的机会比较少，但是这些也是硬功夫，一旦有需要手工编辑，其实发现也是很容易的。 关于编辑使用的命令如下，其实看起来一大堆，主要的命令还是增(input)删(del)改(change)查(list),按照这个思路来看就会容易很多，有些命令也是选择性的使用。
Command | Abbreviation | Purpose |
|---|---|---|
APPEND text | A text | adds text at the end of the current line |
CHANGE/old/new | C/old/new | changes old to new in the current line |
CHANGE/text | C/text | deletes text from the current line |
CLEAR BUFFER | CL BUFF | deletes all lines |
DEL | (none) | deletes the current line |
DEL n | (none) | deletes line n |
DEL * | (none) | deletes the current line |
DEL n * | (none) | deletes line n through the current line |
DEL LAST | (none) | deletes the last line |
DEL m n | (none) | deletes a range of lines (m to n) |
DEL * n | (none) | deletes the current line through line n |
INPUT | I | adds one or more lines |
INPUT text | I text | adds a line consisting of text |
LIST | ; or L | lists all lines in the SQL buffer |
LIST n | L n or n | lists line n |
LIST * | L * | lists the current line |
LIST n * | L n * | lists line n through the current line |
LIST LAST | L LAST | lists the last line |
LIST m n | L m n | lists a range of lines (m to n) |
LIST * n | L * 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操作略有不同，但是还是比较实用的。