Oracle的cursor游标

游标
1. 什么是游标？
游标是在关系数据库中用来操作记录结果集。
游标指示结果集的当前位置，即游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，
在这个过程中可能返回至原始表，也可能不返回至原始表。
换句话说，游标从概念上讲基于数据库的表返回结果集。
由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，
“游标”由此得名。

2.游标的基本原理：
在oracle中，会为sql的CURD操作在内存中分配一个缓冲区，游标就是指向该区的一个指针。
这里一般来说是指通过查询出来的结果集，结果集存放在缓冲区中，游标指向该结果集，
可以指向结果集中的任何一行数据，
开始时，它会指向首记录，
它可以对结果集进行逐行操作，也就是说它为应用程序提供一种对结果集进行行操作的方法。

3.游标的作用？
3.1 指定结果集中特定行位置
3.2 基于当前的结果集位置检索一行或连续多行
3.3 在结果集的当前位置修改行中的数据
3.4 对其他用户所做的数据更改定义不同的敏感性级别
3.5 可以以编程的方式访问数据库

4.为什么避免使用游标？
4.1 在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”
因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

5.游标的类型：
5.1 分类为两种，静态和REF(动态)
静态就像一个数据快照，打开游标后的结果集是对数据库的一个备份
数据不随对表执行DML操作后而改变。
静态分为显式与隐式游标
隐式游标由oracle自动管理，用户控制不了，不检测数据行的变化
显式游标在使用前必须先声明或者定义，这样的游标通常会关联数据查询语句，
返回结果集
REF(游动)游标：动态关联结果集的临时对象
动态与静态的区别在于，sql语句通过编译，运行，才会显示出结果。
在运行时，sql语句是不变的，那么这个游标才是静态的；
sql语句是变化的，那么这个游标是动态的。

6.显示游标
声明--》打开--》loop --》 读取--》
判断游标属性，走“读取” ，或者“关闭” --》end loop ---》 关闭

7.游标属性
%Found ：Fetch语句（获取记录）执行情况 True or False。
%NotFound : 最后一条记录是否提取出 True or False。
%ISOpen ： 游标是否打开True or False。
%RowCount ：游标当前提取的行数，并非结果集中的记录数。

8.为游标传递参数

9.隐式游标
每当执行select活DML语句时，PL/SQL会自动打开一个隐式游标
特性： 隐式游标由PL/SQL(ORACLE)自动管理;
默认名称为SQL;
select 或DML操作会产生隐式游标
隐式有标的属性值始终是最新执行的SQL语句的结果值
SQL游标和cursor for 游标是Oracle内置的游标
SQL游标与当前会话有关，当前会话中的update,delete都会影响SQL隐式游标的属性
cursor for游标用于循环

10.隐式游标的属性
sql%isopen 判断当前游标是否打开，打开为true,没有为false;
sql%rowcount 对于DML语句，该属性值为受影响的数据行数。
对于select语句，如果发生异常，其值为1
sql%found 对于DML语句，该属性表明表中是否有数据值受影响。没有影响为false,
有影响为true.
对于select语句，如果发生异常，其值为true
sql%notfound 与属性sql%found正好相反


11. REF游标
动态游标不与特定的查询绑定，在打开游标时，才确定对应的sql。
REF游标可以对应多个查询。
分为两类，强类型与弱类型游标

强类型REF游标
不指定游标的查询，需要定义游标的类型。
--------------
REF游标时ref类型的变量，还有return语句，
type 游标类型 is ref cursor
return 记录类型
-----------------

打开动态游标
open 游标变量 for <select语句>


弱类型REF游标
与强类型REF游标的区别在于，没有return 类型
type 游标类型 is ref cursor

 

/**
游标
  基于结果集的， 可以对结果集进行逐行操作。

游标的类型： 
   静态就像一个数据快照，打开游标后的结果集是对数据库的一个备份
    数据不随对表执行DML操作后而改变。
    静态分为显式与隐式游标
      隐式游标由oracle自动管理，用户控制不了，不检测数据行的变化
      显式游标在使用前必须先声明或者定义，这样的游标通常会关联数据查询语句，
        返回结果集
    REF(游动)游标：动态关联结果集的临时对象
  
游标的使用顺序
  1. 定义或者声明
  2. 打开
  3. 使用
  4. 关闭
  5. 释放(在sql server中存在，在oracle中不存在)
获取游标的数据
   fetch  [next| prior | first | 
   last | absolute n | relative n from ]  游标名 into 变量 
   --批量提取数据
   fetch  游标名 bulk collect into 变量名 limit (限制条数)
游标属性
  %Found ：Fetch语句（获取记录）执行情况 True or False。
  %NotFound : 最后一条记录是否提取出 True or False。
  %ISOpen ： 游标是否打开True or False。
  %RowCount ：游标当前提取的行数，并非结果集中的记录数。  
   
for...in...loop
end loop;

 */     
 /***************************/
-- 游标的定义 
/***************************/
declare 
--声明游标cursor
 cursor myCur is select * from a;
 --声明列变量，类型为a.aname的类型
 var_n a.aname%type;
 --声明列变量，类型为a.age的类型
 var_age a.age%type;
 --声明行变量，类型是游标的行类型
 var_row myCur%rowtype;
 begin 
   --打开游标，此时游标的指向是结果集中首条记录
   open myCur;
   --使用loop循环读取多行数据，游标指针就会不断的前进
   --没有loop，读取当行记录
   loop
     --读取行数据, myCur只能给行类型，
     fetch myCur  into  var_age;
     --需要有退出的操作，否则一直往下走,直到卡死
     exit when  myCur%notFound;
     dbms_output.put_line(var_age); 
   
   end loop;
   --关闭游标，结束资源
   close myCur;
 end;
/***************************/
--使用游标的属性。 其中insert into 之后，不需要commit，它会自动提交
/***************************/
declare
begin
  insert into a values (4, 'la4', '12'); 
  if SQL%found then
    DBMS_OUTPUT.put_line('insert values');
  else
    DBMS_OUTPUT.put_line('no insert values');
  end if;
end;
/***************************/
--遍历循环游标loop, 
/***************************/
declare
  cursor mycur is select a.aid, a.aname,a.age from a;
  vara a.aid%type;
  varb a.aname%type;
  varc a.age%type;
begin
  open mycur;
  fetch mycur into vara,varb,varc;
  dbms_output.put_line('vara:'||vara);
  dbms_output.put_line('varb:'||varb);
  dbms_output.put_line('varc:'||varc);
  close mycur;
end;
--
declare
  cursor mycur is select a.aid, a.aname,a.age from a;
  varrow mycur%rowtype;  
begin 
  open mycur;
  loop
       fetch mycur into varrow;
       exit  when mycur%notfound; 
       dbms_output.put_line('-------'||mycur%rowcount||'-------');
       dbms_output.put_line('1:'||varrow.aid||',2:'||varrow.aname||',3:'||varrow.age); 
  end loop;
  close mycur;
end;
--使用For循环遍历游标, 不需要声明变量，也不需要打开游标，不需要关闭游标
declare
  cursor mycur is
    select a.aid, a.aname, a.age from a;
begin   
  for varrow in mycur loop
    dbms_output.put_line('-------' || mycur%rowcount || '-------');
    dbms_output.put_line('1:' || varrow.aid || ',2:' || varrow.aname ||
                         ',3:' || varrow.age); 
  end loop;
end;
/************************** 
--更新和删除显示游标中的记录
--UPDATE或DELETE语句中的WHERE CURRENT OF子串
专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
  要使用这个方法，在声明游标时必须使用FOR UPDATE子串，
  当对话使用FOR UPDATE子串打开一个游标时，
  所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，
  其他对象只能查询这些数据行，
  不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
   在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，
   那么所有表中选择的数据行都将被锁定。
   如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，
   直到数据行解锁。 
**************************/
declare
  cursor mycur is
    select * from a for update;
  var_name a.aname%type;
  var_temp a.aname%type;
  var_row  mycur%rowtype;
begin
  open mycur;
  loop
    fetch mycur
      into var_row;
    var_name := var_row.aname;
    dbms_output.put_line(var_name);
    exit when mycur%notfound;
    case (var_name)
      when 'a1' then
        var_temp := 'A';
      when 'a2' then
        var_temp := 'U';
      else
        var_temp := null;
    end case;
    --where current of mycur锁定当前行,
    --update之后需要commit提交事务，才能在数据库中保证变化,不能加在loop之内，
    --那样会影响mycur的指针指向
    update a set a.aname = a.aname || var_temp where current of mycur;
  end loop;
  commit;
  close mycur;
end;



-->http://www.cnblogs.com/shengtianlong/archive/2010/12/31/1922767.html
/*
select * from A;
select * from B;*/

 

>Oracle中Cursor使用