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

Weeeee, what a quick and easy soiotlun.

发表评论:

邮箱: