SQL物化视图 自动更新 定时刷新http://www.bieryun.com/3483.html
创建定时刷新--------------------------------------------------------------
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;
