1.创建表Tsec@ora10g> drop table t purge;
Table dropped.
sec@ora10g> create table t (x number(10), y varchar2(30));
Table created.
2.初始化一条数据
sec@ora10g> insert into t values (1,'secooler');
1 row created.
3.默认输出格式
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
4.使用“ALI[AS] alias”
1)设置x列的同名为xxx
sec@ora10g> col x alias xxx;
2)查看x列的格式定义
sec@ora10g> col x
COLUMN x ON
ALIAS xxx
5.对数字格式的x列格式化演示
sec@ora10g> col x for 9
sec@ora10g> select * from t;
X Y
-- ------------------------------
1 secooler
sec@ora10g> col x for 99
sec@ora10g> select * from t;
X Y
--- ------------------------------
1 secooler
sec@ora10g> col x for 99999
sec@ora10g> select * from t;
X Y
------ ------------------------------
1 secooler
此时对xxx同名进行格式化等同于对x进行格式化
sec@ora10g> col xxx for 9999999999
sec@ora10g> select * from t;
X Y
----------- ------------------------------
1 secooler
sec@ora10g> col x
COLUMN x ON
ALIAS xxx
FORMAT 9999999999
6.对VARCHAR2类型的列进行格式化
sec@ora10g> col y for a8
sec@ora10g> select * from t;
X Y
----------- --------
1 secooler
sec@ora10g> col y for a80
sec@ora10g> select * from t;
X Y
----------- ---------------------------------------------------------
1 secooler
7.使用clear恢复列的默认设置
1)恢复x列的默认设置
sec@ora10g> col x clear
sec@ora10g> select * from t;
X Y
---------- ----------------------------------------------------------
1 secooler
2)恢复y列的默认设置
sec@ora10g> col y clear
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
也可以使用“clear columns”命令清除所有列格式来达到目的。
sec@ora10g> clear columns;
columns cleared
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
8.使用“HEA[DING]”格式化x列的名字
sec@ora10g> col x heading 'This is column X'
sec@ora10g> select * from t;
This is column
X Y
-------------- ------------------------------
1 secooler
9.演示“NOPRI[NT] PRI[NT]”
1)不显示x列内容
sec@ora10g> clear columns;
columns cleared
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
sec@ora10g> col x noprint
sec@ora10g> select * from t;
Y
------------------------------
secooler
2)显示x列内容
sec@ora10g> col x print
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
10.演示“NUL[L] text”,格式化NULL的显示内容
sec@ora10g> insert into t values (2,null);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2
sec@ora10g> col y null "It's NULL"
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2 It's NULL
此时第二行的y列NULL内容显示为“It's NULL”。
11.“JUS[TIFY] {L[EFT] C[ENTER] R[IGHT]}”演示
调整列名的现实位置
默认:NUMBER类型靠右显示,其他类型靠左显示
sec@ora10g> col x justify left
sec@ora10g> col y justify left
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2
sec@ora10g> col x justify center
sec@ora10g> col y justify center
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2
sec@ora10g> col x justify right
sec@ora10g> col y justify right
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2
12.“LIKE {expr alias}”的演示
sec@ora10g> clear columns;
columns cleared
sec@ora10g> select * from t;
X Y
---------- ------------------------------
1 secooler
2
sec@ora10g> create table t_like (col1 varchar2(100));
Table created.
sec@ora10g> col col1 for a8
sec@ora10g> col y like col1
sec@ora10g> select * from t;
X Y
---------- --------
1 secooler
2
--转自