日期:2014-05-16  浏览次数:21151 次

求解答,v_sql 为什么一直是null
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
  using i_BeginDate, i_EndDate;
  LOOP
  FETCH c1
  INTO v_exesql;
  execute immediate v_exesql;
  EXIT WHEN c1%NOTFOUND;
  sumc := sumc + sumc;
  END LOOP;
  return sumc;
  CLOSE c1;
exception
  WHEN OTHERS THEN
  return 0;
end f_fw_Information;


------解决方案--------------------
这是从lz存储过程中得到的SQL,看下能找到行吗.
SQL code
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;