ITPub博客

首页 > 大数据 > 数据挖掘 > 一个数据仓库数据刷新的实现机制(三)

一个数据仓库数据刷新的实现机制(三)

原创 数据挖掘 作者:zhyuh 时间:2005-04-07 16:59:38 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/207/viewspace-795298/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2009552