테이블 일별 건수 기록 프로시져

CREATE OR REPLACE PROCEDURE SP_TBL_DAILY_COUNT
/*
-- 2019/10/11
--  일별 건수 카운트    
*/
(   
    p_exec              in number default 1     -- 0 :SQL만 출력 , 1:실행
) authid current_user
IS
    V_OWNER         VARCHAR2(40);
    V_TABLE_NAME    VARCHAR2(40);
    V_ROW_COUNT     NUMBER(22);

    
/*  1.권한 부여 프로그램 */                           
    CURSOR INIT_OBJECT IS                          
        -- 2.TB_MGR_GRANT에서 관리하는 권한에 빠진 권한 부여
            SELECT A.OWNER
                 , A.TABLE_NAME
                 , REGEXP_REPLACE (
                       EXTRACT (
                           DBMS_XMLGEN.getXmlType (
                                  'SELECT COUNT(*)||'' ''  C FROM '
                               || A.OWNER
                               || '.'
                               || A.TABLE_NAME)
                         , '/ROWSET/ROW/C').getStringVal ()
                     , '<C>|</C><C>|</C>'
                     , '') ROW_COUNT
                     , SYSDATE 
              FROM DBA_TABLES  A
             WHERE A.OWNER IN (SELECT USERNAME FROM TB_MGR_USER@DL_scott_DEV_scott_DBA WHERE SCHEMA_YN ='Y' ) 
                 ;     
                                                   
    V_SQL      VARCHAR2(1000);    
    V_MSG       long;        
BEGIN


    DBMS_OUTPUT.ENABLE;   

    FOR V_ROW IN INIT_OBJECT
    LOOP    
            V_OWNER:= V_ROW.OWNER;
            V_TABLE_NAME:= V_ROW.TABLE_NAME;
            V_ROW_COUNT:= V_ROW.ROW_COUNT;            
                
        -- 블록 에러 발생시에도 계속 실행토록 
        BEGIN
--            DBMS_OUTPUT.PUT_LINE('INSERT COUNT ..'||V_ROW.OWNER||','||V_ROW.TABLE_NAME||','||V_ROW.ROW_COUNT);
            V_SQL := 'INSERT INTO '||' TB_TBL_DAILY_COUNT(OWNER,TABLE_NAME,ROW_COUNT) VALUES ('''||V_OWNER||''','''||V_TABLE_NAME||''','||V_ROW_COUNT||');'; 
--            DBMS_OUTPUT.PUT_LINE(V_SQL);
--            DBMS_OUTPUT.PUT_LINE('INSERT INTO '||'TB_TBL_DAILY_COUNT (OWNER,TABLE_NAME,ROW_COUNT) VALUES ('||V_OWNER||','||V_TABLE_NAME||','||V_ROW_COUNT||');');                                  
            INSERT INTO TB_TBL_DAILY_COUNT (OWNER,TABLE_NAME,ROW_COUNT) VALUES (V_OWNER,V_TABLE_NAME,V_ROW_COUNT);            
--            DBMS_OUTPUT.PUT_LINE('INSERT INTO '||'TB_TBL_DAILY_COUNT (OWNER,TABLE_NAME,ROW_COUNT) VALUES ('||V_ROW.OWNER||','||V_ROW.TABLE_NAME||','||V_ROW.ROW_COUNT||');');
--            EXECUTE IMMEDIATE V_SQL;
            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
                ROLLBACK;        
        END;       

    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/
테이블 일별 건수 기록 프로시져

DB CAFE
