Oracle 的视图

Oracle 的视图

源数据：

-- Create table
create table STU_INFO
(
  id    NUMBER not null,
  name  VARCHAR2(8),
  score NUMBER(4,1),
  class VARCHAR2(2)
)
tablespace STUDENT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table STU_INFO
  add primary key (ID)
  using index 
  tablespace STUDENT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

1. 创建普通视图

CREATE [OR REPLACE] VIEW view_name [(column1, column2, ...)]
AS
SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition;

CREATE OR REPLACE VIEW STU_INFO_VIEW AS
SELECT STU_INFO.ID,STU_INFO.NAME,STU_INFO.SCORE
FROM  STU_INFO WHERE STU_INFO.SCORE>60;

ps: 所有的视图的是基于基表的数据,相当于引用那个表的数据,当普通视图的值发生改变时,则基表值也会改变,反之亦然。

优点： 实时性，简化查询，安全性。 缺点： 影响性能，数据一致性（底层数据发生改变视图数据也会改变）

2. 创建物化视图

CREATE [MATERIALIZED] VIEW view_name
[REFRESH {FAST|COMPLETE|FORCE ON | COMMIT | DEMAND}] -- 刷新方式 增量刷新 完全刷新  自动选择|自动刷新 手动刷新  默认：  FORCE ON COMMIT
[START WITH date_value]
[NEXT date_value]
[WITH {PRIMARY KEY|ROWID}]
[ENABLE|DISABLE] QUERY REWRITE
AS
select_statement;

CREATE MATERIALIZED VIEW MV_STU_INFO
REFRESH FORCE ON DEMAND
AS
SELECT STU_INFO.ID,STU_INFO.NAME,STU_INFO.SCORE
FROM  STU_INFO;

BEGIN
  DBMS_MVIEW.REFRESH('MV_STU_INFO', 'C');
END;
-- 执行手动刷新

ps：优点：性能提升，数据一致性，分布式数据处理。 缺点： 占用存储空间，数据更新延迟

3. 创建物化增量视图

-- 前提是必须创建物化视图日志，记录基表发生了哪些变化（增删改查），用这些记录去更新物化视图
CREATE MATERIALIZED  VIEW LOG  ON "TABLE_NAME" WITH <PRIMARY KEY | ROWID>

CREATE MATERIALIZED VIEW LOG ON STU_INFO WITH ID;
CREATE MATERIALIZED VIEW MV_STU_INFO_FAST 
REFRESH FAST
AS 
SELECT ID,NAME FROM STU_INFO;

3. oracle查询表结构

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'STU_INFO'; -- 替换为实际的表名