欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle游标解析

程序员文章站 2023-08-17 23:15:28
本节对Oracle中的游标进行详细讲解。 本节所举实例来源Oracle中scott用户下的emp表dept表: 一、游标: 1、概念: 游标的本质是一个结果集resultset,主要用来临时存储从数据库中提取出来的数据块。 二、游标的分类: 1、显式游标:由用户定义,需要的操作:定义游标、打开游标、 ......

本节对oracle中的游标进行详细讲解。

本节所举实例来源oracle中scott用户下的emp表dept表:

Oracle游标解析Oracle游标解析

一、游标:

1、概念:

游标的本质是一个结果集resultset,主要用来临时存储从数据库中提取出来的数据块。

二、游标的分类:

1、显式游标:由用户定义,需要的操作:定义游标、打开游标、提取数据、关闭游标,主要用于对查询语句的处理。

属性:%found        %notfound        %isopen          %rowcount

example:打印emp表的员工信息

declare
  cursor emp_cursor is select empno,ename,job from emp;
  v_empno emp.empno%type;
  v_name emp.ename%type;
  v_job emp.job%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into v_empno,v_name,v_job;
    dbms_output.put_line('员工号为:'||v_empno||'姓名是'||v_name||'职位:'||v_job);
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
end; 

Oracle游标解析

这里严格按照显示游标的书写规则:declare emp_cursor定义游标open emp_cursor打开游标fetch emp_cursor into...提取数据close emp_cursor关闭游标,因为提取出来的数据属于多行,所以通过loop循环打印即可。

example2:检验游标是否打开,如果打开显示提取行数

declare
  cursor emp_cursor is select empno,ename,job from emp;
  v_empno emp.empno%type;
  v_name emp.ename%type;
  v_job emp.job%type;
begin
  open emp_cursor;
  loop
       fetch emp_cursor into v_empno,v_name,v_job;
       exit when emp_cursor%notfound;
  end loop;
  if emp_cursor%isopen then
    dbms_output.put_line('游标已打开');
    dbms_output.put_line('读取了'||emp_cursor%rowcount||'行');
  else
    dbms_output.put_line('游标没有打开');
  end if;  
  close emp_cursor;
end;

Oracle游标解析

通过%isopen属性判断游标是否打开,%rowcount判断获取行数。

2、隐式游标:由系统定义并为它创建工作区域,并且隐式的定义打开提取关闭,隐式游标的游标名就是'sql',属性和显示游标相同,主要用于对单行select语句或dml操作进行处理。

example:又用户输入员工号修改员工工资如成功则打印输出成功标志。

为了尽量不改变原表,创建新表emp_new和原表数据相同:

create table emp_new
as
select * from emp;
begin
  update emp_new set sal = sal+500 where empno=&empno;
  if sql%found then
    dbms_output.put_line('成功修改');
    commit;
  else
    dbms_output.put_line('修改失败');
    rollback;
  end if;
end;

Oracle游标解析

Oracle游标解析

这里注意增删改以后要对做的操作进行commit提交,如果操作失败则rollback回滚刚才的操作。

3、参数游标:

在定义游标时加入参数的游标,可以配合游标for循环快速找到需要的数据。这里先讲一下游标for循环

a、游标for循环:

隐含的执行了打开提取关闭数据,代码精简很多。expression:

for table_record in table_cursor loop

  statement;

end loop;

 example:使用游标for循环打印输出员工信息:

declare
cursor emp_cursor is select empno,ename,job from emp;
begin
  for emp_record in emp_cursor loop
    dbms_output.put_line('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job);
  end loop;
end;

Oracle游标解析

这里游标for循环省去了对于取到的数据的变量的命名和赋值,同时如果全部打印则不用写循环条件,代码精简了很多。

如果想让代码更加精简,则可以去掉对游标的声明引入子查询即可,操作如下。

begin
  for emp_record in (select empno,ename,job from emp) loop
    dbms_output.put_line('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job);
  end loop;
end;

代码更加精简,得到的结果相同。和隐式游标是不是有点像,但隐式游标主要用于的是单行select和dml语句的操作,注意2者用法的区别。

下面继续参数游标的实例:

example:输入部门号打印员工信息:

declare
cursor emp_cursor(dno number)is select empno,ename,job from emp where deptno=dno;
begin
  for emp_record in emp_cursor(&dno) loop
    dbms_output.put_line('员工号'||emp_record.empno||'姓名'||emp_record.ename||'职位'||emp_record.job);
  end loop;
end;

Oracle游标解析

 

 Oracle游标解析

这里既然有参数,那么必然会有对游标的声明,在结合游标for循环快速超找所需要的数据。

三、使用游标修改数据的注意事项

1、使用游标修改数据时,为防止他人在自己操作数据时对数据进行修改,oracle提供for update子句进行加锁。

同时在你使用update或delete时,必须使用where current of+name_cursor语句,以及在最后记得提交。如果

是级联操作则可以使用for update of 来进行相关表的加锁。

example1:对职位是president的员工加1000工资,manager的人加500工资

create table emp_new
as
select * from emp;
declare
cursor empnew_cursor is select ename,job from emp_new for update;
begin
  for empnew_record in empnew_cursor loop
    dbms_output.put_line('姓名'||empnew_record.ename||'职位'||empnew_record.job);
    if empnew_record.job='president' then
      update emp_new set sal=sal+1000 where current of empnew_cursor;
    elsif empnew_record.job='manager' then
      update emp_new set sal=sal+500 where current of empnew_cursor;
    end if;
  end loop;
  commit;
end;
select * from emp where job in('president','manager');
select * from emp_new where job in('president','manager');

Oracle游标解析

Oracle游标解析

可以看到这里工资有了相应的变化。

 

 

至此,oracle游标解析完毕,总而言之,游标只是作为我们从数据库中提取出来的一部分数据,我们针对这个结果集做一系列的操作。

                                            2018-09-07    16:15:34