www.9778.com 11

【www.9778.com】Oracle游标(一)显式游标

执行查询,返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。

       where e.department_id=d.department_id;  

   type cursor_type isrefcursor;

c.关闭游标(CLOSE)

  cursor emp_cur is –定义游标  

end;

是一种引用类型,类似于指针。

www.9778.com 1

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

一.Oracle的Cursors概念:

  begin   

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

解析,绑定。。。不会从数据库检索数据

    end loop;  

end;

CURSOR游标名(参数) [返回值类型] IS

      into e_id, hire_date; –将数据逐条存入变量中,并且游标向后移动  

     close cur;

选项:参数和返回类型

       select name,department_name from emplyees s,departments d  

      exitwhen emp_cur%notfound;

1.显式游标:

  (  

 declare

2.REF游标:

         

  whenothersthen

三.Oracle的Cursors详细内容:

www.9778.com 2

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

二.Oracle的Cursors分类:

       where emplyeeid = e_id;  

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

生命周期:

显式游标的使用步骤

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

完成游标处理,用户不能从游标中获取行。还可以重新打开。

    end if;  

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

set serveroutput ondeclarecursor emp_cur ( p_deptid in number)
isselect * from employees where department_id = p_deptid;l_emp
employees%rowtype;begindbms_output.put_line(Getting employees from
department 30);open emp_cur(30);loop fetch emp_cur into l_emp; exit
when emp_cur%notfound; dbms_output.put_line(Employee id ||
l_emp.employee_id || is ); dbms_output.put_line(l_emp.first_name
|| || l_emp.last_name);end loop;close
emp_cur;dbms_output.put_line(Getting employees from department
90);open emp_cur(90);loop fetch emp_cur into l_emp; exit when
emp_cur%notfound; dbms_output.put_line(Employee id ||
l_emp.employee_id || is ); dbms_output.put_line(l_emp.first_name
|| || l_emp.last_name);end loop;close emp_cur;end;/

    if 100 * (2010 – to_char(hire_date, ‘yyyy’)) < 1000 then  

endloop;

游标:用来查询数据库,获取记录集合的指针,可以让开发者一次访问一行结果集,在每条结果集上作操作。

  loop  

 

a.打开游标(OPEN)

    select id, hire_date from employees;  

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

1.静态游标:

www.9778.com 3

  v_emp type_emp;

分为显式游标和隐式游标。

         set salarvalue = salaryvalue + 1000  

       fetch cur into rec;

Select语句

www.9778.com 4

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

b.从游标中获取记录(FETCH INTO)

3.提取游标

    select empno,ename from  emp;  

 

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

  hire_date date; –存放员工入职日期  

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

 

exception

    else  

  loop

1.声明游标

   cur cursor_type;

  open emp_cur; –打开游标  

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

www.9778.com 5

   type cursor_type isrefcursorreturn record_type;

www.9778.com 6

begin

 

end;

  type emplyee_record is record  

    open cur for 

    fetch emp_cur  

     loop

 

    fetch cur into v_emp;

语法

 Commit;

                          100 * (2010 – to_char(hire_date, ‘yyyy’))  

   v_dept dept%rowtype;

语法

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

  end;</span></strong>  

Exception

 

  whenno_data_foundthen

 

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

语法

       exitwhen cur%notfound;

     
dbms_output.put_line(employee_record.name||’在’||emploee_record.department_name);
 

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

  end loop;</span></strong>  

  empno emp.empno%type;

  www.2cto.com  

   endloop;

 

  endif;

显示游标完整示例

     endloop;

 

 declare

 

  a number;

www.9778.com 7
 

    raise e_toomanyrow;–抛出异常

<strong><span style=”font-size:18px;”>declare   

  if total>1 then

 

   empno emp.empno%type;

显式游标是用户手动声明和操作的游标

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

 

 declare

游标的属性

   where empno =47;

2.打开游标

declare

begin    www.2cto.com  

Declare

<strong><span style=”font-size:18px;”>declare  

  );

 

      exit when 游标%notfound;

   close emp_cur;  

  –if sql%notfound then

显示游标经常和循环一起使用,简化的遍历方法如下:

    endloop; 

  cursor emp_cur is  

select*from emp where deptno=i ;

 

  ifnot cur%isopenthen

示例  www.2cto.com  

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

4.关闭游标

  where deptno =10;

www.9778.com 8
 

  when e_null then

为员工添加工资:

 

语法

  ename emp.ename%type,

      name varchar2(50),  

  endloop;

  e_id      number; –存放员工id  

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

  );  

       exitwhencur%notfound;

         set salarvalue = salaryvalue +  

begin

整体示例:

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

[sql]

   emp_cur   cur_type; 强类型游标变量

[sql] 

例如:

    for employee_record in emp_cur loop  

     open cur for

      department_name varchar2(20)  

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

    exit when emp_cur%notfound;  

cursor cur is

www.9778.com 9

   endif;

       where emplyeeid = e_id;  

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

www.9778.com 10

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

www.9778.com 11

begin

Oracle游标(一)显式游标

declare

      update salary  

  select* from emp where deptno =10;

      update salary  

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

②游标%notfound

 

       fetch cur intov_dept;

    select*from  emp;  

如果sal < 2000,长工资1000

end;

①游标%found

  elsif total<=0 then

   where empno =7369;

 end;

  endloop;

  endloop;

 

  from emp

    open cur for 

     null;

  –else

begin

   ifsql%foundthen

       exitwhen cur%notfound;

   deletefrom emp

begin

  cursor cur is

例如:

(2)强类型游标

  endif;

end;

  when no_data_found then

例如:

  dname dept.dname%type,

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

 begin

   emp_record record_type; 记录类型变量

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

(2)显式游标

   else

declare

end loop;

 

When others then

 

例如:

  empno emp.empno%type,

 

  from emp

dbms_output.put_line(item||item2);

  close cur;–关闭游标

例如:

   total integer;

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

   from emp

    fetch cur into v_emp;

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

   e_toomanyrow exception;

    exitwhen cur%notfound;

    open cur;

   rec record_type;

     open cur for

   temp emp%rowtype;

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

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

  select*

   select empno into empno 对于DML之select into

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

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

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

  –捕获并处理异常

end;

      select*from emp;

④sql%isopen

end;

      dbms_output.put_line(‘未找到值’);

变量 某表%rowtype;

①sql%found

declare

 

commit;

 declare

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

cursor cur(i varchar2)is

begin

  else

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

 例如:

   selection varchar2(1):= upper(‘D’);

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

例如:

例如:

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

begin

end;

例如:

(1)隐式游标:

   cur   cur_type;

  from empwww.9778.com, innerjoin dept

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

  close cur;

  select empno into empno

loop

   e_null exception;–自定义的异常

      fetch 游标 into 变量;

     close cur;

   loop

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

   if selection =’E’then

       wherecurrentof cur;

       dbms_output.put_line(rec.empno||’ ‘||rec.ename);

end;

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

  for v_emp in bbb

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


  sal emp.sal%type


open 游标;

 

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

       select*from emp;

 

begin

  v_emp cur%rowtype;

  select empno, ename, dname, sal into v_emp

例如:

close 游标;

  where ename =’SMITH’;

  for v_sal in cur

   open emp_cur for

   else

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

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

1、静态游标:

例如:

 

declare

  select empno into  a from emp where empno=’7369′;

       update emp

end;

       fetch cur into v_emp;

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

 end;

 ②sql%notfound

 begin

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

第二种情况:DML是select into

end;

begin

close cur;                  –注意关闭游标

(1)弱类型游标

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

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

  end if;

游标

变量 游标%type;

open cur;

    loop

       set sal = sal +1000

    raise e_null;

begin

  cursor cur is 

       select*from dept;

    item cur%rowtype;

  Exception when e_toomanyrow then

loop

变量 某表.某列%type;

       select sal from emp

   cur cursor_type;

  loop

 

   cursor cur is

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

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

  endif;

  –end if;

endloop;

    dbms_output.put_line(sqlerrm);

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

  ifsql%rowcount>0then

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

     endloop;

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

   close emp_cur;

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

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

   elsif selection =’D’then

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

      end if;

 

  cursor bbb is

begin

  (

   ifsql%notfoundthen

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

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

begin

end;

    loop

declare

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

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

    dbms_output.put_line(‘查无此人’);

③sql%rowcount 

     loop

   loop

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

变量 游标%rowtype;

  when others then

loop

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

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

  when too_many_rows then

例如:

  endif;

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

begin

declare         — //弱类型游标

  endloop;

   v_emp emp%rowtype;

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

declare

动态游标:

注意:

end;

      dbms_output.put_line(‘已经删除’);

  ifnot cur%isopenthen

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

       fetch cur into temp;

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

       exitwhen cur%notfound;

    endloop; 

  type type_emp isrecord

   –声明一个游标

  –下面是sql%rowcount

  loop

  where empno =7788;

 

  v_emp cur%rowtype;

      if 某条件 then

  on emp.deptno = dept.deptno

   endif;

      where sal <2000forupdate;

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