1.什么是游标?
为了处理SQL语句,Oracle将在内在中分配一个区域,这就是上下文区,这个区包含了完成处理过程的必要信息,其中包括已经处理完的行数、指向被分析语句的指针和在查询情况下的活动集,它是查询语句返回的数据行集,游标就是指向上下文区的句柄或者指针。
2.处理显示游标
在PL/SQL中处理显示游标必须包含如下四个步骤:
1) 声明游标
CURSOR cursor_name IS select_statement;
cursor_name 是游标名,select_statement是使用该游标处理的查询
2) 为查询打开游标
OPEN crusor_name;
cursor_name 为声明过的游标,当游标被打开时,将发生下列情况:
检验绑定变量的值;
基于的绑定变量的值和查询中所引用的表的内容,确定活动集;
活动集指针指向第一行;
3) 取得结果放入PL/SQL变量中
FETCH语句有两种形式:
FETCH cursor_name INTO list_of_variables;
和
FETCH cursor_name INTO PL/SQL_record;
list_of_variables为已经声明的PL/SQL变量列表,此列表用逗号来分隔,PL/SQL_record为已经声明的PL/SQL记录,不论哪一个形式,在INTO子句中变量或变量级必须与查询的选择列表类型匹配。
在每个FETCH之后,活动集指针指向下一个数据行,如此往复,每个FETCH返回活动集中相信的数据行,直到全部数据集合都被检索。
4) 关闭游标
CURSOR cursor_name ;
当所有的活动集都被检索时,游标应该被关闭,关闭游标将通知PL/SQL:程序已完成游标的处理,现在可以释放与游标相关的资源了。这些资源包括用于保存活动集的存储空间,以及用于确定流动集的临时存储空间。
一旦该游标被关闭,那么再使用它来检索数据就是非法的,将产生Oracle错误。
3.游标属性
在PL/SQL中,游标有四个有效属性。在PL/SQL语句块中,游标属性被游标名,与%TYPE和%ROWTYPE相似,然而,游标属性并非返回一个类型,而是返回可以在表达式中使用的值。游标的属性分别是%FOUND、%NOTFOUND 、%ISOPEN 和%ROWCOUNT.
%FOUND %NOTFOUND都是Boolean属性,若前面的FETCH语句返回一行数据,则%FOUND返回TRUE,%NOTFOUND返回FALSE;
%ISOPEN 也是Boolean属性被用于确定相关的游标打开与否,若打开,则%ISOPEN返回TRUE,否则FALSE。
%ROWCOUNT 此数字属性返回了到目前为止游标所检索的数据行的个数。
4.三种检索循环
1) 简单循环
DECLARE
......游标声明部分
OPEN cursos_name;
LOOP
FETCH cursor_name INTO variables...
EXIT WHEN cursor_name%NOTFOUND
........处理部分
END LOOP;
CLOSE cursor_name;
END;
2) WHILE循环
简单循环中的LOOP ..... END LOOP;可用如下WHILE循环替换
FETCH cursor_name INTO variable...
WHILE cursor_name LOOP
................处理部分
FETCH cursor_name INTO variables...;
END LOOP;
FETCH语句出现两次:一次在循环之前,一次在循环处理之后
3) FOR循环
刚刚描述的两种FETCH循环都需要用OPEN、FETCH和CLOSE语句来显示处理游标,PL/SQL提供了更简单的循环类型,可以隐含地处理游标,它被称做游标FOR循环。
简单循环中的LOOP ..... END LOOP;可用如下FOR循环替换,得到的结果是一样的
FOR temp_variable IN cursor_name LOOP
................处理部分
END LOOP;
注意:
一:记录temp_variable不能在该语句块的声明部分中声明,此变量是PL/SQL编译器隐式声明,类似于数值FOR循环的循环索引,此变量的类型是cursor_name%ROWTYPE,并且temp_variable的作用域是FOR循环本身,该循环索引的隐式声明和声明范围;
二:cursor_name 被循环隐式打开、检索值和关闭。
5.修改由游标检索的数据行
经常出现的是,在检索循环中的处理会修改由游标检索的数据行。PL/SQL提供了很方便的语法,这种方法由两部分组成:游标声明中的FOR UPDATE 子句,以及UPDATE或DELETE语句中的WHERE CURRENT OF 子句。
DECLARE CURSOR c_changeid IS select rownum,id,name from m FOR UPDATE; begin for v_id IN c_changeid loop update m set id = v_id.rownum where CURRENT OF c_changeid ; end loop; commit; end; /