oracle18c函数返回表

Question

我试图以表的形式恢复一组数据，我创建了以下类型

create or replace type Structure_type AS OBJECT
(
SWID Integer,
structure_TYPESid Integer,
MAIN_BRANCHid Integer,
Organizational_structureid Integer,
Administrative_structureid Integer,
structure_tree NVarchar2(1000),
structure_NO NVarchar2(500),
Structure_level Integer
)

并创建类型为create的表或将Structure_table_type类型替换为Structure_type表；然后创建一个数据检索函数，如下所示

create or replace function Get_Who_Runs_Stores
(OoA char) return Structure_table_type is
  FunctionResult Structure_table_type;
begin
FunctionResult :=Structure_table_type();
 
 CASE  UPPER(OoA)
 WHEN  'A' then
 FOR x_Structure IN (select b.administrative_tree
    from warehouse w join branches_costcenter b on (w.w_branch=b.swid))
   LOOP
 
 select 
 b.swid  SWID,
 b.costcenter_type_id structure_TYPESid ,
 b.main_branch_id MAIN_BRANCHid,
 b.direct_branch_cost_center_id Organizational_structureid,
 b.direct_adminstration_parent_id Administrative_structureid,
 b.administrative_tree structure_tree,
 b.administrative_no structure_NO,
 b.administrative_level Structure_level
  into FunctionResult
  from branches_costcenter b 

  where 
  x_Structure.Administrative_Tree like  '%-' || b.swid || '-%';
 
 end loop;

  WHEN  'O' then
   
 FOR x_Structure IN (select b.organizationa_tree
    from warehouse w join branches_costcenter b on (w.w_branch=b.swid))
 LOOP
 
 select b.swid  SWID,b.costcenter_type_id structure_TYPESid ,
 b.main_branch_id MAIN_BRANCHid, b.direct_branch_cost_center_id Organizational_structureid,
 b.direct_adminstration_parent_id Administrative_structureid,b.organizationa_tree structure_tree,
 b.organizationa_no structure_NO ,b.organizationa_level Structure_level into FunctionResult
  from branches_costcenter b 
  where 
  x_Structure.Organizationa_Tree like  '%-' || b.swid || '-%';
   
 end loop;
  else 
      return null;
  end case ;
  
return(FunctionResult);
end Get_Who_Runs_Stores;

错误: PL/SQL: ORA-00947:不够用的值行: 45文本:来自branches_costcenter b

我尝试了SQL语句，它正确地工作。

 select 
 b.swid  SWID,
 b.costcenter_type_id structure_TYPESid ,
 b.main_branch_id MAIN_BRANCHid,
  b.direct_branch_cost_center_id Organizational_structureid,
 b.direct_adminstration_parent_id Administrative_structureid,
 b.administrative_tree structure_tree,
 b.administrative_no structure_NO,
 b.administrative_level Structure_level

  from branches_costcenter b 

  where 
'-1-2-3-12-24-' like  '%-' || b.swid || '-%';

我怎样才能得到所需的数据?最好的方法是什么？是否必须定义从函数中以表的形式检索数据的所有内容？

对不起，我的英语很差

Answer 1

你的功能会很慢。除此之外，问题是:如果您运行

FOR x_Structure IN (select ...) LOOP
   SELECT ...
   INTO FunctionResult
   FROM ...;
END LOOP;
RETURN FunctionResult;

然后将逐行插入到FunctionResult中。最后，只返回选择的最后一行，所有其他行都已被覆盖。

试一试

SELECT ...
BULK COLLECT INTO FunctionResult 
FROM ...;

您也可以逐行插入，但这要慢得多：

FOR x_Structure IN (select ...) LOOP
   FunctionResult.Extend();
   SELECT ...
   INTO FunctionResult(FunctionResult.LAST)
   FROM ...;
END LOOP;

您不能直接选择进入FunctionResult。声明必须是这样的：

SELECT Structure_type(b.swid, b.costcenter_type_id, ..., b.administrative_level) 
BULK COLLECT INTO FunctionResult 
FROM ...

更新

根据客户端的不同，返回RefCursor可能更容易，也更好。会是这样：

create or replace function Get_Who_Runs_Stores(OoA char) return SYS_REFCURSOR is
   res SYS_REFCURSOR;
begin
   OPEN res FOR
   SELECT b.swid  SWID,b.costcenter_type_id structure_TYPESid, ...
   FROM ...
   WHERE ...;
   
   RETURN res;
end;

您不必创建任何表或对象类型，只需返回RefCursor即可。