oracle查询游标行数,如何在Oracle PL / SQL游标中找到记录数？

注意：我只是重读了你的问题..如果只有1条记录,你想失败.

我马上发布一个新的更新..

我们从这里开始..

从Oracle®DatabasePL / SQL用户指南和参考

10g第2版(10.2)

部件号B14261-01

reference

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.

所以你不必担心记录解锁.

所以试试这个..

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,而不是原始表.

当您在查询中加入时,这尤其有用.

此外,请记住,您可以使用更新表中的行

UPDATE table_name

SET set_clause

WHERE CURRENT OF cursor_name;

类型语句,但我只有在你没有“获取”第二行时才能工作.

有关游标FOR循环的更多信息,请尝试

Here