如何在 Oracle PL/SQL 游标中找到记录数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2292167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I find the number of records in an Oracle PL/SQL cursor?
提问by aw crud
Here's my cursor:
这是我的光标：
CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;
I immediately open the cursor in order to lock these records for the duration of my procedure.
我立即打开游标以在我的过程中锁定这些记录。
I want to raise an application error in the event that there are < 2 records in my cursor. Using the C1%ROWCOUNT property fails because it only counts the number which have been fetched thus far.
如果游标中有 < 2 条记录，我想引发应用程序错误。使用 C1%ROWCOUNT 属性失败，因为它只计算到目前为止已提取的数量。
What is the best pattern for this use case? Do I need to create a dummy MY_TABLE%ROWTYPE variable and then loop through the cursor to fetch them out and keep a count, or is there a simpler way? If this is the way to do it, will fetching all rows in my cursor implicitly close it, thus unlocking those rows, or will it stay open until I explicitly close it even if I've fetched them all?
这个用例的最佳模式是什么？我是否需要创建一个虚拟的 MY_TABLE%ROWTYPE 变量，然后遍历游标以将它们取出并保持计数，还是有更简单的方法？如果这是这样做的方法，那么获取游标中的所有行是否会隐式关闭它，从而解锁这些行，还是会保持打开状态直到我明确关闭它，即使我已经获取了它们？
I need to make sure the cursor stays open for a variety of other tasks beyond this count.
我需要确保游标保持打开状态以执行超出此计数的各种其他任务。
采纳答案by ShoeLace
NB: i just reread you question.. and you want to fail if there is ONLY 1 record.. i'll post a new update in a moment..
注意：我刚刚重读了你的问题..如果只有 1 条记录，你想失败.. 我稍后会发布一个新的更新..
lets start here..
让我们从这里开始..
From Oracle? Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01reference
来自甲骨文？数据库 PL/SQL 用户指南和参考 10g 第 2 版 (10.2) 部件号 B14261-01参考
All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. Since the rows are no longer locked, you cannot fetch from a FOR UPDATE cursor after a commit.
当您打开游标时，所有行都被锁定，而不是在获取它们时锁定。当您提交或回滚事务时，行将被解锁。由于行不再锁定，因此您无法在提交后从 FOR UPDATE 游标中获取。
so you do not need to worry about the records unlocking.
因此您无需担心记录解锁。
so try this..
所以试试这个..
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;
ALTERNATE ANSWERI read you answer backwards to start and thought you wanted to exit if there was MORE then 1 row.. not exactly one.. so here is my previous answer.
替代答案我读你的答案倒退开始，并认为如果有更多然后 1 行，你想退出.. 不完全是 .. 所以这是我以前的答案。
2 simple ways to check for ONLY 1 record.
检查仅 1 条记录的 2 种简单方法。
Option 1 - Explicit Fetchs
选项 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;
I hope you get the idea.
我希望你能明白。
Option 2 - Exception Catching
选项 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;
AdditionallyRemember: with an explicit cursor.. you can %TYPE your variable off the cursor record rather then the original table.
另外请记住：使用显式游标..您可以将变量从游标记录中删除，而不是原始表。
this is especially useful when you have joins in your query.
当您在查询中加入连接时，这尤其有用。
Also, rememebr you can update the rows in the table with an
此外，请记住，您可以使用
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
type statement, but I that will only work if you haven't 'fetched' the 2nd row..
type 语句，但只有当你没有“获取”第二行时，我才会工作。
for some more information about cursor FOR loops.. try Here
有关游标 FOR 循环的更多信息.. 试试 这里
回答by Adam Musch
If you're looking to fail whenver you have more than 1 row returned, try this:
如果您希望在返回超过 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;
回答by Gary Myers
If this is the way to do it, will fetching all rows in my cursor implicitly close it, thus unlocking those rows
如果这是这样做的方法，将在我的游标中获取所有行隐式关闭它，从而解锁这些行
The locks will be present for the duration of the transaction (ie until you do a commit or rollback) irrespective of when (or whether) you close the cursor.
无论何时（或是否）关闭游标，锁都将在事务期间（即，直到您执行提交或回滚）一直存在。
I'd go for
我会去
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;
There's a very small chance that, between the time the cursor is opened (locking rows) and the select count, someone inserts one or more rows into the table with a salary under 50000. In that case the application error would be raised but the cursor would only process the rows present when the cursor was opened. If that is a worry, at the end do another check on c_1%rowcount and, if that problem was experienced, you'd need to rollback to a savepoint.
在打开游标（锁定行）和选择计数之间，极有可能有人将工资低于 50000 的一行或多行插入到表中。在这种情况下，将引发应用程序错误，但游标只会处理游标打开时存在的行。如果担心，最后再检查 c_1%rowcount，如果遇到该问题，您需要回滚到保存点。
回答by a'r
Create a savepoint before you iterate through the cursor and then use a partial rollback when you find there are < 2 records returned.
在遍历游标之前创建一个保存点，然后在发现返回的记录少于 2 条时使用部分回滚。
回答by Dmitri Kouminov
You can start transaction and check if SELECT COUNT(*) MY_TABLE WHERE SALARY < 50000 greater than 1.
您可以启动事务并检查 SELECT COUNT(*) MY_TABLE WHERE SALARY < 50000 是否大于 1。
