Oracle如何实现视图每日更新一次且更新时覆盖原有数据

首先你当前创建的是普通关系型数据库的标准视图，这类视图本身是虚拟逻辑表，不会实际存储查询结果，每次访问时都会实时执行关联逻辑返回数据，天然不存在「更新数据、覆盖原有数据」的行为。要实现每日定时固化查询结果、覆盖旧数据的需求，有两种主流实现路径：

方案1：使用物化视图（支持的数据库可直接用） #

如果你的数据库是PostgreSQL、Oracle、ClickHouse这类原生支持*物化视图（实体化视图）*的产品，可以直接把普通视图替换为定时刷新的物化视图：

• 首先删除原有普通视图：

DROP VIEW IF EXISTS VW_InformacaoApontamanto;

• 创建物化视图（以PostgreSQL为例）：

CREATE MATERIALIZED VIEW VW_InformacaoApontamanto AS
select *
from INFO_APONTAMENTO as InfoApontamento
INNER JOIN Barco as barco on barco.Id = InfoApontamento.Barco_Id
INNER JOIN LOCAL_OPERACAO as localDaOperacao on localDaOperacao.Id = InfoApontamento.LocalDaOperacao_Id
INNER JOIN CODIGOS_OPERACAO as codigosDeOperacao on codigosDeOperacao.Id = InfoApontamento.CodigosDeOperacao_Id
LEFT JOIN INFO_METEOROLOGICA as infoMetereologica on infoMetereologica.Id = InfoApontamento.InfoMeterologica_Id
LEFT JOIN SUB_CODE_OPERACAO as subCodigosOperacao on subCodigosOperacao.Id = InfoApontamento.SubCodigosDeOperacao_Id
LEFT JOIN INFO_POSICIONAMENTO as infoPosicionamento on infoPosicionamento.Id = InfoApontamento.InfoPosicionamento_Id;

• 配置每日刷新规则，覆盖旧数据：
  PostgreSQL可以配合pg_cron插件设置定时任务，每天凌晨1点刷新：

SELECT cron.schedule('daily-refresh-VW_InformacaoApontamanto', '0 1 * * *', 'REFRESH MATERIALIZED VIEW VW_InformacaoApontamanto;');

方案2：定时任务+普通表（兼容所有数据库，比如MySQL、SQL Server） #

如果你的数据库不支持物化视图，可以用「结果表+定时任务」的方式实现：

• 第一步创建存储结果的普通表，结构和你视图的查询结果一致：

-- 先根据查询结果建表，第一次直接导入数据
CREATE TABLE VW_InformacaoApontamanto AS
select *
from INFO_APONTAMENTO as InfoApontamento
INNER JOIN Barco as barco on barco.Id = InfoApontamento.Barco_Id
INNER JOIN LOCAL_OPERACAO as localDaOperacao on localDaOperacao.Id = InfoApontamento.LocalDaOperacao_Id
INNER JOIN CODIGOS_OPERACAO as codigosDeOperacao on codigosDeOperacao.Id = InfoApontamento.CodigosDeOperacao_Id
LEFT JOIN INFO_METEOROLOGICA as infoMetereologica on infoMetereologica.Id = InfoApontamento.InfoMeterologica_Id
LEFT JOIN SUB_CODE_OPERACAO as subCodigosOperacao on subCodigosOperacao.Id = InfoApontamento.SubCodigosDeOperacao_Id
LEFT JOIN INFO_POSICIONAMENTO as infoPosicionamento on infoPosicionamento.Id = InfoApontamento.InfoPosicionamento_Id;

• 第二步创建定时执行的SQL逻辑，先清空表再插入新数据实现覆盖更新，建议包在事务里避免访问时数据为空：

BEGIN TRANSACTION;
-- 清空旧数据
TRUNCATE TABLE VW_InformacaoApontamanto;
-- 插入最新数据
INSERT INTO VW_InformacaoApontamanto
select *
from INFO_APONTAMENTO as InfoApontamento
INNER JOIN Barco as barco on barco.Id = InfoApontamento.Barco_Id
INNER JOIN LOCAL_OPERACAO as localDaOperacao on localDaOperacao.Id = InfoApontamento.LocalDaOperacao_Id
INNER JOIN CODIGOS_OPERACAO as codigosDeOperacao on codigosDeOperacao.Id = InfoApontamento.CodigosDeOperacao_Id
LEFT JOIN INFO_METEOROLOGICA as infoMetereologica on infoMetereologica.Id = InfoApontamento.InfoMeterologica_Id
LEFT JOIN SUB_CODE_OPERACAO as subCodigosOperacao on subCodigosOperacao.Id = InfoApontamento.SubCodigosDeOperacao_Id
LEFT JOIN INFO_POSICIONAMENTO as infoPosicionamento on infoPosicionamento.Id = InfoApontamento.InfoPosicionamento_Id;
COMMIT;

• 第三步配置定时触发规则：
  • MySQL可以用自带的事件调度器设置每日执行
  • SQL Server可以用SQL Server代理作业配置
  • 也可以用操作系统层面的crontab（Linux）、任务计划程序（Windows）触发脚本执行

注意事项 #

• 部分数据库不支持在事务中执行TRUNCATE操作，可以替换为DELETE FROM VW_InformacaoApontamanto实现旧数据清理，大表场景下TRUNCATE性能更优
• 如果你需要保留视图的查询透明性，也可以保留原来的普通视图，把视图的查询源指向上面的固化结果表即可
• 定时任务的执行时间建议选在业务低峰期，避免关联查询占用过多数据库资源影响正常业务

内容的提问来源于stack exchange，提问作者AllPower