存储过程游标解析:
1. 游标是SQL的一个内存工作区,由系统或用户以变量的形式定义,从表中检索出结果集,从中每次指向一条记录进行交互的机制。
由于游标指示结果集中的当前位置 ,就像计算机屏幕上的光标指示当前位置一样,“游标”由此得名。
2. 游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。有以下几点:
(1)指定结果集中特定行的位置。
(2)基于当前的结果集位置检索一行或连续的几行。
(3)在结果集的当前位置修改行中的数据。
(4)对其他用户所做的数据更改定义不同的敏感性级别。
(5)可以以编程的方式访问数据库。
3. Oracle游标的类型
(1)静态游标:结果集已经确定(静态定义)的游标。分为隐式和显式游标。
A、隐式游标:所有DML语句为隐式游标,通过隐式游标属性可以获取SQL语句信息。当直接在代码中执行一条SQL语句时,只要该代码没有显式声明一个游标,PL/SQL就会产生一个隐式游标。如:插入操作:INSERT、更新操作:UPDATE、删除操作:DELETE、单行查询操作:SELECT...INTO... 。当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。
B、显式游标:用户显示声明的游标,即指定结果集。当查询返回结果超过一行时,就需要一个显式游标。
(2)REF游标(动态游标):动态关联结果集的临时对象。即在运行的时候动态决定执行查询。实现在程序间传递结果集的功能,利用REF CURSOR也可以实现BULK SQL,从而提高SQL性能。
在变量声明部分定义的游标是静态的,不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据,但还是有很大的局限性。通过采用动态游标,可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型,然后声明一个游标变量,游标对应的查询语句可以在程序的执行过程中动态地说明。
4. 静态游标和REF 游标的区别
(1)静态游标是静态定义,REF 游标是动态关联;
(2)使用REF 游标需REF 游标变量。
(3)REF 游标能做为参数进行传递,而静态游标是不可能的。
5. REF 游标变量
REF游标变量是一种 引用 REF游标类型 的变量,指向动态关联的结果集。
特点:
(1)可与不同查询关联,可从不同结果集中取数;
(2)可作为过程或函数的参数传递。实现对游标的共享;
(3)可以使用静态游标所具有的全部功能;
(4)可以将一个游标变量内容(包括它的结果集)赋给另一个游标变量。
6. Oracle游标的属性
游标的状态是通过属性来表示。
%Found :Fetch语句(获取记录)执行情况 True or False。
%NotFound : 最后一条记录是否提取出 True or False。
%ISOpen : 游标是否打开True or False。
%RowCount :游标当前提取的行数 。
7. 使用显示游标与遍历循环游标
A、使用显示游标
(1)声明游标:划分存储区域,注意此时并没有执行Select 语句。
(DELARE) CURSOR 游标名(参数列表) [返回值类型] IS Select 语句;
参数只定义数据类型,没有大小(所有Oracle中的形参只定义数据类型,不指定大小)。游标只能接受传递的值,而不能返回值。
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数,则必须在打开游标时传递相应的实际参数。可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。如:
(DELARE) CURSOR 游标名(参数名 数据类型 DEFAULT 默认值,。。。) [返回值类型] IS Select 语句。
SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句,但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
(2)打开游标:执行Select 语句,获得结果集存储到游标中,此时游标指向结果集头, 而不是第一条记录。
Open 游标名( 参数 列表);
(3)获取记录:移动游标取一条记录
Fetch 游标名 InTo 临时记录或属性类型变量;
(4)关闭游标:将游标放入缓冲池中,没有完全释放资源。可重新打开。
Close 游标名;
显式游标打开后,必须显式地关闭。游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
B、遍历循环游标
(1)For 循环游标
循环游标隐式打开游标,自动滚动获取一条记录,并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
For 变量名 In 游标名
Loop
数据处理语句;
End Loop;
(2)Loop循环游标
Loop
Fatch 游标名 InTo 临时记录或属性类型变量;
Exit When 游标名%NotFound;
数据处理语句;
End Loop;
(3)Loop循环游标
FETCH 游标名 INTO 临时记录或属性类型变量;
WHILE 游标名%FOUND LOOP
数据处理语句;
FETCH 游标名 临时记录或属性类型变量;
END LOOP;
8. 更新和删除显示游标中的记录
A、UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。
B、使用更新或删除:
(1)声明更新或删除显示游标:
Cursor 游标名 IS SELECT 语句 For Update [ Of 更新列列名];
Cursor 游标名 IS SELECT 语句 For Delete [ Of 更新列列名];
(2)使用显示游标来更新或删除当前记录:
Update 表名 SET 更新语句 Where Current Of 游标名;
Delete From 表名 Where Current Of 游标名;
9. 使用 REF游标
A、声明REF 游标类型,确定REF 游标类型
(1)强类型REF游标:指定retrun type,REF 游标变量的类型必须和return type一致。
语法:Type REF游标名 IS Ref Cursor Return 结果集返回记录类型;
(2)弱类型REF游标:不指定return type,能和任何类型的CURSOR变量匹配,用于获取任何结果集。
语法:Type REF游标名 IS Ref Cursor;
B、声明Ref 游标类型变量
语法:变量名 已声明Ref 游标类型;
C、打开REF游标,关联结果集
语法:Open Ref 游标类型变量 For 查询语句返回结果集;
D、获取记录,操作记录
语法:Fatch REF游标名 InTo 临时记录类型变量或属性类型变量列表;
E、关闭游标,完全释放资源;
语法:Close REF游标名;