Oracle PL/SQL中如何删除嵌套表内的NULL空值元素

你遇到的嵌套表空行本质是extend()扩展后未成功赋值、或赋值为NULL生成的占位空元素，Oracle嵌套表没有内置的一键清理方法，可通过以下两种方案实现：

方案1：遍历删除法（适合小数据量场景） #

从后往前遍历嵌套表下标，匹配空元素后调用DELETE()方法删除，倒序遍历可避免删除元素后下标偏移导致的漏判：

-- 先定义嵌套表类型，示例为数值类型，可替换为你自定义的类型
CREATE OR REPLACE TYPE num_nested_tab IS TABLE OF NUMBER;
/

DECLARE
  v_my_tab num_nested_tab := num_nested_tab(1, NULL, 3, NULL, 5, NULL);
BEGIN
  -- 倒序遍历所有下标
  FOR i IN REVERSE 1..v_my_tab.COUNT LOOP
    IF v_my_tab(i) IS NULL THEN
      v_my_tab.DELETE(i);
    END IF;
  END LOOP;

  -- 输出验证：只会打印1、3、5，无空行
  FOR i IN v_my_tab.FIRST..v_my_tab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(v_my_tab(i));
  END LOOP;
END;
/

特点：直接修改原嵌套表，无需额外变量，适合元素量小于1000的场景

方案2：SQL过滤重生成法（适合大数据量场景） #

借助TABLE()函数将嵌套表转为行集，过滤空值后用COLLECT聚合为新的紧凑嵌套表，执行效率远高于遍历：

DECLARE
  v_old_tab num_nested_tab := num_nested_tab(1, NULL, 3, NULL, 5, NULL);
  v_new_tab num_nested_tab;
BEGIN
  SELECT CAST(COLLECT(column_value) AS num_nested_tab)
  INTO v_new_tab
  FROM TABLE(v_old_tab)
  WHERE column_value IS NOT NULL;

  -- 输出验证
  FOR i IN v_new_tab.FIRST..v_new_tab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(v_new_tab(i));
  END LOOP;
END;
/

注意：如果嵌套表存储的是自定义对象类型，将过滤条件中的column_value替换为对应对象的非空属性判断即可

补充说明 #

Oracle中空字符串等价于NULL，如果你的“空行”是元素值为空白字符串的场景，上述判断逻辑同样适用。

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