oracle存储过程
为公司写的,放在这里留个脚印,怕忘了。
create or replace procedure insertToForDay
AS
---声明:一天中PV和IP的总数---
countPV count_list.list_pv%type;
countIP count_list.list_IP%type;
---设置查询PV游标---
CURSOR listCurPV IS
Select sum(list_pv) as countPV
From Count_list
where to_char(LIST_TIME,'yyyy-mm-dd') like '%2007-07-13%';
---设置查询IP游标---
CURSOR listCurIP IS
Select count(list_IP) as countPV
From Count_list
where to_char(LIST_TIME,'yyyy-mm-dd') like '%2007-07-13%';
---设置查询SEQ游标---
CURSOR curSEQ is
select list_seq, count(list_ip) as SEQ_IP, sum(list_pv) as SEQ_PV
from count_list
where to_char(LIST_TIME,'yyyy-mm-dd') like '%2007-07-13%'
group by list_seq;
Fc1SEQ varchar2(10);
Fc2SEQ varchar2(10);
Fc3SEQ varchar2(10);
sSEQ varchar2(4000);
---设置查询Article游标---
CURSOR curArticle is
select Article_ID, count(list_ip) as SEQ_IP, sum(list_pv) as SEQ_PV
from v_count_article_forday
where to_char(LIST_TIME,'yyyy-mm-dd') like '%2007-07-13%'
group by Article_ID;
Fc1Article varchar2(10);
Fc2Article varchar2(10);
Fc3Article varchar2(10);
sArticle varchar2(4000);
begin
---将取得的SEQ形成数组---
open curSEQ ;
loop
fetch curSEQ into fc1SEQ,Fc2SEQ,fc3SEQ;
exit when curSEQ%notfound;
sSEQ:=sSEQ||fc1SEQ||','||fc2SEQ||','||fc3SEQ||'|';
end loop;
close curSEQ;
sSEQ:=substr( sSEQ,1,length(sSEQ)-1);
---end ---
---将取得的Article形成数组---
open curArticle ;
loop
fetch curArticle into fc1Article,Fc2Article,fc3Article;
exit when curArticle%notfound;
sArticle:=sArticle||fc1Article||','||fc2Article||','||fc3Article||'|';
end loop;
close curArticle;
sArticle:=substr( sArticle,1,length(sArticle)-1);
---end ---
open listCurPV; --打开PV游标
open listCurIP; --打开IP游标
---将游标取得的值放入变量中
FETCH listCurPV into countPV;
FETCH listCurIP into countIP;
dbms_output.put_line('countPV:'||countPV||'countIP:'||countIP||'sSEQ-------'||sSEQ||'sArticle------'||sArticle);
insert into count_ForDay VALUES (SEQ_FORDAY.nextval,SYSDATE,countIP,countPV,sSEQ,sArticle);
--关闭游标
close listCurPV;
close listCurIP;
end insertToForDay;
最新评论:
no.10250 oepb8zww6 188.143.232.27 2016-06-08 14:07