oracle - SELECT COUNT(*) 与使用显式游标获取两次

Question

我读过一本书，书名是 Steven Feuerstein 和 Bill Pribyl 的“Oracle PL SQL 编程”(第二版)。在第 99 页上，有一点建议

不要从表中“SELECT COUNT(*)”，除非你真的需要知道“命中”的总数。如果您只需要知道是否存在多个匹配项，只需使用显式游标获取两次即可。

你能通过提供例子向我解释这一点吗？谢谢你。

更新:

由于 Steven Feuerstein 和 Bill Pribyl 建议我们不要使用 SELECT COUNT() 来检查表中的记录是否存在，有人可以帮我编辑下面的代码以避免使用 SELECT COUNT(*) 而不是使用显式游标吗？这段代码是写在 Oracle 存储过程中的。

我有一个表 emp(emp_id, emp_name, ...)，所以要检查提供的员工 ID 是否正确:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

Answer 1

开发人员可能会从 PL/SQL 程序中的表中执行 select COUNT(*) 的原因有很多:

1)他们真的需要知道表中有多少行。

在这种情况下，别无选择:选择 COUNT(*) 并等待结果。这在许多 table 上会很快，但在大 table 上可能需要一段时间。

2)他们只需要知道一行是否存在。

这并不保证计算表中的所有行。许多技术是可能的:

a) 显式游标方法:

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- A row exists
      ...
   ELSE
      -- No row exists
      ...
   END IF;
END;

b) SELECT INTO 方法

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop fetching if 1 found
   -- At least one row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
END;

c) SELECT COUNT(*) 使用 ROWNUM 方法

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop counting if 1 found
   IF cnt = 0 THEN
      -- No row found
   ELSE
      -- Row found
   END IF;
END;

3) 他们需要知道是否存在超过 1 行。

(2) 工作技术的变化:

a) 显式游标方法:

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- 2 or more rows exists
      ...
   ELSE
      -- 1 or 0 rows exist
      ...
   END IF;
END;

b) SELECT INTO 方法

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... ;
   -- Exactly 1 row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
   WHEN TOO_MANY_ROWS THEN
      -- More than 1 row exists
END;

c) SELECT COUNT(*) 使用 ROWNUM 方法

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM <= 2; -- Stop counting if 2 found
   IF cnt = 0 THEN
      -- No row found
   IF cnt = 1 THEN
      -- 1 row found
   ELSE
      -- More than 1 row found
   END IF;
END;

您使用哪种方法很大程度上取决于偏好(以及一些宗教狂热!)Steven Feuerstein 一直偏爱显式游标而不是隐式(SELECT INTO 和游标 FOR 循环)； Tom Kyte 喜欢隐式游标(我同意他的观点)。

重要的一点是在不限制 ROWCOUNT 的情况下选择 COUNT(*) 是昂贵的，因此应该只在真正需要计数时才这样做。

至于你关于如何用显式游标重写这个的补充问题:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

那将是:

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
    CURSOR c IS SELECT 1
                FROM emp
                WHERE emp_id = emp_id_in;
    v_dummy INTEGER;
BEGIN
    ...

    OPEN c;    
    FETCH c INTO v_dummy;
    IF c%FOUND > 0 THEN
        /* do sth */
    END;
    CLOSE c;

    /* more statements */
    ...

END do_sth;

但实际上，在您的示例中，它没有好坏之分，因为您正在选择主键，而 Oracle 足够聪明，知道它只需要获取一次。