Oracle学习--->4、游标

 实例：

数据源：

--打印出员工的姓名和薪资
WHILE语句：
DECLARE
   --声明一个记录类型
   TYPE EMP_RECORD IS RECORD
   (
      V_SAL       EMPLOYEER.EMPLOYEER_SALARY%TYPE,
      V_EMPNAME   EMPLOYEER.EMPLOYEER_NAME%TYPE
   );
   --声明一个记录类型的变量
   V_EMP_RECORD   EMP_RECORD;
   --1、定义游标
   CURSOR EMP_SAL_CURSOR
   IS
      SELECT EMPLOYEER_SALARY, EMPLOYEER_NAME FROM EMPLOYEER;
BEGIN
   --2、打开游标
   OPEN EMP_SAL_CURSOR;
   --3、提取游标
   FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;

   WHILE EMP_SAL_CURSOR%FOUND
   LOOP
      DBMS_OUTPUT.PUT_LINE (
            '姓名：'
         || V_EMP_RECORD.V_EMPNAME
         || '---''薪资：'
         || V_EMP_RECORD.V_SAL);

      FETCH EMP_SAL_CURSOR INTO V_EMP_RECORD;
   END LOOP;
   --4、关闭游标
   CLOSE EMP_SAL_CURSOR;
END;

FOR语句：
DECLARE
   CURSOR EMP_SAL_CURSOR
   IS
      SELECT EMPLOYEER_SALARY, EMPLOYEER_NAME FROM EMPLOYEER;
BEGIN
   FOR C IN EMP_SAL_CURSOR
   LOOP
      DBMS_OUTPUT.PUT_LINE (
            '姓名：'
         || C.EMPLOYEER_NAME
         || '---''薪资：'
         || C.EMPLOYEER_SALARY);
   END LOOP;
END;

输出结果：

 游标学习：

1、游标基本结构：
　　1.1、游标简介：
　　　　游标实际上指的是一块内存区域，这块内存区域位于进程全局区内部，称为上下文区域，包含3类信息
　　　　①：查询返回的数据行
　　　　②：查询所处理的数据行号
　　　　③：指向共享池的已分析的SQL语句
　　1.2、游标分类：
　　　　①：显示游标：使用CURSOR语句显式定义的游标。游标被定义后，需要打开并提取游标
　　　　②：隐式游标：由Oracle为每一个不属于显式游标的SQL DML语句创建一个隐式的游标。由于隐式游标没有名称，因此可以叫做SQL游标
　　1.3、定义游标类型：
　　　　游标语法：
　　　　CURSOR cursor_name [parameter_list]
　　　　[RETURN return_type]
　　　　IS query
　　　　[FOR UPDATE [OF (column_list)][NOWAIT]]
　　　　cursor_name:用于指定一个有效的游标名称  
　　　　parameter_list:指定一个或者多个可选的游标参数，这些参数将用于查询执行 
　　　　RETURN return_type:可选的RETURN子句指定游标将要返回的由return_type指定的数据类型 
　　　　query:可以是任何SELECT语句
　　　　FOR UPDATE:指定该子句将在游标打开期间锁定游标记录，这些记录对其他用户来说为只读模式
　　1.4、打开游标：
　　　　定义游标后需要打开游标才能使用
　　　　语法：OPEN cursor_name [(parameter_values)]
　　1.5、使用游标属性
　　　　游标属性用于返回游标的执行信息。
　　　　无论是显示或者隐式游标都包含%ISOPEN/%FOUND/%NOTFOUND/%ROWCOUNT属性
　　　　%ISOPEN：判断对应的游标变量是否打开，如果打开返回True，否则返回False
　　　　%FOUND：用来检查是否从结果中提取到了数据。游标被打开后，在调用FETCH语句获取数据之前，%FOUND会产生NULL值，而此后每获取一行数据，其值就会为True，如果最后一次取得数据失败，其值就会变为False
　　　　%NOTFOUND：与%FOUND结果相反，没有从游标提取到数据就返回True，负责返回True
　　　　%ROWCOUNT：用来返回到目前为止已经从游标中取出的记录行数。当游标被打开时，%ROWCOUNT值为0，每取一条数据%ROWCOUNT值就加1
　　1.6、提取游标数据
　　　　语法：FETCH cursor_name INTO variable_name
　　1.7、批量提取游标数据
　　　　FETCH语句一次只能从结果集中提取一行，并且只能向前提取
　　　　使用BULK COLLECT批处理子句可以一次性将游标中的结果集保存到集合中，这样就可以在集合中进行前进和后退处理
　　　　语法：FETCH cursor_name BULK COLLECT INTO variable_name
　　1.8、关闭游标
　　　　语法：CLOSE cursor_name

DECLARE
   emp_row   EMPLOYEER%ROWTYPE;--定义游标值存储变量
   CURSOR emp_cursor (depart_id IN NUMBER)                       --定义游标并指定游标参数
   IS
      SELECT * FROM EMPLOYEER WHERE department_id = depart_id;
BEGIN
   IF NOT emp_cursor%ISOPEN                                  --判断游标状态，如果游标没有打开
   THEN
      OPEN emp_cursor (20);                                            --就打开游标
   END IF;
   IF emp_cursor%ISOPEN
   THEN
      DBMS_OUTPUT.put_line ('游标打开了');
   ELSE
      DBMS_OUTPUT.put_line ('游标还没打开了');
   END IF;
   LOOP
      FETCH emp_cursor INTO emp_row;                         --使用FETCH语句提取游标数据
      EXIT WHEN emp_cursor%NOTFOUND;            --每循环一次就判断一次，查询为空值返回True，就退出循环
      DBMS_OUTPUT.put_line ('当前行数为：' || emp_cursor%ROWCOUNT);
   END LOOP;
   CLOSE emp_cursor;                                                    --关闭游标
END;

 　　2、操纵游标数据

　　　　2.1、LOOP循环

　　　　　　语法：LOOP......END LOOP

　　　　　　注意：再游标中使用LOOP循环关键在于要具有EXIT WHEN子句在游标检索结束后退出循环，因此一个游标LOOP循环应该包含FETCH、EXIT WHEN这两个子句

　　　　　　LOOP循环中循环体总会有机会执行一次，因此必须及时使用EXIT WHEN子句进行退出处理。

DECLARE
   emp_row   EMPLOYEER%ROWTYPE;--定义游标值存储变量
   CURSOR emp_cursor (depart_id IN NUMBER)                       --定义游标并指定游标参数
   IS
      SELECT * FROM EMPLOYEER WHERE department_id = depart_id;
BEGIN
      OPEN emp_cursor;                                            --就打开游标
   LOOP
      FETCH emp_cursor INTO emp_row;                         --使用FETCH语句提取游标数据
      EXIT WHEN emp_cursor%NOTFOUND;            --每循环一次就判断一次，查询为空值返回True，就退出循环
      DBMS_OUTPUT.put_line ('当前行数为：' || emp_cursor%ROWCOUNT);
   END LOOP;
   CLOSE emp_cursor;                                                    --关闭游标
END;

 　　　　2.2、WHILE循环

　　　　　　WHILE循环在循环之前就判断是否可以执行循环体中的内容，因此可以通过游标属性控制循环执行次数

          WHILE循环需要调用FETCH两次，第一次调用判断emp_cursor游标是否提取了游标数据，只有在%FOUND属性值为True的情况下才能进入循环体，最后在循环体内再次调用FETCH

DECLARE
   emp_row   EMPLOYEER%ROWTYPE;                                    --定义游标值存储变量
   CURSOR emp_cursor (depart_id IN NUMBER)                       --定义游标并指定游标参数
   IS
      SELECT * FROM EMPLOYEER WHERE DEPARTMENT_ID = depart_id;
BEGIN
   OPEN emp_cursor (9);                                                --就打开游标
   FETCH emp_cursor INTO emp_row;　　　　　　　　　　　　　　　　　　--提取游标数据
   WHILE emp_cursor%FOUND
   LOOP
      DBMS_OUTPUT.put_line ('当前行数为：' || emp_cursor%ROWCOUNT);
      FETCH emp_cursor INTO emp_row;　　　　　　　　　　　　　　　　--提取游标数据
   END LOOP;
   CLOSE emp_cursor;                                                    --关闭游标
END;

　　　　2.3、FOR循环
　　　　　　FOR循环与WHILE、LOOP相比有个显著地特点就是不需要OPEN、FETCH、CLOSE语句打开
　　　　　　emp_cursor不需要显式地声明

DECLARE
   CURSOR emp_cursor                                             --定义游标并指定游标参数
   IS
      SELECT * FROM EMPLOYEER;
BEGIN
   FOR emp_row IN emp_cursor
   LOOP                                                        --在游标FOR循环中检索数据
      DBMS_OUTPUT.put_line ('当前行数为：' || emp_cursor%ROWCOUNT);
   END LOOP;
END;

再简写
BEGIN
   FOR emp_cursor IN (SELECT * FROM EMPLOYEER)
   LOOP                                                        --在游标FOR循环中检索数据
      DBMS_OUTPUT.put_line ('当前行数为：' || emp_cursor.EMPLOYEER_NAME);
   END LOOP;
END;

　　　　　　2.4、修改游标数据

DECLARE
   CURSOR emp_cursor                                             --定义游标并指定游标参数
   IS
      SELECT *
        FROM EMPLOYEER
      FOR UPDATE;　　　　　　　　　　　　　　　　　　　　　　　　　　　--使用WHERE CURRENT OF一定要有FOR UPDATE子句，并且游标要被打开且至少返回一行，不然Oracle会触发错误
BEGIN
   FOR emp_row IN emp_cursor
   LOOP                                                        --在游标FOR循环中检索数据
      UPDATE EMPLOYEER
         SET EMPLOYEER_SALARY = EMPLOYEER_SALARY * 1.5
       WHERE CURRENT OF emp_cursor;
   END LOOP;
   COMMIT;
END;