PL/SQL_游标使用

使用游标

当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配上下文区（Context Area），游标是指向上下文区的指针。对于数据操纵语句和单行SELECT INTO语句来说，Oracle会为它们分配隐含游标。
1、使用显式游标
为了处理SELECT语句返回的多行数据，开发人员可以使用显式游标，使用显式游标包括定义游标、打开游标、提取数据和关闭游标四个阶段。
（1）定义游标
语法如下：

CURSOR cursor_name IS select_statement;

（2）打开游标
语法如下：

OPEN cursor_name;

（3）提取数据

语法如下：

语法一：FETCH cursor_name INTO variable1,variable2,...;语法二：FETCH cursor_NAME BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

（4）关闭游标

CLOSE cursor_name;

示例一：在显式游标中使用FETCH...INTO语句
在Oracle9i之前，使用FETCH...INTO语句每次只能处理一行数据。为了处理结果集中的多行数据，必须要使用循环语句进行处理。

DECLARE  CURSOR cur_emp IS  SELECT ename,sal FROM emp WHERE deptno = 20;  v_ename emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  --打开游标  OPEN cur_emp;    --循环提取  LOOP    FETCH cur_emp INTO v_ename,v_sal;    EXIT WHEN cur_emp%NOTFOUND;    dbms_output.put_line('员工姓名：' || v_ename ||'，工资：' || v_sal);  END LOOP;    --关闭游标  CLOSE cur_emp;END;

示例二：在显式游标中，使用FETCH...BULK COLLECT INTO语句提取所有数据
从Oracle9i开始，通过使用FETCH...BULK COLLECT INTO语句，一次就可以提取结果集的所有数据。

DECLARE  CURSOR cur_emp IS  SELECT ename FROM emp;  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;  ename_table ename_table_type;BEGIN  --打开游标  OPEN cur_emp;  FETCH cur_emp BULK COLLECT INTO ename_table;  FOR i IN 1..ename_table.COUNT LOOP    dbms_output.put_line('员工姓名：' || ename_table(i));  END LOOP;  --关闭游标  CLOSE cur_emp;END;

示例三：在显式游标中使用FETCH...BULK COLLECT INTO...LIMIT语句提取部分数据
当使用FETCH...BULK COLLECT INTO语句提取数据时，默认情况下会提取结果集的所有数据。如果结果集含有大量数据，并且使用VARRAY集合变量接收数据，那么可能需要限制每次提取的行数。

DECLARE  TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);  name_array name_array_type;  CURSOR cur_emp IS  SELECT ename FROM emp;  ROWS INTEGER := 5;  v_count INTEGER := 0;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp BULK COLLECT INTO name_array    LIMIT ROWS;    dbms_output.put('员工姓名：');    FOR i IN 1..(cur_emp%ROWCOUNT-v_count) LOOP      dbms_output.put(name_array(i) || ' ');    END LOOP;    dbms_output.new_line;    v_count := cur_emp%ROWCOUNT;    EXIT WHEN cur_emp%NOTFOUND;  END LOOP;  --关闭游标  CLOSE cur_emp;END;

示例四：使用游标属性

DECLARE  CURSOR cur_emp IS  SELECT ename FROM emp;  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;  ename_table ename_table_type;BEGIN  IF NOT cur_emp%ISOPEN THEN    --打开游标    OPEN cur_emp;  END IF;  FETCH cur_emp BULK COLLECT INTO ename_table;  dbms_output.put_line('提取的总计行数：' || cur_emp%ROWCOUNT);  --关闭游标  CLOSE cur_emp;END;

示例五：基于游标定义记录变量

DECLARE  CURSOR cur_emp IS  SELECT ename,sal FROM emp;  TYPE emp_record_type IS RECORD(    ename emp.ename%TYPE,    sal emp.sal%TYPE  );  emp_record emp_record_type;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp INTO emp_record;    EXIT WHEN cur_emp%NOTFOUND;    dbms_output.put_line('员工姓名：' || emp_record.ename || '，工资：' || emp_record.sal);  END LOOP;  --关闭游标  CLOSE cur_emp;END;

参数游标

参数游标是指带有参数的游标。在定义了参数游标之后，当使用不同参数值多次打开游标时，可以生成不同的结果集。
参数游标语法如下：

CURSOR cursor_name(parameter_name datatype) IS select_statement;

示例如下：

DECLARE  CURSOR cur_emp(dno number) IS  SELECT ename FROM emp WHERE deptno = dno;  v_ename emp.ename%TYPE;BEGIN  --打开游标  OPEN cur_emp(20);  LOOP    FETCH cur_emp INTO v_ename;    EXIT WHEN cur_emp%NOTFOUND;    dbms_output.put_line('员工姓名：' || v_ename);  END LOOP;  --关闭游标  CLOSE cur_emp;END;

注意，定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则失去了定义参数游标的意义。

使用游标更新或删除数据
通过使用显式游标，不仅可以一行一行地处理SELECT语句的结果，而且也可以更新或删除当前游标行的数据。注意，如果要通过游标更新或删除数据，在定义游标时必须要带有FOR UPDATE子句，语法如下：

CURSOR cursor_name(parameter_name datatype)IS select_statementFOR UPDATE [OF column_reference] [NOWAIT];

如上所示，FOR UPDATE子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作；当SELECT语句引用到多张表时，使用OF子句可以确定哪些表要加锁，如果没有OF子句，则会在SELECT语句所引用的全部表上加锁；NOWAIT子句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。语法如下：

UPDATE table_name SET column=... WHERE CURRENT OF cursor_name;DELETE table_name WHERE CURRENT OF cursor_name;

1、使用游标更新数据

DECLARE  CURSOR cur_emp IS  SELECT sal FROM emp FOR UPDATE;  v_oldsal emp.sal%TYPE;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp INTO v_oldsal;    EXIT WHEN cur_emp%NOTFOUND;    IF v_oldsal < 2000 THEN      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;    END IF;  END LOOP;  --关闭游标  CLOSE cur_emp;END;

2、使用游标删除数据

DECLARE  CURSOR cur_emp IS  SELECT deptno FROM emp FOR UPDATE;  v_deptno emp.deptno%TYPE;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp INTO v_deptno;    EXIT WHEN cur_emp%NOTFOUND;    IF v_deptno = 30 THEN      DELETE FROM emp WHERE CURRENT OF cur_emp;    END IF;  END LOOP;  --关闭游标  CLOSE cur_emp;END;

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

DECLARE  CURSOR cur_emp IS  SELECT ename,sal,dname,emp.deptno FROM emp,dept  WHERE emp.deptno = dept.deptno  FOR UPDATE OF emp.deptno;  TYPE emp_record_type IS RECORD(    ename emp.ename%TYPE,    sal emp.sal%TYPE,    dname dept.dname%TYPE,    deptno emp.deptno%TYPE  );  emp_record emp_record_type;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp INTO emp_record;    EXIT WHEN cur_emp%NOTFOUND;    IF emp_record.deptno = 20 THEN      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;    END IF;  END LOOP;  --关闭游标  CLOSE cur_emp;END;

4、使用NOWAIT子句
使用FOR UPDATE语句对被作用行加锁，如果其他会话已经在被作用行上加锁，那么在默认情况下当前会话要一直等待对象释放锁。通过在FOR UPDATE子句中指定NOWAIT语句，可以避免等待锁。

DECLARE  CURSOR cur_emp IS  SELECT ename,sal FROM emp FOR UPDATE;  v_ename emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  --打开游标  OPEN cur_emp;  LOOP    FETCH cur_emp INTO v_ename,v_sal;    EXIT WHEN cur_emp%NOTFOUND;    IF v_sal < 2000 THEN      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;    END IF;  END LOOP;  --关闭游标  CLOSE cur_emp;END;

游标FOR循环

游标FOR循环是在PL/SQL块中使用游标最简单的方式，简化了对游标的处理。当使用游标FOR循环时，Oracle会隐含地打开游标、提取游标数据并关闭游标。使用游标FOR循环的语法如下：

FOR record_name IN cursor_name LOOP  statement1;  statement2;END LOOP;

1、使用游标FOR循环
当使用游标开发PL/SQL应用程序时，为了简化程序代码，建议大家使用游标FOR循环。

DECLARE  CURSOR cur_emp IS  SELECT ename,sal FROM emp;BEGIN  FOR emp_record IN cur_emp LOOP    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);  END LOOP;END;

2、在游标FOR循环中直接使用子查询
当使用游标FOR循环时，习惯作法是首先在定义部分定义游标，然后在游标FOR循环中引用该游标。如果在使用游标FOR循环时不需要使用任何游标属性，那么可以直接在游标FOR循环中使用子查询。

BEGIN  FOR emp_record IN (    SELECT ename,sal FROM emp  ) LOOP    dbms_output.put_line(emp_record.ename);  END LOOP;END;

使用动态游标

在PL/SQL块中使用游标变量包含定义游标变量、打开游标、提取游标数据、关闭游标等四个阶段。具体步骤如下：
（1）定义REF CURSOR类型和游标变量
语法如下：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];cursor_variable ref_type_name;

（2）打开游标
语法如下：

OPEN cursor_variable FOR select_statement;

（3）提取游标数据
语法如下：

语法一：FETCH cursor_variable INTO variable1,variable2,...;语法二：FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

（4）关闭游标变量

CLOSE cursor_variable;

示例一：在定义REF CURSOR类型时不指定RETURN子句
如果在定义REF CURSOR类型时不指定RETURN子句，那么在打开游标时可以指定任何的SELECT语句。

DECLARE  TYPE refcur_emp_type IS REF CURSOR;  cur_emp refcur_emp_type;  emp_record emp%ROWTYPE;BEGIN  --打开游标  OPEN cur_emp FOR SELECT * FROM emp;  LOOP    FETCH cur_emp INTO emp_record;    EXIT WHEN cur_emp%NOTFOUND;    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);  END LOOP;  --关闭游标  CLOSE cur_emp;END;

示例二：在定义REF CURSOR类型时指定RETURN子句

DECLARE  TYPE emp_record_type IS RECORD(    ename emp.ename%TYPE,    sal emp.sal%TYPE  );  TYPE refcur_emp_type IS REF CURSOR;  cur_emp refcur_emp_type;  emp_record emp_record_type;BEGIN  --打开游标  OPEN cur_emp FOR  SELECT ename,sal FROM emp;  LOOP    FETCH cur_emp INTO emp_record;    EXIT WHEN cur_emp%NOTFOUND;    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);  END LOOP;  --关闭游标  CLOSE cur_emp;END;

使用CURSOR表达式

CURSOR表达式是Oracle9i新增加的特征，用于返回嵌套游标。在Oracle9i之前，使用显式游标时，结果集只能包含普通数据；从Oracle9i开始，结果集不仅可以包含普通数据，而且允许包含嵌套游标的数据。使用CURSOR表达式的语法如下：
CURSOR(subquery)
示例如下：

DECLARE  TYPE refcursor IS REF CURSOR;  CURSOR cur_dept(dno NUMBER) IS  SELECT d.dname,CURSOR(    SELECT ename,sal FROM emp WHERE deptno = d.deptno  )  FROM dept d WHERE d.deptno = dno;  cur_emp refcursor;  v_dname dept.dname%TYPE;  v_ename emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  --打开游标  OPEN cur_dept(20);  LOOP    FETCH cur_dept INTO v_dname,cur_emp;    EXIT WHEN cur_dept%NOTFOUND;    dbms_output.put_line('部门名：' || v_dname);    LOOP      FETCH cur_emp INTO v_ename,v_sal;      EXIT WHEN cur_emp%NOTFOUND;      dbms_output.put_line('雇员名：' || v_ename || '，工资：' || v_sal);    END LOOP;  END LOOP;  --关闭游标  CLOSE cur_dept;END;