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

一個資料倉儲資料重新整理的實現機制（四）

相關文章