Oracle游标

一:游标简介

游标是什么?
游标是映射在结果集中一行数据上的位置实体.

1.游标种类: 隐式游标(Implicit Cursor) / 显式游标(Explicit Cursor)

oracle为所有sql操作都声明了一个隐式游标
但是:对于返回多条记录的查询来说,必须声明显式游标

2.游标控制命令:

open:初始化游标
fetch:取出检索到的数据,或者使用bulk collect批量选取数据
close:关闭游标
还可以打开多个游标并发处理

oracle会打开一个隐式游标去处理所有不与显式游标相关联的sql语句
隐式游标不可以使用open/fetch/close操作
通过属性处理:

3.游标4个属性:

 3.1.%ISOPEN : 判断游标是否被打开,是则等于true,否则false
 3.2.%FOUND : 判断游标所在的行是否有效,如果有效则true,否则false
 3.3.%NOTFOUND : 
 3.4.%ROWCOUNT : 返回当前位置为止游标所读取的记录行数

二:声明游标

DECLARE CURSOR c1[(参数1 参数类型 default 默认值,参数2...)] IS select语句;
        CURSOR c2 RETURN 表名%ROWTYPE IS SELECT语句;

注意:
1.游标名称是一个未声明的标识符,不是变量,不可以赋值也不可以在表达式中使用
2.游标作用域,如果在循环内定义,则不能出循环使用
3.游标可以接收参数,而且参数都是IN类型,传入值形式,不能对其进行赋值
4.游标参数不能有not null约束
5.使用游标前先open

三:打开/关闭游标

3.1打开游标

DECLARE CORSOR c1 is select ...;
...
begin
 open c1;  --打开游标,但是不能选取出结果集,只有fetch语句才可以将数据读取出来
...
end;

使用游标更新特定的行: FOR UPDATE 语句,有互斥锁机制

DECLARE CURSOR c2 IS
     select name,status,address from table where id > 10 FOR UPDATE OF id;   --会加上互斥锁,直到commit后解锁
BEGIN
OPEN c2;
WHILE id%FOUND LOOP
     UPDATE table set id = 666 WHERE CURRENT OF id;
END LOOP;
CLOSE c2;
COMMIT;
END;

3.2传递游标参数

如果游标声明了参数,又没有默认值,则必须在 open的时候给定实参.

比如 :
OPEN c1(1,‘参数2’); 这样传递参数

3.3从游标中取值

两种取值方式:
1.FETCH: 每次只取一条数据
2.BULK COLLECT: 批量读取数据

3.3.1 FETCH方式

LOOP
 FETCH c1 INTO ...;
     EXIT WHEN c1%NOTFOUND;
END LOOP;

注意: 游标应用变量,在游标声明->游标打开之间该变量发生变化会影响到查询结果,但是在open之后再改变该变量,就不会影响到查询结果.

示例:

DECLARE 
  ID INT;
  CURSOR c1 IS 
     SELECT ID*2 FROM ...;
BEGIN
 OPEN c1;    --在open后修改变量id不会影响fetch结果
 LOOP
     FETCH c1      
          INTO tmp;
     EXIT WHEN c1%NOTFOUND;
     ID := ID +2;   --不影响c1结果
   END LOOP;
 CLOSE c1;
END;

3.4 BULK COLLECT 批量获取游标数据

示例:

DECLARE CURSOR c1 
     IS SELECT * FROM TABLE1;
BEGIN
     OPEN c1;
     FETCH c1
          BULK COLLECT INTO tmp;  --抓取后批量获取数据
CLOSE c1;
END;

3.5 关闭游标

CLOSE 游标名称;

注意: 操作一个已经关闭了的游标就会抛出异常 INVALID_CURSOR;

四:在游标中使用子查询

DECLARE CURSOR c1 IS SELECT … FROM (SELECT … FROM …);

五:游标属性

属性:
%ISOPEN
%FOUND/NOTFOUND
%ROWCOUNT

注意:
1.还有一个专门为FORALL 语句设计的属性 %BULK_ROWCOUNT
2.当一个显示游标或者游标变量被打开时,满足查询条件的行会被标记,最终形成结果集
3.在oracle打开SQL游标之前,隐式游标的所有属性都是NULL
4.oracle在执行完成语句后会自动关闭SQL游标,所以SQL游标的%ISOPEN属性总是FALSE

5.1 %FOUND属性

在一个游标或游标变量被打开后且在首次取得数据之前,%FOUND会产生NULL值,此后每次取得数据就为TRUE,直到最后一次取数失败则为FALSE.

5.2 %ISOPEN属性

OPEN则为TRUE
否则为 FALSE

5.3 %NOTFOUND属性

EXIT WHEN c1%NOTFOUND ;  --存在不足

注意:
在取数之前 %NOTFOUND会返回 NULL , 所以如果FETCH从来没有执行成功,那么 %NOTFOUND就不会为true,循环不会退出

为了安全,可以修改为

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

5.4 %ROWCOUNT属性

当游标或者游标变量被打开的时候,%ROWCOUNT的值为 0 ,每成功取得一条数据就 +1 ,

注意:
在首次调用FETCH之后,如果结果集是空的,
则%FOUND = FALSE
%NOTFOUND = TRUE
%ROWCOUNT = 0;

六:用游标进行循环

可以使用for循环来代替 open->fetch->close
游标for循环隐式的声明了一个 %ROWTYPE 类型记录作为它的循环索引,打开游标然后反复执行把结果集中的行放到索引中去,最后所有行都被处理完成后关闭游标;

DECLARE tmp table.col%type;
     CURSOR c1 IS
     SELECT ... FROM ...;
BEGIN 
     FOR i IN c1 LOOP   --在每次循环开始之前,pl/sql都会把数据放到tmp中,所以tmp的作用范围只在循环内
          tmp := ...;
     INSERT INTO table values(tmp);
END LOOP;
COMMIT;
END;

6.1 使用子查询代替显示游标

begin
     for tmp in (select ... from ...) loop   --这里的子查询就相当于游标
          ...
     end loop;
commit;
end;

6.2 在游标FOR循环中为表达式定义别名

CURSOR c1 IS SELECT ... , nvl(COL,0) AS NEW_COL FROM ... ;  --这里的nvl(COL,0) AS NEW_COL 必须起别名

注意:
有表达式的列必须起别名;

6.3 为游标FOR循环传递参数

FOR ... IN c1(实参) LOOP
...

七:游标变量

游标: 静态固定,不可赋值
游标变量: 动态不固定,可以赋值

7.1 游标变量简介

类似于指针,指向一块内存地址,而不是地址内容本身.
游标变量数据类型: REF CURSOR
游标指向同一个工作区,而游标变量可以指向不同工作区

注意: 游标和游标变量不能交互使用,不能在使用游标的地方使用游标变量,反之亦然

7.2 游标变量的定义和声明

REF CURSOR类型:
强类型: 有返回值,编译检查类型兼容性,避免错误

DECLARE TYPE ref_cur IS REF CURSOR RETUEN table%ROWTYPE; --强类型

弱类型: 无返回值,不检查兼容,更灵活

DECLARE TYPE ref_cur IS REF CURSOR ; --弱类型

声明游标变量:

DECLARE TYPE ref_cur IS REF CURSOR RETUEN table%ROWTYPE;
     tmp_cur ref_cur ; --声明游标变量

八:控制游标变量

打开: OPEN…FOR …
关闭: CLOSE
抓取: FETCH

8.1打开游标

IF NOT tmp_cur%ISOPEN  THEN
     ...
  OPEN tmp_cur  FOR SELECT ....;   --使用 open ... for ...打开游标变量tmp_cur
END IF;

注意:
当游标变量被用于子程序的形式参数的时候,一定要指定类型为IN / IN OUT 类型
但是如果在子程序中还需要打开游标的话,那么就必须声明为IN OUT类型

CREATE 
...
     TYPE tmp_cur REF CURSOR RETURN ...;
     PROCEDURE tmp_pro (emp IN OUT tmp_cur) ;  --这里把游标变量emp声明为 IN OUT 类型

8.2从游标变量中取值

8.2.1 FETCH 依次每条取出

LOOP
     FETCH emp INTO ...;   --每次取一条
EXIT WHEN emp%NOTFOUND;
END LOOP;

8.2.2 BULK COLLECT 批量读取

LOOP
...
     FETCH emp
     BULK COLLECT INTO ...;  --批量读取
END LOOP;

8.3关闭游标

CLOSE …

九:使用游标变量作为主变量

使用命令VARIABLE声明
VARIABLE tmp_cur NUMBER;

十:游标变量的限制

10.1不能在包中声明游标变量
10.2如果把主游标变量传递到pl/sql中,就不能在服务器端从这个游标变量中取得数据了,除非打开这个游标变量得操作也是在同一个服务器调用中进行
10.3不能简单地用比较操作符来判断两个游标变量是否相等,或者不等,或空
10.4不能为游标变量赋空值
10.5不能在创建表/创建视图的时候指定字段类型为REF CURSOR类型,因为数据库字段是不能存放游标变量值的
10.6不能把REF CURSOR类型作为集合的元素类型,也就是说索引表,嵌套表和变长数组不能存放游标变量值
10.7游标和游标变量不能互相替换

十一:游标表达式

CURSOR(select…)