Oracle data_length和data_precision的区别
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 column in bytes/*列的数据类型的字节长度*/
- DATA_PRECISION NUMBER Y Length: decimal digits (NUMBER) or binary digits (FLOAT)/*数字类型的实际长度*/
- DATA_SCALE NUMBER Y Digits to right of decimal point in a number/*小数点位数*/
- NULLABLE VARCHAR2(1) Y Does column allow NULL values?
- COLUMN_ID NUMBER Y Sequence number of the column as created
- DEFAULT_LENGTH NUMBER Y Length of default value for the column
- DATA_DEFAULT LONG Y Default value for the column
- NUM_DISTINCT NUMBER Y The number of distinct values in the column
- LOW_VALUE RAW(32) Y The low value in the column
- HIGH_VALUE RAW(32) Y The high value in the column
- DENSITY NUMBER Y The density of the column
- NUM_NULLS NUMBER Y The number of nulls in the column
- NUM_BUCKETS NUMBER Y The number of buckets in histogram for the column
- LAST_ANALYZED DATE Y The date of the most recent time this column was analyzed
- SAMPLE_SIZE NUMBER Y The sample size used in analyzing this column
- CHARACTER_SET_NAME VARCHAR2(44) Y Character set name
- 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 column in bytes
- CHAR_LENGTH NUMBER Y The maximum length of the column in characters
- CHAR_USED VARCHAR2(1) Y C is maximum length given in characters, B if in bytes
- V80_FMT_IMAGE VARCHAR2(3) Y Is column data in 8.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
评论暂时关闭