oracle------如何在方法中返回自己需要的column集合

来源：互联网发布：哪里有unix系统源码编辑：程序博客网时间：2024/05/29 02:09

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 ;