vincent's Blog

以前寫的一個store procedure ，起因是某個離職的主管要我每個月report DB內某一個USER下所有TABLE
row count的差異量，開玩笑不是10個或是20 table，是將近1000個，用excel要做到名國幾年阿。

先建立一個統計表

CREATE TABLE DB_RPT1
(
NAM_TABLE VARCHAR2(50) NOT NULL,
NAM_YEAR VARCHAR2(4),
MOON_1 VARCHAR2(30),
MOON_2 VARCHAR2(30),
MOON_3 VARCHAR2(30),
MOON_4 VARCHAR2(30),
MOON_5 VARCHAR2(30),
MOON_6 VARCHAR2(30),
MOON_7 VARCHAR2(30),
MOON_8 VARCHAR2(30),
MOON_9 VARCHAR2(30),
MOON_10 VARCHAR2(30),
MOON_11 VARCHAR2(30),
MOON_12 VARCHAR2(30),
PRIMARY KEY(NAM_TABLE,NAM_YEAR )
);




再建立store procedure


CREATE OR REPLACE procedure DB_RPT is

v_empty_flag varchar2(1);
V_CNT varchar2(30);
sql_stmt varchar2(1000);
SQL_STR VARCHAR2(1000);
SQL_STR1 VARCHAR2(1000);
V_NAM_MOON VARCHAR2(2);
V_NAM_MOON_FLG VARCHAR2(6);
V_NAM_YEAR VARCHAR2(4);

CURSOR C1 is
select A.TABLE_NAME from all_tables A where owner='ERPUSER';

BEGIN
V_NAM_MOON :=TO_CHAR(SYSDATE,'MM');
V_NAM_YEAR :=TO_CHAR(SYSDATE,'YYYY') ;
FOR c1_rec IN C1 loop
sql_stmt := 'select count(*) from '||c1_rec.table_name;
EXECUTE IMMEDIATE sql_stmt into V_CNT;
BEGIN
CASE V_NAM_MOON
WHEN '01' THEN
V_NAM_MOON_FLG:='MOON_1';
WHEN '02' THEN
V_NAM_MOON_FLG:='MOON_2';
WHEN '03' THEN
V_NAM_MOON_FLG:='MOON_3';
WHEN '04' THEN
V_NAM_MOON_FLG:='MOON_4';
WHEN '05' THEN
V_NAM_MOON_FLG:='MOON_5';
WHEN '06' THEN
V_NAM_MOON_FLG:='MOON_6';
WHEN '07' THEN
V_NAM_MOON_FLG:='MOON_7';
WHEN '08' THEN
V_NAM_MOON_FLG:='MOON_8';
WHEN '09' THEN
V_NAM_MOON_FLG:='MOON_9';
WHEN '10' THEN
V_NAM_MOON_FLG:='MOON_10';
WHEN '11' THEN
V_NAM_MOON_FLG:='MOON_11';
WHEN '12' THEN
V_NAM_MOON_FLG:='MOON_12';
END CASE;

SQL_STR :='UPDATE DB_RPT1 SET '||V_NAM_MOON_FLG||'='''||V_CNT||''' WHERE NAM_TABLE='''||c1_rec.table_name ||''' AND NAM_YEAR='''||V_NAM_YEAR||'''';
EXECUTE IMMEDIATE SQL_STR;
if(SQL%NOTFOUND)THEN
--ROLLBACK;
SQL_STR1:= 'INSERT INTO DB_RPT1( NAM_TABLE,NAM_YEAR,'||V_NAM_MOON_FLG||') VALUES('''|| c1_rec.table_name||''','''||V_NAM_YEAR||''','''||v_cnt||''')';
EXECUTE IMMEDIATE SQL_STR1;
END IF;
COMMIT;
END;
END LOOP;
COMMIT;
--exception
--when others then
-- rollback;
-- p_msg := to_char(sqlcode);
end;
/


再去產生一個job 看是每月的那一天執行，要看的時候就用SQL查詢出來吧！！
管你是要看今年還是明年 .....select 就對了