游标的概念
在ORACLE中, 游标实际上就是在内存中开辟的一个专用SQL区的工作区
用于存储执行SQL查询命令返回的信息
PL/SQL中有两种类型的光标,显示光标和隐式光标
对于返回多行的查询,为了单个地处理每一行,必须显示地定义一个光标
显示光标的操作
该操作包括定义光标,打开光标,取数据,关闭光标
可用CURSOR OPEN，FETCH和CLOSE控制光标
1．定义游标
在PL／SQL块的说明部分定义游标
定义形式：
CURSOR 游标名称 IS
SELECT 语句；
DECLARE
cursor c1 is
select ename,deptno
from emp
where sal>2000;
...
BEGIN
NULL;
END;
定义游标时可以带参数(形参),给查询传递需要的参数值
游
标的参数在其查询中,可以在出现常数的地方使用
定义时只要把参数名及其数据类型与游标名称一起说明
传递的参数值可用来在查询中控制查询的结果
带参数的游标定义形式：
CURSOR 游标名(参数[,参数]…) IS
SELECT 语句;
参数名[IN] 数据类型[{ := | DEFAULT}值]
游标参数的使用范围局限于游标即仅在游标定义中指定的
查询命令内引用。游标参数的值在打开（OPEN）时提供
CURSOR C2(median numbre) IS
SELECT job,ename
from emp
where sal>median;
2. 打开游标
在PL/SQL块中执行语句部分BEGIN之后,使用时要先打开游标 (用OPEN 命令) 。
打开游标的形式:
OPEN 游标名[(实参数)];
OPEN C1 ;
OPEN C2 (300)；
执行打开游标语句,系统执行游标定义中的查询命令, 标识活
动集
对带参数的游标,OPEN语句对于游标说明中每一个形式参 数必须有相应的实在参数
若形式参数具有缺省值, 则可不需要有相应的实在参数
游标形参必须是IN参数, 所以不能将值返回给实在参数
在OPEN语句中实在参数与形式参数之间的联系有两种方法
位置表示法：实参与形参个数, 位置一一对应:
OPEN C2 （300）；
命名表示法：以形参=>实参的形式表示:
ey: CURSOR C3(my_name char(10),my_comm number) IS
SELECT ...
OPEN C3('ATTLEY',300);
OPEN C3(my_name=>'ATTLEY',my_comm=>300);
3. 利用游标取值
游标被打开后,要用FETCH 语句把查询的记录 取到 PL/SQL
程序块的变量中
语句形式：
FETCH 游标名 INTO 变量名表;
FETCH语句检索工作区(活动集)中的行,每次只能取一行。
每执行一次FETCH语句, 游标指针下移一行,等待取下一行记录
变量名表中变量的数量和数据类型与查询返回的列的数量与类型相匹配, 位置对应
FETCH C1 INTO my_ename,my_deptno;
注意：
（1） 使用FETCH语句前, 必须先打开游标。
（2）在FETCH 语句执行过程中, 游标指针只能逐行下移，不能回退。
4. 关闭游标
CLOSE 游标名称
处理完游标工作区中的记录行后, 就以关闭游标了, 游标关闭后,系统释放与该游标相关的所有资源,并使该游标的工作区失效
若此时对游标再执行FETCH语句则出错 但可重新打开游标
对未关闭
的 游标执行打开也会发生错误
ey:取出部门号为10的雇员姓名和工资
DECLARE
v_no emp.deptno%tyoe:=10;
v_sal emp.sal%type;
v_ename emp.ename%type;
n number(3);
CURSOR C1 IS
select ename,sal from emp
where deptno=v_no;
BEGIN
select count(*) into n from emp
where deptno=v_no;
open c1;
for i in 1..n loop
fetch ci into v_ename,v_sal;
dbms_output.put_line(v_ename || to_char(v_sal));
end loop;
close c1;
end;
declare
my_sal emp.sal%TYPE;
my_job emp.job%TYPE;
factor integer:=2;
cursor c1 is
select factor *sal from emp
where job=my_job;
begin
…
my_job :=’MANAGER’;
open c1;
loop
fetch c1 into my_sal;
exit when c1%NOTFOUND;
…
factor:=factor+1;
end loop;
close c1;
end;
游标的属性:
每一个显示定义的游标有四种属性
％NOTFOUND ， ％FOUND
％ROWCOUNT， ％ISOPEN
利用游标属性可存取有关多行查询执行的信
注意仅在过程性语句中可使用游标属性息
而在SQL语句中不能使用
(1) ％NOTFOUND 属性
布尔型属性,若最后FETCH语句执行,没有返回行,则值为
TRUE
(2) ％FOUND 属性
布尔型属性,如果最后一个FETCH语句执行时返回行,
％FOUND为TRUE
在第一个FETCH语句执行前,％FOUND计算得到为NULL值
(3) ％ISOPEN属性
布尔型属性,当一个游标是打开时,该属性值为TRUE,否则
为FALSE
对游标工作区检索记录必须先打开游标,否则导致系统错
误。可用该属性判断游标是否已打开
(4) ％ROWCOUNT
数字型属性,返回当前已从游标工作区中读取的记录数
该属性返回游标打开后,至今由FETCH语句已获取的行数
在刚打开游标时,该属性值为0,所以第一个FETCH语句执行之前,％ROWCOUND返回 0。
ey:
loop
fetch c1 into my_ename,my_deptno;
if c1%rowcount>10 then
...
end if;
end loop;
在一个PL／SQL块中,可打开多个游标
1: 在数据库表data_table中,存放有实验数据,现对实验的数据进行处理, 将计算结果存入库表temp中。
declare
num1 data_table.n1%type;
num2 date_table.n2%type;
num3 data_table.n3%type;
result temp.col1%type;
cursor c1 is
select n1,n2,n3 from data_table
where exper_num=1;
begin
open c1;
loop
fetch c1 into num1,num2,num3;
exit when c1%NOTFOUND;
result:=num2/(num1+num3);
insert into temp values (result,null,null);
end loop;
close c1;
commit;
end;
2: 查询10号部门雇员工资,当查询到第一个工资大于$2000的
雇员时停止,并按工资少于$2000的雇员人数在TMP表中生成 相同个数的雇员号
DECLARE
v_deptno emp.deptno%type:=10;
v_sal emp.sal%type;
cursor c1 is
select sal from emp
where deptno=v_deptno;
order by sal ASC;
BEGIN
open c1;
loop
fetch c1 into v_sal
exit when v_sal>=2000;
end loop;
for i in 1..c1%rowcount loop
insert into tmp(deptno,empnp)
values(10,8000+i*100);
end loop;
close c1;
commit work;
END;
3: 查询10号部门所有雇员姓名、工资,并插入到一个临时表
TMP 中
DECLARE
v_deptno emp.deptno%type:=10;
cursor c1 is
select ename,sal from emp
where deptno=v_deptno;
emp_rec c1%rowtype;
BEGIN
open c1;
fetch c1 into emp_rec;
while c1%found loop
insert into tmp(ename,sal)
values(emp_rec.ename,emp_rec.sal);
fetch c1 into emp_rec;
end loop;
close c1;
commit work;
END;
sql>select * from tmp;
隐式光标
ORACLE在处理每一个不与显示说明光标相关的SQL
语句时，隐式地打开一光标
PL／SQL可以以“SQL“引用最近的隐式光标
在程序中不能用OPEN,FETCH,CLOSE控制隐式光标
但可利用光标属性存取最近执行的SQL语句的有关信息
SQL光标与显示光标有相同的四种属性
SQL光标属性值总是涉及最后执行的SQL语句
这些语句
包括有insert, update, delete和 select into语句
隐式光标四种属性类似显示光标属性, 但不同之处是：
％ROWCOUNT属性返回由 insert, update或 delete所影响的行数以及由select into所选择的行数。
注意：
select into返回的行数不能多于一行,如果多于一行,将引
起预定义例外too_many_rows。
%ISOPEN属性：
oracle在执行每一个相关的SQL语句后, 自动地关闭SQL
光标, 所以SQL光标的%isopen 属性总是FALSE。
三、游标中FOR循环的使用
当使用游标FOR循环时,系统隐式地说明它的循环控制
变量为一个记录变量,
执行FOR语句时系统自动打开游标，重复地从游标工作区中读取记录行放置到记录变量的字
段中
在所有记录行处理完成或被中断退出循环时,自动关闭游标
游标FOR循环语法形式：
FOR 循环计数器 IN 游标名 LOOP
.........
END LOOP；
declare
result temp.col1%type;
cursor c1 is
select n1,n2,n3
from data_table
where exper_num=1;
begin
for c1rec IN C1 loop
result:=c1rec.n2/(c1rec.n1+c1rec.n3);
insert into temp
values (result,null,null);
end loop;
commit;
end;
其中,c1rec为系统隐式说明为一记录型变量
等价于 c1rec c1%ROWTYPE
它的各字段可存储由游标c1所获取的全部列值
该记录变量
仅定义在循环内部,循环体中的语句序列是对满足游标查询的
每一行执行一次
declare
v_dno dept.deptno%type:=10;
cursor c1 is
select ename,sal from emp
where deptno=v_dno;
emp_rec c1%rowtype;
begin
for emp_rec in c1 loop;
insert into tmp(ename,sal)
values(emp_rec.ename,emp_rec.sal);
end loop;
commit work;
end;
declare
cursor c1 is
select * from emp
where rownum<=10
order by ename;
begin
for crec in c1 loop
dbms_output.put_line(crec.ename);
end loop;
end;
若不用游标FOR循环,PL／SQL代码
declare
cursor c1 is
select * from emp
where ROWNUM<=10
order by ename;
v_rec emp%rowtype;
begin
open c1;
fetch c1 into v_rec;
while c1%found loop
dbms_output.put_line(
to_ char(c1%rowcount)||‘ ’||v_rec.ename);
fetch c1 into v_rec.ename;
end loop;
close c1;
end;
若用带参数的游标，PL／SQL程序块代码
declare
cursor c1(pattern varchar2) is
select * from emp
where ename like pattern ||’%’ and rownunt<=10
order by ename;
begin
for crec in c1(‘MAR’) loop
dbms_output.put_line (crec.ename);
end loop;
end;
游标使用户可以在PL／SQL程序块中先执行一个查询,然后检索查询所得的记录
在程序块的说明部分定义游标并提供要执行的查询
在程序块的执行部分,需先打开游标,接着读取记录,最后关闭游标
用游标FOR循环处理一个游标工作区记录时,可以把打开游标的过程和读取记录的过程合并在一起
带参数游标提供了更大的灵活性并提高了游标在类似查 询中的重用率
当一个查询返回多条记录时必需使用显示游标
只要有可能,请在代码中使用带参数的游标,这样可重用这些游标,会降低必要的开支量。
四.游标的where current of子句
如果需要对游标选择的行或者列进行更新或者删除，则游标定义语句中必须使用for update 选项
其作用为：
迫使Oracle锁定游标结果集的行，防止其他事务处理更新或 删除相同的行，直到当前事务提交或回滚为止
语法格式:
Select ….from ….for update [of column[,column]…..][nowait]
如果在游标中使用了for update 子句，则在delete 和update 语句中可以使用where current of<cursor_name>子句
以修改或删除游标结果集当前行所对应的表中数据。
带有 for update 子句的游标
declare
cursor sal_cursor is
select sal from emp where deptno=30
for update of sal nowait;
begin
for emp_record in sal_cursor loop
update emp set sal=emp_record.sal*1.1
where current of sal_cursor;
end loop;
end;
五.游标变量(REF CURSOR)
1.游标变量是动态的，它不与任何特定的查询绑定在一起。
2.可以为任意兼容的查询打开游标变量，从而提高更好的灵活性。
创建游标变量(REF CURSOR)的步骤
1.定义Ref cursor类型，即引用游标类型
2.声明这种游标类型的变量。
强类型游标 （有返回值类型）
TYPE DEPT_CUR IS REF CURSOR
RUTURN dept%ROWTYPE;
cur1 DEPT_CUR;
open cur1 for
select * from dept where deptno=20;
弱类型游标 （没有返回类型）
TYPE MYCUR IS REF CURSOR;
游标和游标变量的异同:
游标是数据库中一个命名的工作区,当游标被声明后,他就与一个固定的SQL想关联,
在编译时刻是已知的,是静态的.它永远指向一个相同的查询工作区.
游标变量可以在运行时刻与不同的SQL语句关联,在运行时可以取不同的SQL语句.
它可以引用不同的工作区.
浙公网安备 33010602011771号