Oracle显示游标的使用及游标for循环

下面给大家介绍在什么情况下用隐式游标，什么情况下用显示游标：

1.查询返回单行记录时→隐式游标；

2.查询返回多行记录并逐行进行处理时→显式游标

--显示游标属性

declare
CURSOR cur_emp IS SELECT * FROM emp;
row_emp cur_emp%ROWTYPE;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO row_emp;
WHILE cur_emp%FOUND
LOOP
dbms_output.put_line(row_emp.empno||'----'||row_emp.ename);
FETCH cur_emp INTO row_emp;
END LOOP;
close cur_emp;
END;

--使用显式游标修改数据(给所有的部门经理加薪1000)

DECLARE
CURSOR emp_cur IS
SELECT empno,ename,sal FROM emp WHERE job='MANAGER' FOR UPDATE;
emp_row emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_row;
IF emp_cur%NOTFOUND THEN
EXIT;
ELSE
UPDATE emp SET sal=sal+1000 WHERE CURRENT OF emp_cur;
END IF;
END LOOP;
COMMIT;
CLOSE emp_cur;
END;

·注意：

1、如果游标打开之前或关闭之后，使用游标属性，Oracle会抛出一个INVALID_CURSOR错误（ORA-01001）；

2、如果在第一次fetch后结果集是空的，%found=false，%NotFound=true，%ROWCOUNT=0；

3、如果使用了BULK COLLECT，那么%ROWCOUNT的值可能不是0或1，实际上他返回的是提取到相关集合的行数。

--游标for循环（给所有的部门经理减薪1000）

DECLARE
CURSOR emp_cur IS
SELECT empno,ename,sal FROM emp WHERE job='MANAGER' FOR UPDATE;
BEGIN
FOR emp_row IN emp_cur
LOOP
UPDATE emp SET sal=sal-1000 WHERE CURRENT OF emp_cur;
END LOOP;
COMMIT;
END;

--我们可以看到游标FOR循环确实很好的简化了游标的开发，我们不在需要open、fetch和close语句，不在需要用%FOUND属性检测是否到最后一条记录，这一切Oracle隐式的帮我们完成了。

--给经理加薪5000，其他加薪1000

DECLARE
CURSOR emp_cur IS
SELECT * FROM emp FOR UPDATE;
BEGIN
 FOR emp_row IN emp_cur
 LOOP
 IF emp_row.job='MANAGER' THEN
 UPDATE emp SET sal=sal+5000 WHERE CURRENT OF emp_cur;
 ELSE
 UPDATE emp SET sal=sal+1000 WHERE CURRENT OF emp_cur;
 END IF;
 END LOOP;
 END;

下面给大家介绍oracle游标cursor简单使用

总共介绍两种游标一种高效使用游标cursor 、sys_refcursor 、 bulk collect

1、cursor游标使用

/*简单cursor游标
 *students表里面有name字段，你可以换做其他表测试
 */
--定义
declare
 --定义游标并且赋值(is 不能和cursor分开使用)
 cursor stus_cur is select * from students;
 --定义rowtype
 cur_stu students%rowtype;
 /*开始执行*/
 begin
 --开启游标
 open stus_cur;
  --loop循环
  loop
  --循环条件
  exit when stus_cur%notfound;
  --游标值赋值到rowtype
  fetch stus_cur into cur_stu;
  --输出
  dbms_output.put_line(cur_stu.name);
  --结束循环
  end loop;
 --关闭游标
 close stus_cur;
 /*结束执行*/
 end; 

执行结果

SQL> declare
  --定义游标并且赋值(is 不能和cursor分开使用)
  cursor stus_cur is select * from students;
  --定义rowtype
  cur_stu students%rowtype;
  /*开始执行*/
  begin
  --开启游标
  open stus_cur;
   --loop循环
   loop
   --循环条件
   exit when stus_cur%notfound;
   --游标值赋值到rowtype
   fetch stus_cur into cur_stu;
   --输出
   dbms_output.put_line(cur_stu.name);
   --结束循环
   end loop;
  --关闭游标
  close stus_cur;
  /*结束执行*/
 end;
 / 

杨过
郭靖
付政委
刘自飞
江风
任我行
任盈盈
令狐冲
韦一笑
张无忌
朵儿
谢逊
小龙女
欧阳锋
欧阳锋 

2、sys_refcursor游标使用

/*
 *游标名：sys_refcursor
 *特别注意赋值方式：for
 *与上重复内容不在叙述
 */
declare
 stu_cur sys_refcursor;
 stuone students%rowtype; 

 begin
  --这句赋值方式for
  open stu_cur for select * from students;
  --fetch赋值给rowtype
  fetch stu_cur into stuone; 

  loop
  dbms_output.put_line(stuone.name||' '||stuone.hobby);
  fetch stu_cur into stuone;
  exit when stu_cur%notfound;
  end loop;
 end; 

执行结果

SQL> /*
  *游标名：sys_refcursor
  *特别注意赋值方式：for
  *与上重复内容不在叙述
  */
 declare
  stu_cur sys_refcursor;
  stuone students%rowtype;
  begin
  --这句赋值方式for
  open stu_cur for select * from students;
  --fetch赋值给rowtype
  fetch stu_cur into stuone;
  loop
   dbms_output.put_line(stuone.name||' '||stuone.hobby);
   fetch stu_cur into stuone;
   exit when stu_cur%notfound;
  end loop;
  end;
 /
杨过 保护小龙女
郭靖 修炼降龙十八掌
付政委 看小人书
刘自飞 编程写代码
江风 编程写代码
任我行 修炼神功
任盈盈 游山玩水
令狐冲 行侠仗义
韦一笑 吸拾人雪
张无忌 修行
朵儿 洗浴
谢逊 毕生研究屠龙刀
小龙女 修炼玉女心经
欧阳锋 看小人书 

补充一种循环条件

declare
 stu_cur sys_refcursor;
 stuone students%rowtype;
 begin
  open stu_cur for select * from students;
  fetch stu_cur into stuone;
  --特别注意循环条件的改变
  --这个条件是发现了在循环
  --与上一个notfound不同的
  while stu_cur%found loop
  dbms_output.put_line(stuone.name||' '||stuone.hobby);
  fetch stu_cur into stuone;
  end loop;
 end; 

 --普通的fetch into

/*普通方式*/
declare
cursor myemp_cur is select * from myemp;
v_myemp myemp%rowtype;
begin
 open myemp_cur;
 fetch myemp_cur into v_myemp;
 while myemp_cur%found loop
 dbms_output.put_line(v_myemp.ename);
 fetch myemp_cur into v_myemp;
 end loop;
end; 

 --高效的bulk collect

/*高效bulk collect for*/
declare
cursor myemp_cur
is select * from myemp;
type myemp_tab is table of myemp%rowtype;
myemp_rd myemp_tab;
begin
 open myemp_cur;
 loop
 fetch myemp_cur bulk collect into myemp_rd limit 20;
 for i in 1..myemp_rd.count loop
  dbms_output.put_line('姓名：'||myemp_rd(i).ename);
 end loop;
 exit when myemp_cur%notfound;
 end loop;
end;