原创：oracle PL/SQL编程基础 下(十二)

游标： 游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。在oracle数据库中可以使用显示或隐式两种游标。 隐式游标： 在执行一个sql语句时,oracle服务器将自动创建一个隐式游标,这个游标是内存中处理该语句的工作区域，其中存储了执行SQL语句的结果.通过游标的属性可获得sql语句执行的结果以及游标状态信息。 游标的主要属性如下： %found 布尔属性 如果sql语句至少影响一行则为true 否则为false %notfound 布尔属性与%found相反。 %isopen 布尔属性游标是否打开 打开为true 否则为false %rowcount 数字属性返回受sql语句影响的行数 怎么来使用这些属性呢? 则可以通过 “sql属性名 “来查看结果 //%rowcount 用来检查受影响的行 SQL> declare 2 emp_row emp%rowtype; 3 begin 4 select * into emp_row from emp where empno=7369; 5 dbms_output.put_line(sql%rowcount); 6 end; 7 / 1 //返回的结果为1 //%found用来检查是否影响到了行 SQL> begin 2 update emp set sal=2000 where empno=7369; 3 if sql%found then 4 dbms_output.put_line('更新记录成功影响了'||sql%rowcount||'行'); 5 else 6 dbms_output.put_line('未更新记录'); 7 end if; 8 end; 9 / 更新记录成功 影响了1行 // %notfound //%isopen 一以上两个的使用方式参考 %found进行理解。 另外一种隐式游标cursor for loop可用于处理sql语句的结果集 SQL> begin 2 3 for rec in (select * from emp) loop 4 dbms_output.put_line(rec.empno||'/'||rec.ename||'/'||rec.job||'/'||rec.mgr||'/'||rec.hiredate||'/'||rec.sal||'/'||rec.comm||'/'||rec.deptno); 5 end loop; 6 end; 7 / 显示游标 是在PL/SQL程序中使用包含select语句来声明的游标。如果需要处理从数据库中检索的一组记录,则可以使用显示游标.使用显示游标处理数据需要四个步骤：声明游标,打开游标，检索数据，关闭游标。 1、 声明游标 声明游标就是通过定义游标的名称,游标的特性来声明游标,以及打开游标后就可调用查询语句,声明的语法如下： Cursor cursor_name[parameter[,parameter]….] [return return_type] is select_statement; Parameter作为游标的输入参数,它可以让用户在打开游标式,向游标传递值；语法如下： Parameter_name [in] datatype[{:=|default} expression]/ 举例： declare cursor emp_cursor (pno in number(4) default 7369) is select * from emp where empno=pno; 2、 打开游标 就是指执行声明游标时指定的查询语句。打开的方式只需使用open打开语法： Open cursor_name(参数); 如果没有指定参数就采用默认值执行select语句 3、 检索数据 检索数据就是从检索到的结果集中获取数据保存到变量中,以便变量进行处理。 使用fetch语句找出结果集中的单行,并从中提取单个值传递给主变量。 语法如下： Fetch cursor_name into [variable_list[record_variable]] 变量用于存储检索的数据 4、 关闭游标 close 游标名称 综上所述综合案例如下： SQL> declare 2 cursor emp_cursor (pno in number default 7369) //声明游标 3 is select * from emp where empno=pno; 4 5 emp_row emp%rowtype; //声明变量 6 begin 7 open emp_cursor(7934); //打开游标 8 fetch emp_cursor into emp_row; //检索数据 结果为一行 9 dbms_output.put_line(emp_row.ename); //输出检索结果 10 11 close emp_cursor; //关闭游标 12 end; 13 / 游标for循环 依次读取结果集中的行,当for循环开始时,游标会自动打开(不需要使用open方法开启),每循环读取一次,系统自动读取当前数据(不需要使用fetch),当退出for循环时,游标也会自动关闭(不需要使用close方法)。 SQL> declare 2 cursor emp_cursor (pno in number default 7369) //声明游标 3 is select * from emp where empno=pno; 4 5 begin 6 for emp_row in emp_cursor(7934) loop //for循环开始时 自动打开游标 并且自动获取数据 自动关闭 7 dbms_output.put_line(emp_row.ename); 8 end loop; 9 end; 10 / 游标变量 游标变量也可以处理多行查询结果集。 游标变量的定义包括两个步骤： 1、 定义cursor类型的指针 语法： Type ref_cursor_name is ref cursor[return return_type] 举例： Type var_cursor_name is ref cursor; 2、 定义ref cursor类型的变量 v_rc var_cursor_name; 综合写法如下： Type var_cursor_name is ref cursor; v_rc var_cursor_name; 上面的综合声明的游标变量 称为弱的ref cursor类型,因为它没有指明游标返回的结果,因此它可以指向任何一个具有多列的select查询结果. 相对于上面还有一种称为:强ref cursor类型. 声明方式如下： Type varcursorName is ref cursor return emp%rowtype; //指明了返回的结果 Vcn varcursorName; //声明一个强的ref cursor类型的变量 使用游标变量与游标使用方式一样,也需要声明,打开,检索，关闭游标变量。 综合案例如下： SQL> declare 2 3 type emp_cname is ref cursor return emp%rowtype; //声明游标变量第一步 4 5 ecname emp_cname; //声明游标变量第二步 6 7 emp_row emp%rowtype; //声明用于保存检索数据的变量 8 9 begin 10 dbms_output.put_line('开始'); 11 open ecname for select * from emp where empno=7934; //打开游标变量 12 loop 13 fetch ecname into emp_row; //查询结果赋值给保存的变量 14 exit when ecname%notfound; //退出条件 15 dbms_output.put_line(emp_row.ename); //输出结果 16 end loop; //退出循环 17 close ecname; //关闭游标变量 18 dbms_output.put_line('结束'); 19 end; 20 / 开始 MILLER 结束 //复杂的案例 SQL> declare 2 3 type emp_cname is ref cursor return emp%rowtype; 4 5 ecname emp_cname; 6 7 emp_row emp%rowtype; 8 9 begin 10 dbms_output.put_line('开始'); 11 open ecname for select * from emp; 12 loop 13 fetch ecname into emp_row; 14 exit when ecname%notfound; 15 dbms_output.put_line(emp_row.ename); 16 end loop; 17 close ecname; 18 dbms_output.put_line('结束'); 19 end; 20 / 开始 SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 结束 使用游标更新数据库 1、 定位游标之后进行删除|修改指定的数据行 更新的时候需要使用for update选项 语法如下： Cursor cursor_name is select_statement; For update[of column[,column],[nowait]] Of用来指定要锁定的列,如果忽略of那么表中选择的数据行都将锁定。如果被锁定行已经被锁定了,那么必须等待释放才能锁定对于这种情况我们可以使用nowait语句。 当使用for update语句声明游标后,可以再delete|update语句中使用where current of子句，修改|删除游标结果集中当前行对应的表中的数据。 语法如下： Where { current of cursor_name|search_condition} 举例说明： //修改操作 SQL> declare 2 3 cursor ecname is select * from emp where empno=7934 4 for update of sal nowait; 5 6 esal number(7,2); 7 8 9 begin 10 dbms_output.put_line('开始'); 11 12 for r in ecname loop 13 esal:=r.sal*10; 14 update emp set sal=esal where current of ecname; 15 end loop; 16 17 dbms_output.put_line('结束'); 18 end; 19 / 开始 结束 以上内容归redarmy_chen所有,如需转载请附带出处,如有疑问请发送至redarmy_chen@qq.com