游标

学一样技术，应该知道一下三个方面的知识，就是它是什么？有什么用途？怎么用？其中怎么用是非常重要的。

一、 游标是什么？

游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)游标可以被看作指向结果集中一行指针。结果集又是什么呢？比如select查询语句返回的就是一个集合。

二、 游标有几种类型？

分为显示或隐式游标，隐式游标是oracle中自带的游标，显示游标是需要自己定义的游标。

1、隐式游标：在执行sql语句时，自动创建的一个隐式游标，这个游标是内存中处理该语句的工作区间，其中存储了执行sql语句后的结果，通过游标可获得sql语句执行的结果已经游标状态信息

游标的主要属性如下：

%FOUND 布尔属性：如果sql语句至少影响一行 则为true 否则为false

%NOTFOUND 布尔属性：与%FOUND相反

%ISOPEN ：布尔属性，游标打开返回true，否则返回false

%ROWCOUNT：数组属性 返回受sql语句（insert 、update 、delete）影响的行数 

使用游标前，应先设置输出服务为打开的状态

SQL> set serveroutput on;

使用隐式游标

SQL> begin

  2  update emp set sal=2000 where empno=7369;  //修改的sql语句

  3  if sql%found then//隐式的游标使用是sql%......

  4  dbms_output.put_line('更新了'||sql%rowcount||'行，成功');  

  5  else

  6  dbms_output.put_line('更新失败');

  7  end if;

  8  end;

  9  /

 

更新了1行，成功

 

PL/SQL procedure successfully completed

另一种隐式游标

自动往下走,循环输出检索出来的结果

SQL> begin

  2  for res in (select ename from emp)//res是不用定义的

  3  loop

  4  dbms_output.put_line(res.ename);

  5  end loop;

  6  end;

  7  /

 

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

guo

 

PL/SQL procedure successfully completed

二、显示游标

声明游标

Cursor cursor_name[parameter[,parameter]…]

[return return_type] is select_statement;

Parameter作为游标的输入参数，可以让用户打开游标，向游标中传递值

根据员工的编号查询员工的信息

declare

   cursor emp_cursor (pno in number(4) default 7369)

is select * from emp where empno=pno;

   emp_row emp%rowtype;

begin

open emp_cursor(7934);

fetch emp_cursor into emp_row;

 dbms_output.put_line(emp_row.ename); 

 close emp_cursor;

end;

 /

打开游标：执行声明游标是指定的查询语句。用open打开

Open cursor_name(参数)

如果没有指定参数，就采用默认值执行sql语句

检索数据：从检索到的结果集中获取数据，存到变量中，以便变量的处理

使用fetch语句找出结果集中的单行，并从中提取值存到主变量中

语法：（待会。。。。。）

Declare

Cursor emp_cursor (pno in number(4) default 7369) is select * from emp where empno=pon;

Emp_row emp%rowtype;

Begin

Open emp_cursor;

Fetch emp_cursor into emp_row;

Dbms_output.put_line(emp_row.ename);

Close emp_cursor;

End;

/

游标的for循环

依次读取结果集中的行，当for循环开始时，游标会自动打开（不需要用open方法开启），每循环读取一次，系统自动调用fetch，结束时自动关闭（调用close）

游标变量

游标变量可以出来多行查询结果集

游标变量的定义包括两个步骤

1. 定义cursor类型的指针

语法：

Type ref_cursor_name is ref cursor[return return_type]

2. 定义refcursor类型的变量

V_rc var_cursor_name;

上面称为弱的ref cursor类型，因为没有声明游标的返回结果，可以指向任何的select查询才结果集

强的ref cursor类型

语法：

Type varcursorName is cursor return emp%rowtype

……

使用游标变量与游标使用方式一样，也需要声明、打开、检索

例如：

Declare

Cursor emp_cursor(pno in number default 7369) is select * from emp where empno=pno;

Type emp_cname id ref cursor return emp%rowtype;

Ecname emp_cname;

Begin

Open ecname for select * from emp where empno=7934;

Loop

 Fetch ecnam into emp_row;

Exit when ecname%notfound;

Dbms_output.put_line(emp_row.ename);

End loop;

End;

/

Shift+esc是强制退出PL/SQL的命令窗口

使用游标更新数据库

1. 定位游标，指定修改、删除的数据行 更新的时候用到for update选项

语法如下：

Cursor cursor_name is select_statement;

For update[of column[,column],[nowait]]

Of来指定用来指定锁定的列，如果忽略of将锁定所有的列，如果指定的列已经被锁了，必须解锁后才能锁定，这时候用到了nowait

。。。。。。。。

例如：

Declare

Cursor ecname is select * from emp where empno=7934 for update of sal nowait;

Esal number(7,2);

Begin

Dbms_output.put_line(‘开始’);

For r in ecname 

Loop

Esal:=r.sal+10;

Update emp set sal=esale where empno=7934;

End loop;

Close ecname;

Dbms_output.put_line(‘结束’);

End;