change:
SQL> help change
CHANGE
------
Changes the first occurrence of the specified text on
the current line of the SQL buffer.
C[HANGE] sepchar old [sepchar [new[sepchar]]]
Not available in iSQL*Plus
用法:
change /old text/new text /
将old text替换成新的text
说明:
change和第一个/之间的空格可以省略
最后一个/也很有用,最后一个/与中间的/之间的所有字符都是新字符,包括空格
如果不写最后一个空格,那么最后面的空格将被忽略
第一个/如果没有的话,那么change后面的所有字符都会被认为是old text
例如:
SQL> list
1* SELECT SYSDATE FROM DUAL
SQL> change dual / aaaa
SP2-0023: 未找到字符串
SQL> change dual
1* SELECT SYSDATE FROM D
SQL> change / d/dual/
1* SELECT SYSDATE FROMdual
SQL> change /dual/ dual/
1* SELECT SYSDATE FROM dual
change会严格找两个/之间的字符,包括空格
--例子结束
-----------------------------------------------------------------------------
clean:
SQL> help clear
CLEAR
-----
Resets or erases the current value or setting for the specified option,
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]
说明:
breaks:清除break命令的设置
buffer:清除buffer命令的设置
columns:清除columns命令的设置
computers:清除computer命令的设置
screen:清除screen命令的设置
sql:清除sql缓冲区
timing:清除timing命令的计时
----------------------------------------------------------------------------
column:
SQL> help column
COLUMN
------
Specifies display attributes for a given column, such as:
- column heading text
- column heading alignment
- data format
- column data wrapping
Also lists the current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
SQL>
说明:
alias 别名
clear 清楚所定义的
entmap 为html输出中开/关 实例映射
fold_after 单行到多行时,选择多行在列后
fold_before 单行到多行时,选择多行在列前
format 输出格式
heading text 改变默认列名
justify 对齐方式
like 当前列复制以前列的定义
newline 打印列之前输出一个新行
new_value 命名存在title命令中使用列值的变量
noprint/print 关闭/打开列的显示
NULL 改变空值时显示内容
OFF/ON 关闭/打开所有选项
old_value 命名一个保存在btitle命令使用的列值变量
wrappend 折射到下一行
word_wrapped 同wrappend,但会保留词语完整
truncaped 把值截为列定义宽度
如下有些例子,是从《SQL*PLUS命令的使用大全》中摘录
原作者不详,如有疑问,请于我联系 wuljy_cn@163.com
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上:
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
Employee
Department name Salary
---------- ---------- ----------
10 aaa 11
note: the col heading turn into two lines from one line.
3). 改变列的显示长度:
FOR[MAT] format
Sql>select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
Sql> col ename format a40
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
对于NUMBER型的列,列标题缺省在右边,其它类型的列标题缺省在左边
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). 格式化NUMBER类型列的显示:
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name Salary Commission
---------- ---------- --------- ----------
30 ALLEN $1,600 300
7). 显示列值时,如果列值为NULL值,用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL1
--------------------
HOW ARE YOU?
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A
9). 显示列的当前的显示属性值
SQL> COLUMN column_name