date length 7 oracle,Oracle data_length和data_precision的区别

这两个属性都属于user_tab_columns视图，他们的含义：

data_length:当前列数据类型的字节长度

如:EMPNO    NUMBER(4)    22

ENAME    VARCHAR2(10) 10

data_precision:列相关数据类型(数字类型)的具体长度(有效位数),一个十进制数(NUMBER类型),或一个二进制树(FLOAT类型)

如:SAL      NUMBER(7,2)   7

//

来看看user_tab_columns视图的相关信息,我们主要关注Name,Comments两列和带注释的三行：

desc user_tab_columns;

Name                 Type          Nullable Default Comments

-------------------- ------------- -------- ------- --------------------------------------------------------------------

TABLE_NAME           VARCHAR2(30)                   Table, view or cluster name

COLUMN_NAME          VARCHAR2(30)                   Column name

DATA_TYPE            VARCHAR2(106) Y                Datatype of the column

DATA_TYPE_MOD        VARCHAR2(3)   Y                Datatype modifier of the column

DATA_TYPE_OWNER      VARCHAR2(30)  Y                Owner of the datatype of the column

DATA_LENGTH          NUMBER                         Length of the columninbytes/*列的数据类型的字节长度*/

DATA_PRECISION       NUMBER        Y                Length:decimaldigits (NUMBER) or binary digits (FLOAT)/*数字类型的实际长度*/

DATA_SCALE           NUMBER        Y                Digits to right ofdecimalpointina number/*小数点位数*/

NULLABLE             VARCHAR2(1)   Y                Does column allow NULL values?

COLUMN_ID            NUMBER        Y                Sequence number of the columnascreated

DEFAULT_LENGTH       NUMBER        Y                Length ofdefaultvalueforthe column

DATA_DEFAULT         LONG          Y                Default valueforthe column

NUM_DISTINCT         NUMBER        Y                The number of distinct valuesinthe column

LOW_VALUE            RAW(32)       Y                The low valueinthe column

HIGH_VALUE           RAW(32)       Y                The high valueinthe column

DENSITY              NUMBER        Y                The density of the column

NUM_NULLS            NUMBER        Y                The number of nullsinthe column

NUM_BUCKETS          NUMBER        Y                The number of bucketsinhistogramforthe column

LAST_ANALYZED        DATE          Y                The date of the most recent timethiscolumn was analyzed

SAMPLE_SIZE          NUMBER        Y                The sample size usedinanalyzingthiscolumn

CHARACTER_SET_NAME   VARCHAR2(44)  Y                Charactersetname

CHAR_COL_DECL_LENGTH NUMBER        Y                Declaration length of character type column

GLOBAL_STATS         VARCHAR2(3)   Y                Are the statistics calculated without merging underlying partitions?

USER_STATS           VARCHAR2(3)   Y                Were the statistics entered directly by the user?

AVG_COL_LEN          NUMBER        Y                The average length of the columninbytes

CHAR_LENGTH          NUMBER        Y                The maximum length of the columnincharacters

CHAR_USED            VARCHAR2(1)   Y                Cismaximum length givenincharacters, Bifinbytes

V80_FMT_IMAGE        VARCHAR2(3)   Y                Is column datain8.0 image format?

DATA_UPGRADED        VARCHAR2(3)   Y                Has column data been upgraded to the latest type version format?

HISTOGRAM            VARCHAR2(15)  Y

//

实例1:查看emp表的列对应的data_length,data_precision,data_scale实际值

SQL> desc emp;

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

EMPNO    NUMBER(4)

ENAME    VARCHAR2(10) Y

JOB      VARCHAR2(9)  Y

MGR      NUMBER(4)    Y

HIREDATE DATE         Y

SAL      NUMBER(7,2)  Y

COMM     NUMBER(7,2)  Y

DEPTNO   NUMBER(2)    Y

//

select column_name,data_type,data_length,data_precision,data_scale

from user_tab_columns where table_name='EMP';

COLUMN_NAME                    DATA_TYPE                       DATA_LENGTH DATA_PRECISION DATA_SCALE

------------------------------ ------------------------------- ----------- -------------- ----------

EMPNO                          NUMBER                                   22              4          0

ENAME                          VARCHAR2                                 10

JOB                            VARCHAR2                                  9

MGR                            NUMBER                                   22              4          0

HIREDATE                       DATE                                      7

SAL                            NUMBER                                   22              7          2

COMM                           NUMBER                                   22              7          2

DEPTNO                         NUMBER                                   22              2          0