Oracle “如果存在” 的最佳实践

在SQL Server和IBM商店工作了25年之后，我一直在使用Oracle不到两年。我的任务是更新和维护我们公司开发和销售的应用程序使用的所有PL/SQL。这包括数百个软件包，这些软件包具有用于类似操作的不一致代码样式。在这种情况下，我发现了四种不同样式的测试行的存在 (请参阅LiveSQL脚本)。一种选择计数到int中，两种使用for循环的方式略有不同，第四种使用游标。

任何特定的风格有什么好处或缺点吗？给定正确编写的where子句，它们似乎都具有足够的性能。从美学上讲，我喜欢第二种风格，但是我很容易错过一个重要的警告。

Thanx，
唐

感谢您的测试用例。

独立于PL/SQL或任何其他包装器，执行:

从my_table中选择计数 (*)，其中 [条件]

通常可能是确定存在的一种糟糕的方法，仅仅是因为我们有可能要求数据库做更多的工作。如果 [criteria] 是主键查找，那么就可以了，但是一旦有人开始使用该方法，您到处都会看到 * 任何 * 种标准，然后您就遇到了麻烦。例如:

“是否存在具有所有者 = 系统的行？”

SQL> set autotrace on stat
SQL> select count(*) from t where owner = 'SYSTEM';

  COUNT(*)
----------
     23000


Statistics
----------------------------------------------------------
         10  recursive calls
         13  db block gets
      76019  consistent gets
      76000  physical reads
       1084  redo size
        610  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

哎哟... 76,000读!想想 “存在” 的真正含义。是 “如果我找到一排... 那就停下来”。所以我们可以让优化器知道:

SQL> select count(*) from t where owner = 'SYSTEM' and rownum = 1;

  COUNT(*)
----------
         1


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
         29  consistent gets
         28  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

效率更高。您还可以调整代码以使其更 “自我记录”，因此它实际上具有单词EXISTS，例如

SQL> select count(*) from dual
  2  where exists ( select null from t where owner = 'SYSTEM' );

  COUNT(*)
----------
         1


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

在这最后两种情况下，你总是得到一个单行 (1或0)，所以从PL/SQL的角度来看，你不需要任何光标处理等，你只需做:

select count(*)
into   l_exists_variable
from   ...
where  ...
and    rownum = 1

但是，我将添加此警告... 每当您发现自己编写存在检查时，请花点时间确定是否确实需要对其进行编码。所以我经常看到这样的代码:

1) 检查表中是否存在值 “X”
2) 如果不是，则插入值为 “X” 的行

这是一个程序错误，因为执行 (1) 绝不能保证 * 多用户系统中不存在值 “X”。停止 “X” 的多个值的唯一方法是具有唯一的约束... 如果我有，那么我根本不需要步骤1。我可以做:

1) 插入值为 “X” 的行

要么有效，要么有效。