create or replace PROCEDURE ug
IS
v_sql VARCHAR2(5000);
t_count NUMBER;
e_count NUMBER;
BEGIN
--Huy Vanpull
FOR c IN
(SELECT *
FROM groups)
LOOP
v_sql := 'SELECT COUNT(*) FROM user_tables WHERE TABLE_NAME = ''EMAILS_'||c.id||'''';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql INTO t_count;
--如果表是否存在
IF t_count = 1 THEN
--查出此表的EMAIL数量
v_sql := 'SELECT COUNT(*) FROM EMAILS_'||c.id;
EXECUTE IMMEDIATE v_sql INTO e_count;
--如果查出的EMAIL不等组记录的数量
IF e_count != c.email_count THEN
v_sql := 'UPDATE GROUPS SET email_count = '||e_count||' WHERE id = '||c.id;
dbms_output.put_line(v_sql);
--更改组记录的数量
--EXECUTE IMMEDIATE v_sql;
COMMIT;
END IF;
ELSE
dbms_output.put_line(c.id||':没有找到相应的表!');
END IF;
END LOOP;
END;