你的位置:首页 > 数据库

[数据库]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_l 的语句为:
OPEN cur_1;
游标打开后,就可以取出游标中的数据,并对其进行处理了。
从游标中取出数据的命令是FETCH 。
FETCH命令一次取出一行数据,并将其赋给指定的变量。
FETCH命令的格式为:
FETCH 游标 INTO 变量1,变量2, ...
游标中的数据只有在取出后才能进行处理。
为此,需要在FETCH语句中用INTO子句指定多个变量,分别存放一行数据中各个列的值。
FETCH命令将一行数据中各列的值依次赋给指定的变量。
需要注意的是,变量的类型、数目要与游标中一行的各列相对应。
在用FETCH命令取出游标中的数据时,可以设想有一个指针,指向游标中的一行数据。
当游标刚刚打开时,指针指向第一行,以后每取出一行,指针自动指向下一行,直到将所有的数据都取出为止。
游标在使用完后,应该及时关闭,以释放它所占用的内存空间。
关闭游标的命令是CLOSE,其语法格式为:
CLOSE 游标名;
当游标关闭后,不能再从游标中获取数据。
如果需要,可以再次打开游标。
考虑下面的游标。
从emp表中检索员工7902 的姓名、工资、工作时间。
由于SELECT命令仅返回一行数据,所以处理的过程很简单。