oracle游标的简单学习
/*游标的简单学习*/一.概念：
1.游标：游标是用于在存储过程中迭代SELECT查询出的数据。
2.什么是游标？
①游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
3.游标有什么作用？
①指定结果集中特定行的位置。
②基于当前的结果集位置检索一行或连续的几行。
③在结果集的当前位置修改行中的数据。
④对其他用户所做的数据更改定义不同的敏感性级别。
⑤可以以编程的方式访问数据库。
4.为什么避免使用游标？
①在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”
因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。.
5.Oracle游标的类型？
①静态游标：结果集已经确定（静态定义）的游标。分为隐式和显示游标。
⑴隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。
⑵显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
②REF游标：动态关联结果集的临时对象。
6. Oracle游标的状态有哪些，怎么使用游标属性？
①游标的状态是通过属性来表示。
%Found ：Fetch语句（获取记录）执行情况True or False。
%NotFound : 最后一条记录是否提取出True or False。
%ISOpen ： 游标是否打开True or False。
%RowCount ：游标当前提取的行数 。
说明：
在打开一个游标之后，马上检查它的%found或%notfound属性，
它得到的结果即不是true也不是false.而是null.
必须执行一条fetch语句后，这些属性才有值。
②使用游标的属性。
eg1.
begin
update emp set sal = sal + 0.1 where job = 'CLERK';
if SQL%Found then
dbms_output.put_line('update success');
else
dbms_output.put_line('update failed');
end if;
end;
7.如何使用游标,如何遍历游标？
①使用显示游标
⑴声明游标：划分存储区域，注意此时并没有执行Select 语句。
CURSOR 游标名( 参数 列表) [返回值类型] IS Select 语句；
⑵打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头， 而不是第一条记录。
Open 游标名( 参数 列表);
⑶获取记录：移动游标取一条记录
Fetch游标名InTo临时记录或属性类型变量；
⑷关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
Close游标名；
②遍历循环游标
⑴For 循环游标
循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
For变量名In游标名
Loop
数据处理语句；
End Loop；
⑵Loop循环游标
。。。
Loop
Fatch游标名InTo临时记录或属性类型变量；
ExitWhen 游标名%NotFound；
End Loop；
。。。
eg1.
declare cursor myCur
is
select empno,ename,sal from emp;
vname varchar2(10);
vno number(4);
vsal number(7,2);
begin
open myCur;
fetch myCur into vno,vname,vsal;
--只取了所有结果的第一行！
dbms_output.put_line('vno :' || vno || 'ename: ' || vname || 'sal:' || vsal);
close myCur;
end;
/
eg2.使用loop遍历游标
declare cursor myCur
is
select empno,ename,sal,job from emp;
varObj myCur%rowType;
begin
if myCur%isopen= false then
open myCur;
dbms_output.put_line('Opening ...');
end if;
loop
fetch myCur into varObj;
exit when myCur%notfound;
dbms_output.put_line(myCur%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);
end loop;
if myCur%isopen then
close myCur;
dbms_output.put_line('closeing...');
end if;
end;
/
eg3.使用for循环遍历游标，
【循环游标隐式打开游标，自动滚动获取一条记录，
并自动创建临时记录类型变量存储记录。
处理完后自动关闭游标。】
declare cursor myCur
is
select * from emp;
begin
for varObj in myCur
loop
dbms_output.put_line(myCur%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);
end loop;
end;
/
8.怎样更新和删除显示游标中的记录？
①UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，
所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，
不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。
如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
②使用更新或删除：
⑴声明更新或删除显示游标：
Cursor 游标名ISSELECT 语句 For Update [ Of更新列列名]；
Cursor 游标名ISSELECT 语句 For Delete [ Of更新列列名]；
⑵使用显示游标当前记录来更新或删除：
Update表名 SET 更新语句Where CurrentOf 游标名；
DeleteFrom表名 Where CurrentOf 游标名；
eg1.更新显示游标记录
declare cursor myCur
is
select job from emp for update;
vjob emp.job%type;
rsal emp.sal%type;
begin
open myCur;
loop
fetch myCur into vjob;
exit when myCur%Notfound;
case(vjob)
when 'ANALYST' then rsal := 0.1;
when 'CLERK' then rsal := 0.2;
when 'MANAGER' then rsal := 0.3;
else
rsal := 0.5;
end case;
updateemp set sal = sal + rsal where current of myCur;
end loop;
end;
/
eg2.删除显示游标记录
-- bak table of emp;
create table empa as select * from emp;
declare cursor myCur
is
select sal from empa for update;
rsal empa.sal%type;
begin
open myCur;
loop
fetch myCur into rsal;
exit when myCur%Notfound;
if rsal < 800 then
delete from empa where current of myCur;
end if;
end loop;
close myCur;
end;
/
9.什么是带参数的显示游标？
①与存储过程和函数相似，可以将参数传递给游标并在查询中使用。
参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。
与过程不同的是，游标只能接受传递的值，而不能返回值。
可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。
游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。
②使用带参数的显示游标
⑴声明带参数的显示游标：
CURSOR 游标名[(parameter[,parameter],...)] IS Select语句；;
参数形式：1，参数名 数据类型
2，参数名 数据类型DEFAULT默认值
eg1.
declare cursor myCur(pSal Number default 800)
is
select * from empa where sal > pSal;
varA myCur%rowtype;
begin
open myCur;
loop
fetch myCur into varA;
exit when myCur%notfound;
DBMS_OUTPUT.PUT_LINE(MyCur%RowCount||' '||varA.empno||' '||varA.ename||''||varA.sal);
end loop;
close myCur;
end;
/
参考地址： http://www.iyunv.com/database/201203/122387.html
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
======================2013-12-16-REF-CURSOR===================================
1，什么是REF游标 ？
动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
2，REF 游标 有什么作用？
实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。
3，静态游标和REF 游标的区别是什么？
①静态游标是静态定义，REF 游标是动态关联；
②使用REF 游标需REF 游标变量。
③REF 游标能做为参数进行传递，而静态游标是不可能的。
4，什么是REF 游标变量？
REF游标变量是一种 引用REF游标类型的变量，指向动态关联的结果集。
5，怎么使用REF游标 ？
①声明REF 游标类型，确定REF 游标类型；
⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
语法：Type REF游标名 IS Ref Cursor Return结果集返回记录类型；
⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
语法：Type REF游标名 IS Ref Cursor；
②声明Ref 游标类型变量；
语法：变量名已声明Ref 游标类型；
③打开REF游标，关联结果集 ；
语法：Open Ref 游标类型变量 For 查询语句返回结果集；
④获取记录，操作记录；
语法：Fetch REF游标名InTo 临时记录类型变量或属性类型变量列表；
⑤关闭游标，完全释放资源；
语法：Close REF游标名；
eg1.弱类型的例子：
Declare
Type MyRefCurISRefCursor;
vRefCur MyRefCur;
vtempvarchar2(20);
Begin
Open vRefCurFor Selectenamefrom emp Where empno = 7499;
fetch vRefCur into vtemp;
dbms_output.put_line(vtemp);
ClosevRefCur;
End;
页:
[1]