ALL_COLUMNS
📄字数 2.9K
👁️阅读量 加载中...
功能描述
ALL_COLUMNS系统视图用于存储和管理表字段信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | TABLE_ID | INTEGER | 表ID | √ | √ |
| 2 | COL_NO | INTEGER | 字段序号 | √ | √ |
| 3 | COL_NAME | VARCHAR | 字段名 | √ | √ |
| 4 | TYPE_NAME | VARCHAR | 类型名 | √ | √ |
| 5 | SCALE | INTEGER | 数据尺寸 | √ | √ |
| 6 | IS_HIDE | BOOLEAN | 是否隐藏 | √ | √ |
| 7 | DELETED | BOOLEAN | 是否删除 | √ | √ |
| 8 | VARYING | BOOLEAN | 是否变长（对char binary有效） | √ | √ |
| 9 | NOT_NULL | BOOLEAN | 是否可为空 | √ | √ |
| 10 | IS_SERIAL | BOOLEAN | 是否为序列值 | √ | √ |
| 11 | SERIAL_ID | INTEGER | 序列值发生器的ID号 | √ | √ |
| 12 | TIMESTAMP_T | CHAR | 时间戳类型（'i':仅在插入时计值; 'u': 更改时计值; 'n': 非时间戳类型，不计值） | √ | √ |
| 13 | COLLATOR | VARCHAR | 校验器名 | √ | √ |
| 14 | DEF_VAL | VARCHAR | 默认值 | √ | √ |
| 15 | DOMAIN_ID | INTEGER | DOMAIN_ID号 | √ | √ |
| 16 | IS_VIRTUAL | BOOLEAN | 该字段是否是虚字段 | √ | √ |
| 17 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 18 | REPET_RATE | DOUBLE | 重复率 | √ | √ |
| 19 | DISPERSION | DOUBLE | 离散度 | √ | √ |
| 20 | MAX_VAL | VARCHAR | 最大值 | √ | √ |
| 21 | MIN_VAL | VARCHAR | 最小值 | √ | √ |
| 22 | HISTOGRAM | CLOB | 柱状图 | √ | √ |
| 23 | ANA_FLAG | TINYINT | 分析控制标记('2':禁止收集统计信息; 其他值，允许收集) | √ | √ |
| 24 | ON_NULL | TINYINT | 是否用默认值替换显式空值(0:无替换; 1:仅插入时替换; 2:插入和更新时替换) | x | √ |
| 25 | NON_NULL_RATE | DOUBLE | 非空值比例 | x | √ |
| 26 | RESERVED3 | VARCHAR | 保留字段 | √ | √ |
应用举例
- on_null字段举例
sql
SQL> CREATE TABLE tab_on_null(c1 VARCHAR DEFAULT NULL, c2 VARCHAR DEFAULT ON NULL 'apple', c3 VARCHAR DEFAULT ON NULL FOR INSERT ONLY 'pear', c4 VARCHAR DEFAULT ON NULL FOR INSERT AND UPDATE 'banana');
-- c2、c3、c4字段插入时null被替换为默认值
SQL> INSERT INTO tab_on_null VALUES(null, null, null, null);
SQL> INSERT INTO tab_on_null VALUES('a', 'b', 'c', 'd');
SQL> SELECT * FROM tab_on_null;
+--------+-------+------+--------+
| C1 | C2 | C3 | C4 |
+--------+-------+------+--------+
| <NULL> | apple | pear | banana |
| a | b | c | d |
+--------+-------+------+--------+
SQL> UPDATE tab_on_null SET c2 = null;
Error: [E16005] 字段C2不能取空值
SQL> UPDATE tab_on_null SET c3 = null;
Error: [E16005] 字段C3不能取空值
-- 修改c4字段，空值被替换为默认值
SQL> UPDATE tab_on_null SET c4 = null;
SQL> SELECT * FROM tab_on_null;
+--------+-------+------+--------+
| C1 | C2 | C3 | C4 |
+--------+-------+------+--------+
| <NULL> | apple | pear | banana |
| a | b | c | banana |
+--------+-------+------+--------+
SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null FROM ALL_COLUMNS uc JOIN ALL_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_on_null';
+-------------+----------+----------+----------+---------+
| TABLE_NAME | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL |
+-------------+----------+----------+----------+---------+
| TAB_ON_NULL | C1 | F | <NULL> | 0 |
| TAB_ON_NULL | C2 | T | 'apple' | 1 |
| TAB_ON_NULL | C3 | T | 'pear' | 1 |
| TAB_ON_NULL | C4 | T | 'banana' | 2 |
+-------------+----------+----------+----------+---------+- VARYING字段举例
char(10): 定长，分配了10个字符空间；若字符长度<10，则剩余空间用空格补齐
varchar(10): 变长，按实际输入进行存储；若字符长度<10，则不会补齐
sql
SQL> CREATE TABLE tab_vary(c1 CHAR, c4 CHAR(10), c2 VARCHAR, c3 VARCHAR(10));
SQL> SELECT t1.COL_NAME, t1.TYPE_NAME, t1."VARYING" FROM ALL_COLUMNS t1 JOIN ALL_TABLES t2 ON t1.db_id = t2.db_id and t1.table_id=t2.table_id and t2.table_name='tab_vary';
+----------+-----------+---------+
| COL_NAME | TYPE_NAME | VARYING |
+----------+-----------+---------+
| C1 | CHAR | F |
| C4 | CHAR | F |
| C2 | CHAR | T |
| C3 | CHAR | T |
+----------+-----------+---------+- scale字段举例
numeric、decimal、interval等类型精度和标度计算规则如下：
- 精度(PRECISION): (scale/65536)::INT
- 标度(SCALE): MOD(scale,65536)
sql
SQL> CREATE TABLE col_info(id INT,name CHAR,col1 VARCHAR(10),col2 NUMERIC(32,6),col3 INTERVAL YEAR TO MONTH);
SQL> SELECT st.table_name,sc.col_name,sc.type_name,scale,(sc.scale/65536)::INT precision,MOD(sc.scale,65536) scaler,"VARYING" FROM ALL_COLUMNS sc JOIN ALL_TABLES st ON sc.db_id=st.db_id AND sc.table_id=st.table_id WHERE st.table_name='COL_INFO';
+------------+----------+------------------------+---------+-----------+--------+---------+
| TABLE_NAME | COL_NAME | TYPE_NAME | SCALE | PRECISION | SCALER | VARYING |
+------------+----------+------------------------+---------+-----------+--------+---------+
| COL_INFO | ID | INTEGER | -1 | 0 | -1 | F |
| COL_INFO | NAME | CHAR | 1 | 0 | 1 | F |
| COL_INFO | COL1 | CHAR | 10 | 0 | 10 | T |
| COL_INFO | COL2 | NUMERIC | 2097158 | 32 | 6 | F |
| COL_INFO | COL3 | INTERVAL YEAR TO MONTH | 524288 | 8 | 0 | F |
+------------+----------+------------------------+---------+-----------+--------+---------+- 查询表列信息
SQL
SQL> CREATE TYPE type_object AS OBJECT(c1 INT, c2 VARCHAR);
SQL> CREATE TABLE col_info(id INT,name CHAR,col1 VARCHAR(10),col2 NUMERIC(32,6),col3 INTERVAL YEAR TO MONTH,col4 type_object);
SQL> SELECT t1.* FROM ALL_COLUMNS t1 JOIN ALL_TABLES t2 ON t1.db_id = t2.db_id and t1.table_id=t2.table_id and t2.table_name='col_info';
+-------+----------+--------+----------+------------------------+---------+---------+---------+---------+----------+-----------+-----------+-------------+----------+---------+-----------+------------+----------+------------+------------+---------+---------+-----------+----------+---------+---------------+-----------+
| DB_ID | TABLE_ID | COL_NO | COL_NAME | TYPE_NAME | SCALE | IS_HIDE | DELETED | VARYING | NOT_NULL | IS_SERIAL | SERIAL_ID | TIMESTAMP_T | COLLATOR | DEF_VAL | DOMAIN_ID | IS_VIRTUAL | COMMENTS | REPET_RATE | DISPERSION | MAX_VAL | MIN_VAL | HISTOGRAM | ANA_FLAG | ON_NULL | NON_NULL_RATE | RESERVED3 |
+-------+----------+--------+----------+------------------------+---------+---------+---------+---------+----------+-----------+-----------+-------------+----------+---------+-----------+------------+----------+------------+------------+---------+---------+-----------+----------+---------+---------------+-----------+
| 1 | 1048585 | 0 | ID | INTEGER | -1 | <NULL> | <NULL> | F | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
| 1 | 1048585 | 1 | NAME | CHAR | 1 | <NULL> | <NULL> | F | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
| 1 | 1048585 | 2 | COL1 | CHAR | 10 | <NULL> | <NULL> | T | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
| 1 | 1048585 | 3 | COL2 | NUMERIC | 2097158 | <NULL> | <NULL> | F | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
| 1 | 1048585 | 4 | COL3 | INTERVAL YEAR TO MONTH | 524288 | <NULL> | <NULL> | F | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
| 1 | 1048585 | 5 | COL4 | SYSDBA.TYPE_OBJECT | -1 | <NULL> | <NULL> | F | F | F | 0 | n | <NULL> | <NULL> | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 0 | <NULL> | <NULL> |
+-------+----------+--------+----------+------------------------+---------+---------+---------+---------+----------+-----------+-----------+-------------+----------+---------+-----------+------------+----------+------------+------------+---------+---------+-----------+----------+---------+---------------+-----------+