如何将字段空白值替换为NULL并过滤非空值完成表数据插入

• 你编写的SELECT语句中，别名number不能直接在JOIN、WHERE条件里使用。SQL执行顺序是先处理JOIN、WHERE逻辑，再执行SELECT段的别名定义，你写的过滤条件实际生效对象不是你转大写后的USER_NUM，过滤空白的逻辑完全失效。
• 你的写入逻辑完全错误：INSERT INTO table2是新增全新行，不是更新匹配到的现有行，新增行没有填写OWNER_ID、USER_NUM等非空字段，才会触发空值报错，你的需求应该用UPDATE操作而非INSERT。

最优方案（单语句UPDATE，无需循环，性能更高） #

UPDATE table2 t2
SET USER_NAME = (
  SELECT t1.FIRST_NAME || ' ' || t1.LAST_NAME
  FROM table1 t1
  WHERE UPPER(TRIM(t1.USER_NUM)) = UPPER(TRIM(t2.USER_NUM))
  AND TRIM(t1.USER_NUM) IS NOT NULL
  AND TRIM(t1.USER_NUM) != ''
)
WHERE EXISTS (
  SELECT 1
  FROM table1 t1
  WHERE UPPER(TRIM(t1.USER_NUM)) = UPPER(TRIM(t2.USER_NUM))
  AND TRIM(t1.USER_NUM) IS NOT NULL
  AND TRIM(t1.USER_NUM) != ''
);

兼容原有循环逻辑的修正版本 #

BEGIN
  FOR c1 IN (
    SELECT UPPER(TRIM(t1.USER_NUM)) as user_num_upper,
           t1.FIRST_NAME || ' ' || t1.LAST_NAME as user_name
    FROM table1 t1
    INNER JOIN table2 t2
      ON UPPER(TRIM(t1.USER_NUM)) = UPPER(TRIM(t2.USER_NUM))
    WHERE TRIM(t1.USER_NUM) IS NOT NULL
      AND TRIM(t1.USER_NUM) != ''
  )
  LOOP
    -- 使用UPDATE更新匹配行，而非INSERT新增行
    UPDATE table2
    SET USER_NAME = c1.user_name
    WHERE UPPER(TRIM(USER_NUM)) = c1.user_num_upper;
  END LOOP;
END;
/

• 取消了对SELECT别名的错误引用，所有过滤、匹配逻辑直接操作原始t1.USER_NUM字段
• 用TRIM()函数直接去除首尾空格，比正则替换性能更高，同时判断非空、非空字符串，完全过滤USER_NUM为空白的行
• 把错误的INSERT操作改为UPDATE，避免新增无必填字段的空行触发非空报错
• 匹配逻辑增加TRIM处理，避免两个表的USER_NUM存在首尾空格导致匹配失败的问题

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