oracle 存储过程！定期查询 整合数据
create or replace procedure text（
startdate in date
）
as
v_startdate date;
a number ;
b number;
c number;
d number;
e number
Begin
v_sql := 'select count(*) from (' || Psql || ')';
set a='select count(*) as t from (select * from web_user where status = 1 and to_char(regcode_date,'yyyy-mm-dd') ='"+startdate+"' ';
set b='select count(*) as t from (select distinct(user_id) from web_log where to_char(rec_date,'yyyy-mm-dd') ='"+startdate+"' ';
set c='select count(*) as t from (select distinct(user_id) from web_log where url like '%gotoLunPan%' and to_char(rec_date,'yyyy-mm-dd') ='"+startdate+"' '
insert into Aszk_tongji (Rec_date,New_id,Long_id,Coupon_down_count,Lp_long_count,Lp_down_count) values ("+startdate+","+a+","+b+","+c+","+d+","+e+");
Exception
when others then
rollback;
End text;
不知道大家能不能看懂我的意思！写的肯定是不对了 不咋会存储过程 给个正确的写法吧！
[解决办法]
定期执行，那你弄个JOB来调用procedure.
[解决办法]
帮你改了下，楼主试试可否
create or replace procedure text（ startdate in date ）
as
a number;
b number;
c number;
d number;
Begin
select count(1) into a
from web_user
where status = 1
and to_char(regcode_date, 'YYYY-MM-DD') =
to_char(startdate, 'YYYY-MM-DD');
select count(distinct user_id) into b
from web_log
where to_char(rec_date, 'YYYY-MM-DD') =
to_char(startdate, 'YYYY-MM-DD');
select count(distinct user_id) into c
from web_log
where url like '%gotoLunPan%'
and to_char(rec_date, 'YYYY-MM-DD') =
to_char(startdate, 'YYYY-MM-DD');
select count(t1.user_id) into d
from user_lunpan t, user_lunpan t1, lunpan_product t3
where t3.product_no = t.product_id
and t.user_id = t1.user_id
and to_char(t.get_date, 'YYYY-MM-DD') =
to_char(startdate, 'YYYY-MM-DD');
insert into Aszk_tongji
(Rec_date, New_id, Long_id, Lp_long_count, Lp_down_count) values
(startdate, a, b, c, d);
commit;
End text;
