oracle数据库PL/SQL之游标

三）游标

1.游标的基本原理

对数据结果进行逐条处理。

执行效率低、占用资源。

2.游标属性

%FOUND

%ISOPEN是否打开

%NOTFOUND

%ROWCOUNT游标每抽取一行增加1，记录游标抽取过的行数。

参数化游标

1）

DECLARE

CURSOR cur_para<id varchar2> IS

SELECT books_name FROM books WHERE books_id=id;

t_name books.books_name%TYPE;

BEGIN

OPEN cur_para<'0001'>;

LOOP

FETCH cur_para INTO t_name;

EXIT WHEN cur_para%NOTFOUND;

DBMS_OUTPUT.PUT_LINE<t_name>;

END LOOP;

CLOSE cur_para;

END;

/

2）

ACCEPT my_tid prompt 'Please input the tid:'

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<cur.name>;

END LOOP;

END;

/

5.游标变量

1)声名

TYPE <类型名> IS REF CURSOR

RETURN <返回类型>;

2)打开游标变量

OPEN <游标变量> FOR <SELECT语句>

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||','||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||'_t' WHERE CURRENT OF cur;

FETCH cur INTO text;

END LOOP;

CLOSE cur;

END;

/