1、定义你自己需要的对象
如下:
create or replace type poi_columns as object
(
quailty NUMBER(5,2),
serving NUMBER(5,2),
condition NUMBER(5,2),
loopnum NUMBER(8)
)
2、将对象定义成类型
CREATE OR REPLACE TYPE poiinfos AS TABLE OF poi_columns
3、type可以用了,使用方法如下:
create or replace function toplistcounter
(
poiid in number
)
return poiinfos PIPELINED IS//设定返回值类型为数据集和类型
counts number(5,2);
commentcount number(8);
loops number;
voteresult VARCHAR2(16);
quality varchar2(2);
serving varchar2(2);
condition varchar2(2);
quality_g_num number(8);
quality_b_num number(8);
quality_n_num number(8);
serving_g_num number(8);
serving_b_num number(8);
serving_n_num number(8);
condition_g_num number(8);
condition_b_num number(8);
condition_n_num number(8);
results poi_columns := poi_columns(0 , 0 , 0 , 0) ;//声明一个自己的对象变量
cursor poi_voteresult is
select
t.voteresult
from
edz_comment t
where
t.intime > = SYSDATE - 7
and t.objecttype = 1
and t.objectid = poiid;
begin
quality_g_num := 0;
quality_n_num := 0;
quality_b_num := 0;
serving_g_num := 0;
serving_n_num := 0;
serving_b_num := 0;
condition_g_num := 0;
condition_n_num := 0;
condition_b_num := 0;
counts := 0;
loops := 0;
results.loopnum := 0;
results.quailty := 0;
results.serving := 0;
results.condition :=0;
select
count(t.commentid) into loops
from
edz_comment t
where
t.intime > = SYSDATE - 7
and t.objecttype = 1
and t.objectid = poiid;
if(loops = 0) then PIPE ROW(results); return ;end if;
results.loopnum := loops;
open poi_voteresult;
loop
fetch poi_voteresult into voteresult;
exit when loops <= 0;
loops := loops - 1;
quality := substr(voteresult , 1 , 1);
serving := substr(voteresult , 3 , 1);
condition := substr(voteresult , 5 , 1);
if(quality = 'G') then
quality_g_num := quality_g_num + 1;
end if;
if(quality = 'N') then
quality_n_num := quality_g_num + 1;
end if;
if(quality = 'B') then
quality_b_num := quality_g_num + 1;
end if;
if(serving = 'G') then
serving_g_num := serving_g_num + 1;
end if;
if(serving = 'N') then
serving_n_num := serving_g_num + 1;
end if;
if(serving = 'B') then
serving_b_num := serving_g_num + 1;
end if;
if(condition = 'G') then
condition_g_num := condition_g_num + 1;
end if;
if(condition = 'N') then
condition_n_num := condition_n_num + 1;
end if;
if(condition = 'B') then
condition_b_num := condition_b_num + 1;
end if;
end loop;
results.quailty := ((quality_g_num*3+quality_n_num)/((quality_g_num*3+quality_n_num)+(quality_b_num*3+quality_n_num)));
results.serving := ((serving_g_num*3+serving_n_num)/((serving_g_num*3+serving_n_num)+(serving_b_num*3+serving_n_num)));
results.condition := (condition_g_num*3+condition_n_num)/((condition_g_num*3+condition_n_num)+(condition_b_num*3+condition_n_num));
PIPE ROW(results);//将对象放入返回值
return ;
end ;