储存过程入门06--游标

游标属性：%FOUND %ISOPEN  %NOTFOUND %ROWCOUNT

 

 

 

DECLARE
CURSOR mysur IS
select * from books;
myrecord books%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur INTO myrecord;
WHILE mysur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(myrecord.books_id||','||myrecord.books_name);
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END;
/

 

 

游标参数

DECLARE
CURSOR cur_para(id varchar2) IS  --参数只给类型 不给精度
SELECT books_name FROM books WHERE book_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;
/

 

 

DECLARE
CURSOR cur_para(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
BEGIN
DBMS_OUTPUT.PUT_LINE('*******结果集*****');
FOR cur IN cur_para('0001') LOOP
DBMS_OUTPUT.PUT_LINE(cur.books_name);
END LOOP;
END;
/

 

DECLARE
t_name books.books_name%TYPE;
CURSOR cur(id varchar2) IS
SELECT books_name FROM books WHERE  books_id=id;
BEGIN
IF cur%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已打开');
ELSE
OPEN cur('000');
END IF;
FETCH cur INTO t_name;
CLOSE cur;
DBMS_OUTPUT.PUT_LINE(t_name);
END;
/

 

 

DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT name FROM deptment;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO tname;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是：'||mycur%ROWCOUNT);
END LOOP;
CLOSE mycur;
END;
/

 

用游标更新数据

DECLARE
CURSOR cur IS
SELECT name FROM dept FOR UPDATE;--更新一定要设置
text varchar2(10);
BEGIN
OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDATE depart SET name=name||'_t' WHERE CURRENT OF cur;
FETCH cur INTO text;
END LOOP;
CLOSE cur;
END;
/

 

--隐式游标

BEGIN
FOR cur IN(SELECT name FROM dept) LOOP
DBMS_OUTPUT.PUT_LINE(cur.name);
END LOOP;
END;
/