远程刷新表数据物化视图更新表

1、创建物化视图

CREATE MATERIALIZED VIEW MV_MATERIALIZED_B_CTN_NORMAL_N

REFRESH FAST ON DEMAND

WITH ROWID

AS

SELECT "B_CTN_NORMAL"."ID" "ID","B_CTN_NORMAL"."C_BALE" "C_BALE","B_CTN_NORMAL"."N_CHECKORGAN" "N_CHECKORGAN","B_CTN_NORMAL"."C_CHECKORGAN" "C_CHECKORGAN","B_CTN_NORMAL"."N_MADEIN" "N_MADEIN","B_CTN_NORMAL"."C_MADEIN" "C_MADEIN","B_CTN_NORMAL"."N_HVINO" "N_HVINO","B_CTN_NORMAL"."N_FACTORY" "N_FACTORY","B_CTN_NORMAL"."C_FACTORY" "C_FACTORY","B_CTN_NORMAL"."C_LOTNO" "C_LOTNO","B_CTN_NORMAL"."N_BAGNUM" "N_BAGNUM","B_CTN_NORMAL"."N_MADETYPE" "N_MADETYPE","B_CTN_NORMAL"."C_MADETYPE" "C_MADETYPE","B_CTN_NORMAL"."D_PRODATETIME" "D_PRODATETIME","B_CTN_NORMAL"."N_VARIETY" "N_VARIETY","B_CTN_NORMAL"."C_VARIETY" "C_VARIETY","B_CTN_NORMAL"."N_WEIGHT" "N_WEIGHT","B_CTN_NORMAL"."N_MOISTURE" "N_MOISTURE","B_CTN_NORMAL"."N_FIBER" "N_FIBER","B_CTN_NORMAL"."N_COLOR" "N_COLOR","B_CTN_NORMAL"."N_COLORGRADE" "N_COLORGRADE","B_CTN_NORMAL"."C_COLORGRADE" "C_COLORGRADE","B_CTN_NORMAL"."N_MIKE" "N_MIKE","B_CTN_NORMAL"."N_MIKEGRADE" "N_MIKEGRADE","B_CTN_NORMAL"."C_MIKEGRADE" "C_MIKEGRADE","B_CTN_NORMAL"."N_LENGTH" "N_LENGTH","B_CTN_NORMAL"."C_LENGRADE" "C_LENGRADE","B_CTN_NORMAL"."N_LENGRADE" "N_LENGRADE","B_CTN_NORMAL"."N_SHORTFIBER" "N_SHORTFIBER","B_CTN_NORMAL"."N_INTENVALUE" "N_INTENVALUE","B_CTN_NORMAL"."N_INTENSION" "N_INTENSION","B_CTN_NORMAL"."C_INTENSION" "C_INTENSION","B_CTN_NORMAL"."N_COLORRD" "N_COLORRD","B_CTN_NORMAL"."N_COLORB" "N_COLORB","B_CTN_NORMAL"."N_TRASHRATE" "N_TRASHRATE","B_CTN_NORMAL"."N_PARTICLE" "N_PARTICLE","B_CTN_NORMAL"."N_UNIFORMITY" "N_UNIFORMITY","B_CTN_NORMAL"."N_UNIFORMITYID" "N_UNIFORMITYID","B_CTN_NORMAL"."C_UNIFORMITYID" "C_UNIFORMITYID","B_CTN_NORMAL"."N_ELONGATION" "N_ELONGATION","B_CTN_NORMAL"."N_NEPS" "N_NEPS","B_CTN_NORMAL"."N_MATURITY" "N_MATURITY","B_CTN_NORMAL"."N_SEEDWOOL" "N_SEEDWOOL","B_CTN_NORMAL"."N_SEEDWOOLID" "N_SEEDWOOLID","B_CTN_NORMAL"."C_SEEDWOOL" "C_SEEDWOOL","B_CTN_NORMAL"."N_GRADEMARK" "N_GRADEMARK","B_CTN_NORMAL"."C_GRADEMARK" "C_GRADEMARK","B_CTN_NORMAL"."N_GIN" "N_GIN","B_CTN_NORMAL"."C_GIN" "C_GIN","B_CTN_NORMAL"."C_SURFACEGRADE" "C_SURFACEGRADE","B_CTN_NORMAL"."D_VERIFYDATETIME" "D_VERIFYDATETIME","B_CTN_NORMAL"."C_SIGNTYPE" "C_SIGNTYPE","B_CTN_NORMAL"."N_GRADE" "N_GRADE","B_CTN_NORMAL"."N_REMARK" "N_REMARK","B_CTN_NORMAL"."N_LEAFGRADE" "N_LEAFGRADE","B_CTN_NORMAL"."C_RETEST" "C_RETEST","B_CTN_NORMAL"."C_RETESTFLAGS" "C_RETESTFLAGS","B_CTN_NORMAL"."C_TWOSIDEDFLAG" "C_TWOSIDEDFLAG","B_CTN_NORMAL"."N_STUDY1" "N_STUDY1","B_CTN_NORMAL"."N_STUDY2" "N_STUDY2","B_CTN_NORMAL"."C_HVI900" "C_HVI900","B_CTN_NORMAL"."C_CHECKLOT" "C_CHECKLOT","B_CTN_NORMAL"."N_OPERATOR" "N_OPERATOR","B_CTN_NORMAL"."N_SUPERVISOR" "N_SUPERVISOR","B_CTN_NORMAL"."D_UPLOADTIME" "D_UPLOADTIME","B_CTN_NORMAL"."B_ISLOTED" "B_ISLOTED","B_CTN_NORMAL"."C_MEMO" "C_MEMO","B_CTN_NORMAL"."N_LINE" "N_LINE","B_CTN_NORMAL"."C_PRODUCTNAME" "C_PRODUCTNAME","B_CTN_NORMAL"."N_LAB" "N_LAB","B_CTN_NORMAL"."C_LAB" "C_LAB","B_CTN_NORMAL"."N_TRASH" "N_TRASH","B_CTN_NORMAL"."C_NEWHVINO" "C_NEWHVINO","B_CTN_NORMAL"."N_MAINMADE" "N_MAINMADE","B_CTN_NORMAL"."D_GINVERIFYTIME" "D_GINVERIFYTIME","B_CTN_NORMAL"."N_BALECHECK" "N_BALECHECK","B_CTN_NORMAL"."C_DEFECT" "C_DEFECT","B_CTN_NORMAL"."C_DEFECTID" "C_DEFECTID","B_CTN_NORMAL"."N_LENGTH_FIX" "N_LENGTH_FIX","B_CTN_NORMAL"."N_LENGRADE_FIX" "N_LENGRADE_FIX","B_CTN_NORMAL"."C_LENGRADE_FIX" "C_LENGRADE_FIX","B_CTN_NORMAL"."N_COLORGRADE_GIN" "N_COLORGRADE_GIN","B_CTN_NORMAL"."C_COLORGRADE_GIN" "C_COLORGRADE_GIN","B_CTN_NORMAL"."D_COLORGRADETIME" "D_COLORGRADETIME","B_CTN_NORMAL"."N_MIKELEVEL" "N_MIKELEVEL","B_CTN_NORMAL"."C_MIKELEVEL" "C_MIKELEVEL","B_CTN_NORMAL"."N_GINRETEST" "N_GINRETEST","B_CTN_NORMAL"."D_GINRETESTTIME" "D_GINRETESTTIME","B_CTN_NORMAL"."C_JOBNUMBER" "C_JOBNUMBER","B_CTN_NORMAL"."D_DISTIME" "D_DISTIME","B_CTN_NORMAL"."C_XZQHCODE" "C_XZQHCODE","B_CTN_NORMAL"."C_FACTORYBATCHNO" "C_FACTORYBATCHNO","B_CTN_NORMAL"."N_HASFOREIGNFIBER" "N_HASFOREIGNFIBER","B_CTN_NORMAL"."N_ISFLOWOUT" "N_ISFLOWOUT","B_CTN_NORMAL"."D_OUTDOWNTIME" "D_OUTDOWNTIME","B_CTN_NORMAL"."N_WORK_YEAR" "N_WORK_YEAR" FROM "ZXJ"."B_CTN_NORMAL"@"D_ZXJ" "B_CTN_NORMAL" WHERE "B_CTN_NORMAL"."N_WORK_YEAR">2009;

2、创建存储过程刷新物化视图

CREATE OR REPLACE PROCEDURE pro_mv_normal_refresh

AS

BEGIN

dbms_mview.REFRESH('mv_materialized_b_ctn_normal');

END;

3、创建存储过程根据物化视图做表更新

create or replace procedure pro_update_inspect_day

AS

CURSOR sel_emp IS SELECT * FROM MV_MATERIALIZED_b_ctn_normal

where n_madein in(66,65) and d_uploadtime between trunc(sysdate-1)

and trunc(sysdate)-1/24/60/60;--定义游标,该游标指向查询结果

---d_uploadtime between to_date('2017-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and

---to_date('2017-05-14 23:59:59', 'yyyy-mm-dd hh24:mi:ss');

 

 

 

rowresult MV_MATERIALIZED_b_ctn_normal%ROWTYPE;

BEGIN

OPEN sel_emp;--打开游标

LOOP FETCH sel_emp INTO rowresult;--将游标中的值赋给rowresult

EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环

delete from cotton_lint_inspect where barcode = rowresult.c_bale;--删除

insert into cotton_lint_inspect(BARCODE, AGENCYNAME, TESTTIME, PRODUCTNAME, PROCESSTYPE, PACKETCODE, PLACE, COLOR, MKLGRADE, LENGTHLEVEL, LENGTHVALUE, LENNEATINDEX, MKLVALUE, STAPLEINDEX, REFLECTIVITY, YELLOWNESS, IMPURITYNUM, IMPURITYAREA, FRACTUREELONG, FRACTURESTR, NEP, MATURECOEFF, ROLLINGQUALITY, GRADE, IMPUTITYRATE, REMARK, MKLVALLEVEL, SENSECOLVALUE, ENTERPRISECODE, FOREFIBER, QUALITYMARK, WORKYEAR, SAMPLECODE, LABCODE, LABNAME, FCTFLAG, UPTIME, PROCESSBATCHCODE)

values(

rowresult.c_bale,

rowresult.c_checkorgan,

to_char(rowresult.D_VERIFYDATETIME, 'yyyy-mm-dd'),

decode(rowresult.C_MADETYPE, '长绒棉', '002-016-002', '002-016-001'),

decode(rowresult.C_MADETYPE,

'锯齿细绒棉',

'002-001-001',

'皮辊细绒棉',

'002-001-002',

'锯齿机采棉',

'002-001-003',

'长绒棉',

'002-001-004',

'皮辊机采棉',

'002-001-005',

'棕色天然彩色细绒棉',

'002-001-006',

'绿色天然彩色细绒棉',

'002-001-007',

rowresult.C_MADETYPE),

rowresult.N_BAGNUM,

rowresult.C_MADEIN,

case when rowresult.c_madetype in ('锯齿细绒棉','锯齿机采棉')

then decode( rowresult.C_COLORGRADE,

'白棉1级',

'002-011-001',

'白棉2级',

'002-011-002',

'白棉3级',

'002-011-003',

'白棉4级',

'002-011-004',

'白棉5级',

'002-011-005',

'淡点污棉1级',

'002-011-006',

'淡点污棉2级',

'002-011-007',

'淡点污棉3级',

'002-011-008',

'淡黄染棉1级',

'002-011-009',

'淡黄染棉2级',

'002-011-010',

'淡黄染棉3级',

'002-011-011',

'黄染棉1级',

'002-011-012',

'黄染棉2级',

'002-011-013')

else

null end ,

rowresult.C_MIKEGRADE,

rowresult.c_lengrade,

rowresult.N_LENGTH,

rowresult.n_uniformity,

case

when substr(rowresult.N_MIKE, instr(rowresult.N_MIKE, '.') + 2, 1) = 5 and

mod(substr(rowresult.N_MIKE, instr(rowresult.N_MIKE, '.') + 1, 1), 2) = 0 then

trunc(rowresult.N_MIKE, 1)

else

round(rowresult.N_MIKE, 1)

end,

'/',

rowresult.N_COLORRD,

rowresult.n_colorb,

'/',

'/',

'/',

rowresult.N_INTENVALUE,

'/',

'/',

rowresult.C_GIN,

decode(rowresult.C_MADETYPE,

'锯齿细绒棉',

null,

'锯齿机采棉',

null,

rowresult.C_GRADEMARK),

'/',

rowresult.c_memo,

substr(rowresult.C_MIKELEVEL,1,1),

'/',

substr(rowresult.c_factorybatchno,1,5),

decode(rowresult.n_hasforeignfiber,

'0',

'002-004-002',

'1',

'002-004-001',

rowresult.n_hasforeignfiber),

decode(rowresult.c_madetype,

'锯齿细绒棉',

rowresult.n_color || substr(rowresult.c_lengrade, 1, 2)||rowresult.c_mikelevel,

'锯齿机采棉',

rowresult.n_color || substr(rowresult.c_lengrade, 1, 2)||rowresult.c_mikelevel,

substr(rowresult.c_grademark, 1, 1) || substr(rowresult.c_lengrade, 1, 2) ||substr(rowresult.c_mikelevel, 1, 1)

),

2016,

trim(rowresult.c_lotno),

rowresult.N_LAB,

rowresult.C_LAB,

3,

to_char(rowresult.D_UPLOADTIME,'yyyy-mm-dd hh24:mi:ss'),

rowresult.C_FACTORYBATCHNO

);

END LOOP;

CLOSE sel_emp;--关闭游标

commit;

END;

4、创建存储过程调用存储过程2和3

CREATE OR REPLACE PROCEDURE pro_mv_inspect_call

AS

BEGIN

pro_mv_normal_refresh;

Dbms_Output.put_line('==>pro_mv_normal_refresh over');

pro_update_inspect_day;

Dbms_Output.put_line('==>pro_update_inspect_day over');

END;

5、创建job定期执行存储过程4

declare test_job number;

begin

dbms_job.submit(test_job,pro_mv_inspect_call;',sysdate,'sysdate+1/1440');

commit;

end;