游标

简介

  1. 游标提供一种从表中检索数据并进行操作灵活手段,主要用在服务器上,客户端发送给服务端的sql语句,或是批处理、存储过程、触发器中的数据处理请求。游标的作用相当于指针,通过游标pl/sql可以一次查询结果集中的一行,并对改行数据进行特定的操作。

  2. 分类

  • 隐式游标
  • 显示游标
  1. 显示游标使用过程
  • 申明游标 --> 打开游标(open cur_name) --> 使用游标 --使用完-> 关闭游标(close cur_name)

申明游标

cursor cur_name [(input_parameter1..)] [return ret_type] is
select_sentences;
  1. 解析

ret_type 表示返回的值类型

input_parameter1 游标也有自己的参数传递 并在 select_sentences中使用。

打开游标

open cur_name[(para_value)];


注意: 游标也有参数,在打开游标过程中,将游标的参数传入,查询出游标所用的所有数据并存入临时表中,然后将“指针”指向第一条记录,与序列不同的是,序列开始指向是没有数据的。

读取游标

将游标打开后,即可通过 fetch cur_name into variable 读取数据。fecth完后,指针自动指向下一条记录

关闭游标

游标使用完需要关闭,已释放系统资源,

desc cur_name

游标属性

  • cur_name%found : bool查看当前指针指向位置是否有记录,没有返回空
  • cur_name%notfound : 与%found相反
  • cur_name%rowcount : 返回游标查询到的记录数
  • cur_name%isopen : 查看游标是否打开

隐式游标

在执行一个SQL语句时,Oracle会自动创建一个隐式游标,这个游标是内存中处理该语句的工作区域。 隐式游标主要处理数据操作语句(如update、delete语句)的执行结果,当然特殊情况下,也可以处理select语句的查询结构。

隐式游标属性 :

  • sql%found
  • sql%notfound
  • sql%rowcount
  • sql%isopen

如:

begin 
update emp set sal = sal*(1+0.2) where job = 'SALESMAN';
-- 若update语句影响到任何一行数据 就执行
if sql%found then dbms_output.put_line('有'||sql%rowcount||'个员工工资上调20%');
else dbms_output.put_line('没有员工工资上调');
end if;
end
--其中 sql就是update语句的默认游标。

注意; 无论是显示还是隐式游标,他们的属性总是反映最近的一条SQl语句的处理结果。因此,一个PL/SQl块中出现多个SQL语句,游标的属性只能反映出紧挨着它的上面那条SQL的处理结果。

for…in简易操作游标

无论是显示还是隐式游标,都可以通过以下方式使用游标

for rowtype in cur_name | sql | (select * from 表) loop

end loop;

注意:该种方式使用游标,自动打开游标,自动读取游标,自动关闭游标,不要手动,否则会报错。

游标进阶

for update使用

  1. for update真正理解

在多数情况下,当我们读取游标某一条记录时又要对该记录进行修改时,PL/SQL提供了进行这样处理的一种语法:

这种语法包括两部分——在游标声明部分的FOR UPDATE子句在UPDATE或DELETE语句中的WHERE CURRENT OF 子句
 
 问题在于:通常,SELECT操作将不会对正处理的行执行任何锁定设置,这使得连接到该数据库的其他会话可以改变正在选择的数据不是正在update的数据。

但是,结果集仍然是一致性的。当确定了活动集以后,在执行OPEN的时刻,ORACLE会截取下该表的一个快照。在此时刻以前所提交的任何更改操作都会在活动集中反映出来。在此时刻以后所进行的任何更改操作,即使已经提交了它们,都不会被反映出来,除非将该游标重新打开。但是使用FOR UPDATE子句,在OPEN返回以前的活动集的相应行上会加上互斥锁,这些锁会避免其他的会话对活动集中的行进行更改。直到整个事务被提交为止。 换句话说就是,游标select的记录在open游标时就确定下来了,在open之后其他事务对其数据更改,该游标都不会知晓,因为他是取了原来数据的一个复印份,后面基于该游标进行的修改也自然就是基于该复印份改了,此时就相当于一个瞎子,不知道底层数据有啥变化,反正就是闭着眼直接就改底层数据了。这样改的肯定自己不放心,最后改的数据也不会按照自己的意愿来。

此时for update的作用就来了,他是一上来就将游标open的所有记录都加上行锁,只能自己事务处理数据,自然其他事务就不能改变数据了。这样改的数据就相当于在自己眼皮子底下清清楚楚改的,绝没有第三方偷改数据。这样自然就放心了。

  1. update如果没有索引,所以update会锁表,如果加了索引,就会锁行。
  2. for update 的使用是对select的数据加锁,update操作默认情况下,数据库都会加行锁。
  3. for update 加锁对象是锁跟 游标select有关每一个表的****所有记录。而for update of 字段 是只对含该字段的表的 游标select记录加上锁
  4. 共享更新锁即行级锁,是粒度最小的锁,没有字段锁。
  5. 只要加上行级锁,就不能有第二个事务对其修改,除非 数据库reboot,数据表空间执行离线/在线处理,或者当事务正常提交、回滚时,手动rollback、commit时才可以解除行级锁。
  6. for update 加锁时机 open 游标,去锁时机是 事务自动完成或者手动commit 和 rollback 而不是 close 游标。

参考文献:oracle游标中的多表update行锁讨论

  1. 举例 :
  • 没有使用for update

create or replace procedure o1 as
r emp%rowtype;
s number;
cursor o is select * from emp ;
begin
for r in o loop
case lower( r.job)
when 'clerk' then s :=-500;
when 'salesman' then s:=-750;
when 'analyst' then s:=-1000;
else s:=-2000;
end case;
update emp set sal = sal+s where empno = r.empno;
end loop;
end;


没有使用for update 照样更新。

  • 使用for update 且提前 commit
create or replace procedure o1 as
r emp%rowtype;
s number;
cursor o is select * from emp for update ;
begin
for r in o loop
case lower( r.job)
when 'clerk' then s :=-500;
when 'salesman' then s:=-750;
when 'analyst' then s:=-1000;
else s:=-2000;
end case;
update emp set sal = sal+s where current of o;
commit;
end loop;
end;

报错:

BEGIN o1; END;
错误报告 -
ORA-01002: 提取违反顺序
ORA-06512: 在 "C##UNIV.O1", line 6
ORA-06512: 在 "C##UNIV.O1", line 6
ORA-06512: 在 line 1
01002. 00000 - "fetch out of sequence"
*Cause: This error means that a fetch has been attempted from a cursor
which is no longer valid. Note that a PL/SQL cursor loop
implicitly does fetches, and thus may also cause this error.
There are a number of possible causes for this error, including:
1) Fetching from a cursor after the last row has been retrieved
and the ORA-1403 error returned.
2) If the cursor has been opened with the FOR UPDATE clause,
fetching after a COMMIT has been issued will return the error.
3) Rebinding any placeholders in the SQL statement, then issuing
a fetch before reexecuting the statement.
*Action: 1) Do not issue a fetch statement after the last row has been
retrieved - there are no more rows to fetch.
2) Do not issue a COMMIT inside a fetch loop for a cursor
that has been opened FOR UPDATE.
3) Reexecute the statement after rebinding, then attempt to
fetch again.

大概就是 commit一次释放了所有行级锁,该游标失效。

  • 使用for update 事务自动完成。
create or replace procedure o1 as
r emp%rowtype;
s number;
cursor o is select * from emp for update ;
begin
for r in o loop
case lower( r.job)
when 'clerk' then s :=-500;
when 'salesman' then s:=-750;
when 'analyst' then s:=-1000;
else s:=-2000;
end case;
update emp set sal = sal+s where current of o;

end loop;

end;

没报错。