易强xi

Oracle学习笔记之游标详解

游标

游标存在意义:解决“select *”返回空、多行记录问题,但凡select,就可能多行结果集,也就需要用游标。

游标分4步走:cursor、open、fetch、close

可能省略open、close,用for ... in ... loop ... end loop;

1、静态游标:

(1)隐式游标:

在PL/SQL中DML(select into、insert、update、delete)时,Oracle隐式定义一个叫SQL的游标,自动声明、打开、关闭。

①sql%found

如果DML语句影响一行或多行,则sql%found为true

例如:

declare

   empno emp.empno%type;

begin

   select empno into empno -- 对于DML之select into

   from emp

   where empno =7369;

   ifsql%foundthen

      dbms_output.put_line('有一行记录');-- 用sql%found、sql%notfound判断是否返回一行记录

  endif;

Exception

  when no_data_found then

    dbms_output.put_line('查询返回空行');-- 用no_data_found判断是否返回空行记录

  when too_many_rows then

dbms_output.put_line('查询返回多行');-- 用too_many_rows判断是否返回多行记录

When others then

dbms_output.put_line(‘系统错误');

end;

例如:

 declare

   total integer;

   e_null exception;--自定义的异常

   e_toomanyrow exception;

begin

  select count(*) into total from emp where emp.deptno=20;   

  if total>1 then

    raise e_toomanyrow;--抛出异常

  elsif total<=0 then

    raise e_null;

  end if;

  --捕获并处理异常

  Exception when e_toomanyrow then

    dbms_output.put_line('Exception:返回多行记录');

  when e_null then

     dbms_output.put_line('Exception:无返回记录');

  when others then

     dbms_output.put_line('Exception:其他异常');

 

end;

 ②sql%notfound

如果DML语句没有影响任何行,则sql%notfound为true

第一种情况:DML是insert、delete、update

例如:

declare

begin

   deletefrom emp

   where empno =47;

   ifsql%notfoundthen

      dbms_output.put_line('未找到值');

   else

      dbms_output.put_line('已经删除');

   endif;

end;

第二种情况:DML是select into

PL/SQL中的“select into”的结果必须“有且只有一行”,该行结果可以赋给列变量、或记录变量

 例如:

declare

  type type_emp isrecord

  (

  empno emp.empno%type,

  ename emp.ename%type,

  dname dept.dname%type,

  sal emp.sal%type

  );

  v_emp type_emp;

begin

  select empno, ename, dname, sal into v_emp

  from emp innerjoin dept

  on emp.deptno = dept.deptno

  where ename ='SMITH';

  --if sql%notfound then

    --dbms_output.put_line('查无此人'); -- 永远执行不到,因为查询返回空行时触发no_data_found异常

  --else

    dbms_output.put_line(v_emp.empno||'*'||v_emp.ename||'*'||v_emp.dname||'*'||v_emp.sal);

  --end if;

exception

  whenno_data_foundthen

    dbms_output.put_line('查无此人');

  whenothersthen

    dbms_output.put_line(sqlerrm);

end;

 

注意:

如果查询返回空行,PL/SQL抛出no_data_found异常;

如果查询返回多行,PL/SQL抛出too_many_rows异常;

所以在DML之“select into”引发异常时,不能使用sql%notfound属性查看DML是否影响了行。

③sql%rowcount 

declare

  empno emp.empno%type;

begin

  select empno into empno

  from emp

  where empno =7788;

  ifsql%rowcount>0then

    dbms_output.put_line('从表中选择了'||sql%rowcount||'行');

  else

    dbms_output.put_line('从表中未选择行');

  endif;

end;

④sql%isopen

SQL%ISOPEN返回一个布尔值,如果游标打开,则为TRUE, 如果游标关闭,则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE,这是因为隐式游标在DML语句执行时打开,结束时就立即关闭。

(2)显式游标

重点在“步骤”:定义游标,打开游标, 抓取数据,关闭游标;

①游标%found

如果最后一条fetch语句成功提取行,则“游标%found”为true

②游标%notfound

如果最后一条fetch语句未能提取行,则“游标%notfound”为true

例如:

declare

cursor cur is

select ename,job from emp;  --这里enamel,job 两列

item emp.ename%type;   --定义item是emp表ename列的类型

item2 emp.job%type;    --定义item2 是emp表job列类型。 这里声明的变量必须对性查询--出的列对应。

begin

open cur;

loop

fetch cur into item,item2;  --捕捉了游标cur 的元素放入,item,item2中。

 

exitwhencur%notfound;  -- 当cur游标都便利完就exit退出。

endloop;

dbms_output.put_line(item||item2);

 

close cur;                  --注意关闭游标

end;

 

 

③游标%isopen,判断该游标是否被打开;

例如:

Declare

   --声明一个游标

  cursor cur is 

  select* from emp where deptno =10;

  v_emp cur%rowtype;

begin

  ifnot cur%isopenthen

    open cur;--如果游标没有被打开,那么就代开游标

  endif;

--循环,从游标中抓取数据

  loop

    fetch cur into v_emp;

    exitwhen cur%notfound;--当最后一条数据被获取后,exit退出循环

    dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

  endloop;

  close cur;--关闭游标

end;

④游标%rowcount:返回游标查询记录数;

注意:SQL%ROWCOUNT:在执行任何DML语句之前,SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句,如果执行成功,SQL%ROWCOUNT的值为1,如果没有成功,SQL%ROWCOUNT的值为0,同时产生一个异常NO_DATA_FOUND.

例如:(上面例子多了几行代码) declare

  cursor cur is

  select*

  from emp

  where deptno =10;

  v_emp cur%rowtype;

  a number;

begin

  ifnot cur%isopenthen

    open cur;

  endif;

  loop

    fetch cur into v_emp;

    exitwhen cur%notfound;

    dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

  endloop;

-----------------------------------------------------------

 

  --下面是sql%rowcount

  select empno into  a from emp where empno='7369';

  dbms_output.put_line(sql%rowcount);--如果查询sql 有记录数,此时返回1否则为null

  dbms_output.put_line(cur%rowcount);--返回游标查询的最终记录数

-----------------------------------------------------------

  close cur;

end;

例如:

 declare

cursor cur(i varchar2)is

select*from emp where deptno=i ;

    item cur%rowtype;

 

begin

 

for r in cur('10')  -- 注意这里的in。。。 后面跟的是游标 cur 而不是 tiem

      --这里可以写成:for admin in cur(&部门编号);用通配符,使键盘键入内容。

loop

dbms_output.put_line(r.ename|| cur%rowcount);--其中cur%rowcount 是有标明%rowcount 显示游标序列

endloop;

end;

⑤使用显式游标更新或删除行:

declare

-- select语句必须只包括一个表

-- 某表不能是“含有distinct、order by的子查询”查询来的结果集(视图)

cursor 游标名 is select 某列 from 某表 for update[ of 某列];

变量 某表.某列%type;

变量 某表%rowtype;

变量 游标%type;

变量 游标%rowtype;

begin

open 游标;

loop

      fetch 游标 into 变量;

      exit when 游标%notfound;

      if 某条件 then

            -- update、delete语句只有在打开游标、提取特定行之后才能使用

            -- update语句中使用的列必须出现在“select-for update of”语句中

            update 某表 set 某列 = 某值 where current of 游标;

      end if;

end loop;

close 游标;

commit;

end;

例如:

-- 如果sal < 2000,长工资1000

declare

   cursor cur is

       select sal from emp

      where sal <2000forupdate;

  cursor bbb is

       select*from emp;

begin

  for v_sal in cur

   loop

       update emp

       set sal = sal +1000

       wherecurrentof cur;

  endloop;

 

 Commit;

  for v_emp in bbb

  loop

    dbms_output.put_line(v_emp.ename||'*'||v_emp.sal);

  endloop;

end;

动态游标:

(1)弱类型游标

例如:

declare         -- //弱类型游标

   type cursor_type isrefcursor;

   cur cursor_type;

   temp emp%rowtype;

begin

    open cur for 

    select*from  emp;  

    loop

       fetch cur into temp;

       exitwhencur%notfound;

       dbms_output.put_line(temp.empno||temp.ename);

    endloop; 

end;

例如:

 declare

   type cur_type isrefcursor;-- 弱类型游标 通过is ref 定义

   cur   cur_type;

   v_emp emp%rowtype;

   v_dept dept%rowtype;

   selection varchar2(1):= upper('D');

 begin

   if selection ='E'then

     open cur for

      select*from emp;

     loop

       fetch cur into v_emp;

       exitwhen cur%notfound;

       dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

     endloop;

     close cur;

   elsif selection ='D'then

     open cur for

       select*from dept;

     loop

       fetch cur intov_dept;

       exitwhen cur%notfound;

       dbms_output.put_line(v_dept.deptno||':'||v_dept.dname);

     endloop;

     close cur;

   else

     null;

   endif;

 end;

 

 

(2)强类型游标

例如:

 declare      --强类型游标 强制返回内容

   type record_type isrecord(empno number,ename varchar2(20));

   type cursor_type isrefcursorreturn record_type;

   rec record_type;

   cur cursor_type;

begin

    open cur for 

    select empno,ename from  emp;  

    loop

       fetch cur into rec;

       exitwhen cur%notfound;

       dbms_output.put_line(rec.empno||' '||rec.ename);

    endloop; 

end;

例如:

  -- 用强类型游标显示员工姓名和工资

 declare

   type record_type isrecord(namevarchar2(10), sal number);-- 记录类型

   type cur_type isrefcursorreturn record_type;-- 强类型游标,将来必须返回record_type类型的结果集

   emp_record record_type;-- 记录类型变量

   emp_cur   cur_type;-- 强类型游标变量

 

 begin

   open emp_cur for

   select ename, sal from emp;-- 动态SQL两边的单引号可以省略,必须返回record_type类型的结果集

   loop

      fetch emp_cur into emp_record;-- 把record_type类型的结果集向record_type类型的变量中填充

      exitwhen emp_cur%notfound;

      dbms_output.put_line(emp_record.name||':'||emp_record.sal);

   endloop;

   close emp_cur;

 end;

 

posted on 2017-10-20 10:41  易强xi  阅读(698)  评论(0编辑  收藏  举报

导航