ORACLE游标实例讲解 (2012-12-1510:16:54)转载▼

ORACLE游标实例讲解 (2012-12-1510:16:54)转载▼

标签： ref cursor where current of it    分类： Oracle笔记

一.使用游标

  1.当在PL/SQL块中执行查询语句SELECT和数据操纵语句DML时,ORACLE会为其分配上下文区(CONTEXTAREA),游标指上下文区指针

  对于数据操纵语句和单行SELECTINTO语句来说,ORACLE会为他们分配隐含游标.

  使用显示游标处理多行数据,也可使用SELECT..BULKCOLLECT INTO 语句处理多行数据.

  1.显示游标

    定义游标

   cursor cursor_name is select_statement;

  2.打开游标:执行对应的SELECT语句并将SELECT语句的结果暂时存放到结果集中.

   open cursor_name;

  3.提取数据

    打开游标后,SELECT语句的结果被临时存放到游标结果集中,使用FETCH语句只能提取一行数据

    通过使用FETCH..BULKCOLLECT INTO语句每次可以提取多行数据

   fetch cursor_name into variable1,varibale2,...;

   fetch cursor_name bulk collect into collect1,collect2,...[limit rows];

  4.关闭游标

   close cursor_name;

  9.2显示游标属性

    用于返回显示游标的执行信息,包括%isopen,%found,%notfound,%rowcount

 1.%isopen:确定游标是否打开 if cl%isopen then ... else open c1; end if;

 2.%found:检查是否从结果集中提取到了数据

   loop

     fetch c1 into var1,var2;

     if c2%found then ... else exit;

   end loop;

 3.%notfound

   loop

      fetch c1 into var1,var2;

      exit when c2%notfound;

      ...

   end loop;

 4.%rowcount:返回当前行为止已经提取到的实际行数

   loop

     fetch c1 into my_ename,my_deptno;

     if c1%rowcount>10 then

     ...

     end if;

     ...

   end loop;

 

  9.3显示游标使用示例

  1.在显示游标中使用fetch..into语句:只能处理一行数据,除非用循环语句

 declare

   cursor emp_cursor is select ename,sal from emp where deptno=10;

   v_ename emp.ename%type;

   v_sal emp.sal%type;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into v_ename,v_sal;

     exit when emp_cursor%notfound;

     dbms_output.put_line(v_ename||': '||v_sal);

  end loop;

  close emp_cursor;

 end;

  2.在显示游标中,使用FETCH..BALKCOLLECT INTO语句提取所有数据

 declare

   cursor emp_cursor is select ename from emp where deptno=10;

   type ename_table_type is table of varchar2(10);

   ename_table ename_table_type;

 begin

   open emp_cursor;

   fetch emp_cursor bulk collect into ename_table;

   for i in 1..ename_table.count loop

       dbms_output.put_line(ename_table(i));

   end loop;

   close emp_cursor;

 end;

   3.在显示游标中使用FETCH..BULKCOLLECT INTO ..LIMIT语句提取部分数据

 declare

   type name_array_type is varray(5) of varchar2(10);

   name_array name_array_type;

   cursor emp_cursor is select ename from emp;

   rows int:=5;

   v_count int:=0;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor bulk collect into name_array limit rows;

     dbms_output.pur('雇员名');

     for i in 1..(emp_currsor%rowcount-v_count) loop

       dbms_output.put(name_array(i)||' ');

     end loop;

     dbms_output.new_line;

    v_count:=emp_cursor%rowcount;

    exit when emp_cursor%notfound;

   end loop;

   close emp_cursor;

 end;

 

  4.使用游标属性

 declare

   cursor emp_cursor is select ename from emp where deptno=10;

   type ename_table_type is table of varchar2(10);  

   ename_table ename_table_type;

 begin

   if not emp_cursor%isopen then

      open emp_cursor;

   end if;

   fetch emp_cursor bulk collect into ename_table;

   dbms_output.put_line('提取的总计行数:'||emp_cursor%rowcount);

   close emp_cursor;

 end;

 

  5.基于游标定义记录变量

 declare

   cursor emp_cursor is select ename,sal from emp;

   emp_record emp_cursor%rowtype;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into emp_record;

     exit when emp_cursor%notfound;

     dbms_output.put_line('雇员名:'||emp_record.ename||',雇员工资:'||emp_record.sal);

   end loop;

 end;

  9.4参数游标

  定义参数游标时,游标参数只能指定数据类型,而不能指定长度.

 cursor cursor_name(parameter_name datatype) is select_statment;

 declare

   cursor emp_cursor(no number) is select ename from emp where deptno=no;

   v_ename emp.ename%type;

 begin

   open emp_cursor(10);

   loop

     fetch emp_cursor into v_ename;

     exit when emp_cursor%notfound;

     dbms_output.put_line(v_ename);

   end loop;

   close emp_cursor;

 end;

 

  9.5使用游标更新或删除数据

  要通过游标更新或删除数据,在定义游标时必须要带有FORUPDATE子句

 cursor cursor_name(parameter_name datetype) is select_statement forupdate [of column_reference] [nowait];

  forupdate子句用于在游标结果集数据上家行共享锁,防止其他用户在相应行执行DML操作

  of子句确定哪些表要加锁,没有OF子句,则在所引用的全部表上加锁

 nowait子句用于指定不等待锁

  必须在UPDATE后DELETE语句中引用WHERECURRENT OF子句

 update table_name set column=.. where current of cursor_name;

 delete table_name where current of cursor_name;

  1.使用游标更新数据

 declare

   cursor emp_cursor is select ename,sal from emp for update;

   v_ename emp.ename%type;

   v_sal emp.sal%tyep;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into v_ename,v_oldsal;

     exit when emp_cursor%notfound;

     if v_oldsal<2000 then

        update emp set sal=sal+100 where current of emp_cursor;

     end if;

   end loop;

   close emp_cursor;

 end;

  2.使用游标删除数据

 declare

   cursor emp_cursor is select ename,sal,deptno from emp for update;

   v_ename emp.ename%type;

   v_oldsal emp.sal%type;

   v_deptno emp.deptno%type;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into v_ename,v_oldsal,v_deptno;

     exit when emp_cursor%notfound;

     if v_deptno=30 then

        delete from emp where current of emp_cursor;

     end if;

   end loop;

   close emp_cursor;

 end;

 

  3.使用OF子句在特定表上加行共享锁

 declare

   cursor emp_cursor is select ename,sal,dname,emp.deptno from emp,deptwhere emp.deptno=dept.deptno

   for update of emp.deptno;

   emp_record emp_cursor%type;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into emp_record;

     exit when emp_cursor%notfound;

     if emp_record.deptno=30 then

        update emp set sal=sal+100 where current of emp_cursor;

     end if;

     dbms_output.put_line('雇员名:'||emp_record.ename||',工资:'||emp_record.sal||',部门名:'||emp_record.dname);

   end loop;

   close emp_cursor;

 end;

 

  4.使用nowait子句

  通过在FOR UPDATE子句中指定NOWAIT语句,可以避免等待锁.若已经被作用行加锁,则提示错误信息

 declare

   cursor emp_cursor is select ename,sal from emp for update nowait;

   v_ename emp.ename%type;

   v_oldsal emp.sal%type;

 begin

   open emp_cursor;

   loop

     fetch emp_cursor into v_ename,v_sal;

     exit when emp_cursor%notfound;

     if v_oldsal<2000 then

       update emp set sal=sal+100 where current of emp_cursor;

     end if;

   end loop;

   close emp_cursor;

 end;

  9.6游标FOR循环

  使用FOR循环时,ORACLE会隐含的打开游标,提取游标数据并关闭游标

  forrecord_name in cursor_name loop

     statement1;

     statement2;

     ...

  endloop;

  每循环一次提取一次数据，在提取了所有数据后,自动退出循环并隐含的关闭游标

  1.使用游标FOR循环

 declare

   cursor emp_cursor is select ename,sal from emp;

 begin

   for emp_record in emp_cursor loop

     dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员:'||emp_record.ename);

   end loop;

 end;

 

  2.在游标FOR循环中直接使用子查询

 begin

   for emp_record in (select ename,sal from emp) loop

     dbms_output.put_line(emp_record.ename);

   end loop;

 end;

  9.7使用游标变量

 PL/SQL的游标变量中存放着指向内存地址的指针.

  1.游标变量使用步骤

    包括定义游标变量,打开游标,提取游标数据,关闭游标等四个阶段

  1.1定义ref cursor类型和游标变量

 type ref_type_name is ref cursor [return return_type];

 cursor_varibale ref_type_name;

  当指定RETURN子句时,其数据类型必须是记录类型,不能在包内定义游标变量

  1.2打开游标

  opencursor_variable for select_statement;

  1.3提取游标数据

 fetch cursor_varibale into variable1,variable2,...;

 fetch cursor_varibale bulk collect into collect1,collect2,...[limitrows]

  1.4关闭游标变量

 close cursor_varibale;

 

  2.游标变量使用示例

  2.1在定义FEF CURSOR类型时不指定RETURN子句

  在打开游标时可以指定任何的SELECT语句

 declare

   type emp_cursor_type is ref cursor;

   emp_cursor emp_cursor_type;

   emp_record emp%rowtype;

 begin

   open emp_cursor for select * from emp where deptno=10;

   loop

     fetch emp_cursor into emp_record;

     exit when emp_cursor%notfound;

     dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员:'||emp_record.ename);

   end loop;

   close emp_cursor;

 end;

 

  2.2在定义REF CURSOR类型时指定RETURN子句

  在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型相匹配.

 declare

   type emp_record_type is record(name varchar2(10),salary number(6,2));

   type emp_cursor_type is ref cursor return emp_record_type;

   emp_cursor emp_cursor_type;

   emp_record emp_record_type;

 begin

   open emp_cursor for select ename,sal from emp where deptno=20;

   loop

     fetch emp_cursor into emp_record;

     exit when emp_cursor%notfound;

     dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员:'||emp_record.ename);

   end loop;

   close emp_cursor;

 end;

  9.7使用CURSOR表达式

 CURSOR表达式用于返回嵌套游标

  结果集不仅可以包含普通数据,而且允许包含嵌套游标的数据

 cursor(subquery)

 declare

   type refcursor is ref cursor;

   cursor dept_cursor(no number) is select a.dname,cursor(select ename,salfrom emp where deptno=a.deptno)

   from dept a where a.deptno=no;

   empcur refcursor;

   v_dname dept.dname%type;

   v_ename emp.ename%type;

   v_sal emp.sal%type;

 begin

   open dept_cursor(&no);

   loop

     fetch dept_cursor into v_danme,empcur;

     exit when dept_cursor%notfound;

     dbms_output.put_line('部门名:'||v_dname);

     loop

       fetch empcur into v_ename,v_sal;

       exit when empcur%notfound;

       dbms_output.put_line('雇员名:'||v_ename||',工资:'||v_sal);

     end loop;

   end loop;

   close dept_cursor;

 end;