oracle - 需要关于如何优化我的函数 PL/SQL 的建议

Question

我需要关于优化我的功能的帮助/建议。REASON_ATTR_VALUE该函数应使用输入 XML 文件中的属性名称在表中查找属性值并生成输出 XML 文件。在输入文件的函数中，我将数据传输到集合v_attrs_in（包含要检查的原因标识符和属性名称），检查表中的数据REASON_ATTR_VALUE，并形成另一个集合v_attrs（包含原因标识符和属性名称和值）。对于这个集合，我生成一个输出文件。函数全文：

function get_reason_attr_value (p_reasons clob) return clob 
  is
    v_attrs reason_attrs;
    v_attrs_in reasons;
    v_reason_ids reason_id_list := reason_id_list();
    i pls_integer;
    v_xml_reasons_in xmltype;
    v_xml_reasons_out xmltype;
    v_xml_reason xmltype;
  begin  
    if p_reasons is null then
      throw('Input parameter is empty');
    end if;
    
    v_xml_reasons_in := xmltype(p_reasons);
      
    select xmlelement("Reasons") into v_xml_reasons_out from dual;
       
    select 
      id as reason_id, 
      name as attr_name   
    bulk collect into v_attrs_in 
    from XMLTable('Reasons/Reason' passing (v_xml_reasons_in) 
      columns id number path 'Id',
      attr_list XMLType path 'AttrList/Attr')(+) xt_att,
      XMLTable('Attr' passing (xt_att.attr_list) 
      columns name varchar2(200) path 'Name');

    if v_attrs_in is not empty then
      select
        x.reason_id as reason_id,
        t.attr_name as attr_name,
        a.attr_value as attr_value
      bulk collect into v_attrs
      from table(v_attrs_in) x
      join reason_attrs t on t.attr_name = x.attr_name
      left join reason_attr_value a on a.reason_id = x.reason_id and a.attr_id = t.attr_id  
        and sysdate between a.date_begin and nvl(a.date_end, sysdate + 1);   
   
      if v_attrs is not empty then
        
        select distinct x.reason_id
        bulk collect into v_reason_ids
        from table (v_attrs) x
        where x.attr_value is not null;
        
        if v_reason_ids is not empty then
          for i in v_reason_ids.first..v_reason_ids.last loop
            select xmlelement("Reason",
              xmlforest
              (
                v_reason_ids(i) as "Id",
                xmlagg(xmlelement("Attr", XMLAttributes(x.attr_name as "name"), x.attr_value)) as "AttrList" 
              ))
            into v_xml_reason
            from table (v_attrs) x
            where x.reason_id = v_reason_ids(i);
            
            v_xml_reasons_out := v_xml_reasons_out.appendChildXML('//Reasons',v_xml_reason);         
          end loop;
        end if;
      end if;
    end if;

    return v_xml_reasons_out.getClobVal(); 
  end get_reason_attr_value;  

函数中使用的集合说明：

type reason is record
  (
    reason_id number,
    attr_name varchar2(200)
  );
  type reason_attr is record
  (
    reason_id number,
    attr_name varchar2(200),
    attr_value varchar2(2000)
  );
  
  type reasons is table of reason;
  type reason_attrs is table of reason_attr;
  type reason_id_list is table of number; 

示例输入 XML 文件（可能包含数千个元素Reason）：

<Reasons>
  <Reason>
    <Id>3</Id>
    <AttrList>
      <Attr>
        <Name>include_in_recalculation</Name>
      </Attr>
    </AttrList>
  </Reason>
  <Reason>
    <Id>5</Id>
    <AttrList>
      <Attr>
        <Name>include_in_recalculation</Name>
      </Attr>
    </AttrList>
  </Reason>
  <Reasons> 

标签： oraclestored-proceduresplsqlxml-parsing