【Oracle】PL/SQL——游标CURSOR

在 PL/SQL 程序中，对于 处理多行记录的事务经常使用游标来实现。

其中，游标又分为显式游标和隐式游标，这里重点说明显式游标的用法。

显示游标

显示游标的处理

四个PL/SQL步骤：

1）定义游标 ：就是定义一个游标名，以及与其相对应的 SELECT 语句。

格式：

CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;

游标参数只能为输入参数，其格式为：

parameter_name [IN] datatype [{:= | DEFAULT} expression]

在指定数据类型时，不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。

2）打开游标 ：就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式：

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示 法。PL/SQL程序不能用 OPEN 语句重复打开一个游标。

3）提取游标数据 ：就是检索结果集合中的数据行，放入指定的输出变量中。

格式：

FETCH cursor_name INTO {variable_list | record_variable };

4）对该记录进行处理，直到活动集合中没有记录；

5） 关闭游标 ：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。

格式：

CLOSE cursor_name;

注： 定义的游标不能有 INTO 子句

游标属性：

%FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；

%NOTFOUND 布尔型属性，与%FOUND 相反；

%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

游标属性调用方式：游标名%属性

SET SERVEROUTPUT ON

-- 打印出 80 部门的所有的员工的工资 salary
DECLARE
   --声明一个记录类型
	 TYPE EMP_RECORD IS RECORD(
        V_SAL EMPLOYEES.SALARY%TYPE,
        V_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE
	 );
	 
   --声明一个记录类型的变量
	 V_EMP_RECORD EMP_RECORD;
	 
   --定义游标
   CURSOR EMP_SAL_CURSOR IS SELECT E.SALARY, E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = 80;

BEGIN
  --打开游标
  OPEN EMP_SAL_CURSOR;
  
  --提取游标
  FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;
  
  WHILE EMP_SAL_CURSOR%FOUND LOOP
     DBMS_OUTPUT.put_line(V_EMP_RECORD.V_EMPID || ':' || V_EMP_RECORD.V_SAL);
     FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;
  END LOOP;
  
  --关闭游标
  CLOSE EMP_SAL_CURSOR;
	
END;
/  

游标的 FOR 循环

游标 FOR 循环语句，自动执行游标的 OPEN 、FETCH 、CLOSE 语句和 循环 语句的功能；当进入循环时，游标 FOR 循环语句 自动 打开游标，并提取第一行游标数据，当程序处理完 当前所提取的数据而进入下一次循环时，游标 FOR 循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

这里把各种使用操作都封装起来了，十分的方便，一般都是用FOR循环使用游标。

SET SERVEROUTPUT ON

-- 打印出 80 部门的所有的员工的工资 salary
DECLARE
   --定义游标
   CURSOR EMP_SAL_CURSOR(DEP_ID NUMBER DEFAULT 80) IS SELECT E.SALARY, E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEP_ID;

BEGIN
   FOR C IN EMP_SAL_CURSOR(DEP_ID => 100) LOOP
		 DBMS_OUTPUT.put_line(C.EMPLOYEE_ID || ':' || C.SALARY);
   END LOOP;
  
END;
/  

上面都是用游标读取数据，如果需要更新数据，这需要在游标声明的后面添加FOR UPDATE。

这里给个例子：将所有员工中工资小于5000的人全部更新为10000。

SET SERVEROUTPUT ON

DECLARE 
		CURSOR EMP_SAL_CURSOR IS SELECT E.EMPLOYEE_ID, E.SALARY FROM EMPLOYEES E FOR UPDATE;

		V_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
		V_SAL EMPLOYEES.SALARY%TYPE;
		V_NEW_SALARY EMPLOYEES.SALARY%TYPE;
BEGIN
	  FOR C IN EMP_SAL_CURSOR LOOP
			  IF C.SALARY < 5000 THEN
					 DBMS_OUTPUT.put_line('调整前：'||C.SALARY);
				   UPDATE EMPLOYEES E SET E.SALARY = 10000
				   WHERE CURRENT OF EMP_SAL_CURSOR;
					 
					 SELECT E2.SALARY INTO V_NEW_SALARY FROM EMPLOYEES E2 WHERE E2.EMPLOYEE_ID = C.EMPLOYEE_ID;
					 
					 DBMS_OUTPUT.put_line('调整后：'|| V_NEW_SALARY);
		    END IF;
		END LOOP;
END;  
/

隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些 由系统隐含创建的游标称为隐式游标， 隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。

隐式游标的属性和显式游标的属性相同，而调用方式则是为：SQL%属性名。