PL / SQL中的递归

我的任务是在n个集合中进行元素组合。这样一个集合中的任何元素都不会与自身结合。结构为：

产品--->控件---> LOV [值列表]

示例场景：

1产品->具有3个控件1->具有3个LOV控件2->具有2个LOV控件3->具有5个LOV

产品1的可能组合编号：3 * 2 * 5 = 30。

作为一名程序员和SQL的新手，我立即求助于递归。我不知道PL / SQL中递归的效率。但是我通过遍历所有控件（如树）并在叶子处拾取值来获得所需的结果。该解决方案有效，但是如果要在不进行递归的情况下进行，可能的方法是什么？

procedure postcombinations(pProductID in varchar2,
                           lovs       in varchar2,
                           ctrl       in varchar2) is
  lv_cp varchar2(100);
  lv_cl varchar2(100);
begin

  -- Loop through All the controls defined against a product other
  -- than the ones already traversed (Not in condition) also 
  -- restrict results to One branch since order is doesn't matter.

  for i in (select cp.control_id
              from tbl_control_product cp, tbl_control c
             where cp.product_id = pProductID
               and cp.control_id = c.control_id
               and c.control_type = 2
               and cp.control_id not in
                   (select regexp_substr(ctrl, '[^,]+', 1, level)
                      from dual
                    connect by regexp_substr(ctrl, '[^,]+', 1, level) is not null)
               and rownum < 2) loop
    begin

      -- Loop through all the LOV's in the controls and append them to Input 
      --to recurse the function 
      for p in (select cl.lov_id
                  from tbl_control_lov cl
                 where cl.control_id = i.control_id) loop
        if lovs is not null then
          pkg_product.postcombinations(pProductID => pProductID,
                                       lovs       => lovs || ',' ||
                                                     p.lov_id,
                                       ctrl       => ctrl || ',' ||
                                                     i.control_id);
        else
          pkg_product.postcombinations(pProductID => pProductID,
                                       lovs       => lovs,
                                       ctrl       => ctrl || ',' ||
                                                     i.control_id);
        end if;

      end loop;
    end;
  end loop;

  -- When A leaf is encountered the select statement returns a null 
  --the inputs are dumped into a table and voila.
  begin
    select cp.control_id
      into lv_cp

      from tbl_control_product cp, tbl_control tc
     where cp.product_id = pProductID
       and cp.control_id = tc.control_id
       and tc.control_type = 2
       and cp.control_id not in
           (select regexp_substr(ctrl, '[^,]+', 1, level)
              from dual
            connect by regexp_substr(ctrl, '[^,]+', 1, level) is not null)
       and rownum < 2;
  Exception
    When NO_DATA_FOUND then
      insert into tbl_test values (ctrl, lovs);
      commit;
  end;

end;