ORACLE 游标CURSOR

cmlhwcbhrh

　　1.本质
　　
　　游标Cursor是ORACLE系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。
　　==> 当返回结果集过大时，需考虑使用带参数的游标来进行分页循环处理。
　　
　　2.游标变量
　　
　　V_CURSOR%ROWCOUNT
　　V_CURSOR%FOUND
　　V_CURSOR%NOTFOUND
　　V_CURSOR%ISOPEN
　　
　　显示游标中V_CURSOR为CURSOR变量名，隐式游标中需使用SQL
　　
　　3.分类和使用 --有代码有真相
　　
　　1)显示游标
　　

DECLARE
--DECLARE
CURSOR CURSOR_TEST IS
SELECT U.USERID FROM USER U WHERE ROWNUM <= 5;
V_USERID USER.USERID%TYPE;
BEGIN
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--OPEN
OPEN CURSOR_TEST;
DBMS_OUTPUT.put_line('Then count of record which is influenced is ' ||
CURSOR_TEST%ROWCOUNT);
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--Iterator
LOOP
--Result pick
FETCH CURSOR_TEST
INTO V_USERID;
IF CURSOR_TEST%FOUND THEN
DBMS_OUTPUT.put_line('Record is found');
END IF;
IF CURSOR_TEST%NOTFOUND THEN
DBMS_OUTPUT.put_line('Record is not found, exit');
END IF;
--Exit Iterator
EXIT WHEN CURSOR_TEST%NOTFOUND;
DBMS_OUTPUT.put_line('Then count of record which is influenced is ' ||
CURSOR_TEST%ROWCOUNT);
DBMS_OUTPUT.put_line('Current user is ' || V_USERID);
END LOOP;
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--CLOSE
CLOSE CURSOR_TEST;
IF NOT CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS NOT OPEN');
END IF;
END;
　　
　　Result：

Then count of record which is influenced is 0
CURSOR_TEST IS OPEN
Record is found
Then count of record which is influenced is 1
Current user is 101
Record is found
Then count of record which is influenced is 2
Current user is 102
Record is found
Then count of record which is influenced is 3
Current user is 103
Record is found
Then count of record which is influenced is 4
Current user is 104
Record is found
Then count of record which is influenced is 5
Current user is 105
Record is not found, exit
CURSOR_TEST IS OPEN
CURSOR_TEST IS NOT OPEN
　　
　　2)带参数的显示游标
　　

DECLARE
--DECLARE
CURSOR CURSOR_TEST(V_PAGE_SIZE INTEGER) IS
SELECT TMP.USERID
FROM (SELECT USERID, ROWNUM ROW_NUM FROM USER) TMP
WHERE TMP.ROW_NUM BETWEEN (V_PAGE_SIZE - 1) * 5 + 1 AND
V_PAGE_SIZE * 5;
V_USERID USER.USERID%TYPE;
BEGIN
--LOOP
FOR PAGE_SIZE IN 1 .. 3 LOOP
DBMS_OUTPUT.put_line('Current page is ' || PAGE_SIZE);
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--OPEN
OPEN CURSOR_TEST(PAGE_SIZE);
DBMS_OUTPUT.put_line('Then count of record which is influenced is ' ||
CURSOR_TEST%ROWCOUNT);
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--Iterator
LOOP
--Result pick
FETCH CURSOR_TEST
INTO V_USERID;
IF CURSOR_TEST%FOUND THEN
DBMS_OUTPUT.put_line('Record is found');
END IF;
IF CURSOR_TEST%NOTFOUND THEN
DBMS_OUTPUT.put_line('Record is not found, exit');
END IF;
--Exit Iterator
EXIT WHEN CURSOR_TEST%NOTFOUND;
DBMS_OUTPUT.put_line('Then count of record which is influenced is ' ||
CURSOR_TEST%ROWCOUNT);
DBMS_OUTPUT.put_line('Current user is ' || V_USERID);
END LOOP;
IF CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS OPEN');
END IF;
--CLOSE
CLOSE CURSOR_TEST;
IF NOT CURSOR_TEST%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR_TEST IS NOT OPEN');
END IF;
END LOOP;
END;

　　
　　Result

Current page is 1
Then count of record which is influenced is 0
CURSOR_TEST IS OPEN
Record is found
Then count of record which is influenced is 1
Current user is 101
Record is found
Then count of record which is influenced is 2
Current user is 102
Record is found
Then count of record which is influenced is 3
Current user is 103
Record is found
Then count of record which is influenced is 4
Current user is 104
Record is found
Then count of record which is influenced is 5
Current user is 105
Record is not found, exit
CURSOR_TEST IS OPEN
CURSOR_TEST IS NOT OPEN
Current page is 2
Then count of record which is influenced is 0
CURSOR_TEST IS OPEN
Record is found
Then count of record which is influenced is 1
Current user is 106
Record is found
Then count of record which is influenced is 2
Current user is 107
Record is found
Then count of record which is influenced is 3
Current user is 108
Record is found
Then count of record which is influenced is 4
Current user is 109
Record is found
Then count of record which is influenced is 5
Current user is 110
Record is not found, exit
CURSOR_TEST IS OPEN
CURSOR_TEST IS NOT OPEN
Current page is 3
Then count of record which is influenced is 0
CURSOR_TEST IS OPEN
Record is found
Then count of record which is influenced is 1
Current user is 111
Record is found
Then count of record which is influenced is 2
Current user is 112
Record is found
Then count of record which is influenced is 3
Current user is 113
Record is found
Then count of record which is influenced is 4
Current user is 114
Record is found
Then count of record which is influenced is 5
Current user is 115
Record is not found, exit
CURSOR_TEST IS OPEN
CURSOR_TEST IS NOT OPEN
　　
　　3)REF显示游标

DECLARE
TYPE CURSOR_TYPE IS ref CURSOR;
V_CURSOR_USERID CURSOR_TYPE;
V_USERID USER.USERID%TYPE;
BEGIN
OPEN V_CURSOR_USERID FOR
SELECT USERID FROM USER WHERE ROWNUM <= 5;
LOOP
FETCH V_CURSOR_USERID INTO V_USERID;
EXIT WHEN V_CURSOR_USERID%NOTFOUND;
DBMS_OUTPUT.put_line('Current user is ' || V_USERID);
END LOOP;
CLOSE V_CURSOR_USERID;
END;
　　
　　Result

Current user is 101
Current user is 102
Current user is 103
Current user is 104
Current user is 105
　　
　　4)隐式游标 -- FOR循环
　　

DECLARE
BEGIN
FOR V_USERID IN (SELECT USERID FROM USER U WHERE ROWNUM <= 5) LOOP
DBMS_OUTPUT.put_line('Current user is ' || V_USERID.USERID);
END LOOP;
END;
　　
　　Result

Current user is 101
Current user is 102
Current user is 103
Current user is 104
Current user is 105
　　
　　5) 隐式游标 -- DML语句+SELECT INTO 语句
　　

DECLARE
BEGIN
DELETE FROM USER;
DBMS_OUTPUT.put_line(sql%rowcount);
IF sql%found THEN
DBMS_OUTPUT.put_line('influence records were found');
END IF;
IF sql%notfound THEN
DBMS_OUTPUT.put_line('influence record were not found');
END IF;
IF not sql%isopen THEN
DBMS_OUTPUT.put_line('Implicit cursor is not open');
END IF;
COMMIT; -- focus on the position of COMMIT
END;
　　
　　Result -- first time

22
influence records were found
Implicit cursor is not open
　　
　　Result -- second time 

0
influence record were not found
Implicit cursor is not open
　　
　　4.参考资料
　　
　　http://apq-hibernate.iyunv.com/blog/1032655