物化视图 自动更新 定时刷新

创建定时刷新--------------------------------------------------------------

create  MATERIALIZED VIEW LOG on IT_INOUTROOM with rowid;

CREATE MATERIALIZED VIEWHJM_TEST_IT_INOUTROOM

REFRESH FAST on demand

WITH rowid

STAR TWITH SYSDATE NEXT SYSDATE + 1/1440

AS

SELECT "A"."ID"             "ID",

       "A"."CODE"            "CODE",

      "A"."INOUT_CODE"     "INOUT_CODE",

      "A"."ERP_PACT_CODE"  "ERP_PACT_CODE",

      "A"."ERP_STOCK_CODE" "ERP_STOCK_CODE",

      "A"."CUSTODY_ACCOUNT" "CUSTODY_ACCOUNT",

      "A"."CREDIT_TYPE"    "CREDIT_TYPE",

      "A"."MEDICINE_NAME"  "MEDICINE_NAME",

       "A"."STANDARD"        "STANDARD",

      "A"."PRODUCING_AREA" "PRODUCING_AREA",

       "A"."BASE_UNIT"       "BASE_UNIT",

      "A"."IN_QUANTITY"    "IN_QUANTITY",

       "A"."USE_STATE"       "USE_STATE",

      "A"."OUT_QUANTITY"   "OUT_QUANTITY",

      "A"."PARENT_UNIT_ID" "PARENT_UNIT_ID",

       "A"."STORAGEID"       "STORAGEID",

       "A"."CREDATE"         "CREDATE",

       "A"."KEEPDATE"        "KEEPDATE",

       "A"."INVTYPEID"       "INVTYPEID",

      "A"."SOURCETABLE"    "SOURCETABLE",

       "A"."INOUTFLAG"       "INOUTFLAG",

      "A"."STORAGENAME"    "STORAGENAME",

       "A"."ENTRY_ID"        "ENTRY_ID",

      "A"."ENTRY_NAME"     "ENTRY_NAME",

      "A"."COMPANY_ERP_ID" "COMPANY_ERP_ID",

      "A"."COMPANY_NAME"   "COMPANY_NAME",

       "A"."TRDTLID"         "TRDTLID",

      "A"."LIMIT_TYPE"     "LIMIT_TYPE"

  FROM "IT_INOUTROOM" "A"

 WHERE "A"."KEEPDATE" >=

       TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd  HH24:mi:ss')

   AND "A"."KEEPDATE" <=

      TO_DATE('2016-01-16 23:59:59', 'yyyy-mm-dd HH24:mi:ss');

成功之后会自动生成一个JOB:

 

 

下面是自动跟随更新-----------------------------------------------------

CREATE materialized view log on IT_INOUTROOM;

CREATE MATERIALIZED VIEWHJM_TEST_IT_INOUTROOM

REFRESH FAST ON commit

AS

SELECT "A"."ID"             "ID",

       "A"."CODE"            "CODE",

      "A"."INOUT_CODE"     "INOUT_CODE",

      "A"."ERP_PACT_CODE"  "ERP_PACT_CODE",

      "A"."ERP_STOCK_CODE" "ERP_STOCK_CODE",

      "A"."CUSTODY_ACCOUNT" "CUSTODY_ACCOUNT",

      "A"."CREDIT_TYPE"    "CREDIT_TYPE",

      "A"."MEDICINE_NAME"  "MEDICINE_NAME",

       "A"."STANDARD"        "STANDARD",

      "A"."PRODUCING_AREA" "PRODUCING_AREA",

       "A"."BASE_UNIT"       "BASE_UNIT",

      "A"."IN_QUANTITY"    "IN_QUANTITY",

       "A"."USE_STATE"       "USE_STATE",

      "A"."OUT_QUANTITY"   "OUT_QUANTITY",

      "A"."PARENT_UNIT_ID" "PARENT_UNIT_ID",

       "A"."STORAGEID"       "STORAGEID",

       "A"."CREDATE"         "CREDATE",

       "A"."KEEPDATE"        "KEEPDATE",

       "A"."INVTYPEID"       "INVTYPEID",

      "A"."SOURCETABLE"    "SOURCETABLE",

       "A"."INOUTFLAG"       "INOUTFLAG",

      "A"."STORAGENAME"    "STORAGENAME",

       "A"."ENTRY_ID"        "ENTRY_ID",

      "A"."ENTRY_NAME"     "ENTRY_NAME",

      "A"."COMPANY_ERP_ID" "COMPANY_ERP_ID",

      "A"."COMPANY_NAME"   "COMPANY_NAME",

       "A"."TRDTLID"         "TRDTLID",

      "A"."LIMIT_TYPE"     "LIMIT_TYPE"

  FROM "IT_INOUTROOM" "A"

 WHERE "A"."KEEPDATE" >=

       TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd  HH24:mi:ss')

   AND "A"."KEEPDATE" <=

      TO_DATE('2016-01-16 23:59:59', 'yyyy-mm-dd HH24:mi:ss');

 

删除------------------------------

DROP materialized view log on IT_INOUTROOM;

DROP  materialized  view  HJM_TEST_IT_INOUTROOM;