<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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 ;
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 ;