Oracle格式化输出

《Oracle格式化输出》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、1.COL命令：主要格式化列的显示形式。该命令有许多选项，具体如下：COL[UMN][{column

2、expr}[option...]]Option选项可以是如下的子句:ALI[AS]aliasCLE[AR]FOLD_A[FTER]FOLD_B[EFORE]FOR[MAT]formatHEA[DING]textJUS[TIFY]{L[EFT]

3、C[ENTER]

4、C[ENTRE]

5、R[IGHT]}LIKE{expr

6、alias}NEWL[INE]NEW_V[ALUE]variableNOPRI[NT]

7、PRI[NT]NUL

8、[L]textOLD_V[ALUE]variableON

9、OFFWRA[PPED]

10、WOR[D_WRAPPED]

11、TRU[NCATED]1).改变缺省的列标题COLUMNcolumn_nameHEADINGcolumn_headingForexample:Sql>select*fromdept;DEPTNODNAMELOC-----------------------------------------------10ACCOUNTINGNEWYORKsql>colLOCheadinglocationsql>select

12、*fromdept;DEPTNODNAMElocation------------------------------------------------10ACCOUNTINGNEWYORK2).将列名ENAME改为新列名EMPLOYEENAME并将新列名放在两行上：Sql>select*fromempDepartmentnameSalary------------------------------10aaa11SQL>COLUMNENAMEHEADING'Employee

13、Name'Sql>select*frome

14、mpEmployeeDepartmentnameSalary------------------------------10aaa11note:thecolheadingturnintotwolinesfromoneline.3).改变列的显示长度：FOR[MAT]formatSql>selectempno,ename,jobfromemp;EMPNOENAMEJOB-----------------------------7369SMITHCLERK7499ALLENSALESMAN7521WARDSALESMANSq

15、l>colenameformata40EMPNOENAMEJOB-----------------------------------------------------------7369SMITHCLERK7499ALLENSALESMAN7521WARDSALESMAN4).设置列标题的对齐方式JUS[TIFY]{L[EFT]

16、C[ENTER]

17、C[ENTRE]

18、R[IGHT]}SQL>colenamejustifycenterSQL>/EMPNOENAMEJOB--------------------------

19、---------------------------------7369SMITHCLERK7499ALLENSALESMAN7521WARDSALESMAN对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边5).不让一个列显示在屏幕上NOPRI[NT]

20、PRI[NT]SQL>coljobnoprintSQL>/EMPNOENAME--------------------------------------------------7369SMITH7499ALLEN7521WARD6).格式化NUMB

21、ER类型列的显示：SQL>COLUMNSALFORMAT$99,990SQL>/EmployeeDepartmentNameSalaryCommission---------------------------------------30ALLEN$1,6003007).显示列值时，如果列值为NULL值，用text值代替NULL值COMMNUL[L]textSQL>COLCOMMNUL[L]text8).设置一个列的回绕方式WRA[PPED]

22、WOR[D_WRAPPED]

23、TRU[NCATED]COL1---------

24、-----------HOWAREYOU?SQL>COLCOL1FORMATA5SQL>COLCOL1WRAPPEDCOL1-----HOWAREYOU?SQL>COLCOL1WORD_WRAPPEDCOL1-----HOWAREYOU?SQL>COLCOL1WORD_WRAPPEDCOL1-----HOWA9).显