怎样判断CURSOR为空？

[liudj]

检索一组数据，定义一个Cursor，
Cursor打开后，如何判断Cursor是否为空？

[gelyon]

if

cursor%notfound 

then
...
end if;

[jonsoncheng]

学习了！@@@

[Diza1986]

%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真，否则为假

[sinpoal]

loop
fetch cursor_name into recor_type_date ;
exit when cursor_name%notfound;
end loop;

[真的不知道取什么名]

--给个例子
declare
cursor emp_cur is select empno,ename,sal from emp for update;
emp_row emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_row;
exit when emp_cur%notfound;
if emp_row.sal<2000 then
--使用current of emp_cur时一定要for update;
update emp set sal=sal*1.1 where current of emp_cur;
end if;
end loop;
close emp_cur;
end;

[ngx20080110]

DECLARE
  CURSOR your_cursor IS SELECT first_name || ' ' || last_name FROM employees WHERE salary > 10000;
  fn VARCHAR2(30);
BEGIN
  OPEN your_cursor;
  LOOP
    FETCH your_cursor INTO fn;
    EXIT WHEN your_cursor%notfound;
    
    DBMS_OUTPUT.PUT_LINE(fn);
  END LOOP;
  CLOSE your_cursor;
END;

-- 结果
Neena Kochhar
Lex De Haan

[kpkero]

notfound

[siriusraider]

cursor%notfound

[心中的彩虹]

[Quote=引用楼主 liudj 的回复:]
检索一组数据，定义一个Cursor，
Cursor打开后，如何判断Cursor是否为空？
[/Quote]

--一个游标操作的模版


declare
v_name varchar2(10);
cursor cur is select name from tb;
begin
open cur;
fetch cur into v_name ;
while cursor%found loop
dbms_output.put_line(v_name);
fetch cur into v_name;
end loop;
close cur;
end;

[minitoy]

SQL> set serveroutput on
SQL> 
SQL> declare
  2  cv_c  sys_refcursor;
  3  begin
  4   open cv_c for select 1 from dual where 1=0;
  5   dbms_output.put_line(cv_c%rowcount);
  6   close cv_c;
  7  end;
  8  /
 
0
 
PL/SQL procedure successfully completed
 
SQL> 

[liudj]

谢谢各位，可能是我没有描述清楚。

我的意思是，定义一个CURSOR，处理时，如果Cursor内的ROWCOUNT为0件，抛出Warning信息，转向异常。
RowCount非空时，正常处理后面业务。

[minitoy]

SQL> set serveroutput on
SQL> 
SQL>     declare
  2      cv_c  sys_refcursor;
  3      zero_row_in_cursor   EXCEPTION;
  4  
  5      begin
  6       open cv_c for select 1 from dual where 1=0;
  7       dbms_output.put_line(cv_c%rowcount);
  8       if cv_c%rowcount=0 then
  9          close cv_c;
 10          RAISE zero_row_in_cursor;
 11       end if;
 12      exception
 13        when zero_row_in_cursor then
 14             rollback;
 15         dbms_output.put_line('error,zero row find in cursor');
 16      end;
 17  /
 
0
error,zero row find in cursor
 
PL/SQL procedure successfully completed
 
SQL> 

[minitoy]

奇怪,代码怎么怪怪的,没语法着色了.

SQL> set serveroutput on
SQL> 
SQL>     declare
  2      cv_c  sys_refcursor;
  3      zero_row_in_cursor   EXCEPTION;
  4  
  5      begin
  6       open cv_c for select 1 from dual where 1=0;
  7       dbms_output.put_line(cv_c%rowcount);
  8       if cv_c%rowcount=0 then
  9          close cv_c;
 10          RAISE zero_row_in_cursor;
 11       end if;
 12      exception
 13        when zero_row_in_cursor then
 14             rollback;
 15         dbms_output.put_line('error,zero row find in cursor');
 16      end;
 17  /
 
0
error,zero row find in cursor
 
PL/SQL procedure successfully completed
 
SQL>