Procedure EDEN_REFRESH.REFRESH_MV
用于刷新MV并纪录刷新信息
[@more@]这是一个包(package)的一部分，附上包头：
CREATE OR REPLACE PACKAGE EDEN_REFRESH IS
PROCEDURE REFRESH_MV(P_USER_NAME IN VARCHAR2,
P_MV_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2);
PROCEDURE REFRESH_TAB(P_USER_NAME IN VARCHAR2,
P_TAB_NAME IN VARCHAR2,
P_PROC_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2); --the proc to rebuild tab
END EDEN_REFRESH;
过程EDEN_REFRESH.REFRESH_MV的脚本如下。注意刷新的时候调用了dbms_refresh.make / dbms_refresh.refresh / dbms_refresh.destroy，之所以不调用package dbms_mview, 是为了实现刷新失败时能自动回滚。参见文档http://blog.itpub.net/post/334/15305 和 http://blog.itpub.net/post/334/15868
CREATE OR REPLACE PACKAGE BODY EDEN_REFRESH IS
PROCEDURE REFRESH_MV(P_USER_NAME IN VARCHAR2,
P_MV_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2) IS
V_USER_NAM EDEN_REFR_LOG.USER_NAM%TYPE := '';
V_OBJ_NAM EDEN_REFR_LOG.OBJ_NAM%TYPE := '';
V_STRT_DAT_TIM EDEN_REFR_LOG.STRT_DAT_TIM%TYPE := NULL;
V_END_DAT_TIM EDEN_REFR_LOG.END_DAT_TIM%TYPE := NULL;
V_REFR_SEQ_NB EDEN_REFR_LOG.REFR_SEQ_NB%TYPE := 0;
V_TOT_DRTN_TIM INTEGER := 0; -- time consumed in seconds
V_ROWS_BEF_REFR EDEN_REFR_LOG.ROWS_BEF_REFR%TYPE := 0;
V_ROWS_AFT_REFR EDEN_REFR_LOG.ROWS_AFT_REFR%TYPE := 0;
V_ERR_TXT EDEN_REFR_LOG.ERR_TXT%TYPE := '';
V_BEGIN EDEN_REFR_LOG.STRT_DAT_TIM%TYPE := NULL;
V_END EDEN_REFR_LOG.END_DAT_TIM%TYPE := NULL;
V_PRG_DRTN INTEGER := 0;
V_COUNT NUMBER;
V_FULL_OBJ_NAME VARCHAR2(50) := '';
MISS_MV EXCEPTION;
BEGIN
V_USER_NAM := LOWER(P_USER_NAME); --user and object...
V_OBJ_NAM := LOWER(P_MV_NAME);
V_FULL_OBJ_NAME := V_USER_NAM || '.' || V_OBJ_NAM;
SELECT SYSDATE INTO V_BEGIN FROM DUAL;
SELECT MAX(REFR_SEQ_NB) --max refresh num...
INTO V_REFR_SEQ_NB
FROM EDEN_REFR_LOG L
WHERE L.USER_NAM = V_USER_NAM
AND L.OBJ_NAM = V_OBJ_NAM;
IF V_REFR_SEQ_NB IS NULL THEN
V_REFR_SEQ_NB := 0;
END IF;
SELECT count(*)
INTO V_COUNT
FROM DBA_MVIEWS
WHERE OWNER = upper(V_USER_NAM)
AND MVIEW_NAME = upper(V_OBJ_NAM);
IF V_COUNT = 0 THEN
RAISE MISS_MV;
END IF;
SELECT count(*)
INTO V_COUNT
FROM user_refresh
WHERE rname = 'EDEN_GROUP';
IF V_COUNT = 1 THEN
dbms_refresh.destroy('EDEN_GROUP');
END IF;
--row num before refresh...
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_BEF_REFR;
--start time...
SELECT SYSDATE INTO V_STRT_DAT_TIM FROM DUAL;
--THIS IS THE REAL JOB...
DBMS_REFRESH.make(name => 'EDEN_GROUP',
list => 'EDEN_DUMMY,' || V_FULL_OBJ_NAME,
next_date => sysdate,
interval => 'sysdate+1/24',
implicit_destroy => true);
DBMS_REFRESH.refresh('EDEN_GROUP');
COMMIT;
DBMS_REFRESH.destroy('EDEN_GROUP');
COMMIT;
--end time...
SELECT SYSDATE INTO V_END_DAT_TIM FROM DUAL;
--what,total time of the job...
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_AFT_REFR;
V_TOT_DRTN_TIM := (V_END_DAT_TIM - V_STRT_DAT_TIM) * 3600 * 24;
--refresh successfully,and write log table...
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
TOT_DRTN_TIM,
ROWS_BEF_REFR,
ROWS_AFT_REFR,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_STRT_DAT_TIM,
V_END_DAT_TIM,
V_TOT_DRTN_TIM,
V_ROWS_BEF_REFR,
V_ROWS_AFT_REFR,
'',
'Y');
COMMIT;
SELECT SYSDATE INTO V_END FROM DUAL;
V_PRG_DRTN := (V_END - V_BEGIN) * 3600 * 24;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_strt_dat_tim, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refrsh end: ' ||
TO_CHAR(v_end_dat_tim, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh duration: ' || V_TOT_DRTN_TIM ||
' seconds');
dbms_output.put_line('program start: ' ||
TO_CHAR(V_BEGIN, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program end: ' ||
TO_CHAR(V_END, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program duration: ' || V_PRG_DRTN ||
' seconds');
dbms_output.put_line('rows before refresh: ' || v_rows_bef_refr);
dbms_output.put_line('rows after refresh : ' || v_rows_aft_refr);
dbms_output.put_line('error text: ' || '');
dbms_output.put_line('refresh status: ' || 'Y');
EXCEPTION
WHEN MISS_MV THEN
V_ERR_TXT := 'ORA-00942: materialized view does not exist';
BEGIN
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_BEGIN,
SYSDATE,
V_ERR_TXT,
'N');
COMMIT;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_begin, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh end: ' ||
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('error text: ' || v_err_txt);
dbms_output.put_line('refresh status: ' || 'N');
EXCEPTION
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
DBMS_OUTPUT.PUT_LINE(V_ERR_TXT); --write err msg to call env...
END;
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_AFT_REFR;
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
TOT_DRTN_TIM,
ROWS_BEF_REFR,
ROWS_AFT_REFR,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_STRT_DAT_TIM,
V_END_DAT_TIM,
V_TOT_DRTN_TIM,
V_ROWS_BEF_REFR,
V_ROWS_AFT_REFR,
V_ERR_TXT,
'N');
COMMIT;
SELECT SYSDATE INTO V_END FROM DUAL;
V_PRG_DRTN := (V_END - V_BEGIN) * 3600 * 24;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_strt_dat_tim,
'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refrsh end: ' ||
TO_CHAR(v_end_dat_tim,
'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh duration: ' || V_TOT_DRTN_TIM ||
' seconds');
dbms_output.put_line('program start: ' ||
TO_CHAR(V_BEGIN, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program end: ' ||
TO_CHAR(V_END, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program duration: ' || V_PRG_DRTN ||
' seconds');
dbms_output.put_line('rows before refresh: ' || v_rows_bef_refr);
dbms_output.put_line('rows after refresh : ' || v_rows_aft_refr);
dbms_output.put_line('error text: ' || v_err_txt);
dbms_output.put_line('refresh status: ' || 'N');
EXCEPTION
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
DBMS_OUTPUT.PUT_LINE(V_ERR_TXT); --write err msg to call env...
END;
END REFRESH_MV;
执行方式如下：
SQL>exec eden_refresh.refresh_mv(schema_name,MV_name,log_path); 其实log_path这个参数没用到，先不去删它
SQL> exec eden_refresh.refresh_mv('edenfr','EDEN_RLF_INVC_MV','c:');
PL/SQL procedure successfully completed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/207/viewspace-795298/ ，如需转载，请注明出处，否则将追究法律责任。
