Oracle Cursor详解与实例
摘要：详细介绍oracle数据库中关于游标的定义和使用。通过实例操作来深入了解cursor的用法和用处。
一：相关概念
1、concep
When Oracle Database executes aSQL statement , it stores the result set and processing information in anunnamed private SQL area . A pointer to this unnamed area , called a cursor ,let you retrieve the rows of the result set one at a time . Cursor attributesreturn information about the state of the cursor .
2、概念：
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。Cursor类型：静态游标——分为显式（explicit）游标和隐式（implicit）游标、REF游标——动态游标、是一种引用类型、类似于指针。
二：具体类型及使用
1、implicit cursor
1） explanation：Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. You can get information about this cursor from itsattributes, but you cannot control it. After the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.
2） implicit cursor（隐式游标）由系统自动打开和关闭、当我们执行一个SQL DML时、系统会自动打开一个cursor、当执行完毕之后系统会关闭cursor、我们不能直接控制cursor、但是却可以通过implicit Cursor的属性来了解操作的状态和结果、从而达到流程的控制——Cursor的属性包括：
i、SQL%ROWCOUNT整形——代表DML语句成功执行的行数
ii、SQL%FOUND布尔型——值为true时代表插入、删除、更新或查询操作成功
iii、SQL%NOTFOUND布尔型——与上面相反
v、SQL%ISOPEN布尔型——DML执行过程中为真、否则为假
3）示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
begin
update
student
set
sname=
'chy'
WHERE
sno=
'1'
;
if sql%isopen
then
dbms_output.put_line(
'cursor is opening !'
);
else
dbms_output.put_line(
'cursor is closed !'
);
end
if;
if sql%found
then
dbms_output.put_line(
'DML is successed !'
);
else
dbms_output.put_line(
'DML is failed !'
);
end
if;
if sql%notfound
then
dbms_output.put_line(
'DML is failed !'
);
else
dbms_output.put_line(
'DML is successed !'
);
end
if;
dbms_output.put_line(sql%rowcount||
' is the number of result !'
);
exception
when
no_data_found
then
dbms_output.put_line(
'Sorry No data'
);
when
too_many_rows
then
dbms_output.put_line(
'Too Many rows'
);
end
;
|
2、explicit cursor
1）explanation：PL/SQLalso lets you declare explicit cursors. An explicit cursor has a name and isassociated with a query (SQLSELECT statement)—usually one that returns multiplerows. After declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement), and close the cursor (with the CLOSE statement). After closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.
很直白的说明了显示游标的用处、以及用法。
2）explicit cursor的属性包含：
游标的属性返回值类型意义
%ROWCOUNT 整型获得FETCH语句返回的数据行数
%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型与%FOUND属性返回值相反
%ISOPEN 布尔型游标已经打开时值为真，否则为假
3）对于explicit Cursor使用分四个步骤：
a 定义游标——Cursor [ Cursor Name[param_name, param_type]] IS select xxx from xxxwhere xxx;
b 打开游标——Open [ Cursor Name[varialbe_value] ] ;
c 操作游标——Fetch [ Cursor Name ];
d 关闭游标——Close [ Cursor Name ] ;
4）具体使用显示游标,遍历循环游标步骤：
a）使用显示游标
i、声明游标：划分存储区域，注意此时并没有执行Select语句。CURSOR游标名(参数列表) [返回值类型] IS Select 语句；
ii、打开游标：执行Select语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。open游标名(参数列表);
iii、获取记录：移动游标取一条记录 fetch 游标名 into 临时记录或属性类型变量；
v、关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
close 游标名；
b）遍历循环游标
i、for循环游标
循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
……
for 变量名 In 游标名
loop
数据处理语句；
end loop；
ii、loop循环游标
……
loop
fetch 游标名 into 临时记录或属性类型变量；
exit when 游标名%notfound；
end loop；
iii、while循环
……
open 游标名
fetch 游标名into临时记录或属性类型变量；
while 游标名%foundloop
-- do something
fetch 游标名into临时记录或属性类型变量；
end loop;
……
close 游标名
5）常见显式Cursor用法：
i、使用for循环来使用cursor：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare
cursor
cur
is
select
*
from
t_user
where
age = 22;
userinfo t_user%rowtype;
begin
for
userinfo
in
cur loop
exit
when
cur%notfound;
dbms_output.put_line(
'user id : '
|| userinfo.id ||
'-'
||
'user name : '
|| userinfo.username);
end
loop;
exception
when
others
then
dbms_output.put_line(sqlerrm);
end
;
|
ii、使用fetch来使用cursor： exp2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
declare
cursor
cur
is
select
*
from
t_user
where
age = 22;
userinfo t_user%rowtype;
begin
open
cur;
loop
exit
when
cur%notfound;
fetch
cur
into
userinfo;
dbms_output.put_line(
'user id : '
|| userinfo.id ||
'-'
||
'user name : '
|| userinfo.username);
end
loop;
exception
when
others
then
dbms_output.put_line(sqlerrm);
close
cur;
end
;
|
iii、使用fetch结合while使用cursor：exp3
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
declare
cursor
cur
is
select
*
from
t_user
where
age = 23;
userinfo t_user%rowtype;
begin
open
cur;
fetch
cur
into
userinfo;
if cur%isopen
then
while cur%found loop
dbms_output.put_line(
'user id : '
|| userinfo.id ||
'-'
||
'user name : '
|| userinfo.username);
fetch
cur
into
userinfo;
end
loop;
dbms_output.put_line(
'totle result : '
|| cur%rowcount);
else
dbms_output.put_line(
'cursor is closed!'
);
end
if;
close
cur;
exception
when
others
then
dbms_output.put_line(sqlerrm);
close
cur;
end
;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
-- 给工作为CLERK的员工加薪
--one
declare
cursor
cur(c_job varchar2)
is
select
*
from
emp1
where
emp1.job=c_job
for
update
of
sal;
ef emp1%rowtype;
c_sal emp1.sal%type;
begin
for
ef
in
cur(
'CLERK'
) LOOP
EXIT
WHEN
CUR%NOTFOUND;
IF EF.SAL < 1000
THEN
C_SAL := EF.SAL*1.2;
ELSIF EF.SAL < 2000
THEN
C_SAL := EF.SAL*1.5;
ELSIF EF.SAL < 3000
THEN
C_SAL := EF.SAL*2;
ELSE
C_SAL := EF.SAL*2.2;
END
IF;
UPDATE
EMP1
SET
EMP1.SAL=C_SAL
WHERE
CURRENT
OF
CUR;
END
LOOP;
EXCEPTION
WHEN
OTHERS
THEN
dbms_output.put_line(sqlerrm);
END
;
--two
declare
cursor
cur(c_job varchar2)
is
select
*
from
emp1
where
emp1.job=c_job
for
update
of
sal;
EF emp1%rowtype;
C_SAL emp1.sal%type;
begin
open
cur(
'CLERK'
);
fetch
cur
into
EF;
while cur%found loop
EXIT
WHEN
CUR%NOTFOUND;
IF EF.SAL < 1000
THEN
C_SAL := EF.SAL*1.2;
ELSIF EF.SAL < 2000
THEN
C_SAL := EF.SAL*1.5;
ELSIF EF.SAL < 3000
THEN
C_SAL := EF.SAL*2;
ELSE
C_SAL := EF.SAL*2.2;
END
IF;
update
emp1
set
emp1.sal=C_SAL
where
current
of
cur;
fetch
cur
into
EF;
end
loop;
close
cur;
end
;
--three
declare
--define the cursor Note: the select sql is not excuted!
cursor
cur(c_job varchar2)
is
select
*
from
emp1
where
emp1.job=c_job
for
update
of
sal;
ef emp1%rowtype;
c_sal emp1.sal%type;
begin
open
cur(
'CLERK'
);
fetch
cur
into
ef;
while cur%found loop
exit
when
cur%notfound;
case
when
ef.sal < 1000
then
c_sal := ef.sal*1.2;
when
ef.sal < 2000
then
c_sal := ef.sal*1.4;
when
ef.sal < 3000
then
c_sal := ef.sal*1.6;
end
case
;
update
emp1
set
emp1.sal = c_sal
where
current
of
cur;
fetch
cur
into
ef;
end
loop;
close
cur;
end
;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
--use cursor to delect date
create
table
emp3
as
select
*
from
emp;
--delete the date of emp3 where the job is 'CLERK';
declare
cursor
cur(c_job varchar2)
is
select
*
from
emp3
where
emp3.job=c_job
for
update
;
ef emp3%rowtype;
begin
for
ef
in
cur(
'CLERK'
) loop
exit
when
cur%notfound;
delete
from
emp3
where
current
of
cur;
end
loop;
end
;
|
补充：
ref cursor会在下一个笔记中出现、这里补充一个循环时使用的判断条件if的东西。注意看下面两段代码：|
1
2
3
4
5
6
7
8
9
|
IF EF.SAL < 1000
THEN
C_SAL := EF.SAL*1.2;
ELSIF EF.SAL < 2000
THEN
C_SAL := EF.SAL*1.5;
ELSIF EF.SAL < 3000
THEN
C_SAL := EF.SAL*2;
ELSE
C_SAL := EF.SAL*2.2;
END
IF;
|
|
1
2
3
4
5
6
7
8
9
|
IF EF.SAL < 1000
THEN
C_SAL := EF.SAL*1.2;
ELSE
IF EF.SAL < 2000
THEN
C_SAL := EF.SAL*1.5;
ELSE
IF EF.SAL < 3000
THEN
C_SAL := EF.SAL*2;
ELSE
C_SAL := EF.SAL*2.2;
END
IF;
|
当我们使用多个if条件的时候写成后则就会出错、必须要写成前面的elsif来结合if多条件的情况！
扫一扫
1307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
