oracle中的游标详解和示意图

什么是游标

结果集，结果集就是select查询之后返回的所有行数据的集合。

游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

1. 定位到结果集中的某一行。
2. 对当前位置的数据进行读写。
3. 可以对结果集中的数据单独操作，而不是整行执行相同的操作。
4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

游标的分类

根据游标检测结果集变化的能力和消耗资源的情况不同，SQL Server支持的API服务器游标分为一下4种：

• 静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。
• 动态游标:这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。
• 只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
  
• 键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。。

游标的生命周期

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

 1.声明游标，语法

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

参数说明：

• cursor_name：游标名称。
• Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
• Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
• [Local | Global]:默认为local。
• Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
• Static:静态游标
• KeySet：键集游标
• Dynamic：动态游标，不支持Absolute提取选项
• Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
• Read_Only:不能通过游标对数据进行删改。
• Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
• Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
• Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
• For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标

declare orderNum_02_cursor cursor scroll
for select OrderId from bigorder where orderNum='ZEORD003402'

3.打开游标

--打开游标语法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。

--打开游标
open orderNum_02_cursor

4.提取数据

--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

参数说明：

• Frist：结果集的第一行
• Prior：当前位置的上一行
• Next：当前位置的下一行
• Last：最后一行
• Absoute n：从游标的第一行开始数，第n行。
• Relative n：从当前位置数，第n行。
• Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

例子：

--提取数据
fetch first from orderNum_02_cursor
fetch relative 3 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch absolute 4 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch last from orderNum_02_cursor 
fetch prior from orderNum_02_cursor
select * from bigorder where orderNum='ZEORD003402'

结果（对比一下，就明白啦）：

例子：

--提取数据赋值给变量
declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
select @OrderId as id
select * from bigorder where orderNum='ZEORD003402'

结果：

通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移动游标
 end 

5.利用游标更新删除数据 

--游标修改当前数据语法
Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

---游标更新删除当前数据
---1.声明游标
declare orderNum_03_cursor cursor scroll
for select OrderId ,userId from bigorder where orderNum='ZEORD003402'
--2.打开游标
open orderNum_03_cursor
--3.声明游标提取数据所要存放的变量
declare @OrderId int ,@userId varchar(15)
--4.定位游标到哪一行
fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同
while @@fetch_status=0  --提取成功，进行下一条数据的提取操作 
 begin
   if @OrderId=122182
     begin
     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行
     end
   if @OrderId=154074
      begin
      Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行
      end
   fetch next from orderNum_03_cursor into @OrderId ,@userId  --移动游标
 end  

6.关闭游标

 游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。

--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--关闭游标
close orderNum_03_cursor

7.删除游标

删除游标，释放资源

--释放游标语法
deallocate  [ Global ] cursor_name | cursor_variable_name
--释放游标
deallocate orderNum_03_cursor

游标 游标的简介：

逐行处理查询结果，以编程的方式访问数据

游标的类型：

1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql。

2，显式游标：显式游标用于处理返回多行的查询。

3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

隐式游标：

---

q在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 q隐式游标的属性有： q%FOUND – SQL 语句影响了一行或多行时为 TRUE q%NOTFOUND – SQL 语句没有影响任何行时为TRUE q%ROWCOUNT – SQL 语句影响的行数 q%ISOPEN - 游标是否打开，始终为FALSE

begin update student s set s.sage = s.sage + 10 ; if sql %FOUND then dbms_output.put_line('这次更新了' || sql% rowcount ); else dbms_output.put_line ('一行也没有更新' ); end if; end;

在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS

SQL> declare 2 sname1 student.sname%TYPE; 3 begin 4 select sname into sname1 from student; 5 if sql%found then 6 dbms_output.put_line(sql%rowcount); 7 else 8 dbms_output.put_line('没有找到数据'); 9 end if; 10 exception 11 when too_many_rows then 12 dbms_output.put_line('查找的行记录多于1行'); 13 when no_data_found then 14 dbms_output.put_line('未找到匹配的行'); 15 end; 16 / 查找的行记录多于1行 PL/SQL procedure successfully completed SQL>

显式游标：

---

sqlserver与oracle的不同之处在于： 最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步： 声明游标、打开游标、使用游标读取记录、关闭游标。
显式游标的使用：

------------------------------------无参数游标------------------------------- declare sname varchar2( 20); --声明变量 cursor student_cursor is select sname from student ; --声明游标 begin open student_cursor;--打开游标 fetch student_cursor into sname ;--让游标指针往下移动 while student_cursor%found --判断游标指针是否指向某行记录 loop--遍历 dbms_output.put_line ('学生姓名' ||sname ); fetch student_cursor into sname; end loop; close student_cursor; end; ------------------------------------有参数游标------------------------------- declare sname student.sname%type; sno student.sno%type; cursor student_cursor (input_sno number) is select s.sname, s.sno from student s where s.sno > input_sno; --声明带参数的游标 begin sno := &请输入学号 ;--要求从客户端输入参数值，"&"相当于占位符； open student_cursor( sno); --打开游标，并且传递参数 fetch student_cursor into sname, sno; --移动游标 while student_cursor% found loop dbms_output.put_line ('学号为：' ||sno ||'姓名为：' ||sname ); fetch student_cursor into sname,sno; end loop; close student_cursor; end; ------------------------------------循环游标------------------------------- -- Created on 18-1月-15 by 永文 declare stu1 student%rowtype ;--这里也不需要定义变量来接收fetch到的值 cursor student_cursor is select * from student ; begin open student_cursor; --这里不需要开启游标 for stu1 in student_cursor loop dbms_output.put_line ('学生学号：' ||stu1.sno ||'学生姓名：' ||stu1.sname ); fetch student_cursor into stu1;--也不需要fetch了 end loop; close student_cursor; --这里也不需要关闭游标 end; ------------------------------------使用游标更新行------------------------------- declare stu1 student%rowtype ; cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标 begin open student_cursor; fetch student_cursor into stu1;--移动游标 while student_cursor%found --遍历游标，判断是否指向某个值 loop update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据 fetch student_cursor into stu1;--移动游标 end loop; close student_cursor; end; declare stu1 student%rowtype ; cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标 begin open student_cursor; -- fetch student_cursor into stu1;--移动游标 -- while student_cursor%found--遍历游标，判断是否指向某个值 loop fetch student_cursor into stu1 ;--移动游标 exit when student_cursor %notfound ; update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据 end loop; close student_cursor; end; ------------------------------------使用fetch ... bulk collect into------------------------------- declare cursor my_cursor is select ename from emp where deptno= 10; --声明游标 type ename_table_type is table of varchar2 (10 );--定义一种表类型，表中的属性列为varchar2类型 ename_table ename_table_type;--通过上面定义的类型来定义变量 begin open my_cursor; --打开游标 fetch my_cursor bulk collect into ename_table; --移动游标 for i in 1 ..ename_table.count loop dbms_output.put_line(ename_table(i)); end loop ; close my_cursor; end; -----------------------------------显示游标题目-------------------------------------- SQL > select * from student ; XH XM ---------- ---------- 1 A 2 B 3 C 4 D SQL > select * from address ; XH ZZ ---------- ---------- 2 郑州 1 开封 3 洛阳 4 新乡 完成的任务 :给表student添加一列zz ,是varchar2 (10 )类型； 再从address中，将zz字段的数值取出来，对应的插入到 student新增的zz列中。 即：得到的结果：student表中，是： XH XM ZZ -- ---------- ------ 1 A 开封 2 B 郑州 3 C 洛阳 4 D 新乡 declare stu1 student %rowtype ; add1 address %rowtype ; cursor student_cursor is select * from student for update;--声明更新游标 cursor address_cursor is select * from address ;--声明游标 begin open student_cursor ;--打开游标 fetch student_cursor into stu1;--移动游标 while student_cursor% found--判断游标是否指向某条记录 loop open address_cursor ;--打开另外一个游标 fetch address_cursor into add1 ;--移动游标 while address_cursor %found--判断游标是否指向某条记录 loop if add1.xh = stu1.xh then--判断两个游标所指向的记录中xh的值是否相等 update student s set s.zz = add1.zz where current of student_cursor;--假如相等就更新游标所指向的记录值 end if; fetch address_cursor into add1 ;--移动游标 end loop; close address_cursor ;--关闭游标 fetch student_cursor into stu1 ;--移动游标 end loop; close student_cursor ;--关闭游标 end;

REF游标也叫动态游标：

---

qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤： q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明 REF 游标类型的语法为：

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

-----------------------------------ref游标---------------------------------
declare
type ref_cursor  is ref cursor; --声明一个ref游标类型
tab_cursor ref_cursor ;--声明一个ref游标
sname student.xm %type ;
sno student.xh %type ;
tab_name varchar2 (20 );
begin
  tab_name := '&tab_name'; --接收客户输入的表明
  if tab_name = 'student' then
    open tab_cursor for select xh ,xm  from student ; --打开ref游标
    fetch tab_cursor into sno ,sname ;--移动游标
    while tab_cursor %found
      loop
        dbms_output.put_line ('学号：' ||sno ||'姓名：' ||sname );
        fetch tab_cursor into sno ,sname ;
        end loop;
        close tab_cursor ;
        else
          dbms_output.put_line ('没有找到你想要找的表数据信息' );
          end if;
        end;

 
-----------------------------------ref游标题目---------------------------------
SQL > select * from student ;
        XH KC
---------- ----------
         1 语文
         1 数学
         1 英语
         1 历史
         2 语文
         2 数学
         2 英语
         3 语文
         3 英语
9 rows selected

SQL >       
完成的任务 :
生成student2表 (xh number, kc  varchar2 (50 ))；
对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。
即，student2中的结果如下：
                      XH KC
                 --- -------------------------------------------
                       1 语文数学英语历史
                       2 语文数学英语
                       3 语文英语

create table student2 (xh number, kc varchar2 (50 ));

declare
kcs varchar2 (50 );
kc varchar2 (50 );
type ref_cursor is ref cursor; --声明一个ref游标类型
stu_cursor ref_cursor ;--定义一个ref游标类型的变量
type tab_type is table of number; --声明一个table类型
tab_xh tab_type ;--定义一个表类型的变量
cursor cursor_xh is select distinct( xh) from student; --声明一个游标
begin
  open cursor_xh; --打开游标
  fetch cursor_xh bulk collect into tab_xh; --提取数据到表中
  for i in 1 .. tab_xh.count
    loop
      kcs :='' ;
      open stu_cursor for select kc from student s where s.xh = tab_xh(i ); --打开ref游标
      fetch stu_cursor into kc ; --移动游标
      while stu_cursor %found
        loop
          kcs := kc ||kcs ; --连接字符串使用||而不是+
          fetch stu_cursor into kc ; --移动游标
          end loop;
          insert into student2 (xh , kc ) values( i, kcs);
          close stu_cursor ;
      end loop;
      close cursor_xh ;
      end;