Oracle PL/SQL中快速校验表ID集合与数组是否完全一致的最优方法

问题场景 #

现有表my_table的结构及数据如下：

+----+--------------------+
| id | some_other_columns |
+====+====================+
| 2  | data_2             |
+----+--------------------+
| 4  | data_4             |
+----+--------------------+
| 6  | data_6             |
+----+--------------------+

同时在PL/SQL中定义了如下数组：

type t_numbers_type is table of number;
my_array t_numbers_type := t_numbers_type(2, 4, 6);

核心需求 #

快速验证my_table中所有id的集合与my_array完全一致：

• 表中id的数量必须和数组元素数量相等
• 表中所有id都存在于数组中
• 数组中所有元素都存在于表的id中

现有实现方案 #

当前使用的是PL/SQL遍历校验的方式：

l_cnt        := 0;
l_missing_id := false;

for r_rec in (select id
                from my_table)
loop
  l_cnt := l_cnt +1;
  if r_rec.id not member of my_array then
    l_missing_id := true;
    exit;   
  end if;
end loop;

if l_cnt = my_array.count and not l_missing_id then
  dbms_output.put_line('OK');
else
  dbms_output.put_line('Not OK');
end if;

疑问 #

是否存在更简洁高效的实现方式？比如以下语句是否可行：

exists(select * from my_table where id in my_array)

或：

not exists(select * from my_table where id not in my_array)

最优实现方案 #

推荐使用SQL集合对比的方式，相比PL/SQL遍历，它能利用数据库优化器，在数据量较大时性能更优，且代码更简洁，一次查询即可完成双向校验。

方法1：双向差集校验（最直观） #

通过MINUS运算符分别计算表与数组、数组与表的差集，若差集总条数为0，说明两个集合完全一致：

declare
  l_diff_count number;
begin
  select count(*)
  into l_diff_count
  from (
    -- 表中有但数组中没有的ID
    select id from my_table
    minus
    select column_value from table(my_array)
    union all
    -- 数组中有但表中没有的ID
    select column_value from table(my_array)
    minus
    select id from my_table
  );

  if l_diff_count = 0 then
    dbms_output.put_line('OK');
  else
    dbms_output.put_line('Not OK');
  end if;
end;
/

方法2：聚合函数组合校验（性能更优） #

通过聚合函数一次性校验数量匹配、无多余ID、无缺失ID三个条件：

declare
  l_is_match number;
begin
  select case
           when count(distinct id) = my_array.count
                and count(case when id not member of my_array then 1 end) = 0
                and (select count(*) from my_table where id member of my_array) = my_array.count
           then 1
           else 0
         end
  into l_is_match
  from my_table;

  dbms_output.put_line(case l_is_match when 1 then 'OK' else 'Not OK' end);
end;
/

对疑问中语句的分析 #

你提到的两个语句都无法满足完整需求：

• exists(select * from my_table where id in my_array)：仅判断表中存在至少一个匹配数组的ID，完全未校验ID数量、是否有多余ID，也没验证数组元素是否全部在表中。
• not exists(select * from my_table where id not in my_array)：仅确保表中没有不在数组里的ID，但无法验证表的ID数量是否和数组一致，也无法保证数组所有元素都在表中（比如数组有3个元素，表只有2个匹配ID，该语句仍会返回true）。

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