Oracle存储过程学习_游标CURSOR使用

游标CURSOR的使用学习

游标的类型：

1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐

式游标，名字固定叫sql。

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

3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询

的结果

一、隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息q隐式游标的属性有：

q%FOUND – SQL 语句影响了一行或多行时为 TRUE

q%NOTFOUND – SQL 语句没有影响任何行时为TRUE

q%ROWCOUNT – SQL 语句影响的行数

q%ISOPEN - 游标是否打开，始终为FALSE

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 declare

sname1 student.sname%TYPE;

begin

select sname into sname1 from student;

if sql%found then

dbms_output.put_line(sql%rowcount);

else

dbms_output.put_line('没有找到数据');

end if;

exception

when too_many_rows then

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

when no_data_found then

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

end;

显式游标：

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

游标。

显式游标的使用：

----------------------------无参数游标-------------------------------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 + 10where 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 + 10where 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 in1 .. ename_table.count loop

dbms_output.put_line(ename_table(i));

end loop;

close my_cursor;

end;

e

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

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 IS REF CURSOR

[RETURN ]; ?

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

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 完成的任务 :

生成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;

pagesize 10

page 1

分页

between (page-1)*pagesize+1and page*pagesize

select * from(

select * from (select rownum rn,t.* from test1 t)) where rn between91and100;