oracle 物化视图

CREATE MATERIALIZED VIEW MV_LAST_EMPLOYEE
REFRESH COMPLETE ON DEMAND
 
AS
SELECT
--*************************************************************
-- CURRENT VERSION: V1.0
-- AUTHOR  : YJ
-- CREATED : 2014-04-17
-- PURPOSE : 查询员工最新网点
--           数据来源为TM_EMPLOYEE
-- MODIFY HISTORY
-- -------------------------------------------------------------
--*************************************************************


 "EMP_CODE","EMP_NAME","DEPT_CODE","LEV"
  FROM (SELECT T.EMP_CODE,t.EMP_NAME,
               T.DEPT_CODE,
               RANK() OVER(PARTITION BY T.EMP_CODE ORDER BY T.VALID_FLG DESC, T.Modified_Tm DESC, T.LOGOUT_DT DESC) AS LEV
          FROM TM_EMPLOYEE T
        ) R

WHERE R.LEV = 1;

begin
  sys.dbms_job.submit(job => :job,
                      what => 'begin   dbms_mview.refresh(''MV_LAST_EMPLOYEE'',''C'');end;',
                      next_date => to_date('06-02-2017 17:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(sysdate,''HH24'') + 8/24');
  commit;
end;
/