使用函数和数组返回多个值的脚本

Question

我需要创建一个脚本来返回使用 pl/sql

脚本获取的多个值：

create or replace package body dummy_m  
 is 
  type arr1 is table of temp_particulars.event_start_date%type;  
  var temp_particulars.event_start_date%type;  
      type  c1  is ref cursor
  return temp_date%rowtype;  
    function TEST4(  
 infranchise  IN   temp_particulars.franchise%TYPE,  
      inoperator   IN   temp_particulars.billing_operator%TYPE,  
      inbillingperiod  IN   temp_particulars.billing_period%TYPE,  
      intrafficperiod  IN   temp_particulars.traffic_period_name%TYPE,  
      incost           IN   temp_particulars.unit_cost_used%TYPE  
      )  
      return arr1%rowtype--error  
      is  test1 c1;
    my_arr arr1;
    cnt number; 
  begin
 --my_arr :=arr1();
 cnt:=0;
 delete from temp_date;
      insert into temp_date  SELECT distinct t.event_start_date
        FROM temp_particulars t
       WHERE t.billing_operator = inoperator
         AND t.franchise = infranchise
         AND t.billing_period= inbillingperiod
         AND t.traffic_period_name= intrafficperiod
         and t.unit_cost_used=incost;

     open test1 for select * from temp_date ;

     fetch test1 bulk collect into  my_arr;

     loop

     fetch test1 bulk collect into  my_arr;
     cnt:=cnt+1;

     dbms_output.put_line(cnt);

     exit when test1%notfound;
     end loop; 
      return my_arr; --error 

  close test1;  
  end test4;  
  end;  
/  

这里我必须返回 event_start_date 的值，其中返回多个值，但它显示多个错误，如第一个错误是：

PLS-00371: at most one declaration for 'ARR1' is permitted              

我什至尝试过这个

create or replace package body dummy_m2  
 is   
  type arr1 is table of temp_particulars.event_start_date%type;  
  var temp_particulars.event_start_date%type;  

   type  c1  is ref cursor  
  return temp_date%rowtype;  

  function TEST4(  
 infranchise  IN   temp_particulars.franchise%TYPE,  
      inoperator   IN   temp_particulars.billing_operator%TYPE,  
      inbillingperiod  IN   temp_particulars.billing_period%TYPE,  
      intrafficperiod  IN   temp_particulars.traffic_period_name%TYPE,  
      incost           IN   temp_particulars.unit_cost_used%TYPE  
      )  
      return c1  
      is   
     test1 c1;  
    my_arr arr1;  
    cnt number;   
  begin  
 --my_arr :=arr1();  
 cnt:=0;  
 delete from temp_date;  
      insert into temp_date  SELECT distinct t.event_start_date  
        FROM temp_particulars t  
       WHERE t.billing_operator = inoperator  
         AND t.franchise = infranchise  
         AND t.billing_period= inbillingperiod  
         AND t.traffic_period_name= intrafficperiod  
         and t.unit_cost_used=incost;  

     open test1 for select * from temp_date ;    

  close test1;  
  end test4;  
  end;  

我有甚至尝试过这个但返回相同的错误：

PLS-00371: at most one declaration for 'C1' is permitted  

Answer 1

arr1 类型是否已在包头中声明？如果您打算从包外部的代码调用此函数，则应该如此；这可以解释为什么您会收到有关多个声明的错误。验证该类型是否已在标头中声明，并从包体中删除该声明。

更一般地说，您的代码看起来像是无缘无故地对游标进行了很多处理。看来您要做的就是用查询结果填充数组。只需直接在数组中进行选择即可。

create or replace package body dummy_m  
 is 
  -- This should probably be declared in the package header
  --type arr1 is table of temp_particulars.event_start_date%type;  

    function TEST4(  
      infranchise  IN   temp_particulars.franchise%TYPE,  
      inoperator   IN   temp_particulars.billing_operator%TYPE,  
      inbillingperiod  IN   temp_particulars.billing_period%TYPE,  
      intrafficperiod  IN   temp_particulars.traffic_period_name%TYPE,  
      incost           IN   temp_particulars.unit_cost_used%TYPE  
      )  
      return arr1
      is
        my_arr arr1 := arr1();
  begin
      SELECT distinct t.event_start_date
      BULK COLLECT INTO my_arr
        FROM temp_particulars t
       WHERE t.billing_operator = inoperator
         AND t.franchise = infranchise
         AND t.billing_period= inbillingperiod
         AND t.traffic_period_name= intrafficperiod
         and t.unit_cost_used=incost;

      return my_arr;

  end test4;  
  end;  
/

Answer 2

return 子句应该是：

return arr1  

不是：

return arr1%rowtype--error  

下面是一些运行时没有错误的等效代码：

declare
  type arr1 is table of user_tables.last_analyzed%type;  
  var user_tables.last_analyzed%type;  
  type  c1  is ref cursor
      return user_tables%rowtype;  
  function TEST4 (p number)
      return arr1
  is
    test1 c1;
    my_arr arr1;
    cnt number; 
  begin
   null;
  end TEST4;
begin
  null;
end;