目录
我们为什么需要游标?
当我们在PL/SQL代码块中,对SELECT查询结果进行处理时,若结果只有一行,我们可以使用select into 语句来获取查询结果,以便在查询结果的基础上进行操作。
但查询结果为多行记录时,是不可以使用select into语句的(若使用,会报错,所以现在也有人提出少使用select into语句,因为无法保证结果只有一行)。那么对于多行查询结果的处理,我们便缺少了方法,我们会很自然的想到用循环语句来遍历查询结果。但如何遍历呢?这时就需要使用PL/SQL中提供的游标机制了。
游标简介
若对C语言的指针熟悉,我们可以把游标和指针类比着看。游标就是指向select查询语句结果的内存块的一个指针。
游标在定义时,即使是显式定义,也不会立刻获取查询结果的数据,只有当游标被打开时,游标的查询语句才会被执行,然后将查询的结果保存到内存中。对打开的游标进行循环,像移动指针一样,来遍历获取查询结果。
打开的游标使用完毕后,需要进行关闭,否则查询结果一直保存于内存中,用户不断开连接,该块内存一直不会释放,占用资源,至使服务缓慢,甚至奔溃。
游标分类与定义
显式游标与隐式游标
根据游标定义方式的不同,可以区分为显式游标与隐式游标。
- 显式游标:使用关键字
CURSOR
来显式得定义一个游标,并在使用时需要先打开游标。 - 隐式游标:由Oracle自动地为查询语句创建的隐式游标,隐式游标没有名称。
显式游标
在使用显式游标前,需要定义游标:为游标命名,并指定游标的select查询语句。定义语法如下:
cursor 游标名 [ (入参) ]
[return 返回类型]
is 查询语句
[for update [of (一个或多个列名)] [nowait] ]
其中被 [ ] 起来的均为可选项。最简单也是最常用的游标定义示例:
declare
cursor user_cursor
is select * from sys_user r where r.dept_no = 1111;
begin
null;
end;
游标定义中,return用来限定游标的查询结果类型,一般为某张表的rowtype,如user%rowtype
,有了return类型限制后,游标相对固定,但即使不限定也并不会影响使用,索引日常开发过程中并不会加上return。
游标在打开时也可以输入参数,来动态获取想要的游标数据,带入参的游标示例:
declare
cursor user_cursor_param (p_age in number,p_dept_no in number)
is select * from sys_user r where r.age >= p_age and r.dept_no = p_dept_no;
begin
null;
end;
而for update则比较重要了,是游标使用中事务控制与锁的关键。有for update指定的游标,在被打开时,查询结果的数据会被锁定,不再允许其他事务对这些行数据进行DML操作,是行级锁。
当只想锁定行数据中某些字段时,接上of 字段名,多个字段间用 , 分隔开,即可实现字段锁。
当本事务想对这些数据上锁时,发现已经有人捷足先登,为已锁状态了,则本事务会一直等待,直到目标数据为未锁定作态才继续向下执行。而加上nowait则是不再等待,若已被上锁则头也不回地跳过~
隐式游标
在Oracle中,DML语句及select into语句被执行时,都会隐式得创建对应游标,并可通过SQL关键字来访问游标的属性,我们称这种Oracle自动创建的游标为隐式游标。
特别且常用的是,在for循环中,我们对查询结果的遍历时oracle可以自动帮我们做很多事情,甚至可以省去对游标的显式定义,这也是一种隐式游标,具体使用在下文游标操作中介绍。
静态游标与动态游标
由于有游标变量的存在,可以在使用时在为游标绑定具体查询语句,灵活性大大增强,我们称这种游标变量为动态游标。
而一般的显式游标是在声明时就指定了查询语句,不可改变,我们称这种游标为静态游标。
使用游标
打开、关闭游标
当游标定以后,游标绑定的查询语句并未立马执行,而是需要等到我们打开游标时,才会执行。若游标包含FOR UPDATE
语句,游标在打开前并不会锁住记录,游标打开后锁的影响才会反映出来,直到游标关闭。
打开游标的语法如下:
OPEN 游标名 [ (入参) ];
对游标声明的案例中的两个游标,打开示例为:
OPEN user_cursor;
OPEN user_cursor_param(1111,0);
游标打开后,查询的结果集会放在运行内存中,当游标使用完成后应立即关闭,以释放资源。但若尝试关闭一个尚未打开的游标,将会抛出异常。关闭游标的语法如下:
CLOSE 游标名;
游标属性
不论是显式游标还是隐式游标,都拥有四大游标属性:%ISOPEN、%FOUND、%NOTFOUND以及%ROWCOUNT。显式游标获取这些属性值时,需带上游标名作为前缀;隐式游标则使用SQL作为游标前缀。
%ISOPEN
%ISOPEN属性判断游标是否为已打开状态,若游标已打开则返回Ture,否则返回False,测试示例:
declare
cursor user_cursor is
select * from sys_user r where r.dept_no = 1111;
begin
--声明游标后,尚未打开时
IF (user_cursor%ISOPEN) THEN
dbms_output.put_line('游标已打开');
ELSE
dbms_output.put_line('游标未打开');
END IF;
--打开游标
OPEN user_cursor;
IF (user_cursor