oracle 游标使用详解程序员

游标 游标的简介: /

逐行处理查询结果,以编程的方式访问数据

游标的类型:

1,隐式游标:在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标,名字固定叫sql。

2,显式游标:显式游标用于处理返回多行的查询。

3,REF 游标:REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

隐式游标:


q在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭,其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 q隐式游标的属性有: q%FOUND – SQL 语句影响了一行或多行时为 TRUE q%NOTFOUND – SQL 语句没有影响任何行时为TRUE q%ROWCOUNT – SQL 语句影响的行数 q%ISOPEN – 游标是否打开,始终为FALSE

?
1
2
3
4
5
6
7
8
begin
  
update student s set s.sage = s.sage +
10
;
  
if
sql %FOUND   then
    
dbms_output.put_line(
‘这次更新了’
|| sql% rowcount );
    
else
      
dbms_output.put_line (
‘一行也没有更新’
);
      
end
if
;
      
end;

 

在select中有两个中比较常见的异常: 1. NO_DATA_FOUND 2. TOO_MANY_ROWS

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> declare
  

sname1 student.sname%TYPE;
  

begin
  
4   
select sname into sname1 from student;
  
5   
if
sql%found then
  
6     
dbms_output.put_line(sql%rowcount);
  
7   
else
  
8     
dbms_output.put_line(
‘没有找到数据’
);
  
9     
end
if
;
 
10     
exception
 
11       
when too_many_rows then
 
12         
dbms_output.put_line(
‘查找的行记录多于1行’
);
 
13        
when no_data_found then
 
14           
dbms_output.put_line(
‘未找到匹配的行’
);
 
15      
end;
 
16 
/
查找的行记录多于
1
PL/SQL procedure successfully completed
 
SQL>

 

显式游标:


/
sqlserver与oracle的不同之处在于: 最后sqlserver会deallocate 丢弃游标,而oracle只有前面四步: 声明游标、打开游标、使用游标读取记录、关闭游标。
显式游标的使用:

?
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
————————————无参数游标——————————-
declare
  
sname varchar2(
20
); –声明变量
  
cursor student_cursor is select sname from student ; –声明游标
  
begin
    
open student_cursor;–打开游标
      
fetch student_cursor into sname ;–让游标指针往下移动
    
while
student_cursor%found –判断游标指针是否指向某行记录
      
loop–遍历
        
dbms_output.put_line (
‘学生姓名’
||sname );
        
fetch student_cursor into sname;
      
end loop;
       
close student_cursor;
      
end;
      
————————————有参数游标——————————-
declare
sname student.sname%type;
sno student.sno%type;
cursor student_cursor (input_sno number) is select s.sname, s.sno from student s where s.sno > input_sno; –声明带参数的游标
begin
  
sno := &请输入学号 ;–要求从客户端输入参数值,
“&”
相当于占位符;
  
open student_cursor( sno); –打开游标,并且传递参数
  
fetch student_cursor into sname, sno; –移动游标
  
while
student_cursor% found
    
loop
      
dbms_output.put_line (
‘学号为:’
||sno ||
‘姓名为:’
||sname );
      
fetch student_cursor into sname,sno;
      
end loop;
       
close student_cursor;
      
end;
————————————循环游标——————————-   
— Created on
18

1
月-
15
by 永文
declare
stu1 student%rowtype ;–这里也不需要定义变量来接收fetch到的值
cursor student_cursor is select * from student ;
begin
 
open student_cursor; –这里不需要开启游标
  
for
stu1 in student_cursor
    
loop
      
dbms_output.put_line (
‘学生学号:’
||stu1.sno ||
‘学生姓名:’
||stu1.sname );
      
fetch student_cursor into stu1;–也不需要fetch了
      
end loop;
    
close student_cursor;  –这里也不需要关闭游标
      
end;
      
————————————使用游标更新行——————————-  
  
declare
  
stu1 student%rowtype ;
  
cursor student_cursor is select * from student s where s.sno in (
2
,
3
)
for
update;–创建更新游标
  
begin
   
open student_cursor;
   
fetch student_cursor into stu1;–移动游标
   
while
student_cursor%found –遍历游标,判断是否指向某个值
     
loop
       
update student set sage = sage +
10
where current of student_cursor;–通过游标中的信息更新数据
        
fetch student_cursor into stu1;–移动游标
       
end loop;
       
close student_cursor;
       
end;
 
  
declare
  
stu1 student%rowtype ;
  
cursor student_cursor is select * from student s where s.sno in (
2
,
3
)
for
update;–创建更新游标
  
begin
   
open student_cursor;
  
— fetch student_cursor into stu1;–移动游标
  

while
student_cursor%found–遍历游标,判断是否指向某个值
     
loop
         
fetch student_cursor into stu1 ;–移动游标
         
exit when student_cursor %notfound ;
       
update student set sage = sage +
10
where current of student_cursor;–通过游标中的信息更新数据
       
end loop;
       
close student_cursor;
       
end;
————————————使用fetch … bulk collect into——————————-  
declare
  
cursor   my_cursor is select ename from emp where deptno=
10
; –声明游标
  
type   ename_table_type is table of varchar2 (
10
);–定义一种表类型,表中的属性列为varchar2类型
  
ename_table  ename_table_type;–通过上面定义的类型来定义变量
begin
  
open   my_cursor; –打开游标
  
fetch my_cursor bulk collect into   ename_table; –移动游标
  
for  
i in
1
..ename_table.count  loop
     
dbms_output.put_line(ename_table(i));
  
end   loop ;
  
close my_cursor;
end;
 
 
———————————–显示游标题目————————————–
 
SQL > select * from student ;
        
XH XM
———- ———-
         
1
A
         
2
B
         
3
C
         
4
D
 
SQL > select * from address ;
        
XH ZZ
———- ———-
         
2
郑州
         
1
开封
         
3
洛阳
         
4
新乡
         
完成的任务 :给表student添加一列zz ,是varchar2 (
10
)类型;
再从address中,将zz字段的数值取出来,对应的插入到
student新增的zz列中。
即:得到的结果:student表中,是:
          
XH XM         ZZ
         
— ———- ——
          
1
A          开封
          
2
B          郑州
          
3
C          洛阳
          
4
D          新乡
 
 
declare
stu1 student %rowtype ;
add1 address %rowtype ;
cursor student_cursor is select * from student
for
update;–声明更新游标
cursor address_cursor is select * from address ;–声明游标
begin
  
open student_cursor ;–打开游标
  
fetch student_cursor into stu1;–移动游标
  
while
student_cursor% found–判断游标是否指向某条记录
    
loop
      
open address_cursor ;–打开另外一个游标
      
fetch address_cursor into add1 ;–移动游标
      
while
address_cursor %found–判断游标是否指向某条记录
        
loop
          
if
add1.xh = stu1.xh then–判断两个游标所指向的记录中xh的值是否相等
            
update student s set s.zz = add1.zz where current of student_cursor;–假如相等就更新游标所指向的记录值
            
end
if
;
            
fetch address_cursor into add1 ;–移动游标
            
end loop;
            
close address_cursor ;–关闭游标
            
fetch student_cursor into stu1 ;–移动游标
            
end loop;
            
close student_cursor ;–关闭游标
            
end;

 

REF游标也叫动态游标:


qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤: q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明 REF 游标类型的语法为:

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

 
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
75
76
77
78
———————————–ref游标———————————
declare
type ref_cursor  is ref cursor; –声明一个ref游标类型
tab_cursor ref_cursor ;–声明一个ref游标
sname student.xm %type ;
sno student.xh %type ;
tab_name varchar2 (
20
);
begin
  
tab_name :=
‘&tab_name’
; –接收客户输入的表明
  
if
tab_name =
‘student’
then
    
open tab_cursor
for
select xh ,xm  from student ; –打开ref游标
    
fetch tab_cursor into sno ,sname ;–移动游标
    
while
tab_cursor %found
      
loop
        
dbms_output.put_line (
‘学号:’
||sno ||
‘姓名:’
||sname );
        
fetch tab_cursor into sno ,sname ;
        
end loop;
        
close tab_cursor ;
        
else
          
dbms_output.put_line (
‘没有找到你想要找的表数据信息’
);
          
end
if
;
        
end;
 
  
———————————–ref游标题目———————————
SQL > select * from student ;
        
XH KC
———- ———-
         
1
语文
         
1
数学
         
1
英语
         
1
历史
         
2
语文
         
2
数学
         
2
英语
         
3
语文
         
3
英语
9
rows selected
 
SQL >      
完成的任务 :
生成student2表 (xh number, kc  varchar2 (
50
));
对应于每一个学生,求出他的总的选课记录,把每个学生的选课记录插入到student2表中。
即,student2中的结果如下:
                      
XH KC
                 
— ——————————————-
                       
1
语文数学英语历史
                       
2
语文数学英语
                       
3
语文英语
 
create table student2 (xh number, kc varchar2 (
50
));
 
declare
kcs varchar2 (
50
);
kc varchar2 (
50
);
type ref_cursor is ref cursor; –声明一个ref游标类型
stu_cursor ref_cursor ;–定义一个ref游标类型的变量
type tab_type is table of number; –声明一个table类型
tab_xh tab_type ;–定义一个表类型的变量
cursor cursor_xh is select distinct( xh) from student; –声明一个游标
begin
  
open cursor_xh; –打开游标
  
fetch cursor_xh bulk collect into tab_xh; –提取数据到表中
  
for
i in
1
.. tab_xh.count
    
loop
      
kcs :=

;
      
open stu_cursor
for
select kc from student s where s.xh = tab_xh(i ); –打开ref游标
      
fetch stu_cursor into kc ; –移动游标
      
while
stu_cursor %found
        
loop
          
kcs := kc ||kcs ; –连接字符串使用||而不是+
          
fetch stu_cursor into kc ; –移动游标
          
end loop;
          
insert into student2 (xh , kc ) values( i, kcs);
          
close stu_cursor ;
      
end loop;
      
close cursor_xh ;
      
end;

declare
  type typ_cur_nd is ref cursor return ndqj%rowtype;
  cur_nd typ_cur_nd;
procedure pro_nd_emp (nd_emp in typ_cur_nd) is
  nn ndqj%rowtype;  
 begin
  dbms_output.put_line(‘———-‘);
  loop
    fetch nd_emp into nn;
   exit when nd_emp%notfound;
   dbms_output.put_line(‘ndqj:’||nn.kjnd||nn.kjqj);
  end loop;
 end;
 
 begin
  open cur_nd for select * from ndqj where rownum<10;
  pro_nd_emp(cur_nd);
  close cur_nd;
 
  open cur_nd for select * from ndqj where kjnd=’2015′;
  pro_nd_emp(cur_nd);
  close cur_nd;
 end;  
/

 

 

游标的类型:
  1、静态游标(不检测数据行的变化)
  2、动态游标(反映所有数据行的改变)
  3、仅向前游标(不支持滚动)
  4、键集游标(能反映修改,但不能准确反映插入、删除)

游标使用顺序:
   1、定义游标
   2、打开游标
   3、使用游标
   4、关闭游标
   5、释放游标

Transact-SQL:
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS] 
  for selet语句   [for  update[of 列名[,列名]]
 注:LOCAL 局部游标     GLOBAL 全局游标
     FORWARD_ONLY 仅向前  SCROLL 滚动
     STATIC 静态  KEYSET 键集 DYNAMIC 动态
     READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

获取游标的数据
  FETCH [[NEXT | PRIOR | FIRST | LAST |
  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
  From ] 游标名 [into 变量]
  注:
    NEXT  下一行  PRIOR  上一行  FIRST 第一行
    LAST  最后一行  ABSOLUTE n 第n行
    RELATIVE n 当前位置开始的第n行
    into 变量 把当前行的各字段值赋值给变量

游标状态变量:
    @@fetch_status  游标状态
         0 成功  -1 失败  -2 丢失
    @@cursor_rows 游标中结果集中的行数
        n 行数 -1 游标是动态的  0 空集游标
操作游标的当前行:
   current of 游标名

 

-- 声明游标;CURSOR cursor_name IS select_statement

--For 循环游标
--(1)定义游标
--(2)定义游标变量
--(3)使用for循环来使用这个游标
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ,该类型为游标c_emp中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;



--Fetch游标
--使用的时候必须要明确的打开和关闭

declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量
c_row c_job%rowtype;
begin
open c_job;
loop
--提取一行数据到c_row
fetch c_job into c_row;
--判读是否提取到值,没取到值就退出
--取到值c_job%notfound 是false
--取不到值c_job%notfound 是true
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
--关闭游标
close c_job;
end;

--1:任意执行一个update操作,用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
begin
update emp set ENAME='ALEARK' WHERE EMPNO=7469;
if sql%isopen then
dbms_output.put_line('Openging');
else
dbms_output.put_line('closing');
end if;
if sql%found then
dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行
else
dbms_output.put_line('Sorry');
end if;
if sql%notfound then
dbms_output.put_line('Also Sorry');
else
dbms_output.put_line('Haha');
end if;
dbms_output.put_line(sql%rowcount);
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;
declare
empNumber emp.EMPNO%TYPE;
empName emp.ENAME%TYPE;
begin
if sql%isopen then
dbms_output.put_line('Cursor is opinging');
else
dbms_output.put_line('Cursor is Close');
end if;
if sql%notfound then
dbms_output.put_line('No Value');
else
dbms_output.put_line(empNumber);
end if;
dbms_output.put_line(sql%rowcount);
dbms_output.put_line('-------------');

select EMPNO,ENAME into empNumber,empName from emp where EMPNO=7499;
dbms_output.put_line(sql%rowcount);

if sql%isopen then
dbms_output.put_line('Cursor is opinging');
else
dbms_output.put_line('Cursor is Closing');
end if;
if sql%notfound then
dbms_output.put_line('No Value');
else
dbms_output.put_line(empNumber);
end if;
exception
when no_data_found then
dbms_output.put_line('No Value');
when too_many_rows then
dbms_output.put_line('too many rows');
end;

Oracle 游标使用全解

 

原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/7148.html

(0)
上一篇 2021年7月17日
下一篇 2021年7月17日

相关推荐

发表回复

登录后才能评论