PL/SQL如何对IS TABLE OF集合类型实现存在更新不存在插入逻辑

你遇到的两个问题核心是操作内存中PL/SQL嵌套表集合的语法，以下是两种可直接落地的实现方案：

方案1：基于TABLE()函数的通用兼容方案 #

你的FLUXO_TAB是Schema级别创建的自定义集合类型，可直接通过TABLE()函数将内存集合转为临时关系表执行SQL查询，兼容所有支持对象类型的Oracle版本。

问题1（存在性校验）修正 #

将统计查询的数据源替换为TABLE(v_FLUXO_OBJ)即可：

SELECT COUNT(*)
INTO v_COUNT
FROM TABLE(v_FLUXO_OBJ)
WHERE DT_VENC = v_VENC 
  AND TYPE = v_TYPE;

问题2（集合更新）修正 #

Oracle不支持直接对内存集合执行UPDATE SQL语句，需要先查询匹配记录的下标，再通过下标修改元素属性：

ELSE
  -- 查询匹配记录的下标
  SELECT rn
  INTO v_MATCH_IDX
  FROM (
    SELECT ROWNUM rn, t.*
    FROM TABLE(v_FLUXO_OBJ) t
    WHERE DT_VENC = v_VENC 
      AND TYPE = v_TYPE
  ) WHERE rn = 1;
  -- 直接通过下标更新属性
  v_FLUXO_OBJ(v_MATCH_IDX).VALOR := v_FLUXO_OBJ(v_MATCH_IDX).VALOR + v_VALOR;
END IF;

注意需要提前声明变量v_MATCH_IDX PLS_INTEGER

方案2：基于关联数组索引的高性能方案 #

如果处理的数据量较大，方案1每次全集合扫描的性能较差，可以用关联数组做索引缓存匹配键对应的集合下标，避免重复扫描：

CREATE OR REPLACE FUNCTION FUNC_FLUXO (
     P_DATE1 TABLE1.DT_VENC%TYPE, 
     P_DATE2 TABLE1.DT_VENC%TYPE) 
     RETURN FLUXO_TAB IS
 
   v_COUNT INTEGER; v_FLUXO INTEGER := 0; v_VALOR NUMBER := 0;
   v_VENC VARCHAR2(10); v_TYPE VARCHAR2(2); 
   v_SCRIPT VARCHAR2(100); v_SELECT SYS_REFCURSOR;
   v_FLUXO_OBJ FLUXO_TAB := FLUXO_TAB();
   -- 新增关联数组做索引，key为DT_VENC拼接TYPE，value为集合下标
   TYPE idx_map_t IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(12);
   v_idx_map idx_map_t;
   v_key VARCHAR2(12);
BEGIN
      v_SCRIPT := 'SELECT DT_VENC, TYPE, VALOR FROM TABLE1';
      OPEN v_SELECT FOR v_SCRIPT;
      LOOP
        FETCH v_SELECT INTO v_VENC, v_TYPE, v_VALOR;
        EXIT WHEN v_SELECT%NOTFOUND;
        v_key := v_VENC || v_TYPE;
        -- 直接通过关联数组判断是否存在，时间复杂度O(1)
        IF v_idx_map.EXISTS(v_KEY) THEN
          -- 存在直接更新
          v_FLUXO_OBJ(v_idx_map(v_KEY)).VALOR := v_FLUXO_OBJ(v_idx_map(v_KEY)).VALOR + v_VALOR;
        ELSE
          -- 不存在则插入，同时缓存下标到索引
          v_FLUXO := v_FLUXO + 1;
          v_FLUXO_OBJ.EXTEND;
          v_FLUXO_OBJ(v_FLUXO) := FLUXO_OBJ(v_VENC, v_TYPE,v_VALOR);
          v_idx_map(v_KEY) := v_FLUXO;
        END IF;
      END LOOP;
      CLOSE v_SELECT;
      RETURN v_FLUXO_OBJ;
END;
/

该方案无需每次遍历全集合做校验，性能远高于方案1，推荐优先使用。

内容的提问来源于stack exchange，提问作者Joelson Oliveira