日期:2014-05-16 浏览次数:21151 次
select 'SELECT count(*) FROM ' || t.table_name || ' WHERE LOGDATE >= :i_BeginDate AND LOGDATE < :i_EndDate' from sys.user_all_tables t where t.table_name like '%T_TEMPLATE%' AND T.table_name NOT LIKE '%APPLY%' AND T.table_name NOT LIKE '%HISTRY%'
------解决方案--------------------
create or replace function f_fw_Information(i_BeginDate date,
i_EndDate date) return number as
v_sql VARCHAR2(200);
v_exesql VARCHAR2(200);
sumc number;
type o_cursor is ref cursor;
c1 o_cursor;
begin
sumc := 0;
v_sql := 'select ''SELECT count(*) FROM '' || t.table_name ||
'' WHERE LOGDATE >= :i_BeginDate AND LOGDATE < :i_EndDate''
from sys.user_all_tables t
where t.table_name like ''%T_TEMPLATE%''
AND T.table_name NOT LIKE ''%APPLY%''
AND T.table_name NOT LIKE ''%HISTRY%''';
OPEN c1 for v_sql;
LOOP
FETCH c1
INTO v_exesql;
EXIT WHEN c1%NOTFOUND;--移上来试试呢
execute immediate v_exesql into sumc
using i_BeginDate, i_EndDate;
--EXIT WHEN c1%NOTFOUND;
sumc := sumc + sumc;
END LOOP;
CLOSE c1;
return sumc;
exception
WHEN OTHERS THEN
return 0;
end f_fw_Information;