检索一组数据,定义一个Cursor,
Cursor打开后,如何判断Cursor是否为空?
Cursor打开后,如何判断Cursor是否为空?
解决方案 »
- 如何查找查看类日志表记录
- 本人初学oracle 不清楚 sequser.table_seq.nextval
- oracle 中where条件能不能用IF
- 请教查询ORACLE数据库所有的表名和列名
- 安装Oracle9i出现错误,无法创建目录,提示目录不可写或者空间不足
- Enterprise Manager Console 以sysman进去后,数据库没有了.怎么办?谢谢
- 高手请赐教。如何才能把可变的 字符串 传到PROCEDURE 的程序里呢。
- 受不了了, 怎么oracle那么难上手啊??高手们.你们是怎么学的?给点建议
- 请问ORACLE的PL/SQL中的字符串续行符是什么,谢谢!
- 做ORACLE的全文检索遇到的问题,高手救急!
- 紧急求助 SQL语句编译后提示缺失右括号
- 如何获取出错的SQL语句??
...
end if;
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
--一个游标操作的模版
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;
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
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;
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>
RowCount非空时,正常处理后面业务。
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>
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>