一、游标简介:
游标是PL&SQL中的一种控制结构。可以分为显式游标和隐式游标。pl&sql会为每一条select语句创建隐式游标。但是当我们需要处理多条数据时,我们就需要创建显式游标。注意:游标不是模式对象。
二、游标的几种常见属性:
1、%FOUND
--判断游标中是否还有数据,若有,返回true,否则,返回false。
2、%NOTFOUND
--与%FOUND 相反
3、%ISOPEN
--判断游标是否为打开状态
4、%ROWCOUNT
--记录已从游标中取出的记录数
三、游标应用实例:
1、%FOUND属性的使用
- DECLARE
- CURSOR mycur IS
- SELECT * FROM student;
- myrecord student%ROWTYPE;
- BEGIN
- OPEN mycur;
- FETCH mycur INTO myrecord;
- WHILE mycur%FOUND LOOP
- DBMS_OUTPUT.PUT_LINE(myrecord.stuno||','||myrecord.stuname);
- FETCH mycur INTO myrecord;
- END LOOP;
- CLOSE mycur;
- END;
2、%NOTFOUND属性的使用:
- DECLARE
- CURSOR cur_para(id varchar2) IS
- SELECT stuname FROM student WHERE stuno=111;
- t_name student.stuname%TYPE;
- BEGIN
- OPEN cur_para(111);
- LOOP
- FETCH cur_para INTO t_name;
- EXIT WHEN cur_para%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(t_name);
- END LOOP;
- CLOSE cur_para;
- END;
3、for循环中游标的特殊使用:
- SQL> DECLARE
- 2 CURSOR cur_para(id varchar2) IS
- 3 SELECT stuname FROM student WHERE stuno=id;
- 4 BEGIN
- 5 DBMS_OUTPUT.PUT_LINE('*****');
- 6 FOR cur IN cur_para('111') LOOP
- 7 DBMS_OUTPUT.PUT_LINE(cur.stuname);
- 8 END LOOP;
- 9 END;
4、%ISOPEN属性的使用:
- SQL> DECLARE
- 2 t_name student.stuname%TYPE;
- 3 CURSOR cur(id varchar2) IS
- 4 SELECT stuname FROM student WHERE stuno=id;
- 5 BEGIN
- 6 IF cur%ISOPEN THEN
- 7 DBMS_OUTPUT.PUT_LINE('THE cur has been opened');
- 8 ELSE
- 9 OPEN cur('111');
- 10 END IF;
- 11 FETCH cur INTO t_name;
- 12 CLOSE cur;
- 13 DBMS_OUTPUT.PUT_LINE(t_name);
- 14 END;
5、%ROWCOUNT属性的使用:
- SQL> DECLARE
- 2 t_name VARCHAR2(10);
- 3 CURSOR mycur IS
- 4 SELECT stuname FROM student;
- 5 BEGIN
- 6 OPEN mycur;
- 7 LOOP
- 8 EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
- 9 DBMS_OUTPUT.PUT_LINE('*****ROWCOUNT****'||mycur%ROWCOUNT);
- 10 END LOOP;
- 11 CLOSE mycur;
- 12 END;