SQL*Plus是DBA最親密的夥伴，很多蘊含在SQL*Plus中列格式化命令可以幫助我們便利的獲取並輸出資料。簡單演示一些常用的列格式化命令，供參考。

1.建立表T
sec@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

13.小結
SQL*Plus的個性化定製內容非常的豐富，若想讓其充分發揮出效力，就需要深入其中，必得其樂。

Good luck.

secooler
10.03.16

-- The End --

【SQL*Plus】常用列格式化命令實驗參考

相關文章