GE_OG_CALC_COLUMN_EMPTY

时间:2021-12-07 13:21:55
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;