关于Oracle和PLSQL的学习记录12

最新推荐文章于 2025-03-10 22:19:32 发布

原创最新推荐文章于 2025-03-10 22:19:32 发布 · 170 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

64 篇文章

订阅专栏

本文深入讲解Oracle数据库中游标的基本概念、声明、打开、提取数据、关闭等操作流程，涵盖循环使用、视图中应用、整行数据提取、带参数游标、for循环优化、数据更新与删除、判断游标状态及记录行数获取等方面，是Oracle数据库开发者不可多得的实用指南。

------游标------
1.游标，是指一种能从包括多条数据记录的结果集中每次提取一条记录的机制
游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的工作行，从而可以有选择地按行采取操作
游标是映射在结果集中一行数据上的位置试题，有了游标，用户就可以访问结果集中的任意一行数据了

2.声明游标
游标在使用前必须先进行声明
声明游标必须指明目标对象
declare cursor cur_stu
is
select * from stu;
begin
dbms_output.put_line('游标cur_stu已声明');
end;
声明了一个游标cur_stu，该游标能够对stu表的搜友数据进行操作，因为is子句后面的select的语句获取了stu表的所有记录
声明一个显式游标必须在PLSQL语句块的声明部分完成，即在declare关键字之后，begin关键字之前完成

3.打开游标
游标在使用前必须打开，在Oracle中，打开游标的操作目的是激活游标声明中的查询语句并识别活动数据集
declare cursor cur_stu
is
select * from stu;
begin
open cur_stu;
dbms_output.put_line('游标cur_stu已打开');
end;
open语句后面的游标名必须是在PLSQL语句块中的declare部分被声明过的游标，不能打开还没有声明的游标

4.提取数据
declare
c_no stu.sno%type;
c_name stu.sname%type;
cursor cur_stu
is
select sno,sname from stu order by sno;
begin
open cur_stu;
fetch cur_stu into c_no,c_name;
dbms_output.put_line('学号：'||c_no||'姓名'||c_name);
fetch cur_stu into c_no,c_name;
dbms_output.put_line('学号：'||c_no||'姓名'||c_name);
fetch cur_stu into c_no,c_name;
dbms_output.put_line('学号：'||c_no||'姓名'||c_name);
end;

从游标中提取数据以fetch命令实现，fetch命令以每次一条记录的方式取回活动集的记录
如果游标中select语句后加的是"*"通配符，则提取数据时要为该表的每个列指定一个PLSQL变量用于获取值

5.关闭游标
declare cursor cur_stu
is
select * from stu;
begin
dbms_output.put_line('游标cur_stu已声明');
open cur_stu;
dbms_output.put_line('游标cur_stu被打开');
close cur_stu;
dbms_output.put_line('游标cur_stu被关闭');
end;

6.循环语句中的游标使用
declare
c_no stu.sno%type;
c_name stu.sname%type;
cursor cur_stu
is
select sno,sname from stu order by sno;
begin
open cur_stu;
loop
   fetch cur_stu into c_no,c_name;
   exit when cur_stu%notfound;
   dbms_output.put_line('学号：'||c_no||'姓名'||c_name);
   end loop;
close cur_stu;
end;
循环条件中cur_stu%notfound表示游标指针的取值为空，如果需要表示指针值不为空可以使用cur_stu%found表示

7.视图中游标的使用
declare
v_name v_grade.姓名%type;
v_course v_grade.课程%type;
v_score v_grade.成绩%type;
cursor cur_v_grade
is
select 姓名,课程,成绩 from v_grade where 成绩 >= 60;
begin
open cur_v_grade;
loop
fetch cur_v_grade into v_name,v_course,v_score;
exit when cur_v_grade%notfound;
dbms_output.put_line('姓名'||v_name||'课程'||v_course||'成绩'||v_score);
end loop;
close cur_v_grade;
end;
当视图中的数据发生变化的时候，已打开的游标中的数据不会随之改变，因为其是静态的，只有用户重新打开游标后数据才会更新

8.提取整行数据
declare
stu_row stu%rowtype;
cursor cur_stu
is
begin
open cur_stu;
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
dbms_output.put_line(stu_row.sno||''||stu_row.sname);
end loop;
close cur_stu;
end;

9.用游标名定义记录变量
declare
cursor cur_stu
is
select * from stu where sdept = '12计算机';
stu_row cur_stu%rowtype;
begin
open cur_stu;
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
dbms_output.put_line(stu_row.sno||''||stu_row.sname);
end loop;
close cur_stu;
end;

如果一个游标已经被定义为具有stu表所有列的数据时，这个游标已经可以代替表进行某些定义，因为此时该游标的数据结构与表完全相同
使用游标代替表定义记录变量时，游标一定要先于记录变量被定义，且需要具有表的结构，否则将出现异常

10.使用带参数的游标
declare
cursor cur_stu(dept varchar2)
is
select * from stu where sdept = dept;
stu_row cur_stu%rowtype;
begin
open cur_stu('12 工商管理');
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
dbms_output.put_line(stu_row.sno||''||stu_row.sname);
end loop;
close cur_stu;
end;

11.使用游标for循环
在Oracle PLSQL语句块中使用游标都遵循下面的步骤：
打开游标-->开始循环-->从游标中取值-->检查哪一行被返回-->处理-->关闭循环-->关闭游标
for循环不需要遵循以上步骤
用于for循环的游标按照正常的声明方式声明，其优点在于不需要显式的打开、关闭、提取数据以及定义存放数据的变量等操作
declare cursor cur_stu
is
select sno,sname,sdept from stu;
stu_row stu%rowtype;
begin
for stu_row in cur_stu
loop
dbms_output.put_line(stu_row.sno||''||stu_row.sname||''||stu_row.sdept);
end loop;
end;

12.游标for循环中使用子查询
declare
stu_row stu%rowtype;
begin
for stu_row in (select sno,sname,sdept from stu)
loop
dbms_output.put_line(stu_row.sno||''||stu_row.sname||''||stu_row.sdept);
end loop;
end;

13.使用游标更新数据
使用游标更新数据需要手动为目标数据加上锁
declare cursor cur_stu
is
select * from stu for update;
stu_row stu%rowtype;
begin
open cur_stu;
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
update stu set sage = sage + 1 where current of cur_stu;
end loop;
close cur_stu;
end;
如果要使用游标更新数据，在声明游标时select语句后必须带有for update子句，用于在游标结果集数据加行锁，以防止其他用户在相应行上执行更新或删除操作；在提取了游标数据之后，为了更新当前游标所在的行数据，必须在update语句中引用where current of子句

14.使用游标删除数据
declare cursor cur_stu
is
select * from stu for update;
stu_row stu%rowtype;
begin
open cur_stu;
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
if stu_row.sno = '2222' then
delete from stu where current of cur_stu;
end if;
end loop;
close cur_stu;
end;
当声明游标的select语句引用到多张表时，需要使用of子句以确定哪些表要加锁，如果没有of子句，则会在select语句锁引用的全部表上加锁，此外，当表被锁定时，在for update子句后加上nowait子句可以避免等待锁

15.判断游标是否打开
当用户使用open打开游标时，如果遇到Oracle系统错误的时候，游标可能没有被成功打开，此时使用该游标提取数据将出现异常
declare cursor cur_stu
is
select * from stu;
begin
open cur_stu;
if cur_stu%isopen = false then
dbms_output.put_line('游标cur_stu未打开，请先打开游标');
else
dbms_output.put_line('游标cur_stu已打开，请继续操作');
end if;
close cur_stu;
end;

16.获取游标读取的记录行数
declare cursor cur_stu
is
select * from stu where sdept = '12计算机';
c_sum integer;
stu_row stu%rowtype;
begin
open cur_stu;
loop
fetch cur_stu into stu_row;
exit when cur_stu%notfound;
end loop;
c_sum := cur_stu%rowcount;
dbms_output.put_line("游标中的记录行数：'||c_sum);
close cur_stu;
end;

在Oracle中，游标有4个属性，分别为%found、%notfound、%isopen、%rowcount

17.游标中的子查询
declare cursor cur_stu
is
select * from stu where sdept in(select sdept from stu where sname = '陈斌');
stu_row stu%rowtype;
begin
open cur_stu;
loop
   fetch cur_stu into stu_row;
   exit when cur_stu%notfound;
   dbms_output.put_line(stu_row.sno||''||stu_row.sname||''||stu_row.sdept);
   end loop;
close cur_stu;
end;