Oracle格式化输出列COLUMN的使用

转载 2007年09月17日 08:35:00

COLUMN

Syntax

COL[UMN] [{column|expr} [option ...]]

where option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
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]

Specifies display attributes for a given column, such as

  • text for the column heading

     

  • alignment of the column heading

     

  • format for NUMBER data

     

  • wrapping of column data

     

Also lists the current display attributes for a single column or all columns.

Terms | Usage | Examples | Top

Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.

Refer to the following list for a description of each term or clause:

{column|expr}

Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command.

If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column LAST_NAME applies to all columns named LAST_NAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes.

To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column's alias.

ALI[AS] alias

Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands.

CLE[AR]

Resets the display attributes for the column to default values.

To reset the attributes for all columns, use the CLEAR COLUMNS command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.

ENTMAP {ON|OFF}

Allows entity mapping to be turned on or off for selected columns in HTML output. This feature allows you to include, for example, HTML hyperlinks in a column of data, while still mapping entities in other columns of the same report. By turning entity mapping off for a column containing HTML hyperlinks, the HTML anchor tag delimiters, <, >, " and &, are correctly interpreted in the report. Otherwise they would be replaced with their respective entities, &lt;, &gt;, &quot; and &amp;, preventing web browsers from correctly interpreting the HTML.

Entities in the column heading and any COMPUTE labels or output appearing in the column are mapped or not mapped according to the value of ENTMAP for the column.

The default setting for COLUMN ENTMAP is the current setting of the MARKUP HTML ENTMAP option. For more information about the MARKUP HTML ENTMAP option, see MARKUP Options in Chapter 7 and SET later this Chapter.

FOLD_A[FTER]

Inserts a carriage return after the column heading and after each row in the column. SQL*Plus does not insert an extra carriage return after the last column in the SELECT list.

FOLD_B[EFORE]

Inserts a carriage return before the column heading and before each row of the column. SQL*Plus does not insert an extra carriage return before the first column in the SELECT list.

FOR[MAT] format

Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.

Character Columns The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.

A LONG, CLOB or NCLOB column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.

To change the width of a datatype to n, use FORMAT An. (A stands for alphanumeric.) If you specify a width shorter than the column heading, SQL*Plus truncates the heading. If you specify a width for a LONG, CLOB, or NCLOB column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width.

DATE Columns The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default width is A9. In Oracle9i, the NLS parameters may be set in your database parameter file or may be environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. (See the documentation for Oracle9i for a complete description of the NLS parameters).

You can change the format of any DATE column using the SQL function TO_CHAR in your SQL SELECT statement. You may also wish to use an explicit COLUMN FORMAT command to adjust the column width.

When you use SQL functions like TO_CHAR, Oracle automatically allows for a very wide column.

To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.

NUMBER Columns To change a NUMBER column's width, use FORMAT followed by an element as specified in Table 5-1.

Element  Examples  Description 

9999
 

Number of "9"s specifies number of significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. 

0999
9990
 

Displays a leading zero or a value of zero in this position as 0. 

$9999
 

Prefixes value with dollar sign. 

B9999
 

Displays a zero value as blank, regardless of "0"s in the format model. 

MI 

9999MI
 

Displays "-" after a negative value. For a positive value, a trailing space is displayed. 

S9999
 

Returns "+" for positive values and "-" for negative values in this position. 

PR 

9999PR
 

Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed. 

99D99
 

Displays the decimal character in this position, separating the integral and fractional parts of a number.  

9G999
 

Displays the group separator in this position. 

C999
 

Displays the ISO currency symbol in this position. 

L999
 

Displays the local currency symbol in this position. 

, (comma) 

9,999
 

Displays a comma in this position. 

. (period) 

99.99
 

Displays a period (decimal point) in this position, separating the integral and fractional parts of a number. 

999V99
 

Multiplies value by 10n, where n is number of "9"s after "V". 

EEEE 

9.999EEEE
 

Displays value in scientific notation (format must contain exactly four "E"s). 

RN or rn 

RN
 

Displays upper- or lowercase Roman numerals. Value can be an integer between 1 and 3999. 

DATE 

DATE
 

Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian dates. 

 

The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first or last position.

If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.

A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.

SQL*Plus formats NUMBER data right-justified. A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.

SQL*Plus may round your NUMBER data to fit your format or field width.

If a value cannot fit within the column width, SQL*Plus indicates overflow by displaying a pound sign (#) in place of each digit the width allows.

If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).

Table 5-1 Number Formats

HEA[DING] text

Defines a column heading. If you do not use a HEADING clause, the column's heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, "|") begins a new line.

For example,

COLUMN LAST_NAME HEADING 'Employee |Name'

would produce a two-line column heading. See the HEADSEP variable of the SET command in this chapter for information on changing the HEADSEP character.

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}

Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT.

LIKE {expr|alias}

Copies the display attributes of another column or expression (whose attributes you have already defined with another COLUMN command). LIKE copies only attributes not defined by another clause in the current COLUMN command.

NEWL[INE]

Starts a new line before displaying the column's value. NEWLINE has the same effect as FOLD_BEFORE.

NEW_V[ALUE] variable

Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).

NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.

For information on displaying a column value in the bottom title, see COLUMN OLD_VALUE. For more information on referencing variables in titles, see the TTITLE command later in this chapter. For information on formatting and valid format models, see the FORMAT command.

NOPRI[NT]|PRI[NT]

Controls the printing of the column (the column heading and all the selected values). NOPRINT turns off the screen output and printing of the column. PRINT turns the printing of the column on.

NUL[L] text

Controls the text SQL*Plus displays for null values in the given column. The default is a white space. SET NULL controls the text displayed for all null values for all columns, unless overridden for a specific column by the NULL clause of the COLUMN command. When a NULL value is SELECTed, a variable's type will always become CHAR so the SET NULL text can be stored in it.

OLD_V[ALUE] variable

Specifies a variable to hold a column value. You can reference the variable in BTITLE commands. Use OLD_VALUE to display column values in the bottom title. You must include the column in a BREAK command with the SKIP PAGE action.

OLD_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause.

For information on displaying a column value in the top title, see COLUMN NEW_VALUE. For more information on referencing variables in titles, see the TTITLE command later in this chapter.

ON|OFF

Controls the status of display attributes for a column. OFF disables the attributes for a column without affecting the attributes' definition. ON reinstates the attributes.

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

Specifies how SQL*Plus will treat a datatype or DATE string that is too wide for a column. WRAPPED wraps the string within the column bounds, beginning new lines when required. When WORD_WRAP is enabled, SQL*Plus left justifies each new line, skipping all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped. TRUNCATED truncates the string at the end of the first line of display.

Usage | Terms | Examples | Top

You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command. Thus, the COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.

When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.

Examples | Terms | Usage | Top

To make the LAST_NAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading, enter


COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'

To format the SALARY column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, enter


COLUMN SALARY FORMAT $9,999,990.99

To assign the alias NET to a column containing a long expression, to display the result in a dollar format, and to display <NULL> for null values, you might enter



COLUMN SALARY+COMMISSION_PCT+BONUS-EXPENSES-INS-TAX ALIAS NET
COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'

Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.

Also note that in the first command you must enter the expression exactly as you enter it in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.

To wrap long values in a column named REMARKS, you can enter


COLUMN REMARKS FORMAT A20 WRAP


CUSTOMER   DATE        QUANTITY REMARKS
---------- ---------   -------- --------------------
123        25-AUG-2001      144 This order must be s
                                hipped by air freigh
                                t to ORD

If you replace WRAP with WORD_WRAP, REMARKS looks like this:

CUSTOMER   DATE        QUANTITY REMARKS
---------- ---------   -------- ---------------------
123        25-AUG-2001      144 This order must be
                                shipped by air freight
                                to ORD

If you specify TRUNCATE, REMARKS looks like this:

CUSTOMER   DATE        QUANTITY REMARKS
---------- ---------   -------- --------------------
123        25-AUG-2001      144 This order must be s

In order to print the current date and the name of each job in the top title, enter the following. Use the EMPLOYEES table of the HR schema in this case instead of EMP_DETAILS_VIEW as you have used up to now. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page and Report Titles and Dimensions" in Chapter 4.)


COLUMN JOB_ID NOPRINT NEW_VALUE JOBVAR
COLUMN TODAY  NOPRINT NEW_VALUE DATEVAR
BREAK ON JOB_ID SKIP PAGE ON TODAY
TTITLE CENTER 'Job Report' RIGHT DATEVAR  SKIP 2 -
LEFT 'Job:     ' JOBVAR SKIP 2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY,
LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMPLOYEES WHERE JOB_ID IN ('MK_MAN', 'SA_MAN')
ORDER BY JOB_ID, LAST_NAME;

Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:


                                      Job Report                           
04/19/01

Job:     MK_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
Hartstein                   100 17-FEB-96     $13,000.00            20
                                          --------------
                                              $13,000.00

                                   Job Report                           04/19/01

Job:     SA_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
Errazuriz                   100 10-MAR-97     $12,000.00            80
Zlotkey                     100 29-JAN-00     $10,500.00            80
Cambrault                   100 15-OCT-99     $11,000.00            80
Russell                     100 01-OCT-96     $14,000.00            80
Partners                    100 05-JAN-97     $13,500.00            80
                                          --------------

                                   Job Report                           04/19/01

Job:     SA_MAN

LAST
NAME                 MANAGER_ID HIRE_DATE         SALARY DEPARTMENT_ID
-------------------- ---------- --------- -------------- -------------
                                              $12,200.00

6 rows selected.

To change the default format of DATE columns to 'YYYY-MM-DD', you can enter


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';


Session altered.

To display the change, enter a SELECT statement, such as:


SELECT HIRE_DATE
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 206;


                                   Job Report                           04/19/01

Job:     SA_MAN

HIRE_DATE
----------
1994-06-07

See the Oracle9i SQL Reference for information on the ALTER SESSION command.

Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.  

ORACLE——日期时间格式化参数详解

ORACLE数据库SQL中,我们常用的yyyy-MM-dd、yyyy/MM/dd等几种格式,Reference官方文档,并附上详细的应用示例综合成文,希望能够对大家学习和使用有所帮助。 1. 测试环...
  • jdk2006
  • jdk2006
  • 2015年12月27日 19:50
  • 7650

Oracle 格式化日期

-- Start 所谓格式化日期指的是将日期转为字符串,或将字符串转为日期,下面几个函数可以用来格式化日期。 TO_CHAR(datetime, 'format') TO_DATE(characte...
  • shangboerds
  • shangboerds
  • 2015年06月15日 13:24
  • 10313

form或plsql中数字格式掩码的设置

在项目上遇到这样一个需求:在EBS界面直接显示数字的时候,如果后台数据是0.85,但显示在界面上却是.85,这样挺不人性化的。那么怎么设置显示为0.85呢。 有几个方法: 1、 直接在form中修...
  • huojian1993
  • huojian1993
  • 2014年10月27日 14:16
  • 1168

oracle 12c (内存列存储)IM column store

IM column store ,内存列存储
  • ivory_town1
  • ivory_town1
  • 2017年03月14日 18:29
  • 428

oracle:表、列、约束等操作,table,constraint,column

创建表的语法 GLOBAL TEMPORARY 说明该表为临时表。行的有效期有 ON COMMIT 子句决定。临时表对于说有会话都可见,但是这些行则是特定于某个会话的。 const...
  • VagueCoder
  • VagueCoder
  • 2014年08月13日 11:22
  • 698

一日一学Oracle --- Oracle virtual column(虚拟列)

1. 为什么要使用虚拟列        (1)可以为虚拟列创建索引(oracle为其创建function index)        (2)可以搜集虚拟列的统计信息statistics,为CBO提...
  • wangke8476
  • wangke8476
  • 2011年12月01日 22:41
  • 5594

#425–使用SharedSizeGroup在Grid间共享列的大小(SharedSizeGroup Allows Sharing Column Sizes Across Differet Grid)

 你使用ColumnDefinition 的SharedSizeGroup 属性使ListBox中所有的子项的列大小都一样。这是因为列表中的每一项中都有自己Grid实例,并且各个不同的Gri...
  • Libby1984
  • Libby1984
  • 2016年11月24日 18:01
  • 497

在 SharePoint 2013中使用 RelatedItems 列(Site column)

本文讲述 在 SharePoint 2013中使用 RelatedItems 列(Site column)
  • farawayplace613
  • farawayplace613
  • 2014年06月08日 13:22
  • 1322

【学习笔记】oracle11g multi column statistics深入研究笔记

oracle研究中心:目前,CBO(Cost-Based Optimizer)是Oracle默认使用的查询优化器Query Optimizer模式。在CBO中,SQL执行计划的生成,是以一种寻找成本(...
  • techsupporter
  • techsupporter
  • 2017年11月18日 22:37
  • 55

Oracle导数时报错:ORA-12899: value too large for column

原文链接:http://blog.csdn.net/shuiqingtinglin/article/details/7095787实际场景用Oracle自身支持的sqlldr语句导数据的时候,本来一直...
  • wuya814070935
  • wuya814070935
  • 2017年06月13日 12:03
  • 1162
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle格式化输出列COLUMN的使用
举报原因:
原因补充:

(最多只允许输入30个字)