oracle数据库PL/SQL之游标

adxltygsdc

三）游标
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;
/