作者：哈罗xeh_406 | 来源：互联网 | 2014-07-08 01:11
oracle数据库PL/SQL之游标三）游标www.2cto.com1.游标的基本原理对数据结果进行逐条处理。执行效率低、占用资源。2.游标属性%FOUND%ISOPEN是否打开%NOTFOUND%ROWCOUNT游标每抽取一行增加1，记录游标抽取过的...SyntaxHighl
三）游标 www.2cto.com
1.游标的基本原理
对数据结果进行逐条处理。
执行效率低、占用资源。
2.游标属性
%FOUND
%ISOPEN是否打开
%NOTFOUND
%ROWCOUNT游标每抽取一行增加1，记录游标抽取过的行数。
参数化游标 www.2cto.com
1）
DECLARE
CURSOR cur_para IS
SELECT books_name FROM books WHERE books_id=id;
t_name books.books_name%TYPE;
BEGIN
OPEN cur_para<&＃39;0001&＃39;>;
LOOP
FETCH cur_para INTO t_name;
EXIT WHEN cur_para%NOTFOUND;
DBMS_OUTPUT.PUT_LINE;
END LOOP;
CLOSE cur_para;
END;
/
2）
ACCEPT my_tid prompt &＃39;Please input the tid:&＃39;
DECLARE
CURSOR teacher_cur(CURSOR_id NUMBER) IS
SELECT TNAME,TITLE,SEX
FROM TEACHERS
WHERE TID=CURRSOR_id;
BEGIN
OPEN teacher_cur(my_tid);
LOOP
FETCH teacher_cur INTO teacher_name,teacher_title,teacher_sex;
EXIT WHEN teacher_cur%NOTFOUND;
...
END LOOP;
CLOSE teacher_cur;
END;
3.显式游标
使用过程:
1)声明
CURSOR<游标名> IS SELECT<语句>;
2)打开
OPEN <游标名>
3)提取
FETCH <游标名> INTO <变量列表>;
FETCH <游标名> INTO PL/SQL记录;
4)判断游标是否为空，空转入第5）非空转入3）
5)关闭
CLOSE <游标名>
4.隐式游标
1）SELECT 只能返回一条记录
BEGIN
SELECT TID,TNAME,TITLE,SEX INTO teacher_id,teacher_name,teacher_title,teacher_sex FROM TEACHERS
WHERE TID=113;
END;
2）SELECT可以返回多条记录
BEGIN
FOR cur IN(SELECT name FROM deptment) LOOP
DBMS_OUTPUT.PUT_LINE;
END LOOP;
END;
/
5.游标变量
1)声名
TYPE <类型名> IS REF CURSOR
RETURN <返回类型>;
2)打开游标变量
OPEN <游标变量> FOR
3)关闭游标变量
CLOSE
6.使用实例
1)
DECLARE
CURSOR mycur IS
SELECT * FROM books;
myrecord books%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur INTO myrecord;
WHILE mycur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(myrecord.books_id||&＃39;,&＃39;||myrecord.books_name);
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END;
2)通过游标修改数据
DECLARE
CURSOR cur IS
SELECT name FROM deptment FOR UPDATE;
text varchar2<10>;
BEGIN
OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDATE deptment SET name=name||&＃39;_t&＃39; WHERE CURRENT OF cur;
FETCH cur INTO text;
END LOOP;
CLOSE cur;
END;
/
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有