Oracle 函数返回表结构和 oracle中排序的应用

create or replace type zmd_member_price_temple_OBj as object(procode varchar2(500),memberprice number(8,2));
create type zmd_member_price_temple as table of zmd_member_price_temple_OBj;

create or replace function zmd_member_price_Fun(vDeptCode varchar2,
                                                vUserid   varchar2)
  return zmd_member_price_temple
  pipelined is
  tempRow zmd_member_price_temple_OBj;
begin
  if (NVL(vDeptCode,'EM') = 'EM') then
    for thisrow in (select productcode, memberprice
                      from (Select ZMP.productcode,
                                   ZMP.memberprice,
                                   rank() over(partition by productcode order by ZMP.productcode, TO_NUMBER(A.DEPTLVL), Effdt desc, Lastupddttm desc) rn
                              FROM ZMD_MEMBER_PRICE ZMP
                             INNER JOIN (SELECT ZSD.DEPTLVL,
                                               ZSD.DEPTCODE,
                                               ZSD.DEPTDESCR
                                          FROM ZMD_SYS_DEPT ZSD
                                        CONNECT BY PRIOR
                                                    ZSD.DEPTPID = ZSD.DEPTID
                                         START WITH ZSD.DEPTCODE IN
                                                    (SELECT DEPTCODE
                                                       FROM ZMD_AC_USERS ZAU
                                                      WHERE ZAU.USERID =
                                                            vUserid)) A
                                ON A.DEPTCODE = ZMP.DEPT
                             WHERE ZMP.APPROVEDDTTM is not NULL
                               and ZMP.Effdt <= trunc(sysdate)
                               and ZMP.Enabled = '1')
                     where rn = 1) loop
      tempRow := zmd_member_price_temple_OBj(thisrow.productcode,
                                             thisrow.memberprice);
      pipe row(tempRow);
    end loop;
  else
    for thisrow in (select productcode, memberprice
                      from (Select ZMP.productcode,
                                   ZMP.memberprice,
                                   rank() over(partition by productcode order by ZMP.productcode, TO_NUMBER(A.DEPTLVL), Effdt desc, Lastupddttm desc) rn
                              FROM ZMD_MEMBER_PRICE ZMP
                             INNER JOIN (SELECT ZSD.DEPTLVL,
                                               ZSD.DEPTCODE,
                                               ZSD.DEPTDESCR
                                          FROM ZMD_SYS_DEPT ZSD
                                        CONNECT BY PRIOR
                                                    ZSD.DEPTPID = ZSD.DEPTID
                                         START WITH ZSD.DEPTCODE = vDeptCode) A
                                ON A.DEPTCODE = ZMP.DEPT
                             WHERE ZMP.APPROVEDDTTM is not NULL
                               and ZMP.Effdt <= trunc(sysdate)
                               and ZMP.Enabled = '1')
                     where rn = 1)
                     loop
      tempRow := zmd_member_price_temple_OBj(thisrow.productcode,
                                             thisrow.memberprice);
      pipe row(tempRow);
    end loop;
  end if;
  return;
end zmd_member_price_Fun;