oracle游标讲解

一 什么是游标？
oracle 数据库中执行的每个SQL 语句都有对应的单独的游标。

二 游标的类型：
<1> 隐式游标：所有的DML语句和PL/SQL SELECT 语句都有
<2> 显式游标：由开发人员声明和控制

三 显式游标的使用：
<1> 可以用于暂存查询取出的多行结果
<2> 按行处理查询返回的多行结果
<3> 在PL/SQL块中通过循环手动控制游标

四 显式游标属性：
属性  类型  描述
%ISOPEN  Boolean   如果游标打开，则为TRUE
%NOTFOUND Boolean 如果最近的提取没有返回一条记录，则为TRUE
%FOUND Boolean 一直为TRUE ，直到最近提取没有取回行记录
%ROWCOUNT  Number 到目前为止，提取的总行数

五 显式游标的使用流程：
<1> 声明游标：
语法：
DECLARE
  CURSOR cursor_name IS
        select_statement;             

例子：取得一个部门员工的信息：
DECLARE
  . . .
  v_dept employees.department_id%TYPE;
  v_name employees.last_name%TYPE;
  v_sal NUMBER (8);
  CURSOR emp_cursor IS
    SELECT last_name, salary
    FROM employees
    WHERE deoartment_id = v_dept;
BEGIN
. . .
注：在游标声明中，不要包含INTO 子句。

 

<2> 打开游标：
-打开游标将执行查询和取出结果集
-OPEN cursor_name;
不论查询有没有返回记录，都不会引起异常
在一次取操作后，通过使用游标属性进行测试游标状态。

<3> 从游标获取数据：
检索当前记录的值到输出变量中。
FETCH cursor_name INTO variable1, variable2,... ;
变量应该与游标字段个数相同
变量与字段顺序也应该一一对应
测试判断游标是否还包含更多的数据行

例：逐行取得某个部门的员工信息：

FETCH emp_cursor
    INTO v_name, v_sal;

<4> 关闭游标：
在对查询到的所有记录的处理完成后，关闭游标
CLOSE cursor_name;

如果需要，必须重新打开游标
一旦游标已经关闭，不能再提取数据。

 

六 控制多行提取：
使用循环从一个显式游标中取出多行数据
每次重复，取出一行数据
通过使用%NOTFOUND属性，判断上一次的取操作是否成功取到数据
也可以通过%FOUND属性来控制循环取操作

%ISOPEN 属性：
只有在游标打开时，才能从游标中取数据
在执行提取操作前，可以使用%ISOPEN 游标属性测试游标是否已打开
IF emp_cursor%ISOPEN THEN
  FETCH emp_cursor INTO v_name, v_sal;
ELSE
  OPEN emp_cursor;
END IF;

%NOTFOUND 和%ROWCOUNT 属性：
使用%ROWCOUNT 游标属性，准确获取取出的行数
使用%NOTFOUND 游标属性，确定是否已取出所有数据
LOOP
  FETCH emp_cursor
     INTO v_name, v_sal;
   EXIT WHEN emp_cursor%ROWCOUNT > 5
 OR emp_cursor%NOTFOUND;
  v_sal := v_sal + 500;
. . .
END LOOP;

七 带参数的游标：
当游标打开时，通过传入不同的参数，生成最终的查询语句
CURSOR cursor_name
 [(parameter_name datatype, ...)]
  IS
  select_statement;

在调用时，通过给定不同的参数得到不同的结果集

例：传递部门编号和工作职务到游标中
CURSOR emp_cursor
 (v_dept NUMBER, v_job VARCHAR2) IS
  SELECT last_name, salary, hire_date
  FROM employees
  WHERE department_id = v_dept
  AND  job_id = v_job;

八 WHERE CURRENT OF 子句：
<1> 可以使用游标更新或删除当前行
<2> 在游标的查询定义中包含FOR UPDATE 子句来锁定行
例：
使用游标修改符合条件的行
...
  CURSOR emp_cursor IS
    SELECT ...
    FOR UPDATE;
BEGIN
  ...
  FOR emp_record IN emp_cursor LOOP
    UPDATE ...
      WHERE CURRENT OF emp_cursor;
  END LOOP;
  COMMIT;
END;