oracle游标操作

使用的原始表

学生表

create table Student
(
Sno char(10) primary key comment '学号',
Sname varchar(20) comment '姓名',
Ssex char(2) comment '性别',
Sage smallint comment '年龄', 
Sdept varchar(20) comment '系'
);

课程表

create table Course
(
Cno char(10) primary key comment '课程号',
Cname varchar(20) comment '课程名',
Cpno char(10) comment '先行课程号',
Ccredit smallint comment '学分',
snumber int comment '最大选课人数'
);

选课信息表

create table SC
(
Sno char(10) comment '学号' ,
Cno char(10) comment '课程号',
Grade smallint comment '成绩',
CONSTRAINT   PK_SC   PRIMARY   KEY (Sno,Cno) --创建两个主键--
);

一个游标就是一个指向保存有多行SQL查询结果集的工作区的句柄。Oracle打开一个工作区来保存多行查询的结果集。

游标分显式游标和隐式游标 。

游标操作

• 声明游标

cursor cursor_name is select statement;

1. 给游标命名
2. 将一个查询与游标关联起来

• 打开游标

open cursor_name;

打开游标将激活或查询并识别活动集。但是并没有真正的取回记录。此时游标指针指向活动集的第一条记录。

**注意：**游标被打开后，直到关闭之前，取回的活动集的所有数据都是静态的。所以一般只有需要时才打开它。

不能打开已经打开的游标，否则会产生异常。

• 获取游标属性

--布尔型属性，如果还有记录则返回true(注意只有获取一条或以上内容之后才会返回true)
%found  
--它的值和%found属性的值相反
%notfound
--布尔型属性，当光标打开时返回true
%isopen
--数字型属性，返回已经从光标中读取的记录数
%rowcount

• 从光标中获取数据

fetch cursor_name into record_list;

每取回一条数据以后，游标的指针就移向活动集的下一条记录。

取回类型:

--字段类型
fetch cursor_name into v1,v2,...,vn;
--光标类型
fetch cursor_name into cursor_name%rowtype;
--表类型(类似于结构体)
fetch cursor_name into table_name%rowtype;

• 关闭游标

close cursor_name;

关闭游标以后系统才会释放资源。

游标的循环操作

loop

/*
查询CS系的学生的基本信息。对齐打印输出。
*/
declare
  cursor cursor_stu is select * from Student where sdept='CS';
  stu Student%rowtype;
begin
  open cursor_stu;
  loop
  	fetch cursor_stu into stu;
  	exit when cursor_stu%notfound;
    dbms_output.put_line(lpad(stu.sno,5,' ')||lpad(stu.sname,5,' ')||lpad(stu.ssex,5,' ')||lpad(stu.sage,5,' ')||lpad(stu.sdept,5,' '));
  end loop;
  close cursor_stu;  --关闭游标
end;

使用loop时退出更灵活，需要手动使游标下移。

while

/*
查询CS系的学生的基本信息。对齐打印输出。
*/
declare
  cursor cursor_stu is select * from Student where sdept='CS';
  stu Student%rowtype;
begin
  open cursor_stu;
  fetch cursor_stu into stu;
  while cursor_stu%found loop
    dbms_output.put_line(lpad(stu.sno,5,' ')||lpad(stu.sname,5,' ')||lpad(stu.ssex,5,' ')||lpad(stu.sage,5,' ')||lpad(stu.sdept,5,' '));
    fetch cursor_stu into stu;
  end loop;
  close cursor_stu;  --关闭游标
end;

使用while需要打开关闭游标。

在%found使用之前需要先fetch一下，这样才能判断为true。

注意：在没有fetch的情况下%found和%notfound都返回false。

一行数据处理之后还需再次fetch才能到下一行数据。

for

for循环的游标不需要打开和关闭。因为for…in就包含了这些操作。出了for循环游标的操作就无效了。

for循环也不需要fetch，他会自动到下一行数据。

/*
查询CS系的学生的基本信息。对齐打印输出。
*/
declare
  cursor cursor_stu is select * from Student where sdept='CS';
begin
  for I in cursor_stu loop
    dbms_output.put_line(lpad(I.sno,5,' ')||lpad(I.sname,5,' ')||lpad(I.ssex,5,' ')||lpad(I.sage,5,' ')||lpad(I.sdept,5,' '));
  end loop;
end;

带参数的游标

创建游标的时候是形式参数(name type)，此时参数的类型长度是不定的，因为形参的长度去解决于实际参数。

打开游标的时候是实际参数(value)，输入的是参数的实际值。

declare
	cursor stu_cursor(vsno char) is
	select sname from Student where sno=vsno;
	v_sname Student.sname%type;
begin
    open stu_cursor('001');
    loop
        fetch stu_cursor into v_sname;
        exit when stu_cursor%notfound;
        dbms_output.put_line('学生姓名: '||v_sname);
    end loop;
end; 

select for update

可以用SELECT FOR UPDATE游标来更新由游标检索到的那些记录行。后面可以使用of来选择进行加锁的列。

select for update适用于高并发的场所。

它的原理是：一锁二判三更新

工作机制：

1. 首先为游标检索到的行加锁。
2. 对游标标识的每一行，游标会更改该行的列。

declare
  cursor cs1 is select * from SC for update of grade;  --对grade加锁
  v_grade SC.Grade%type;
begin
  for I in cs1 loop
    if I.Grade is null then
      update SC set grade=60 where current of cs1;  --修改游标当前行
      dbms_output.put_line('学号为'||rtrim(I.sno)||'的同学的'||rtrim(I.cno)||'课程成绩已改为60！');
    end if;
  end loop;
  commit;
end;

使用 select for update之后不必为更新操作对表格进行第二次取值。

**注意：**游标的循环外部必须commit，否则不会释放锁。

隐式游标

隐式游标又叫作SQL游标。

在执行SQL语句后，oracle服务器将自动创建一个隐式游标。通过游标可获取SQL语句的执行结果，以及游标的状态信息。

在作DML语句时，会自动的打开一个SQL游标指向相应的工作区。
隐式游标在DML语句操作完毕后自动关闭，所以不需要，也不能用OPEN，FETCH，CLOSE语句操作隐式游标。

属性：

• sql%found——说明DML是否影响到行
• sql%isopen——是否打开，这个值永远是false，因为DML执行完后会自动关闭SQL游标
• sql%notfound——说明DML在修改行时是否失败
• sql%rowcount——说明DML执行完后影响的行数

begin
  update Student set sname='hh' where sno='000';
  if sql%notfound then
    dbms_output.put_line('没有修改');
  else
    dbms_output.put_line('修改');
  end if;
end;

begin
  for I in (select * from Student) loop
    dbms_output.put_line(I.sno||I.sname);
  end loop;
end;

游标变量——动态游标

使用游标变量可以再存储过程之间传递结果集。一个游标变量可以和多个查询相关联。

游标变量的定义有两步：定义一个ref cursor类型的指针和该类型的变量名。(ref关键字表示定义了一个cursor类型的指针)

1. 定义游标：

• 弱游标

没有指定游标返回类型的游标变量。

type rc is ref cursor;

• 强游标

为游标指定返回类型的变量。

type rc is ref cursor return sc%rowtype;

2. 打开游标

open v_rc for 查找子句

eg:

declare
  type rc is ref cursor;  --定义了一个rc的游标指针
  v_rc rc;    --指针变量
  rs SC%rowtype;
begin
  open v_rc for select * from SC;  --指定查询语句
  loop
    fetch v_rc into rs;
    exit when v_rc%notfound;
    dbms_output.put_line(rs.sno||rs.cno||rs.grade);
  end loop;
end;

PL/SQL中提供了一个sys_refcursor类型，该类型定义了一个普通的弱游标类型。

declare
  v_rc sys_refcursor;    --指针变量
  rs SC%rowtype;
begin
  open v_rc for select * from SC;  --指定查询语句
  loop
    fetch v_rc into rs;
    exit when v_rc%notfound;
    dbms_output.put_line(rs.sno||rs.cno||rs.grade);
  end loop;
end;

总结

1. 游标是指向查询的“指针”，当定义静态游标时并没有打开查询，只有打开游标（Open）时才执行查询，一旦游标被打开，查询结果不再改变，若原始表中改变了数据，只需要关闭游标再打开游标，查询将被刷新。
2. 静态游标的定义在Declare中，在Begin后打开，在使用完后关闭
3. 动态游标是在Declare中定义为游标类型（ref cursor），在Begin后使用Open Cursor_name for select statement 指定查询并打开游标
4. 隐式游标（SQL游标）只能在DML语句后使用，它是自动打开和关闭的。
5. 读取游标中的内容使用fetch语句，利用循环遍历游标中的数据。