Oracle存储过程游标作为结果集返回

上面是包声明----------------

 create or replace package body P_inventory_wait_list is
procedure prcInventoryWaitList
(
          planHours float,
          stockGuid varchar2,
          cur in out P_inventory_wait_list.cursorType
)
as
          sumTimes float;
          cnt number;
          i number;
begin
  select count(*) into cnt from inventory_wait_list where status='10' and stock_guid = stockGuid;

  for  i  in 1..cnt loop
  select sum(t2.plan_times) into sumTimes from (select * from inventory_wait_list  t1 where t1.status = '10' and t1.stock_guid=stockGuid order by t1.type, t1.location_guid) t2 where rownum<=i;
  if(sumTimes<planHours)   then
    if(trunc(sumTimes,1) = trunc(planHours,1)) then
     open cur for
      select * from (
      select t1.guid,
  --     case when t1.guid is null then null else
   --      (select sum(t1.plan_times) from dual ) end,
       case when t1.inventory_guid is null then null else
         (select inventory.inventory_id from inventory where t1.inventory_guid = inventory.guid and inventory.state='1000') end as inventoryId,
       t1.stock_guid as stockGuid,
       t1.create_date as createDate,
       t1.product_code_guid as productCodeGuid,
       t1.location_guid as locationGuid,
       case when t1.location_guid is null or t1.stock_guid is null or t1.product_code_guid is null then null else
         (select sl.location_code from stock_location sl where t1.location_guid = sl.guid and t1.stock_guid = sl.stock_guid and t1.product_code_guid = sl.product_code_guid) end as locationCode,
       t1.qty,
       t1.status,
       t1.type,
       t1.inventory_guid as inventoryGuid,
       t1.plan_times as planTimes
       from inventory_wait_list t1 where t1.status = '10' and t1.stock_guid = stockGuid order by type, t1.location_guid ) where rownum<=i;
       exit;
     /*elsif(round(sumTimes) = round(planHours)) then
       open cur for
      select * from (
      select t1.guid,
             rownum,
  --     case when t1.guid is null then null else
   --      (select sum(t1.plan_times) from dual ) end,
       case when t1.inventory_guid is null then null else
         (select inventory.inventory_id from inventory where t1.inventory_guid = inventory.guid and inventory.state='1000') end as inventory_id,
       t1.stock_guid,
       t1.create_date,
       t1.product_code_guid,
       t1.location_guid,
       case when t1.location_guid is null or t1.stock_guid is null or t1.product_code_guid is null then null else
         (select sl.location_code from stock_location sl where t1.location_guid = sl.guid and t1.stock_guid = sl.stock_guid and t1.product_code_guid = sl.product_code_guid) end as location_code,
       t1.qty,
       t1.status,
       t1.type,
       t1.inventory_guid,
       t1.plan_times
       from inventory_wait_list t1 where t1.status = '10' and t1.stock_guid = stockGuid order by type, t1.location_guid ) where rownum<=i;
       exit;*/
    end if;
  else
    if(round(sumTimes) = round(planHours)) then
      open cur for
      select * from (
      select t1.guid,
  --     case when t1.guid is null then null else
   --      (select sum(t1.plan_times) from dual ) end,
       case when t1.inventory_guid is null then null else
         (select inventory.inventory_id from inventory where t1.inventory_guid = inventory.guid and inventory.state='1000') end as inventoryId,
       t1.stock_guid as stockGuid,
       t1.create_date as createDate,
       t1.product_code_guid as productCodeGuid,
       t1.location_guid as locationGuid,
       case when t1.location_guid is null or t1.stock_guid is null or t1.product_code_guid is null then null else
         (select sl.location_code from stock_location sl where t1.location_guid = sl.guid and t1.stock_guid = sl.stock_guid and t1.product_code_guid = sl.product_code_guid) end as locationCode,
       t1.qty,
       t1.status,
       t1.type,
       t1.inventory_guid as inventoryGuid,
       t1.plan_times as planTimes
       from inventory_wait_list t1 where t1.status = '10' and t1.stock_guid = stockGuid order by type, t1.location_guid ) where rownum<=i;
       exit;
    end if;
  end if;
  end loop;
end;
end;