游标的定义
是一种PL/SQL控制结构,它可以命名一个工作区来来存储该工作区的存储信息。游标可以增强SQL语句的功能,它可以对SQL语句的处理进行显示的控制。
游标一般有两种个形式:显示游标和隐式游标。PL/SQL会隐式的为所有的DMLSQL语句创建一个游标,对于那些返回多条记录的查询语句,我们应该显示的声明一个游标来逐个处理这些数据:
declare
cursor mycus is
select s_name , s_age from student where stu_grade = ‘04212’;
上面的例子就返回一个包含有多条记录的结果集,而游标就像是指向当前记录的指针。
以下就显示游标和隐式游标分别介绍;
1) 显式游标
我们可以在PL/SQL块,子程序或任何包的声明部分显示的声明游标,可以用:open、fetch、close三个指令来控制游标。
显示游标的处理一般包括四个步骤:声明游标、打开游标、提取游标(将结果集提取到PL/SQL变量中)。
a) 声明游标
在PL/SQL中不允许前向引用,因此在引用游标前必须在声明部分声明它,详见如下:
cursor cursor_name [(parameter[,parameter])] [return return_type] is select_statement;
解释:
cursor_name要声明游标的名称
return return_type是可选项,是指游标的返回类型,一定要和返回结果集的类型一样
select_statement是将要进行处理的select语句(不能包含into语句因为,into语句是fetch语句的一部分),任何语句在这出现都是合法的,如UNION,JOIN。
为了确保所有的游标声明中被引用的变量在被引用之前已经被声明,一个好的办法就是在声明的结尾部分再声明它。如下;
有时也可以用游标自己带的参数赋值给where子句,但只能在游标内使用。超出游标以外的程序是访问不到的。就是在声明游标的时候设置一个参数cursor(s_grade student.stu_grade%type),然后在打开游标之前,给改参数赋值并传给where子 句。
b)打开游标
我们利用打开游标来执行查询并得到一个结果,该结果集由查询返回的全部记录组成,如果定义的游标用了FOR UPDARE子句的话,打开游标的同时锁住这些记录。当我们打开一个结果集之后,该结果集是静态的,也就是说在游标打开后,所有对数据的错做的SQL语句对游标提取出来的结果集都没有影响,直到游标关闭再打开后。
打开的一般形式为:OPEN CURSOR_NAME.
当OPEN指令打开一个游标的时候,如果游标的SELECT语句有联编变量的话,将会对变量的取值进行检查,同时结果集被确定,游标的指针指向结果集的第一行。 如果游标中有参数的话,可以在OPEN语句中来传递参数给游标,除非用缺省值,如:
c) 提取游标
打开一个游标之后就应该从游标中提取数据,提取用FETCH语句,格式如下:
FETCH CURSOR_NAME INTO VARIABLES_LIST
其中CURSOR_NAME为游标的名称,VARIABLES_LIST为已经被声明的和游标中提取的结果集类型相同的PL/SQL变量列表,或者是和游标中提取的结果集类型相,同的已被声明的PL/SQL记录类型。
在没有用循环控制语句的情况下,除非用了BULK COLLECT(批处理)子句。否则FETCH语句一次只能从结果集中提取一行,而且一次执行完后,游标的指针将自动指向结果集的下一行。而且游标的提取只能向前的,如果要重新提取已经提取过的数据只有重新打开游标。
当游标的指针指向结果集的结尾后,FETCH语句检索出来的值是不确定的,而且Oracle不会报错。当我们在游标中使用BULK COLLECT子句可以使我们一次性绑定游标中的全部结果集,也就是说一次性将所有的记录提取到一个集合里。如下:
declare
type nameTab is table of student.stu_name%type;
type ageTab is table of student.stu_age%type;
names nameTab;
ages ageTab;
s_grade student.stu_grade;
corsor mycos is
select stu_name , stu_age fro student where stu_grade = s_grade;
begin
s_grade := ‘04212’;
open mycos;
fetch mycos bulk collect into stu_name , stu_age;
close mycos;
for i in 1..names.count Loop
dbms_output.put_line(s_name||’今年’||s_age||’岁’);
end Loop;
end;
d)关闭游标
当我们将结果集中的所有数据都检索出来之后,应关闭游标,关闭之后所有的和该游标有关的资源都会被释放,此时我们如果对任何关闭了游标的操作都会引发INVALID_CURSOR错误。关闭游标的形式如下: close cursor_name。同样,引用已关闭的游标也会引发以上错误。
e)显式游标的属性
显示游标的属性返回关于执行多行记录的情况的消息。有四个属性:%ISOPEN、%FOUND、%NOTFOUND、%
ROWCOUNT。
(1)%FOUND
当游标或游标变量被打开且在执行FETCH语句之前,它为NULL.如果FETCH有记录返回,则为true,没有为false。
(2)%NOTFOUND
和上述%FOUND刚好相反,当游标或游标变量被打开但在执行FETCH语句之前时为null,如果FETCH的最后一行有记录返回为false,无则为true。
(3)%ROWCOUNT
属性是用来返回迄今为止从游标中返回的记录数目,在游标变量或游标没有打开而没有执行FETCH之前,它的属性为0.以后就为从游标中取出的记录数,FETCH每成功执行一次值就加一。
(4)%ISOPEN
当游标变量或游标被打开时,为true,否则为false。我们可以用该属性来判断一个游标是否被打开。
2)隐式游标
前面我们说过Oracle为每一个不属于显式游标的SQL DML都创建了一个隐式游标,它没有名字,也称为SQL游标。和隐式不同的是不能对一个隐式游标显式的执行OPEN、CLOSE和FETCH语句。Oracle隐式地打开SQL游标,处理SQL游标,然后再关闭 该游标。但是,和显式游标相同的是也具有四大属性。
但是,由于隐式游标没有名字,Oracle提供了这样的一种方法来访问游标属性:在属性的前面加关键字SQL。
如下:
declare
imp_count integer;
begin
update student set salary = salary * 1.5 ;
imp_count := SQL%ROWCOUNT;
dbms_output.put_line(‘共影响||imp_count||’行’);
end;
由于Oracle自动创建并打开隐式游标,执行完后再自动关闭,所以我们在访问它的%ISOPEN属性时,总是假的。
和显示游标一样,在SQL语句执行前隐式游标的属性:Found和Nofound属性为null,当操作执行完之后,影响一行或多行,则前者为true,后者为false。
如下:
declare
imp_count;
begin
select * from student ;
if SQL%ROOWCOUNT then
imp_count := SQL%ROWCOUNT
dbms_output.put_line(‘共有’||imp_count);
then
dbms_output.put_line(‘没有数据’);
end if;
end;
一个值得注意的问题是:当SELECT . . INTO . .没有检索到数据时,会引发NO_DATA_FOUND错误,它会立即将控制权异常处理部分而不在往下执行。这是如果后面再跟一个NOTFOUND的话,则不起作用。
如下所示:
另一个值得注意的问题是:由于隐式游标的属性值总是引用最近的SQL语句,所以在一个SQL块中,由于范围不同,属性的取值也有不同。