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

begin

updatestudent ssets.sage=s.sage+10;

ifsql%FOUNDthen

dbms_output.put_line('这次更新了'||sql%rowcount);

else

dbms_output.put_line('一行也没有更新');

endif;

end;

在select中有两个中比较常见的异常：

1. NO_DATA_FOUND

2. TOO_MANY_ROWS

SQL> declare

2 sname1 student.sname%TYPE;

3 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 whentoo_many_rowsthen

12 dbms_output.put_line('查找的行记录多于1行');

13 whenno_data_foundthen

14 dbms_output.put_line('未找到匹配的行');

15 end;

16 /

查找的行记录多于1行

PL/SQL procedure successfully completed

SQL>

显式游标：

---

sqlserver与oracle的不同之处在于：

最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步：

声明游标、打开游标、使用游标读取记录、关闭游标。

显式游标的使用：

------------------------------------无参数游标-------------------------------

declare

snamevarchar2(20);--声明变量

cursorstudent_cursorisselectsnamefromstudent;--声明游标

begin

openstudent_cursor;--打开游标

fetchstudent_cursorintosname;--让游标指针往下移动

whilestudent_cursor%found--判断游标指针是否指向某行记录

loop--遍历

dbms_output.put_line('学生姓名'||sname);

fetchstudent_cursorintosname;

endloop;

closestudent_cursor;

end;

------------------------------------有参数游标-------------------------------

declare

sname student.sname%type;

sno student.sno%type;

cursorstudent_cursor(input_snonumber)isselects.sname,s.snofromstudent swheres.sno>input_sno;--声明带参数的游标

begin

sno:=&请输入学号;--要求从客户端输入参数值，"&"相当于占位符；

openstudent_cursor(sno);--打开游标，并且传递参数

fetchstudent_cursorintosname,sno;--移动游标

whilestudent_cursor%found

loop

dbms_output.put_line('学号为：'||sno||'姓名为：'||sname);

fetchstudent_cursorintosname,sno;

endloop;

closestudent_cursor;

end;

------------------------------------循环游标-------------------------------

-- Created on 18-1月-15 by 永文

declare

stu1 student%rowtype;--这里也不需要定义变量来接收fetch到的值

cursorstudent_cursorisselect*fromstudent;

begin

openstudent_cursor;--这里不需要开启游标

forstu1instudent_cursor

loop

dbms_output.put_line('学生学号：'||stu1.sno||'学生姓名：'||stu1.sname);

fetchstudent_cursorintostu1;--也不需要fetch了

endloop;

closestudent_cursor;--这里也不需要关闭游标

end;

------------------------------------使用游标更新行-------------------------------

declare

stu1 student%rowtype;

cursorstudent_cursorisselect*fromstudent swheres.snoin(2,3)forupdate;--创建更新游标

begin

openstudent_cursor;

fetchstudent_cursorintostu1;--移动游标

whilestudent_cursor%found--遍历游标，判断是否指向某个值

loop

updatestudentsetsage=sage+10wherecurrentofstudent_cursor;--通过游标中的信息更新数据

fetchstudent_cursorintostu1;--移动游标

endloop;

closestudent_cursor;

end;

declare

stu1 student%rowtype;

cursorstudent_cursorisselect*fromstudent swheres.snoin(2,3)forupdate;--创建更新游标

begin

openstudent_cursor;

-- fetch student_cursor into stu1;--移动游标

-- while student_cursor%found--遍历游标，判断是否指向某个值

loop

fetchstudent_cursorintostu1;--移动游标

exitwhenstudent_cursor%notfound;

updatestudentsetsage=sage+10wherecurrentofstudent_cursor;--通过游标中的信息更新数据

endloop;

closestudent_cursor;

end;

------------------------------------使用fetch ... bulk collect into-------------------------------

declare

cursor my_cursorisselectenamefromempwheredeptno=10;--声明游标

type ename_table_typeistableofvarchar2(10);--定义一种表类型，表中的属性列为varchar2类型

ename_table ename_table_type;--通过上面定义的类型来定义变量

begin

open my_cursor;--打开游标

fetchmy_cursorbulkcollectinto ename_table;--移动游标

for iin1..ename_table.countloop

dbms_output.put_line(ename_table(i));

endloop;

closemy_cursor;

end;

-----------------------------------显示游标题目--------------------------------------

SQL>select*fromstudent;

XH XM

---------- ----------

1A

2B

3C

4D

SQL>select*fromaddress;

XH ZZ

---------- ----------

2郑州

1开封

3洛阳

4新乡

完成的任务:给表student添加一列zz,是varchar2(10)类型；

再从address中，将zz字段的数值取出来，对应的插入到

student新增的zz列中。

即：得到的结果：student表中，是：

XH XM ZZ

-- ---------- ------

1A 开封

2B 郑州

3C 洛阳

4D 新乡

declare

stu1 student%rowtype;

add1 address%rowtype;

cursorstudent_cursorisselect*fromstudentforupdate;--声明更新游标

cursoraddress_cursorisselect*fromaddress;--声明游标

begin

openstudent_cursor;--打开游标

fetchstudent_cursorintostu1;--移动游标

whilestudent_cursor%found--判断游标是否指向某条记录

loop

openaddress_cursor;--打开另外一个游标

fetchaddress_cursorintoadd1;--移动游标

whileaddress_cursor%found--判断游标是否指向某条记录

loop

ifadd1.xh=stu1.xhthen--判断两个游标所指向的记录中xh的值是否相等

updatestudent ssets.zz=add1.zzwherecurrentofstudent_cursor;--假如相等就更新游标所指向的记录值

endif;

fetchaddress_cursorintoadd1;--移动游标

endloop;

closeaddress_cursor;--关闭游标

fetchstudent_cursorintostu1;--移动游标

endloop;

closestudent_cursor;--关闭游标

end;

REF游标也叫动态游标：

---

qREF游标和游标变量用于处理运行时动态执行的SQL查询

q创建游标变量需要两个步骤：

q声明REF游标类型

q声明REF游标类型的变量

q用于声明REF游标类型的语法为：

TYPE <ref_cursor_name> IS REFCURSOR

[RETURN <return_type>];

-----------------------------------ref游标---------------------------------

declare

typeref_cursorisrefcursor;--声明一个ref游标类型

tab_cursor ref_cursor;--声明一个ref游标

sname student.xm%type;

sno student.xh%type;

tab_namevarchar2(20);

begin

tab_name:='&tab_name';--接收客户输入的表明

iftab_name='student'then

opentab_cursorforselectxh,xmfromstudent;--打开ref游标

fetchtab_cursorintosno,sname;--移动游标

whiletab_cursor%found

loop

dbms_output.put_line('学号：'||sno||'姓名：'||sname);

fetchtab_cursorintosno,sname;

endloop;

closetab_cursor;

else

dbms_output.put_line('没有找到你想要找的表数据信息');

endif;

end;

-----------------------------------ref游标题目---------------------------------

SQL>select*fromstudent;

XH KC

---------- ----------

1语文

1数学

1英语

1历史

2语文

2数学

2英语

3语文

3英语

9rowsselected

SQL>

完成的任务:

生成student2表(xhnumber,kcvarchar2(50))；

对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。

即，student2中的结果如下：

XH KC

--- -------------------------------------------

1语文数学英语历史

2语文数学英语

3语文英语

createtablestudent2(xhnumber,kcvarchar2(50));

declare

kcsvarchar2(50);

kcvarchar2(50);

typeref_cursorisrefcursor;--声明一个ref游标类型

stu_cursor ref_cursor;--定义一个ref游标类型的变量

typetab_typeistableofnumber;--声明一个table类型

tab_xh tab_type;--定义一个表类型的变量

cursorcursor_xhisselectdistinct(xh)fromstudent;--声明一个游标

begin

opencursor_xh;--打开游标

fetchcursor_xhbulkcollectintotab_xh;--提取数据到表中

foriin1.. tab_xh.count

loop

kcs:='';

openstu_cursorforselectkcfromstudent swheres.xh=tab_xh(i);--打开ref游标

fetchstu_cursorintokc;--移动游标

whilestu_cursor%found

loop

kcs:=kc||kcs;--连接字符串使用||而不是+

fetchstu_cursorintokc;--移动游标

endloop;

insertintostudent2(xh,kc)values(i,kcs);

closestu_cursor;

endloop;

closecursor_xh;

end;