【SQL*Plus】常用列格式化命令实验参考

2020-01-06 15:16:52

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 --

分享好友

分享这个小栈给你的朋友们，一起进步吧。