-- 作者：admin
-- 发布时间：2008/11/21 19:11:57
-- [推荐]Oracle数据库PL-SQL学习资料之四——游标
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编辑过]
|