Oracle（九）PL/SQL 游标

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

    游标概念

    为了处理 SQL 语句，ORACLE 必须分配一片叫上下文的区域来处理所必须的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集。

    游标是一个指向上下文的句柄或指针（类似 JAVA 中的 Iterator ）。通过游标， PL/SQL 可以控制上下文区和处理语句时上下文区会发生些什么事情。

    对于不同的 SQL 语句，游标的使用情况不同：

1.1 显示游标的处理

步骤（4）：

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

   格式：

   CURSOR cursor_name [parameter[,parameter]...] IS select statement;

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

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

   在指定数据类型时，不能使用长度约束。

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

   格式：

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

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

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

   格式：

   FETCH cursor_name INTO {variable_list | record_variable};

-- 对该记录进行处理

-- 继续处理，知道活动集合中没有记录；

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

    格式：CLOSE cursor_name。

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

1.2 游标属性

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

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

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

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

1.3 示例

例1：

显示结果如下：

例2：将变量改为记录类型

显示结果如下：

注意：改为记录类型后，部分格式需要进行更改。

例3：

显示结果如下：

查询更改前的表数据：

查询表数据：

1.4 游标的 for 循环

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

格式：

    FOR index_variable IN cursor_name[value[,value]...] LOOP

        -- 游标数据处理代码

    END LOOP；

其中：

    index_variable 为游标 FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提供的游标数据，index_variable 中各元素的名称与游标查询语句选择列表中所指定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后中才能通过游标 FOR 循环语句中的索引变量来访问这些列数据。

注：不要在程序中对游标进行人工操作;

    不要在程序中定义用于控制 FOR 循环的记录。

例1：

例2：

/*

利用游标，调整公司中员工的工资：

   工资范围       调整基数

    0 -- 5k        5%

   5k -- 10k       3%

  10k -- 15k       2%

  15k --           1%

*/

update employees set salary = salary * （1 + 0.03） where employee_id = ？

------------------------------------------------------------------------------------

declare

    -- 该变量用于记录调整基数

    v_temp number(4,2);

    v_sal emp011.salary%type;

    v_eid emp011.employee_id%type;

    -- 定义游标

    cursor emp_sal_cursor is select salary,employee_id from emp011;

begin

  open emp_sal_cursor;

 

  fetch emp_sal_cursor into v_sal,v_eid;

 

  while emp_sal_cursor%found loop

    if v_sal < 5000 then v_temp := 0.05;

       elsif v_sal < 10000 then v_temp := 0.03;

       elsif v_sal < 15000 then v_temp := 0.02;

       else v_temp := 0.01;

    end if;

   

    update emp011 set salary = salary * (1 + v_temp)

    where employee_id = v_eid;

   

    fetch emp_sal_cursor into v_sal,v_eid;

  end loop;

 

  close emp_sal_cursor;

end;

------------------------------------------------------------------------------------

例3：将上述语句改为 for 循环

1.5 处理隐式游标

    显示游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动的为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL ，这些是由 ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动的完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

    隐式游标的属性

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

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

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

    -- SQL%ISOPEN    布尔型属性，取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标

例：