视图-SYS_VIEWS
📄字数 1.0K
👁️阅读量 加载中...
功能描述
SYS_VIEWS系统表用于存储、管理系统库和用户库中所有表对应的视图信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | SCHEMA_ID | INTEGER | 模式ID | √ | √ |
| 2 | USER_ID | INTEGER | 属主ID | √ | √ |
| 3 | VIEW_ID | INTEGER | 视图ID | √ | √ |
| 4 | VIEW_NAME | VARCHAR | 视图名 | √ | √ |
| 5 | DEFINE | CLOB | 视图定义 | √ | √ |
| 6 | OPTION | INTEGER | 选项(0：无 1：with read only 2：with check option) | √ | √ |
| 7 | CREATE_TIME | DATETIME | 创建时间 | √ | √ |
| 8 | VALID | BOOLEAN | 是否有效 | √ | √ |
| 9 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 10 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 11 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 12 | RESERVED2 | 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 WITH CHECK OPTION;
SQL> SELECT * FROM SYS_VIEWS WHERE view_name='view_test';
+-------+-----------+---------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | VIEW_ID | VIEW_NAME | DEFINE | OPTION | CREATE_TIME | VALID | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+-----------+---------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+
| 1 | 1 | 1 | 1048592 | VIEW_TEST | <CLOB> | 2 | 2025-06-27 10:20:43.399 | T | F | <NULL> | <NULL> | <NULL> |
+-------+-----------+---------+---------+-----------+--------+--------+--------------------------+-------+--------+----------+-----------+-----------+- 通过视图名称查询视图定义
sql
SQL> SELECT view_name,TO_CHAR(define) define FROM SYS_VIEWS WHERE view_name='view_test';
+-----------+--------------------------------------------------------------------------------------+
| VIEW_NAME | DEFINE |
+-----------+--------------------------------------------------------------------------------------+
| VIEW_TEST | CREATE VIEW "VIEW_TEST"("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O")+|
| | AS +|
| | SELECT +|
| | "TAB_BASE"."A", +|
| | "TAB_BASE"."B", +|
| | "TAB_BASE"."C", +|
| | "TAB_BASE"."D", +|
| | "TAB_BASE"."E", +|
| | "TAB_BASE"."F", +|
| | "TAB_BASE"."G", +|
| | "TAB_BASE"."H", +|
| | "TAB_BASE"."I", +|
| | "TAB_BASE"."J", +|
| | "TAB_BASE"."K", +|
| | "TAB_BASE"."L", +|
| | "TAB_BASE"."M", +|
| | "TAB_BASE"."N", +|
| | "TAB_BASE"."O" +|
| | FROM +|
| | "TAB_BASE" +|
| | WITH CHECK OPTION |
+-----------+--------------------------------------------------------------------------------------+