如何在Oracle中格式化NUMBER列？ [英] How to format a NUMBER column in Oracle?

问题描述

我尝试以很好的方式输出 spool ，所以我将所有的VARCHAR2列格式化为a5。
但是，我的问题列是NUMBER（2）类型的 SLOTNUM 和NUMBER类型的 FEEADJUSTMENT 5,2）。
我的代码是：

$ p $ lt; code> column SLOTNUM format 999;
列FEEADJUSTMENT格式999.9;
.........
列[other VARCHAR2]格式a5;
从allTables中选择*;

结果是：

  SLOTNUM DATEV ACTUA NOTES FEEADJUSTMENT TREAT名称
 ------- ----- ----- ----- ----------- -  ----- ----- 
 12 19-JU 19-ju Treat 2.5 12345 Flu 
 N-13 n-13 ment 6 Shot 
 00:00 succe 
：00合计
 
 
 15 20-JU 20-ju显示5.5 12345流感
 N-13 n-13 atus 6射击
 02:00坏了
：00 
  

FEEADJUSTMENT 占用的空间比需要的多。为什么不格式999.9 截断 FEEADJUSTMENT 列？

解决方案

因为它使用列标题的长度，所以作为文档说：

NUMBER列的宽度等于标题的宽度或FORMAT的宽度
加上符号的一个空格，以较大者为准。

您可以将列一个简短的名字：

pre $ select slotnum as slotn，...


你也可以把它当成一个字符串，但是你仍然需要一个别名：

<$ p $

select to_char（slotnum，'FM9999'你可以别名为 slotnum ，如果你喜欢，并且使用格式a5 ，但是我认为少一些耳朵。



我不确定我会描述强迫所有的 a5 为'nice'，特别是你比那些强制列包装的价值更有价值。一个品味的问题，我想... ...


I try to spool output in a nice way, so i am formatting all VARCHAR2 columns to a5. However, my problematic columns are: SLOTNUM of type NUMBER(2) and FEEADJUSTMENT of type NUMBER(5,2). My code is:

column SLOTNUM format 999;
column FEEADJUSTMENT format 999.9;
.........
column [other VARCHAR2]     format a5;
select * from allTables;

Result is:

SLOTNUM DATEV ACTUA NOTES FEEADJUSTMENT TREAT NAME                                          
------- ----- ----- ----- ------------- ----- -----                                        
     12 19-JU 19-ju Treat           2.5 12345 Flu                                              
        N-13  n-13  ment                6     Shot                                       
              00:00 succe                                                                                   
              :00   ssful                                                                                    
                    l                                                                                       

     15 20-JU 20-ju Appar           5.5 12345 Flu                                                 
        N-13  n-13  atus                6     Shot                                                         
              02:00 broke                                                                                     
              :00                                                                                              

FEEADJUSTMENT is taking more space than it needs. Why doesn't format 999.9 truncate the FEEADJUSTMENT column?

解决方案

Because it's using the length of the column header, as the documentation says:

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.

You can alias the column with a shorter name:

select slotnum as slotn, ...

You could also treat it as a string, but you'd still need an alias really:

select to_char(slotnum, 'FM9999') as slotn, ...

You could alias is as slotnum, if you preferred, and the use format a5, but I think that would be less clear.

I'm not sure I'd describe forcing everything to a5 as 'nice', particularly as you have values lomger than that which ware forcing column wrapping. A matter of taste, I suppose...

这篇关于如何在Oracle中格式化NUMBER列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！