视图字段列-SYS_VIEW_COLUMNS
📄字数 1.6K
👁️阅读量 加载中...
功能描述
SYS_VIEW_COLUMNS系统表用于存储、管理系统库和用户库中所有表对应的视图的字段信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | VIEW_ID | INTEGER | 视图ID | √ | √ |
| 2 | COL_NAME | VARCHAR | 字段名 | √ | √ |
| 3 | COL_NO | INTEGER | 字段序号 | √ | √ |
| 4 | TYPE_NAME | VARCHAR | 类型名 | √ | √ |
| 5 | SCALE | INTEGER | 数据尺寸 | √ | √ |
| 6 | VARYING | BOOLEAN | 是否变长（对char binary有效） | √ | √ |
| 7 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 8 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 9 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
| 10 | RESERVED3 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 查询视图列信息
sql
SQL> CREATE TABLE tab_base(a INTEGER,b BIGINT,c FLOAT,d DOUBLE,e TINYINT,f SMALLINT,g NUMERIC,h CHAR,i VARCHAR,j CLOB,k BLOB,l GUID,m BOOLEAN,n BINARY,o ROWVERSION);
SQL> CREATE VIEW view_test AS SELECT * FROM tab_base;
SQL> SELECT * FROM SYS_VIEW_COLUMNS svc JOIN SYS_VIEWS sv USING(db_id,view_id) WHERE sv.view_name='view_test';
+-------+---------+----------+--------+------------+--------+---------+----------+-----------+-----------+-----------+-----------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+
| DB_ID | VIEW_ID | COL_NAME | COL_NO | TYPE_NAME | SCALE | VARYING | COMMENTS | RESERVED1 | RESERVED2 | RESERVED3 | SCHEMA_ID | USER_ID | VIEW_NAME | DEFINE | OPTION | CREATE_TIME | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+---------+----------+--------+------------+--------+---------+----------+-----------+-----------+-----------+-----------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+
| 1 | 1048589 | A | 0 | INTEGER | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | B | 1 | BIGINT | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | C | 2 | FLOAT | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | D | 3 | DOUBLE | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | E | 4 | TINYINT | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | F | 5 | SMALLINT | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | G | 6 | NUMERIC | 786432 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | H | 7 | CHAR | 1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | I | 8 | CHAR | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | J | 9 | CLOB | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | K | 10 | BLOB | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | L | 11 | GUID | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | M | 12 | BOOLEAN | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | N | 13 | BINARY | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
| 1 | 1048589 | O | 14 | ROWVERSION | -1 | F | <NULL> | <NULL> | <NULL> | <NULL> | 1 | 1 | VIEW_TEST | <CLOB> | 0 | 2025-06-27 10:02:12.993 | T | F | <NULL> | <NULL> | <NULL> |
+-------+---------+----------+--------+------------+--------+---------+----------+-----------+-----------+-----------+-----------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+- valid字段举例
sql
SQL> DROP TABLE tab_base CASCADE;
SQL> SELECT DISTINCT VALID FROM SYS_VIEW_COLUMNS svc JOIN SYS_VIEWS sv USING(db_id,view_id) WHERE sv.view_name='view_test';
+-------+
| VALID |
+-------+
| F |
+-------+