GE_OG_CALC_COLUMN_EMPTY

CREATE OR REPLACE PROCEDURE CUST_MKT_DWH.GE_OG_CALC_COLUMN_EMPTY(P_TABLE_NAME IN VARCHAR2) IS
    --TYPE  
    TYPE Type_Column_Name IS RECORD(
        TABLE_NAME  ALL_TAB_COLS.TABLE_NAME%TYPE,
        COLUMN_NAME ALL_TAB_COLS.COLUMN_NAME%TYPE
    );
    TYPE XX               IS TABLE OF Type_Column_Name;
    TYPE Type_CalcSet     IS TABLE OF CUST_MKT_DWH.GE_OG_MKT_CALC_COLUMN_EMPTY%ROWTYPE;
    --Variable of Normal
    V_Column_Name         XX;
    CalcSet               Type_CalcSet := Type_CalcSet();
    V_Empty_Column        NUMBER(8);
    V_Not_Empty_Column    NUMBER(8);
    V_Sql                 VARCHAR2(32767);
    --Variable of EXCEPTION
    DML_EXCEPTION         EXCEPTION;
    PRAGMA EXCEPTION_INIT(DML_EXCEPTION,-24381);
BEGIN
    --First,search data and calc number to insert into CalcSet
    SELECT TABLE_NAME,COLUMN_NAME BULK COLLECT INTO V_Column_Name
    FROM ALL_TAB_COLS
    WHERE TABLE_NAME IN(SELECT TABLE_NAME
                        FROM ALL_TABLES
                        WHERE OWNER = 'CUST_MKT_DWH'
                        AND TABLE_NAME LIKE P_TABLE_NAME);
    
    --EXECUTE IMMEDIATE V_Sql;         
    FOR i IN V_Column_Name.FIRST .. V_Column_Name.LAST LOOP
        V_Sql := 'SELECT COUNT('||V_Column_Name(i).column_name||'),COUNT(*)-COUNT('||V_Column_Name(i).column_name||')
                 FROM '||V_Column_Name(i).table_name; 
        BEGIN   
            EXECUTE IMMEDIATE V_Sql INTO V_Not_Empty_Column,V_Empty_Column;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Execute query count sql script exception');
                CONTINUE;
        END;
        IF V_Not_Empty_Column=0 THEN
            CalcSet.EXTEND;
            CalcSet(CalcSet.LAST).TABLE_NAME    := V_Column_Name(i).table_name;
            CalcSet(CalcSet.LAST).COLUMN_NAME   := V_Column_Name(i).column_name;
            CalcSet(CalcSet.LAST).NOT_EMPTY_NUM := V_Not_Empty_Column;
            CalcSet(CalcSet.LAST).EMPTY_NUM     := V_Empty_Column;
        END IF;
    END LOOP;
    --Second,insert into table from data of CalcSet
    EXECUTE IMMEDIATE 'TRUNCATE TABLE CUST_MKT_DWH.GE_OG_MKT_CALC_COLUMN_EMPTY';
    BEGIN
        FORALL i IN CalcSet.FIRST .. CalcSet.LAST SAVE EXCEPTIONS
            INSERT INTO CUST_MKT_DWH.GE_OG_MKT_CALC_COLUMN_EMPTY(TABLE_NAME,COLUMN_NAME,NOT_EMPTY_NUM,EMPTY_NUM,CREATE_TIME)
                   VALUES(CalcSet(i).TABLE_NAME,CalcSet(i).COLUMN_NAME,CalcSet(i).Not_Empty_NUM,CalcSet(i).Empty_NUM,CURRENT_DATE);
            COMMIT;  
    EXCEPTION
        WHEN DML_EXCEPTION THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('DML exception');
            RAISE;
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('Forall insert others exception');
            RAISE;
    END;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Global others exception');
        RAISE;
END;

　　

转载于:https://www.cnblogs.com/JeromeZ/p/5337910.html