在PLSQL中串联集合 [英] Concatenating collections in PLSQL

问题描述

我需要从几个不同的表中收集大量的ID到某种类型的变量中，以传递给另一个函数.从哪个表获取ID是动态的，具体取决于下面的参数iVar.问题是是否没有更好的方法来执行此操作，因为该方法将不得不多次复制并重新分配数组.将它们全部插入到临时表中会更好吗?使用动态sql会更好.请参见下面的get_ids函数:

I need to collect a lot of ids from a couple of different tables into a variable of some sort to be passed to another function. Which tables to take the ids from is dynamic, depending on the param iVar below. The question is if there is no better way to do this as this approach will have to copy and re allocate the arrays multiple times. Would it be better to insert it all in a temp table? Would it be better to use dynamic sql. See the get_ids function below:

FUNCTION concat (
    iList1 IN ID_ARRAY,
iList2 IN ID_ARRAY
) 
RETURN ID_ARRAY IS
    lConcat ID_ARRAY;
BEGIN
    SELECT column_value BULK COLLECT INTO lConcat FROM (
        (SELECT column_value FROM TABLE(CAST( iList1 AS ID_ARRAY))) 
        UNION ALL 
        (SELECT column_value FROM TABLE(CAST( iList2 AS ID_ARRAY)))
    );
    RETURN lConcat;
END concat;

FUNCTION get_ids (
    iVar           IN NUMBER
) 
RETURN ID_ID_ARRAY IS
    lIds ID_ARRAY;
BEGIN
    lids := get_ids0();
    IF iVar = 1 THEN
        lIds := concat(lFilter, get_ids1());
    ELSE
        lIds := concat(lFilter, get_ids3());
        IF iVar = 4 THEN
            lIds := concat(lFilter, get_ids4());
        END IF;
    END IF;
    RETURN lIds;
END get_ids;

推荐答案

如果使用的是10g或更高版本，则可以使用MULTISET UNION运算符使CONCAT()函数的效率更高:

If you are using 10g or later you can make the CONCAT() function a bit more efficient by using the MULTISET UNION operator:

FUNCTION concat (
    iList1 IN ID_ARRAY,
      iList2 IN ID_ARRAY
) 
RETURN ID_ARRAY IS
    lConcat ID_ARRAY;
BEGIN
    lConcat := iList1 
               MULTISET UNION  
               iList2 A
    ;
    RETURN lConcat;
END concat;

您可以通过填充几个不同的数组，然后对所有数组调用一次MULTISET UNION来使事情更高效:

You could make things more efficient by populating several different arrays and then calling MULTISET UNION once for all of them:

   lConcat := iList1 
               MULTISET UNION  
               iList2  
               MULTISET UNION  
               iList3
               MULTISET UNION  
               iList4;  

使用动态SQL(可能是替换各种get_idsN()函数)可能是一种值得研究的方法，但可能不会为您提供很多(如果有的话)改善性能的方法.

Using dynamic SQL - presumably to replace the various get_idsN() functions - might be an approach worth investigating, but probably won't give you much, if anything, in the way of improved performance.

临时表不是一个好主意，因为与在内存中进行处理相比，它们的性能非常差.

Temporary tables are not a good idea, because they perform very badly compared to doing things in memory.

这篇关于在PLSQL中串联集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！