Oracle 游标

游标:
=====

当在PL/SQL块中执行查询语句(SELECT)和数据操纵语句(DML)时,
Oracle会为其分配上下文区(Context Area),游标是指向上下文区的指针.
对于数据操纵语句和单行SELECT INTO语句来说,Oracle会为它们分配隐含游标.
Oracle9i前,为了处理SELECT语句返回的多行数据,必须使用显式游标;
Oracle9i开始,开发人员可以使用显式游标处理多行数据,也可以使用
SELECT...BULK COLLECT INTO语句处理多行数据.

1.显式游标使用:
--------------
显式游标专门处理SELECT语句返回多行数据.使用显式游标包括:
定义(DECLARE),打开(OPEN),提取数据(FETCH),关闭游标(CLOSE)四个阶段.

定义(DECLARE): 
使用显式游标之前,必须先定义.语法如下:
CURSOR cursor_name IS select_statement;

打开(OPEN):
当打开游标时,Oracle会执行所对应的SELECT语句,并将结果暂存到结果集中,语法如下:
OPEN cursor_name;

提取数据(FETCH):
为了处理结果集中的数据,需要使用FETCH语句游标数据.9i前,FETCH只能提取一行数据.
从9i开始,通过使用FETCH ... BULK COLLECT INTO语句,每次可以提取多行.语法如下:
语法1: FETCH cursor_name INTO variable1,variable2,...;
语法1: FETCH cursor_name 
BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

关闭游标(CLOSE):
COLSE cursor_name;

判断游标当前行:
CURRENT OF cursor_name

2.显式游标属性:
---------------
显式游标的属性用于返回显式注标的执行信息,使用时,必须要在显式游标属性前带有显式游标名.

%ISOPEN 游标是否打开.
%FOUND 检查是否从结果集中提取到了数据.
%NOTFOUND 与%FOUND属性相反.
%ROWCOUNT 返回到当前行为止提取到的实际行数.

3.显式游标使用示例
--------------------

1.使用FETCH ... INTO 语句
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp WHERE deptno=10;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename||': '||v_sal);
END LOOP;
CLOSE emp_cursor;
END;
/

2.使用FETCH ... BULK COLLECT INTO语句提取所有数据
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO ename_table;
FOR i IN 1.. ename_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(ename_table(i));
END LOOP;
CLOSE emp_cursor;
END;
/

3.使用FETCH ... BULK COLLECT INTO语句提取部分数据
DECLARE
TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);
name_array name_array_type;
CURSOR emp_cursor IS SELECT ename FROM emp;
rows INT:=5;
v_count INT:=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULk COLLECT INTO name_array
LIMIT rows;
DBMS_OUTPUT.PUT('雇员名:');
FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP
DBMS_OUTPUT.PUT(name_array(1)||' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
v_count:=emp_cursor%ROWCOUNT;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
END;
/

4.使用游标属性
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
FETCH emp_cursor BULK COLLECT INTO ename_table;
DBMS_OUTPUT.PUT_LINE('提取的总计行数: '||emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END;
/

5.基于游标定义变量
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('雇员名:'||emp_record.ename||'雇员工资:'||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
/

4.参数游标:
---------
带有参数的游标定义语法如下:
CURSOR cursor_name(parameter_name datatype) IS select_statement;

例:
DECLARE
CURSOR emp_cursor(no NUMBER) IS
SELECT ename FROM emp WHERE deptno=no;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
/

5.使用游标更新或删除数据
-------------------------
通过游标不仅可以处理SELECT语句结果,而且也可以更新或删除当前游标行数据.
做这些操作,在定义游标时必须带有FOR UPDATE子句.语法如下:
CURSOR cursor_name(parameter_name datatype)
IS select_statement
FOR UPDATE [OF column_reference] [NOWAIT];
如上所示,FOR UPDATE 子句用于在游标结果集数据上加行共享锁.

## 使用游标更新数据
1.
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp FOR UPDATE;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal?2000 THEN
UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/

2.
DECLARE
CURSOR cur IS
SELECT dname from dept FOR UPDATE;        //当要用游标做UPDATE时,一定要用FOR UPDATE声明.
text varchar2(10);
BEGIN OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDSTE dept SET dname=dname||'_t' WHERE CURRENT OF cur;        //判断游标当前行: CURRENT OF cursor_name
FETCH cur INTO text;                //必须再取值
END LOOP;
CLOSE cur;
END;
/

## 使用游标删除数据
DECLARE
CURSOR emp_cursor IS
SELECT enaem,sal,deptno FROM emp FOR UPDATE;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal,v_deptno;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_deptno=30 THEN
DELETE FROM emp WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/

## 使用游标OF子句在特定表上加行共离锁
如果游标子查询涉及到多张表,那么在默认情况下会在所有修改表上加行共享.
为了只在特定表上加行共享锁,需要在FOR UPDATE子句后带胡OF子句.

DECLARE
CURSOR emp_cursor IS
SELECT ename,sal,dname,emp.deptno FROM emp,dept
WHERE emp.deptno=dept.deptno
FOR UPDATE OF emp.deptno;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF emp_record.deptno=30 THEN
UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
DBMS_OUTPUT.PUT_LINE('雇员名: '||emp_record.ename
||', 工资:'||emp_record.sal||',部门名: '||emp_record.dname);
END LOOP;
CLOSE emp_cursor;
END;
/

## 使用NOWAIT子句
如果其它会话已经在被作用行上加锁,默认情况下当前会话要一直等待.
通过使用FOR UPDATE子句中指定NOWAIT语句,可避免等待锁.如果已经锁定,会显示错误提示并退出PL/SQL块.

DECLARE
CURSOR emp_cursor IS
SELECT ename,sal FROM emp FOR UPDATE NOWAIT;
v_ename emp.ename%TYPE;
v_oldsal emp.sal%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_oldsal;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_oldsal?2000 THEN
UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/

## 游标FOR循环
游标FOR循环是在PL/SQL块中使用游标最简单的方式,简化了对游标的处理.当使用游标FOR循环时,
Oracle会隐含地打开游标,撮游标数据并关闭游标.语法如下:
    FOR record_name IN cursor_name LOOP
        statement1
        statement2 ...
    END LOOP;
例:
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员: '||emp_record.ename);
END LOOP;
END;
/



## 更多实例:

# 隐式游标
BEGIN
FOR cur IN(select dname from dept) loop
dbms_output.put_line(cur.dname);
END loop;
END;
/

1.
DECLARE
CURSOR mycur IS
SELECT * from book;        //声明游标
myrecord books%ROWTYPE;        //声明记录变量,记录里的各项与表完全相同.
BEGIN                //开始PL/SQL
OPEN mycur;            //打开游标
FETCH mycur INTO myrecord;    //取游标的一条记录放到变量里面
WHILE mycur%FOUND LOOP        //如果游标里还有记录,开始LOOP
DBMS_OUTPUT.PUT_LINE(myrecord.books_id||','||myrecord.books_name);
FETCH mycur INTO myrecord;   
END LOOP;
CLOSE mycur;            //关闭游标
END;
/

2.
DECLARE
CURSOR cur_para(id varchar2) IS            //声明游标,有一个参数 id.声明游标变量只指定类型,不指定精度
SELECT books_name FROM books WHERE books_id=id;    //查找表,条件是books_id等于游标的参数
t_name books.books_name%TYPE;            //声明变量,类型长度都与books_name字段相同
BEGIN
OPEN cur_para('0001');                //打开游标,传参数'0001'给id
LOOP
FETCH cur_para INTO t_name;            //取游标值存到变量t_name里.
EXIT WHEN cur_para%NOTFOUND;            //如果游标里没有更多记录退出LOOP
DBMS_OUTPUT.PUT_LINE(t_name);   
END LOOP;                    //结束LOOP
CLOSE cur_para;
END;
/

3.
DECLARE
CURSOR cur_para(id varchar2) is
SELECT book_name from book WHERE books_id=id;    //声明游标,查询条件是books_id等于游标变量 id
BEGIN
DBMS_OUTPUT.PUT_LINE('******结果集为:**********');
FOR cur IN cur_para('0001') LOOP        //用FOR LOOP时,可以不显式声明变量.如:cur可以直接用,类型/长度会自动匹

配.
DBMS_OUTPUT.PUT_LINE(cur.book_name);
END LOOP;
END;
/

4.
DECLARE
t_name book.book_name%TYPE;            //声明变量t_name,类型长度与表字段book_name完全相同
CURSOR cur(id varchar2) IS
SELECT book_name FROM book WHERE books_id=id;    //声明游标
BEGIN
IF cur%ISOPEN THEN                //判断游标cur是否打开
DBMS_OUTPUT.PUT_LINE('游标已经被打开.');
ELSE
OPEN cur('0003');                //打开游标,传参数0003
END IF;
FETCH cur INTO t_name;                //取出游标当前行,放到变量t_name
CLOSE cur;                    //关闭游标
DBMS_OUTPUT.PUT_LINE(t_name);            //显示值
END;
/

5.
DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT name from deptment;
BEGIN
OPEN mycur;
LOOP
//FETCH mycur into t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;            //如果没有使用FETCH,游标属性为空.
DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是: '||mycur%ROWCoUNT);
END LOOP;
CLOSE mycur;
END;