Oracle中的游标的原理和使用详解

左手的ㄟ右手 2022-04-04 03:37 333阅读 0赞

# Oracle中的游标的原理和使用详解 #

2016年06月20日 23:20:14 [彖爻之辞][Link 1] 阅读数：8055更多

个人分类： [\--------1.1 ORACLE][--------1.1 ORACLE]

![Center][]

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

游标的类型：

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

2、显式游标：显式游标用于处理返回多行的查询。  
3、REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果  
隐式游标：  
在PL/SQL中使用DML语句时自动创建隐式游标，隐式游标自动声明、打开和关闭，其名为 SQL，通过检查隐式游标的属性可以获得最近执行的 DML 语句的信息，隐式游标的属性有： %FOUND – SQL 语句影响了一行或多行时为 TRUE，%NOTFOUND – SQL 语句没有影响任何行时为TRUE，%ROWCOUNT – SQL 语句影响的行数，%ISOPEN - 游标是否打开，始终为FALSE

1.  `BEGIN`
2.  `UPDATE t_bjqk SET fBL = fBL - 2 WHERE fBJDM='1461';`
3.  `IF SQL%FOUND THEN`
4.  `dbms_output.put_line('这次更新了' || SQL%ROWCOUNT);`
5.  `ELSE`
6.  `dbms_output.put_line('一行也没有更新' );`
7.  `END IF;`
8.  `END;`
9.  `--SELECT * FROM t_bjqk;`

1.  `在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS`
2.  `SQL> declare`
3.  `2 sname1 student.sname%TYPE;`
4.  `3 begin`
5.  `4 select sname into sname1 from student;`
6.  `5 if sql%found then`
7.  `6 dbms_output.put_line(sql%rowcount);`
8.  `7 else`
9.  `8 dbms_output.put_line('没有找到数据');`
10. `9 end if;`
11. `10 exception`
12. `11 when too_many_rows then`
13. `12 dbms_output.put_line('查找的行记录多于1行');`
14. `13 when no_data_found then`
15. `14 dbms_output.put_line('未找到匹配的行');`
16. `15 end;`
17. `16 /`
18. `查找的行记录多于1行`
19. `PL/SQL procedure successfully completed`
20.  
21. `SQL>`

显式游标：

![Center 1][]

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

1.  `declare`
2.  `sname varchar2( 20); --声明变量`
3.  `cursor student_cursor is select sname from student ; --声明游标`
4.  `begin`
5.  `open student_cursor; --打开游标`
6.  `fetch student_cursor into sname; --让游标指针往下移动`
7.  `while student_cursor%found --判断游标指针是否指向某行记录`
8.  `loop --遍历`
9.  `dbms_output.put_line ('学生姓名' ||sname );`
10. `fetch student_cursor into sname;`
11. `end loop;`
12. `close student_cursor;`
13. `end;`
14.  
15. `------------------------------------有参数游标-------------------------------`
16. `declare`
17. `sname student.sname%type;`
18. `sno student.sno%type;`
19. `cursor student_cursor (input_sno number) is`
20. `select s.sname, s.sno from student s where s.sno > input_sno; --声明带参数的游标`
21. `begin`
22. `sno := &请输入学号; --要求从客户端输入参数值，"&"相当于占位符；`
23. `open student_cursor( sno); --打开游标，并且传递参数`
24. `fetch student_cursor into sname, sno; --移动游标`
25. `while student_cursor% found`
26. `loop`
27. `dbms_output.put_line ('学号为：' ||sno ||'姓名为：' ||sname );`
28. `fetch student_cursor into sname,sno;`
29. `end loop;`
30. `close student_cursor;`
31. `end;`
32.  
33.  
34. `------------------------------------循环游标-------------------------------`
35. `-- Created on 18-1月-15 by 永文`
36. `declare`
37. `stu1 student%rowtype ; --这里也不需要定义变量来接收fetch到的值`
38. `cursor student_cursor is select * from student ;`
39. `begin`
40. `open student_cursor; --这里不需要开启游标`
41. `for stu1 in student_cursor`
42. `loop`
43. `dbms_output.put_line ('学生学号：' ||stu1.sno ||'学生姓名：' ||stu1.sname );`
44. `fetch student_cursor into stu1; --也不需要fetch了`
45. `end loop;`
46. `close student_cursor; --这里也不需要关闭游标`
47. `end;`
48.  
49.  
50. `------------------------------------使用游标更新行-------------------------------`
51. `declare`
52. `stu1 student%rowtype ;`
53. `cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标`
54. `begin`
55. `open student_cursor;`
56. `fetch student_cursor into stu1;--移动游标`
57. `while student_cursor%found --遍历游标，判断是否指向某个值`
58. `loop`
59. `update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据`
60. `fetch student_cursor into stu1;--移动游标`
61. `end loop;`
62. `close student_cursor;`
63. `end;`
64.  
65.  
66. `declare`
67. `stu1 student%rowtype ;`
68. `cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标`
69. `begin`
70. `open student_cursor;`
71. `-- fetch student_cursor into stu1;--移动游标`
72. `-- while student_cursor%found--遍历游标，判断是否指向某个值`
73. `loop`
74. `fetch student_cursor into stu1 ;--移动游标`
75. `exit when student_cursor %notfound ;`
76. `update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据`
77. `end loop;`
78. `close student_cursor;`
79. `end;`
80.  
81.  
82. `------------------------------------使用fetch ... bulk collect into-------------------------------`
83. `declare`
84. `cursor my_cursor is select ename from emp where deptno= 10; --声明游标`
85. `type ename_table_type is table of varchar2 (10 );--定义一种表类型，表中的属性列为varchar2类型`
86. `ename_table ename_table_type;--通过上面定义的类型来定义变量`
87. `begin`
88. `open my_cursor; --打开游标`
89. `fetch my_cursor bulk collect into ename_table; --移动游标`
90. `for i in 1 ..ename_table.count loop`
91. `dbms_output.put_line(ename_table(i));`
92. `end loop ;`
93. `close my_cursor;`
94. `end;`
95.  
96.  
97. `-----------------------------------显示游标题目--------------------------------------`
98.  
99. `SQL > select * from student ;`
100. `XH XM`
101. `---------- ----------`
102. `1 A`
103. `2 B`
104. `3 C`
105. `4 D`
106.  
107. `SQL > select * from address ;`
108. `XH ZZ`
109. `---------- ----------`
110. `2 郑州`
111. `1 开封`
112. `3 洛阳`
113. `4 新乡`
114.  
115. `完成的任务 :给表student添加一列zz ,是varchar2 (10 )类型；`
116. `再从address中，将zz字段的数值取出来，对应的插入到`
117. `student新增的zz列中。`
118. `即：得到的结果：student表中，是：`
119. `XH XM ZZ`
120. `-- ---------- ------`
121. `1 A 开封`
122. `2 B 郑州`
123. `3 C 洛阳`
124. `4 D 新乡`
125.  
126.  
127. `declare`
128. `stu1 student %rowtype ;`
129. `add1 address %rowtype ;`
130. `cursor student_cursor is select * from student for update; --声明更新游标`
131. `cursor address_cursor is select * from address ; --声明游标`
132. `begin`
133. `open student_cursor ; --打开游标`
134. `fetch student_cursor into stu1; --移动游标`
135. `while student_cursor% found--判断游标是否指向某条记录`
136. `loop`
137. `open address_cursor ; --打开另外一个游标`
138. `fetch address_cursor into add1 ; --移动游标`
139. `while address_cursor %found--判断游标是否指向某条记录`
140. `loop`
141. `if add1.xh = stu1.xh then--判断两个游标所指向的记录中xh的值是否相等`
142. `update student s set s.zz = add1.zz where current of student_cursor; --假如相等就更新游标所指向的记录值`
143. `end if;`
144. `fetch address_cursor into add1 ; --移动游标`
145. `end loop;`
146. `close address_cursor ; --关闭游标`
147. `fetch student_cursor into stu1 ; --移动游标`
148. `end loop;`
149. `close student_cursor ; --关闭游标`
150. `end;`

REF游标也叫动态游标：  
qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤： q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明 REF 游标类型的语法为：  
TYPE <ref\_cursor\_name> IS REF CURSOR  
\[RETURN <return\_type>\];

1.  `-----------------------------------ref游标---------------------------------`
2.  `declare`
3.  `type ref_cursor is ref cursor; --声明一个ref游标类型`
4.  `tab_cursor ref_cursor ;--声明一个ref游标`
5.  `sname student.xm %type ;`
6.  `sno student.xh %type ;`
7.  `tab_name varchar2 (20 );`
8.  `begin`
9.  `tab_name := '&tab_name'; --接收客户输入的表明`
10. `if tab_name = 'student' then`
11. `open tab_cursor for select xh ,xm from student ; --打开ref游标`
12. `fetch tab_cursor into sno ,sname ;--移动游标`
13. `while tab_cursor %found`
14. `loop`
15. `dbms_output.put_line ('学号：' ||sno ||'姓名：' ||sname );`
16. `fetch tab_cursor into sno ,sname ;`
17. `end loop;`
18. `close tab_cursor ;`
19. `else`
20. `dbms_output.put_line ('没有找到你想要找的表数据信息' );`
21. `end if;`
22. `end;`
23.  
24.  
25. `-----------------------------------ref游标题目---------------------------------`
26. `SQL > select * from student ;`
27. `XH KC`
28. `---------- ----------`
29. `1 语文`
30. `1 数学`
31. `1 英语`
32. `1 历史`
33. `2 语文`
34. `2 数学`
35. `2 英语`
36. `3 语文`
37. `3 英语`
38. `9 rows selected`
39.  
40. `SQL >`
41. `完成的任务 :`
42. `生成student2表 (xh number, kc varchar2 (50 ))；`
43. `对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。`
44. `即，student2中的结果如下：`
45. `XH KC`
46. `--- -------------------------------------------`
47. `1 语文数学英语历史`
48. `2 语文数学英语`
49. `3 语文英语`
50.  
51. `create table student2 (xh number, kc varchar2 (50 ));`
52.  
53. `declare`
54. `kcs varchar2 (50 );`
55. `kc varchar2 (50 );`
56. `type ref_cursor is ref cursor; --声明一个ref游标类型`
57. `stu_cursor ref_cursor ;--定义一个ref游标类型的变量`
58. `type tab_type is table of number; --声明一个table类型`
59. `tab_xh tab_type ;--定义一个表类型的变量`
60. `cursor cursor_xh is select distinct( xh) from student; --声明一个游标`
61. `begin`
62. `open cursor_xh; --打开游标`
63. `fetch cursor_xh bulk collect into tab_xh; --提取数据到表中`
64. `for i in 1 .. tab_xh.count`
65. `loop`
66. `kcs :='' ;`
67. `open stu_cursor for select kc from student s where s.xh = tab_xh(i ); --打开ref游标`
68. `fetch stu_cursor into kc ; --移动游标`
69. `while stu_cursor %found`
70. `loop`
71. `kcs := kc ||kcs ; --连接字符串使用||而不是+`
72. `fetch stu_cursor into kc ; --移动游标`
73. `end loop;`
74. `insert into student2 (xh , kc ) values( i, kcs);`
75. `close stu_cursor ;`
76. `end loop;`
77. `close cursor_xh ;`
78. `end;`

[https://blog.csdn.net/paul50060049/article/details/51724019][https_blog.csdn.net_paul50060049_article_details_51724019]

[Link 1]: https://me.csdn.net/paul50060049
[--------1.1 ORACLE]: https://blog.csdn.net/paul50060049/article/category/6271435
[Center]: /images/20220404/ea40dfa5baf94edca353aeecddedb9d7.png
[Center 1]: /images/20220404/52bbbdf98aa940aaa30d864e0d882d0d.png
[https_blog.csdn.net_paul50060049_article_details_51724019]: https://blog.csdn.net/paul50060049/article/details/51724019