PROCEDURE REFRESH_TAB
用于刷新由存储过程刷新的表数据,并纪录刷新信息
[@more@]比较类似上一个过程refresh_mv,所不同的是在这里刷新数据所调用的存储过程作为传入参数
PROCEDURE REFRESH_TAB(P_USER_NAME IN VARCHAR2,
P_TAB_NAME IN VARCHAR2,
P_PROC_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_TAB_PROC VARCHAR2(300);
V_FULL_OBJ_NAME VARCHAR2(50) := '';
MISS_TAB EXCEPTION;
BEGIN
--user and object...
V_USER_NAM := LOWER(P_USER_NAME);
V_OBJ_NAM := LOWER(P_TAB_NAME);
V_FULL_OBJ_NAME := V_USER_NAM || '.' || V_OBJ_NAM;
SELECT SYSDATE INTO V_BEGIN FROM DUAL;
--max refresh num for current user and object...
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_TABLES
WHERE OWNER = upper(V_USER_NAM)
AND TABLE_NAME = upper(V_OBJ_NAM);
IF V_COUNT = 0 THEN
RAISE MISS_TAB;
END IF;
--start time...
SELECT SYSDATE INTO V_STRT_DAT_TIM FROM DUAL;
-- row num before refresh...
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_BEF_REFR;
--THIS IS THE REAL JOB THAT WILL BE DONE...
--CALL A PROCEDURE TO RECREATE TABLE
V_TAB_PROC := 'begin ' || V_USER_NAM || '.' || P_PROC_NAME || '; end;';
EXECUTE IMMEDIATE V_TAB_PROC;
--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) * 24 * 60 * 60;
--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,
'P',
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: ' || 'P');
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_TAB THEN
V_ERR_TXT := 'ORA-00942: table 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,
'P',
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: ' || 'P');
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
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,
'P',
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: ' || 'P');
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_TAB;
END EDEN_REFRESH;
执行方式如下
SQL> exec eden_refresh.refresh_tab(schema_name,table_name,procedure_name,log_path);此处的log_path也没用到
SQL> exec eden_refresh.refresh_tab('edenfr','eden_flat_vhc_live_rate','EDEN_FLAT_VHC_LIVE_RATE_FR','c:');
PL/SQL procedure successfully completed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-795303/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/207/viewspace-795303/