代码涉及的表及其结构:
游标的定义:
一个游标就是一个指向保存有多行SQL查询结果集的工作区的句柄。Oracle打开一个工作区来保存多行查询的结果集。游标就是给这个工作区命的名称,并能用于处理由多行查询而返回的记录行。
当服务器上执行了一个查询后,查询返回的记录集存放在上下文相关区,通过游标上的操作可以把这些记录检索到客户端的应用程序。游标分显式游标和隐式游标 。
1. 声明游标
声明游标的语句格式如下:
Cursor cursor_name IS SELECT statement;
声明游标完成了下面两个目的:
(1)给游标命名;
(2)将一个查询与游标关联起来。
2.打开游标
打开游标的语句格式如下:
OPEN cursor_name;
打开游标将激活查询并识别活动集,可是在执行游标取回命令之前,并没有真正取回记录。OPEN命令还初始化了游标指针,使其指向活动集的第一条记录。
3.从游标中取回数据
FETCH命令以每次一条记录的方式取回活动集中的记录。FETCH命令每执行一次,游标前进到活动集的下一条记录。
FETCH命令的语句格式如下:
Fetch cursor_name into v1,v2,…,vn;
Fetch cursor_name into cursor_name%rowtype;
Fetch cursor_name into table_name%rowtype
FETCH cursor_name INTO record_list;
执行FETCH命令后,活动集中的结果被取回到PL/SQL变量中,以便在PL/SQL块中使用。每取回一条记录,游标的指针就移向活动集的下一条记录。
4.关闭游标
CLOSE语句关闭以前打开的游标,使得活动集不确定。
CLOSE语句的格式:
CLOSE cursor_name;
一个简单的使用游标的例子
DECLARE
CURSOR csr_s IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s csr_s%rowtype;
BEGIN
OPEN csr_s;
Fetch csr_s into v_s;
Close csr_s;
END;
5.遍历游标
loop...end loop:
DECLARE
cursor c1 is
select sname,cno
from student,sc
where student.sno=sc.sno
order by 1;
v_s c1%rowtype;
begin
open c1;
dbms_output.put_line('学生选课信息');
dbms_output.put_line('------------------------');
dbms_output.put_line(rpad('学生姓名',20,' ')||' '||rpad('所选课程号',30,' '));
dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
loop
fetch c1 into v_s;
exit when c1%notfound;
dbms_output.put_line(rpad(v_s.sname,20,' ')||' '||
rpad(v_s.cno,30,' '));
end loop;
close c1;
end;
while...end loop:
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
OPEN c1;
dbms_output.put_line('学生选课信息');
dbms_output.put_line('------------------------');
dbms_output.put_line(rpad('学生姓名',20,' ')||' '||rpad('所选课程号',30,' '));
dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
FETCH c1 INTO v_s;
WHILE (c1%FOUND) LOOP
dbms_output.put_line(rpad(v_s.sname,20,' ')||' '||
rpad(v_s.cno,30,' '));
FETCH c1 INTO v_s;
END LOOP;
CLOSE c1;
END;
for...end loop:
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
BEGIN
dbms_output.put_line('学生选课信息');
dbms_output.put_line('------------------------');
dbms_output.put_line(rpad('学生姓名',20,' ')||' '||rpad('所选课程号',30,' '));
dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
FOR idx IN c1 LOOP
dbms_output.put_line(rpad(idx.sname,20,' ')||' '||
rpad(idx.cno,30,' '));
END LOOP;
END;
使用rowcount:(%ROWCOUNT属性是递增的)
FOR idx IN c1 LOOP
dbms_output.put_line(rpad(idx.sname,20,' ')||' '||rpad(idx.cno,30,' '));
num_total_rows :=c1%ROWCOUNT;
END LOOP;
IF num_total_rows >0 THEN
dbms_output.new_line;
dbms_output.put_line('总选课数 = '||to_char(num_total_rows));
END IF;
6. 带参数的游标:定义的时候形如:course c1(v_sno varchar2) IS select statement;
使用:形如:open c1('001'); 循环遍历... close;
或者:for ind in c1('001') loop ... end loop;
7. select for update
工作机制:
1. 首先为游标检索到的行加锁。
2. 对游标标识的每一行,游标会更改该行的特定列。(无需再次扫描表格—与UPDATE语句对比)
Create table s1 as select * from student where 1=2;
BEGIN
INSERT INTO s1
SELECT *FROM student;
COMMIT;
END;
/
--使用select for update
DECLARE
CURSOR csr_1 IS
SELECT * FROM s1 FOR UPDATE OF sname;
v_sname VARCHAR2(20);
BEGIN
FOR idx IN csr_1 LOOP
v_sname :=UPPER(idx.sname);
UPDATE s1
SET sname =v_sname
WHERE CURRENT OF csr_1;
END LOOP;
COMMIT;
END;
8. 隐式游标
隐式游标又叫作SQL游标。
在作DML语句时,会自动的打开一个SQL游标指向相应的工作区。
隐式游标在DML语句操作完毕后自动关闭,所以不需要,也不能用OPEN,FETCH,CLOSE语句操作隐式游标。
隐式游标的属性:SQL%FOUND 说明DML是否影响到行(在定义任何DML之前使用该属性,其值都为NULL)
SQL%ISOPEN 是否打开,这个值永远是FALSE,因为DML执行完后会自动关闭SQL游标。
SQL%NOTFOUND 说明DML在修改行时是否失败。(在定义任何DML之前使用该属性,其值都为NULL)
SQL%ROWCOUNT 指明DML执行完后影响的行数。(初值为0)
使用游标变量可以在存储过程之间传递结果集。在运行时,一个游标变量能与多个查询相关联。
游标变量的定义有两步:定义一个REF CURSOR类型的指针和该类型的变量名。
Type rc is ref curcor;
V_rc rc;
其中REF关键字表示定义了一个CURSOR类型的指针。
REF TYPE 表示定义了指向TYPE类型的指针。
没有指明游标返回类型的游标变量称为弱REF CURSOR类型。它能指向任何一个具有任意多列的SELECT查询。
也可以为游标指明返回类型,这样的游标变量称为强REF CURSOR。
Type rc is ref cursor return sc%rowtype
使用游标变量:
DECLARE
TYPE rc is REF CURSOR;
v_rc rc;
v_sc sc%ROWTYPE;
BEGIN
open v_rc for select * from sc;
dbms_output.put_line('选课信息');
dbms_output.put_line('------------------------');
dbms_output.put_line('学号'||' '||rpad('课程号',20,' '));
dbms_output.put_line(rpad('-',4,'-')||' '||rpad('-',20,'-'));
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||' '||rpad(v_sc.cno,20,' '));
END LOOP;
CLOSE v_rc;
END;
使用系统弱游标类型:SYS_REFCURSOR
DECLARE
v_rc SYS_REFCURSOR;
v_sc sc%ROWTYPE;
BEGIN
OPEN v_rc FOR SELECT *from sc;
dbms_output.put_line('选课信息');
dbms_output.put_line('------------------------');
dbms_output.put_line('学号'||' '||rpad('课程号',20,' '));
dbms_output.put_line(rpad('-',4,'-')||' '||rpad('-',20,'-'));
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||' '||rpad(v_sc.cno,20,' '));
END LOOP;
CLOSE v_rc;
END;
10.小结
游标是指向查询的“指针”,当定义静态游标时并没有打开查询,只有打开游标(Open)时才执行查询,一旦游标被打开,查询结果不再改变,若原始表中改变了数据,只需要关闭游标再打开游标,查询将被刷新。
静态游标的定义在Declare中,在Begin后打开,在使用完后关闭
动态游标是在Declare中定义为游标类型(ref cursor),在Begin后使用Open Cursor_name for select statement 指定查询并打开游标
隐式游标(SQL游标)只能在DML语句后使用,它是自动打开和关闭的。
读取游标中的内容使用fetch语句,利用循环遍历游标中的数据。