PL/SQL编程_游标

游标是一种私有的工作区，用于保存SQL语句的执行结果。
在执行一条SQL语句时，数据库服务器会打开一个工作区，将SQL语句的执行结果保存在这里。
在Oracle数据库中有两种形式的游标：隐式游标和显式游标。
隐式游标是由数据库服务器定义的，显式游标是用户根据需要自己定义的。

隐式游标
隐式游标是数据库服务器定义的一种游标。
在执行一条DML语句或SELECT语句时，数据库服务器将自动打开一个隐式游标，存放该语句的执行结果。
在一个PL/SQL块中可能有多条DML或SELECT语句，隐式游标始终存放最近一条语句的执行结果。
隐式游标有几个很有用的属性，可以帮助我们了解游标的信息。
下面列出了隐式游标的几个常用属性。
SQL%ISOPEN 判断当前游标是否打开。如果打开，该属性值为TRUE，否则为FALSE
SQL%ROWCOUNT 对于DML语句，该属性值为受影响的数据行数。对于SELECT语句，如果不发生异常，其值为1
SQL%FOUND 对于DML语句，该属性表明表中是否有数据受到影响。如果DML语句没有影响任何数据，该属性值为FALSE，否则为TRUE。对于SELECT语句，如果不发生异常，其值为TRUE
SQL%NOTFOUND 与属性SQL%FOUND正好相反
其中SQL%FOUND属性值为布尔值，表示是否找到了满足条件的数据，如果找到了相应的数据，其值为TRUE ，否则为FALSE 。
SQL%ROWCOUNT属性表示某个操作影响的数据行数，对于UPDATE语句，表示修改的行数；对于INSERT语句，表示插入的行数；对于DELETE语句，表示被删除的行数。
例如，可以在每一条DML语句之后输出该操作影响的行数。
BEGIN
UPDATE emp set sal=sal+100;
if SQL%FOUND then
dbms_output.put_line('被修改的行数：' || SQL%ROWCOUNT);
END if;
DELETE FROM emp;
if SQL%FOUND then
dbms_output.put_line ('被删除的行数：' || SQL%ROWCOUNT);
END if;
INSERT INTO dept VALUES(70 ,' aaa ',' aaaa ');
INSERT INTO dept VALUES(80 ,'bbb ',' bbbb' ) ;
if SQL%FOUND then
dbms_output.put_line ('最近插入的行数：' || SQL%ROWCOUNT);
END if;
ROLLBACK;
END;
从这个例子可以看出，当有多条DML语句时，隐式游标只记录最近一条DML语句的执行情况。
如果是SELECT语句，情况则比较特殊。
因为当SELECT语句没有检索到满足条件的数据时，将引发NO_DATA_FOUND 异常，而当检索到多行满足条件的数据时，将引发TOO_MANY ROWS异常。
所以只有当SELECT语句正好检索到一行数据时，才可以使用隐式游标的这些属性。
如果要处理这两种特殊情况，就需要借助于显式游标。

显式游标
对于PL/SQL块中的SELECT语句，可以用显式讲标来处理。
显式游标是一个打开的工作区，
在这个工作区里保存SELECT语句的执行结果。
用显式游标可以处理返回0行、一行、多行等各种情况，并且在返回0行或多行数据这两种特殊情况下，不会引发NO_DATA_FOUND 和TOO_MANY ROWS异常。
使用游标处理SELECT语句的步骤如下：
1 ）声明游标
2 ）打开游标
3 ）逐行取出游标中的行，并分别进行处理
4 ）关闭游标
游标的工作过程如下所示。
DECLARE OPEN FETCH CLOSE
声明游标 打开游标 从游标中取数据 关闭游标
注：在从游标中取数据时，要做个判断，判断是和否取完了，如果是，则CLOSE游标；如果否，则继续FETCH游标。
游标的声明在PL/SQL块的声明部分进行。
声明的语法格式为：
DECLARE
CURSOR 游标名 IS
SELECT 语句;
例如，下面的语句声明了一个名为cur_1 的游标：
DECLARE
CURSOR cur_1 IS
SELECT * FROM emp;
由于SELECT语句的执行结果将存放在工作区中，因此不需要使用INTO子句将返回的数据赋给变量。
为了处理游标中的数据，首先要打开游标。
打开游标意味着将指定的SELECT语句交给数据库服务器执行，并将返回结果存放在工作区中。
打开游标的命令是OPEN ，其语法格式为：
OPEN 游标名
例如，打开游标cur_1的语句为：
OPEN cur_1;
游标打开后，就可以取出游标中的数据，并对其进行处理了。
从游标中取出数据的命令是FETCH 。
FETCH命令一次取出一行数据，并将其赋给指定的变量。
FETCH命令的格式为：
FETCH 游标 INTO 变量1，变量2, ...
游标中的数据只有在取出后才能进行处理。
为此，需要在FETCH语句中用INTO子句指定多个变量，分别存放一行数据中各个列的值。
FETCH命令将一行数据中各列的值依次赋给指定的变量。
需要注意的是，变量的类型、数目要与游标中一行的各列相对应。
在用FETCH命令取出游标中的数据时，可以设想有一个指针，指向游标中的一行数据。
当游标刚刚打开时，指针指向第一行，以后每取出一行，指针自动指向下一行，直到将所有的数据都取出为止。
游标在使用完后，应该及时关闭，以释放它所占用的内存空间。
关闭游标的命令是CLOSE,其语法格式为：
CLOSE 游标名;
当游标关闭后，不能再从游标中获取数据。
如果需要，可以再次打开游标。
考虑下面的游标。
从emp表中检索员工7902 的姓名、工资、工作时间。
由于SELECT命令仅返回一行数据，所以处理的过程很简单。

DECLARE
name emp.ename%type;
salary emp.sal%type;
hire_date emp.hiredate%type;
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE empno=7902;
BEGIN
open cur_1;
fetch cur_1 INTO name,salary,hire_date;
dbms_output.put_line ('姓名：' || name || '工资：' || salary || '工作时间：' || hire_date);
close cur_1;
END;

为了使程序更加简洁，在PL/SQL块中可以使用记录变量。

首先声明一个记录变量，它的结构与游标的结构相同。
然后可以使用FETCH语句将游标中的一行数据取出后存放在记录变量中，接下来就可以对这个记录变量进行处理了。
例如，对上面的PL/SQL块进行修改，在PL/SQL块中使用记录变量。
修改后的代码如下：

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE empno=7902;
e cur_1%rowtype;
BEGIN
open cur_1;
fetch cur_1 INTO e.ename, e.sal, e.hiredate;
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
close cur_1;
END;

在上面的块中通过一个简单的游标，处理一行数据。

在使用游标时，必须考虑各种特殊情况。
如果SELECT语句没有返回结果，游标是空的，这时FETCH语句将取不到数据。

如果SELECT语句返回多行数据，这时用一条FETCH语句仅能取到游标中的一行数据。

利用游标的属性可以了解游标当前的状态，防止各种意外情况的发生。
下面出了显式游标的若干属性。

CURSOR%ISOPEN　　判断当前游标是否打开。如果打开，该属性值为TRUE,否则为FALSE

CURSOR%ROWCOUNT　　表示到目前为止，用FETCH语句取到的行数

CURSOR%FOUND　　表示最近一次FETCH操作是否从游标中取到一行数据，如果已经取到，其值为TRUE，否则为FALSE

CURSOR%NOTFOUND　　与属性CURSOR%FOUND相反

备注：CURSOR为PL/SQL块中游标的名称。

在下面的PL/SQL块中使用了显式游标的属性，使得PL/SQL块能够处理各种例外情况，比如没有取到合适的数据，或者取到多行数据。

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN then
OPEN cur_1; --如果游标没有打开，则打开它
END IF;
FETCH cur_1 INTO e; --取第一行数据
WHILE cur_1%FOUND LOOP
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
FETCH cur_1 INTO e;
END LOOP;
dbms_output.put_line('员工总数：' || cur_1%ROWCOUNT); --获取总行数
CLOSE cur_1; --关闭游标
END;

 

这个PL/SQL块的功能是查询部门20的所有员工的姓名、工资和工作时间。

如果这个部门不存在，则不显示任何员工的信息，仅显示“员工总数：。”的信息。
如果该部门有一个或多个员工，则显示他们的信息，井打印该部门员工总数。
在块中首先用游标的%ISOPEN属性判断游标是否打开，如果没有打开，则打开它。

然后用FETCH语句取出第一行，并用游标的%FOUND属性判断是否取到数据。
如果游标是空的，则这个属性的值为FALSE ，这样就不用继续取数据了。
如果取到了数据，则处理这行数据，并试图取下一行。

这样通过循环的方式，每取到一行数据，就试图再取下一行，然后判断是否取到数据，直到将所有数据取出。
在游标的四个属性中， %lSOPEN 属性用于测试游标的状态。

其他三个属性用来测试FETCH命令的执行结果%FOUND和%NOTFOUND属性用来测试最近的一次FETCH是否取到数据， %ROWCOUNT属性表示自游标打开以来，到目前为止，用FETCH命令获取的行数。
下面的例子用另外一种形式的循环处理游标。

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN then
OPEN cur_1; --如果游标没有打开，则打开它
END IF;
LOOP
FETCH cur_1 INTO e; --取第一行数据
EXIT WHEN cur_1%NOTFOUND;  --如果最近一次FETCH没有取得数据，则退出循环，否则，对取得数据进行处理
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
END LOOP;
dbms_output.put_line('员工总数：' || cur_1%ROWCOUNT); --获取总行数
CLOSE cur_1; --关闭游标
END;

游标中的数据一般是通过循环方式来处理的。

在上面两个例子中定义了两个游标，并用常规的循环方法进行处理。

PL/SQL提供了一种更简便的方怯处理游标，这种方法利用FOR循环，远行处理游标中的行。
FOR语句的格式为：

FOR 变量 IN 游标名 LOOP

　　处理变量

END LOOP;

这里把游标中的数据当做一个集合，一次从中取出一行，赋给一个记录类型变量，然后就可以处理这个变量了。
这个变量在使用之前不需要定义，在循环开始时自动产生，在FOR语句中可以直接使用，这个变量的结构与游标的结构完全相同。
利用FOR循环从游标中取数据时，不需要用OPEN命令打开游标。

当循环开始执行时，游标被自动打开。
游标在使用完后，也不需要执行CLOSE命令关闭。
FOR循环的循环体每执行一次，就会自动取出游标中的一行数据，赋给记录类型变量，然后指针自动往下移动，所以不需要通过FETCH命令获取游标中的数据。
例如，下面的PL/SQL块利用FOR循环处理游标中的数据。

DECLARE
e_count number := 0;
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
BEGIN
FOR e IN cur_1 LOOP
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
e_count := e_count + 1;
END LOOP;
dbms_output.put_line('员工总数：' || e_count); --获取总行数
END;

由于在循环执行结束时，游标已经关闭，因此无法再用游标的%rowcount属性统计获取的数据行数。
在块中声明了一个变量e_count ，每进行一次循环，变量e_count加1 ，这样就可以统计出获取的总行数。
从上面的例子可以看出，利用FOR循环可以大大简化游标的处理过程。
需要注意的是，为了重点说明游标的用法，在与游标有关的例子中，对于从游标中取出的数据，仅仅显示在屏幕上。
可以根据需要，对这些数据进行其他的处理，比如写入其他表中。

带参数的游标

在前面介绍游标的例子中， SELECT语句都没有WHERE子句，或者用WHERE子句指定了一个固定的条件，这样每次都查询同样的数据。
在更多的情况下，可能要根据实际情况查询不同的数据。
为了通过游标对数据进行更加灵活的处理，可以为游标定义参数，这些参数可以用在WHERE子句中。
在打开游标时，指定实际的参数值，这样游标在每次打开时，可以根据不同的实际参数值，返回所需的不同数据。
定义带参数的游标的语法格式为：

DECLARE

　　CURSOR 游标名（参数1， 参数2 ...）

　　IS 

　　SELECT 语句;

其中参数的定义方法与子程序中的参数定义完全相同，可以指定默认值，指定参数传递模式。
默认的参数传递模式为IN ，如果要使用OUT或者“IN OUT”模式，就需要明确指定。

由于游标一般不需要通过参数向调用者传递数据，所以OUT模式在游标中没有什么实际用处。
在用OPEN命令打开游标时，要向游标提供实际参数，游标根据提供的参数值，查询符合条件的数据。
打开游标的语法格式为：

OPEN 游标名（实际参数1，实际参数2 ...）

例如，考虑在下面定义的游标。

DECLARE
CURSOR cur_1(d_no IN emp.deptno%type, min_sal IN emp.sal%type := 1000)
IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=d_no AND sal >= min_sal;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN THEN --如果游标没有打开，则打开它
OPEN cur_1(20, 2000);
END IF;
FETCH cur_1 INTO e;
WHILE cur_1%FOUND LOOP
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
FETCH cur_1 INTO e;
END LOOP;
dbms_output.put_line('员工总数：' || cur_1%ROWCOUNT); --获取总行数
CLOSE cur_1;
END;

在这个例子中，用传统的循环方法处理游标。

首先定义了一个带参数的游标，参数d_no表示部门编号， min_sal表示最低工资，两个参数的传递模式都是IN。
游标的功能是查询属于指定部门并且工资不低于指定值的所有员工。
在打开游标时，指定了两个实际参数20和2000 ，这样，检索出来的数据就是属于部门20,并且工资不低于2000的所有员工。
如果再次以“open cur_6 ( 10,3000 ）”的形式打开游标，那么检索到的数据就是属于部门10 ，并且工资不低于3000的员工。
由此可见，带参数的游标在查询数据时更加灵活。
如果要用FOR 循环处理游标中的数据，可以按照同样的方法定义游标。

由于没有使用OPEN命令打开游标，所以实际参数在FOR语句中指定。
这时FOR语句的格式为：

FOR 变量 IN 游标名（实际参数1， 实际参数2 ...） LOOP
...

END LOOP;

这样，在循环开始执行时，游标自动打开，并根据指定的实际参数查询数据。

例如，用FOR循环处理带参数的游标，对上面的PL/SQL块进行修改，代码如下：

DECLARE
e_count number := 0;
CURSOR cur_1(d_no IN emp.deptno%type, min_sal IN emp.sal%type := 1000)
IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=d_no AND sal >= min_sal;
BEGIN
FOR e IN cur_1(20 ,2000) LOOP
dbms_output.put_line ('姓名：' || e.ename || '工资：' || e.sal|| '工作时间：' || e.hiredate);
e_count := e_count + 1;
END LOOP;
dbms_output.put_line('员工总数：' || e_count); --获取总行数
END;

如何通过游标修改表中的数据

游标的主要作用是查询数据，并对数据逐行进行处理。

对于游标中的数据还可以根据需要进行修改，例如修改某个列的值，或者删除某一行。
在定义游标时，需要把游标定义为可以修改的形式，定义格式如下：

DECLARE

　　CURSOR 游标名（参数1，参数2 ...）

　　IS

　　SELECT语句

　　FOR UPDATE;

游标、可以带参数，或者不带参数。

SELECT语句中的FOR UPDATE子句的作用是加锁，它的功能是把游标中的数据锁定，这样可以防止其他用户同时修改这些数据。
由于在并发环境中许多用户可能同时访问数据库，如果把游标打开后不希望其他用户同时修改这些数据，就需要对游标加锁，否则就会导致数据的不一致。
SELECT语句中的FOR UPDATE子句就是为了在游标打开后对它进行加锁，待游标关闭时再释放锁，用这种方式可以保证用户对数据的正确访问。
对游标中的数据是逐行处理的，每次处理指针当前指向的行。

在修改游标中的数据时，也是对当前行进行修改，然后将修改后的结果写人数据库。
PL/SQL提供了一种修改游标当前行的机制，如果在UPDATE 、DELETE语句中使用WHERE CURRENT OF子句，可以保证只对游标当前行进行修改。
WHERE CURRENT OF子句将修改操作限定在游标的当前行。
例如，某部门要为员工增加工资，增加的幅度为10% ，但只限于工资最低的5名员工。

如果本部门员工总数不足5人，则为所有员工都增加工资。
考虑用下面的PL/SQL块实现这个操作。

DECLARE
CURSOR cur_1(d_no emp.deptno%type)
IS
SELECT ename,sal,hiredate FROM emp
WHERE deptno=d_no
ORDER BY sal --保证员工按照工资从低到高的顺序排列
FOR UPDATE;
e_count integer:=0;
e cur_1%rowtype;
BEGIN
open cur_1 ( 30); --打开游标，将部门编号30 作为参数
fetch cur_1 INTO e;
while cur_1%FOUND loop
exit when e_count>=5;
UPDATE emp set sal=sal*1.1 WHERE CURRENT OF cur_1;
e_count:=e_count+1;
fetch cur_1 INTO e;
END loop;
dbms_output.put_line ('增加工资的员工人数：' || e_count);
close cur_1;
END;

再来考虑下面的PL/SQL块，它的功能同样是为某部门工资最低的5名员工增加工资。

不过现在要用FOR循环来处理游标中的行。

DECLARE
CURSOR cur_1(d_no emp.deptno%type)
IS
SELECT ename,sal,hiredate FROM emp
WHERE deptno=d_no
ORDER BY sal --保证员工按照工资从低到高的顺序排列
FOR UPDATE;
e_count integer:=0;
BEGIN
FOR e IN cur_1(30) loop
exit when e_count>=5;
UPDATE emp set sal=sal*1.1 WHERE CURRENT OF cur_1;
e_count:=e_count+1;
END loop;
dbms_output.put_line ('增加工资的员工人数：' || e_count);
END;

通过WHERE CURRENT OF子句，不仅可以修改游标的当前行，还可以删除游标的当前行，实际的结果是从数据库中删除了当前行。
例如，要删除游标cur_1的当前行时，可以使用以下语句：
DELETE FROM emp  WHERE CURRENT OF cur_1;