create or replace procedure GetVip_Log as
v_providerid varchar2(12);
v_tablename varchar2(50);
v_sql varchar2(5000);
Cursor cursor_providerid is
select providerid from tab_A where checked='1' ;
begin
execute immediate 'truncate table Vip_log';
open cursor_providerid;
loop
fetch cursor_providerid into v_providerid;
exit when cursor_providerid%NOTFOUND;
v_tablename :='';
v_tablename:='VISITOR_STAT_'||to_char(substr(v_providerid,-2));
v_sql:='' ;
if v_tablename <>'VISITOR_STAT_' then
v_sql:='insert into vip_log(PROVIDERID,INTOTYPE,COUNT) select v_providerid,c.infotype,c.cou from (select sum(counts) cou,to_char(infotype) infotype from v_tablename where providerid=v_providerid group by to_char(infotype) having sum(counts)>0) C';
v_sql:=replace(v_sql,'''','''''');
-- dbms_output.put_line(v_sql);
execute immediate v_sql USING v_tablename ;
commit;
end if;
end loop;
close cursor_providerid;
commit;
end GetVip_Log;
v_providerid varchar2(12);
v_tablename varchar2(50);
v_sql varchar2(5000);
Cursor cursor_providerid is
select providerid from tab_A where checked='1' ;
begin
execute immediate 'truncate table Vip_log';
open cursor_providerid;
loop
fetch cursor_providerid into v_providerid;
exit when cursor_providerid%NOTFOUND;
v_tablename :='';
v_tablename:='VISITOR_STAT_'||to_char(substr(v_providerid,-2));
v_sql:='' ;
if v_tablename <>'VISITOR_STAT_' then
v_sql:='insert into vip_log(PROVIDERID,INTOTYPE,COUNT) select v_providerid,c.infotype,c.cou from (select sum(counts) cou,to_char(infotype) infotype from v_tablename where providerid=v_providerid group by to_char(infotype) having sum(counts)>0) C';
v_sql:=replace(v_sql,'''','''''');
-- dbms_output.put_line(v_sql);
execute immediate v_sql USING v_tablename ;
commit;
end if;
end loop;
close cursor_providerid;
commit;
end GetVip_Log;
'from (select sum(counts) cou,to_char(infotype) infotype from v_tablename '||
'where providerid=v_providerid group by to_char(infotype) having sum(counts)>0) C';你試試看!小弟學藝不精,不知道行不行!你試把看看!
前面的v_providerid我不知道你是就用这个,还是它是一个字段名,你自己处理!
cursor_d type_cur; --声明 cursor_d 是属于上面的那个游标类型的一个游标begin open cursor_d for 'select distinct tablename ,colnum
from '||v_tmpfromtable ||' '; --这里的 '||v_tmpfromtable ||' 是动态的表名啦! FETCH cursor_d INTO
v_tblname,v_colnum;
WHILE cursor_d%FOUND LOOP这里是循环体,自己写啦! FETCH cursor_d INTO
v_tblname,v_colnum;
end loop;
close cursor_d;
commit;
………………