如何查找 Oracle PL/SQL 游标中的记录数？

Question

这是我的光标：

CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;

我立即打开光标，以便在程序期间锁定这些记录。

我想在存在 << 的情况下提出应用程序错误我的光标中有 2 条记录。使用 C1%ROWCOUNT 属性会失败，因为它只计算迄今为止已获取的数量。

该用例的最佳模式是什么？我是否需要创建一个虚拟 MY_TABLE%ROWTYPE 变量，然后循环游标以将它们取出并保持计数，或者是否有更简单的方法？如果这是这样做的方法，那么获取游标中的所有行会隐式关闭它，从而解锁这些行，或者即使我已经获取了所有行，它也会保持打开状态直到我显式关闭它吗？

我需要确保光标保持打开状态以执行超出此计数的各种其他任务。

Answer 1

注意：我刚刚重读了你的问题..如果只有 1 条记录，你想失败..
我稍后会发布新的更新..

让我们从这里开始..

来自 Oracle® Database PL/SQL 用户指南和参考
10g 版本 2 (10.2)
零件号 B14261-01
参考

所有行在您打开游标时都被锁定，而不是在获取它们时被锁定。当您提交或回滚事务时，这些行将被解锁。由于行不再被锁定，因此您无法在提交后从 FOR UPDATE 游标中获取数据。

所以您无需担心记录解锁。

所以试试这个..

declare 
  CURSOR mytable_cur IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;

  TYPE mytable_tt IS TABLE OF mytable_cur %ROWTYPE
    INDEX BY PLS_INTEGER;

  l_my_table_recs mytable_tt;
  l_totalcount NUMBER;
begin

   OPEN mytable_cur ;
   l_totalcount := 0;

   LOOP
      FETCH mytable_cur 
      BULK COLLECT INTO l_my_table_recs LIMIT 100;

      l_totalcount := l_totalcount + NVL(l_my_table_recs.COUNT,0);

      --this is the check for only 1 row..
      EXIT WHEN l_totalcount < 2;

      FOR indx IN 1 .. l_my_table_recs.COUNT
      LOOP
         --process each record.. via l_my_table_recs (indx)

      END LOOP;

      EXIT WHEN mytable_cur%NOTFOUND;
   END LOOP;

   CLOSE mytable_cur ;
end;

---

替代答案
我从后面读到你的答案，并认为如果有超过 1 行，你想退出。不完全是一行。所以这是我之前的答案。

2 种简单方法仅检查 1 条记录。

选项 1 - 显式获取

declare 
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;
  l_my_table_rec C1%rowtype;
  l_my_table_rec2 C1%rowtype;
begin

    open C1;
    fetch c1 into l_my_table_rec;

    if c1%NOTFOUND then
       --no data found
    end if;

    fetch c1 into l_my_table_rec2;
    if c1%FOUND THEN
      --i have more then 1 row
    end if;
    close c1;

  -- processing logic

end;

我希望您明白这一点。

选项 2 - 异常捕获

declare 
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;
  l_my_table_rec C1%rowtype;
begin
  begin
    select * 
      from my_table
      into l_my_table_rec
     where salary < 50000
       for update;
  exception
    when too_many_rows then
      -- handle the exception where more than one row is returned
    when no_data_found then
      -- handle the exception where no rows are returned
    when others then raise;
  end;

  -- processing logic
end;

另外
请记住：使用显式游标..您可以从游标记录而不是原始表中%TYPE您的变量。

当查询中有联接时，这尤其有用。

另外，请记住，您可以使用 type 语句更新表中的行

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

，但只有在您没有“获取”第二行时，这才有效。.

有关游标 FOR 循环的更多信息..尝试
此处

Answer 2

如果您希望在返回超过 1 行时失败，请尝试以下操作：

declare 
  l_my_table_rec my_table%rowtype;
begin
  begin
    select * 
      from my_table
      into l_my_table_rec
     where salary < 50000
       for update;
  exception
    when too_many_rows then
      -- handle the exception where more than one row is returned
    when no_data_found then
      -- handle the exception where no rows are returned
    when others then raise;
  end;

  -- processing logic
end;

Answer 3

如果这是这样做的方法，将
获取光标中的所有行
隐式关闭它，从而解锁
这些行

锁将在事务持续时间内存在（即直到您执行提交或回滚），无论您何时（或是否）关闭游标。

我会选择

declare
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;;
  v_1 c1%rowtype;
  v_cnt number;
begin
  open c_1;
  select count(*) into v_cnt FROM MY_TABLE WHERE SALARY < 50000 and rownum < 3;
  if v_cnt < 2 then
    raise_application_error(-20001,'...');
  end if;
  --other processing
  close c_1;
end;

在打开游标（锁定行）和​​选择计数之间，有人将工资低于 50000 的一行或多行插入到表中的可能性很小。在这种情况下，应用程序错误将被引发，但游标只会处理打开游标时出现的行。如果这是一个担心，最后对 c_1%rowcount 进行另一次检查，如果遇到该问题，则需要回滚到保存点。

Answer 4

在遍历游标之前创建一个保存点，然后在发现有 << 时使用部分回滚。返回 2 条记录。

Answer 5

您可以启动事务并检查是否 SELECT COUNT(*) MY_TABLE WHERE SALARY < 50000 大于 1。