oracle 格式化操作COL

COL命令：

主要格式化列的显示形式。 
该命令有许多选项，具体如下： 
COL[UMN] [ { column|expr} [ option ...]] 
Option选项可以是如下的子句: 
ALI[AS] alias 
CLE[AR] 
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]

1. 改变缺省的列标题sqlplus内操作

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;

EMPNO           ENAME                   JOB
---
7369 SMITH                                    CLERK 
7499 ALLEN                                    SALESMAN
7521 WARD                                     SALESMAN

对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边

5. 不让一个列显示在屏幕上

NOPRI[NT]|PRI[NT]
SQL> col job noprint;

EMPNO           ENAME
---
7369            SMITH 
7499            ALLEN
7521            WARD

6. 格式化NUMBER类型列的显示：

SQL> COLUMN SAL FORMAT $99,990;

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. 显示列的当前的显示属性值

SQL> COLUMN column_name /查看该列都设置了哪些格式属性

9. 将所有列的显示属性设为缺省值

SQL> CLEAR COLUMNS

格式化查询列名
col empno heading 编号;

格式化列名长度
col empno format a10;