----设置查询SEQ游标----
CURSOR CurSEQ IS
select sum(list_pv) as SEQ_PV , count(list_ip) as SEQ_IP , list_seq
from count_list
group by list_seq;========================
如果是单个查询结果 可以直接传给变量一行可以用rowType(行级变量)以上的结果该如何分别返回呢?我想那查出来的结果放在另一个表中 以字符串的形式 如:01,25,6|02,56,12|........
CURSOR CurSEQ IS
select sum(list_pv) as SEQ_PV , count(list_ip) as SEQ_IP , list_seq
from count_list
group by list_seq;========================
如果是单个查询结果 可以直接传给变量一行可以用rowType(行级变量)以上的结果该如何分别返回呢?我想那查出来的结果放在另一个表中 以字符串的形式 如:01,25,6|02,56,12|........
is
type cur is ref cursor;
procedure getRst( Rst out cur );
end;create or replace package body pkg
is
procedure getRst( Rst out cur )
is
begin
open Rst for select sum(list_pv) as SEQ_PV , count(list_ip) as SEQ_IP , list_seq
from count_list
group by list_seq;
end;
end;
/
insert into t2
select '01','25','6' from dual union all
select '02','56','12' from dual
/
declare
cursor cur is select * from t2;
Fc1 varchar2(10);
Fc2 varchar2(10);
Fc3 varchar2(10);
s varchar2(4000);
begin
open cur ;
loop
fetch cur into fc1,Fc2,fc3;
exit when cur%notfound;
s:=s||fc1||','||fc2||','||fc3||'|';
end loop;
close cur;
s:=substr( s,1,length(s)-1);
dbms_output.put_line( s );
end;
/
--输出结果
01,25,6|02,56,12
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%';
begin
open listCurPV; --打开PV游标
open listCurIP; --打开IP游标 FETCH listCurPV into countPV;----将游标取得的值放入变量中
FETCH listCurIP into countIP;
dbms_output.put_line('countPV:'||countPV||'countIP:'||countIP);
insert into count_ForDay VALUES (SEQ_List.nextval,SYSDATE,1,1,1,1);
close listCurPV;
close listCurIP; --关闭游标
end insertToForDay;