create or replace procedure text(
startdate in date
)as v_startdate date;
a number ;
b number;
c number;
d number;
e numberBegin
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;不知道大家能不能看懂我的意思!写的肯定是不对了 不咋会存储过程 给个正确的写法吧!
startdate in date
)as v_startdate date;
a number ;
b number;
c number;
d number;
e numberBegin
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;不知道大家能不能看懂我的意思!写的肯定是不对了 不咋会存储过程 给个正确的写法吧!
startdate in date
)as a number ;
b number;
c number;
d number;Begin
select count(*) into a from (select * from web_user where status = 1 and to_char(regcode_date,'YYYY-MM-DD') =to_char(startdate,'YYYY-MM-DD' ) );
select count(*) into b from (select distinct(user_id) from web_log where to_char(rec_date,'YYYY-MM-DD') =to_char(startdate,'YYYY-MM-DD' ) );
select count(*) into c from (select distinct(user_id) from web_log where url like '%gotoLunPan%' and to_char(rec_date,'YYYY-MM-DD') =to_char(startdate,'YYYY-MM-DD' ));
select count(*) into d from (select t1.user_id
from 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) value( startdate,a,b,c,d);
End text;谁给看看,那错了!
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;