6 游标
6、1 简述
为了处理数据,内存中会分配一块区域,即上下文,游标即是指向上下文区域的句柄或者指针,通过它可以控制上下文和语句处理变化。
set serveroutput on size 1000000;
DECLARE
v_SID stu.sid%TYPE;
v_NAME stu.name%TYPE;
v_SEX stu.sex%TYPE:='M';
CURSOR c_Students IS
SELECT sid, name FROM stu WHERE sex= v_SEX;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_SID, v_NAME;
EXIT WHEN c_Students%NOTFOUND;
DBMS_OUTPUT.PUT(v_SID);
DBMS_OUTPUT.PUT(v_NAME);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
CLOSE c_Students;
END;
/
注意退出语句不能放在后面,否则会多打印最后一行
set serveroutput on size 1000000;
DECLARE
v_SID stu.sid%TYPE;
v_NAME stu.name%TYPE;
v_SEX stu.sex%TYPE:='M';
CURSOR c_Students IS
SELECT sid, name FROM stu WHERE sex= v_SEX;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_SID, v_NAME;
DBMS_OUTPUT.PUT(v_SID);
DBMS_OUTPUT.PUT(v_NAME);
DBMS_OUTPUT.PUT_LINE('---------');
EXIT WHEN c_Students%NOTFOUND;
END LOOP;
CLOSE c_Students;
END;
/
也可以使用记录来书写
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
v_SEX stu.sex%TYPE:='M';
CURSOR c_Students IS
SELECT * FROM stu WHERE sex= v_SEX;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_STU;
EXIT WHEN c_Students%NOTFOUND;
DBMS_OUTPUT.PUT(v_STU.SID);
DBMS_OUTPUT.PUT(v_STU.NAME);
DBMS_OUTPUT.PUT(v_STU.SEX);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
CLOSE c_Students;
END;
/
6、2 游标属性
有四个属性,分别为%FOUND、%NOTFOUND、%ISOPEN、%ROWCOUNT。
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
CURSOR c_Students IS
SELECT * FROM stu;
BEGIN
OPEN c_Students;
FETCH c_Students INTO v_STU;
WHILE c_Students%FOUND LOOP
DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('---------');
FETCH c_Students INTO v_STU;
END LOOP;
if c_Students%ISOPEN then
DBMS_OUTPUT.PUT_LINE('ISOPEN');
end if;
CLOSE c_Students;
if not c_Students%ISOPEN then
DBMS_OUTPUT.PUT_LINE('ISCLOSED');
end if;
END;
/
注意:ROWCOUNT属性为游标中记录的行号,而非表记录的行号,所以永远都是从1开始递增
6、3 参数化游标
使用参数的游标,如:
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
p_sex stu.sex%TYPE;
CURSOR c_Students IS SELECT * FROM stu where sex=p_sex;
BEGIN
p_sex:='M';
OPEN c_Students;
FETCH c_Students INTO v_STU;
WHILE c_Students%FOUND LOOP
DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('---------');
FETCH c_Students INTO v_STU;
END LOOP;
END;
/
参数化游标即含有传入参数的游标,比一般的写法更为简单,如:
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
CURSOR c_Students(p_sex stu.sex%TYPE) IS SELECT * FROM stu where sex=p_sex;--声明
BEGIN
OPEN c_Students('M');--调用
FETCH c_Students INTO v_STU;
WHILE c_Students%FOUND LOOP
DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE('---------');
FETCH c_Students INTO v_STU;
END LOOP;
END;
/
6、4 隐式游标
当PL/SQL引擎处理SQL语句(包含insert,update,delete,select into)的时候,隐含的打开隐式SQL游标,处理完后,自动关闭游标。隐式游标没有相应的OPEN、FETCH和CLOSE等语句。但是四个常用属性可以使用,如
begin
update stu set sex='F' where name='Alice';
if SQL%NOTFOUND then
insert into stu values (100,'Alice','F');
else
DBMS_OUTPUT.PUT_LINE('---------');
end if;
end;
/
可以观察如下:select * from stu;
下面的效果和上述程序一样
begin
update stu set sex='F' where name='Alice';
if SQL%ROWCOUNT=0 then
insert into stu values (100,'Alice','F');
end if;
end;
/
注意SQL%ROWCOUNT和select into一齐使用没有意义,因为如果select into没有匹配到任何数列,它会产生NO_DATA_FOUND异常(其他DML语句不会产生这个异常,而会设置%NOTFOUND和%FOUND),所以不会继续检查SQL%ROWCOUNT语句,如:
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
BEGIN
SELECT *
INTO v_STU
FROM stu
WHERE sid = 199;
-- The following statement will never be executed, since
-- control passes immediately to the exception handler.
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND is true!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND raised!');
END;
/
6、5 游标的循环
6、5、1 简单的LOOP循环(注意退出循环的语句紧跟在FECTH后面)
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
CURSOR c_Students IS
SELECT * FROM stu;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_STU;
EXIT WHEN c_Students%NOTFOUND;
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
CLOSE c_Students;
END;
/
6、5、2 WHILE循环
此时需要两句fetch,如:
set serveroutput on size 1000000;
DECLARE
v_STU stu%ROWTYPE;
CURSOR c_Students IS
SELECT * FROM stu;
BEGIN
OPEN c_Students;
FETCH c_Students INTO v_STU;
WHILE c_Students%FOUND LOOP
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
FETCH c_Students INTO v_STU;
END LOOP;
END;
/
6、5、3 FOR循环
注意:
无需声明v_STU,FOR语句隐式声明
无需OPEN游标,FOR语句隐式打开,并且利用%FOUND判断循环
set serveroutput on size 1000000;
DECLARE
CURSOR c_Students IS
SELECT * FROM stu;
BEGIN
FOR v_STU IN c_Students LOOP
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
END;
/
6、5、4 隐式FOR循环
游标也被隐式声明,没有名称,如:
set serveroutput on size 1000000;
BEGIN
FOR v_STU IN (SELECT * FROM stu) LOOP
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
END;
/
6、6 可更新的游标
在定义游标时使用for update即可具有对表的更新能力,既可以指定可以更新的字段,也可以不指定字段以默认全部字段可以更新
具体更新的方法是使用update或者delete语句,对当前游标记录进行更新,语法是where current of,如:
set serveroutput on size 1000000;
DECLARE
CURSOR c_Students IS
SELECT * FROM stu for update of name;
BEGIN
FOR v_STU IN c_Students LOOP
update stu set name =name || 's' where current of c_Students;
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
END LOOP;
END;
/
可以观察:
select * from stu;
回复数据为:
update stu set name=substr(name,1,length(name)-1);
注意:
1)由于一般游标是不对对表记录进行锁定,所以其他会话可以在该游标处理期间改变数据,但是可更新游标会对表记录进行锁定,所以如果其他会话也在表记录上设置了锁,该会话会无限期的等待下去,可以使用nowait或者wait n(n为秒数)来设置等待情况。
2)可更新游标的循环中不应该使用commit,否则一旦提交会导致释放会话所拥有的锁,由于for update需要锁,所以游标无效。如果不是可更新游标,没有这种问题,但是既便如此,也不应该在游标循环中使用commit。
如果必须要在循环中使用commit提交,可以考虑下面的方式,如:
set serveroutput on size 1000000;
DECLARE
CURSOR c_Students IS
SELECT * FROM stu;
BEGIN
FOR v_STU IN c_Students LOOP
update stu set name =name || 's' where sid=v_STU.sid;
DBMS_OUTPUT.PUT(v_STU.name);
DBMS_OUTPUT.PUT_LINE('---------');
commit;
END LOOP;
END;
/
如果没有主键,可以使用rowid伪列,如:
set serveroutput on size 1000000;
DECLARE
CURSOR c_Students IS
SELECT rowid,name FROM stu;
BEGIN
FOR v_STU IN c_Students LOOP
update stu set name =name || 's' where rowid=v_stu.rowid;
DBMS_OUTPUT.PUT(v_stu.name);
DBMS_OUTPUT.PUT_LINE('---------');
commit;
END LOOP;
END;
/
6、7 游标变量
类似于游标指针,可以在运行期间动态确定查询语句。
下面的例子需要一个表,创建方法为:
create table stuBak as select * from stu;
相应的程序为:
set serveroutput on size 1000000;
declare
/* Define the cursor variable type */
TYPE t_stu IS REF CURSOR;
p_Table VARCHAR2(10);
/* and the variable itself. */
v_CursorVar t_stu;
/* Variables to hold the output. */
v_sid stu.sid%TYPE;
v_name stu.name%TYPE;
v_sex stu.sex%TYPE;
BEGIN
p_Table:='stuBak';
-- Based on the input parameter, open the cursor variable.
IF p_Table = 'stu' THEN
OPEN v_CursorVar FOR
SELECT * FROM stu;
ELSIF p_table = 'stuBak' THEN
OPEN v_CursorVar FOR
SELECT * FROM stuBak;
ELSE
/* Wrong value passed as input - raise an error */
RAISE_APPLICATION_ERROR(-20000,
'Input must be ''stu'' or ''stuBak''');
END IF;
/* Fetch loop. Note the EXIT WHEN clause after the FETCH. */
LOOP
IF p_Table = 'stu' THEN
FETCH v_CursorVar INTO
v_sid, v_name,v_sex;
EXIT WHEN v_CursorVar%NOTFOUND;
DBMS_OUTPUT.PUT(v_sid);
DBMS_OUTPUT.PUT(v_name);
DBMS_OUTPUT.PUT_LINE(v_sex);
DBMS_OUTPUT.PUT_LINE('---------');
ELSE
FETCH v_CursorVar INTO
v_sid, v_name,v_sex;
EXIT WHEN v_CursorVar%NOTFOUND;
DBMS_OUTPUT.PUT(v_sid);
DBMS_OUTPUT.PUT(v_name);
DBMS_OUTPUT.PUT_LINE(v_sex);
DBMS_OUTPUT.PUT_LINE('+++++++');
END IF;
END LOOP;
/* Close the cursor. */
CLOSE v_CursorVar;
COMMIT;
END ShowCursorVariable;
/
说明:
1)声明游标变量,如:TYPE t_stu IS REF CURSOR;
此时为无约束游标变量,没有return语句,可以为任何查询打开
也可以准确描述返回类型,此时必须返回特定记录格式的结果,如:
TYPE t_stu IS REF CURSOR RETURN stu%rowtype;
也可以写成:
TYPE t_record is RECORD
(
sid stu.sid%type,
name stu.name%type,
sex stu.sex%type
);
TYPE t_stu IS REF CURSOR RETURN t_record;
也可以写成:
TYPE t_record is RECORD
(
sid stu.sid%type,
name stu.name%type,
sex stu.sex%type
);
v_record t_record;
TYPE t_stu IS REF CURSOR RETURN v_record%type;
2)游标变量都是位于服务器端,但是由于游标变量需要运行时分配查询内存,所以相应的被指向的内存区域既可以在客户端分配,也可以在服务器端分配。
[此贴子已经被作者于2010-12-11 20:02:20编辑过]