PL/SQL游标使用详解

每当在PL/SQL中执行一个SQL语时，Oracle数据库都会为这个语句分一个上下文区域（Context Area）来处理所必需的信息，其中包括语句处理的行数，一个指向语句被分析以后的表示形式的指针以及查询的结果集。游标是指向上下文区域的句柄或指针，PL/SQL通过游标可以控制或处理上下文区域。如果按照游标是否绑定到一个专门的查询语句来划分，可以分为静态游标和动态游标。其中静态游标又分为：隐式游标和显示游标；动态游标分为：强类型和弱类型。

1.隐式游标

每当我们执行一个DML语句（包括INSERT，UPDATE，MERGE或者DELETE）或者SELECT INTO语句时，PL/SQL都会声明一个隐式游标并管理这个游标。这种游标之所以叫做隐式游标，是因为数据库自动执行游标相关操作，比如，打开，提取，关闭等。隐式游标又叫做SQL游标。

1.1隐式游标属性

隐式游标属性返回有关DDL语句和DML语句执行的信息，该游标属性值总是返回最近执行的SQL语句。执行打开的隐式游标的属性值是空的。隐式游标可用的属性如下：

隐式游标属性

名    字	说    明
SQL%FOUND	如果取到记录就返回TRUE，否者返回FLASE
SQL%NOTFOUND	如果没有取得记录就返回TRUE，否者返回FALSE
SQL%ROWCOUNT	返回从游标中取出的记录数
SQL%ISOPEN	由于隐式游标总是自动打开和关闭，因此这个属性中是FALSE

 

隐式游标属性总是返回最后一次执行的SQL语句的属性值，而不管这个SQL语句时在哪个代码块或者程序中执行的。

declare
  v_empno emp.empno%type := 3792;
begin
  update emp e set e.sal = 5000 where e.empno = v_empno;
  if sql%notfound then
    insert into emp
      (empno, ename, hiredate, sal, deptno)
    values
      (v_empno, 'CHICLEWU', date '2011-07-08', 4000, 50);
  end if;
end;

 

1.2使用隐式游标属性准则

当使用隐式游标属性是，需要考虑以下准则：

• 游标属性值总是返回最近执行的SQL语句，它可能是在不同的作用域（例如，在一个子块）。

• 在SELECT INTO语句中，%NOTFOUND属性是不生效的：

  • 如果在SELECT INTO语句没有返回行，PL/SQL立即抛出一个预订的NO_DATA_FOUND异常，在检查%NOTFOUND之前中断控制流。

  • SELECT INTO语句调用聚合函数总是返回个值或者空值。%NOTFOUNT属性总是返回FALSE,因此检查它是没有必要的。  

2.显示游标

当你需要精确地控制查询处理时，可以在任何PL/SQL块、子程序或者包的声明部分显示地声明一个游标。显示游标就是在代码的声明部分明确定义的SELECT语句，并同时指定一个名字。然后你可以通过三个语句来控制游标：OPEN、FETCH和CLOSE。首先，你需要使用OPEN语句初始化游标，标识结果集。然后，可以反复地执行FETCH语句直到所有行都被取出，或者使用BULK COLLECT语句一次性取出所有行。最后，只用CLOSE语句释放游标。

2.1声明显示游标

要使用显示游标，必须先在PL/SQL块或包的规范部分声明它。

CURSOR cursor_name
 [ ( cursor_parameter_declaration [, cursor_parameter_declaration ]... )]
   [ RETURN rowtype] IS select_statement ;

其中，cusor_name是显示游标的名字；cursor_parameter_declartion是显示游标的参数，是可选项；select_statement是显示游标指定的SELECT语句。

 

2.2打开显示游标

OPEN cursor_name
  [ ( cursor_parameter_name [ [,] cursor_parameter_name ]... ) ] ;

其中，cusor_name是显示游标的名字；cursor_parameter_name是显示游标的参数，是可选项。如果游标声明时，指定了游标参数，则打开时一定要指定该参数。

 

2.3提取数据

每次只提取一行数据：

FETCH  cursor_name

  INTO [(variable_name [, variable_name ]... | record_name )];

 

一次提取多行数据：

FETCH  cursor_name
  BULK COLLECT INTO [(collection_name [,collection_name...])];

 

2.4关闭显示游标

CLOSE cursor_name;

 

2.5显示游标属性

每个显示游标和游标变量都有四个属性：%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN。可以下面的语法使用这些属性：

cusor%attribute

其中，cursor就是我们声明的游标名字或者游标变量。

显示游标属性

名    字	说    明
cusor%FOUND	如果取到记录就返回TRUE，否者返回FLASE
cusor%NOTFOUND	如果没有取得记录就返回TRUE，否者返回FALSE
cusor%ROWCOUNT	返回当值从指定游标取得的记录数量
cusor%ISOPEN	如果游标时打开的则返回TRUE，否则返回FALE

 

2.6显示游标例子

2.6.1不带参数的显示游标

declare
  cursor cur_emp is
    select e.ename, e.sal from emp e where e.deptno = 10; --声明游标

  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;

 

2.6.2带参数参数的显示游标

declare
  cursor cur_emp(p_empno number) is
    select e.ename, e.sal from emp e where e.empno = p_empno; --声明游标

  v_empno emp.empno%type := 7788;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  open cur_emp(v_empno); --打开游标
  --循环取出
  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;

 

2.6.3一次取出所有记录

declare
  cursor cur_emp is
    select e.ename, e.sal from emp e where e.deptno = 10; --声明游标

  type it_ename is table of emp.ename%type index by binary_integer;
  type it_sal is table of emp.sal%type index by binary_integer;

  vit_ename it_ename;
  vit_sal   it_sal;
begin
  open cur_emp; --打开游标
  fetch cur_emp bulk collect
    into vit_ename, vit_sal; --一次性取出所有记录
  close cur_emp; --关闭游标

  for i in vit_ename.first .. vit_ename.last loop
    dbms_output.put_line(vit_ename(i) || '的工资是' || vit_sal(i));
  end loop;

end;

 

3.游标变量

游标变量是指向或者引用底层游标的变量。显示游标已经为结果集的工作区指定了名字，而游标变量只是指向这个工作区的引用。显示游标和隐式游标都绑定到一个专门的查询语句，而游标变量可以是任何一个查询语句，也可以是查询语句的字符串变量或者字面量。因为游标变量的查询语句可以是多个不相同的查询，因此这种游标称为动态游标。显示游标和隐式游标称为静态游标。

3.1声明REF CURSOR类型

TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

其中，cursor_type_name是游标类型的名字；return_type是该游标类型返回的数据类型。

REF CURSOR类型可是强类型（带有return）或者是弱类型（不带return）。以下两种声明都是有效的声明：

TYPE rc_emp is REF CURSOR RETURN emp%rowtype; --强类型

TYPE rc_emp is REF CURSOR；--弱类型

 

3.2声明游标变量

cursor_variable_name cursor_type_name;

其中，cursor_variable_name是游标变量的名字；cursor_type_name是之前的REF CURSOR声明的游标类型的名字。

 

3.3打开游标变量

OPEN cursor_variable_name FOR select_statement;

其中，cursor_variable_name是游标变量的名字; select_statement可以是一个直接的SQL语句，也可以一个SELECT语句的字符串或者字面。如果select_statement是字符串或者字面量，可以包含绑定变量的占位符，并可以使用USING子句标识相应的占位符值。

 

3.4提取数据

每次只提取一行数据：

FETCH  cursor_name

  INTO [(variable_name [, variable_name ]... | record_name )];

一次提取多行数据：

FETCH  cursor_name
  BULK COLLECT INTO [(collection_name [,collection_name...])];

3.5关闭显示游标

CLOSE cursor_name;

 

3.6游标变量属性

游标变量属性与显示游标属性一样，参见2.5

 

3.7游标变量例子

3.7.1一次只取一行记录

declare
  type rc_emp is ref cursor; --声明ref游标类型
  vrc_emp rc_emp; --声明游标变量
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  open vrc_emp for
    select e.ename, e.sal from emp e where e.deptno = 10; --打开游标变量，并赋值。
  loop
    fetch vrc_emp
      into v_ename, v_sal; --提取记录
    exit when vrc_emp%notfound;
    dbms_output.put_line(v_ename || '的工资是' || v_sal);
  end loop;
  close vrc_emp; ---关闭游标变量

end;

 

3.7.2一次取出所有行记录

declare
  type it_ename is table of emp.ename%type index by binary_integer;
  type it_sal is table of emp.sal%type index by binary_integer;
  type rc_emp is ref cursor; --声明ref游标类型\

  vrc_emp   rc_emp; --声明游标变量
  vit_ename it_ename;
  vit_sal   it_sal;
  v_ename   emp.ename%type;
  v_sal     emp.sal%type;
begin
  open vrc_emp for
    select e.ename, e.sal from emp e where e.deptno = 10; --打开游标变量，并赋值。
  fetch vrc_emp bulk collect
    into vit_ename, vit_sal; --一次性取出所有记录
  close vrc_emp; --关闭游标

  for i in vit_ename.first .. vit_ename.last loop
    dbms_output.put_line(vit_ename(i) || '的工资是' || vit_sal(i));
  end loop;

end;

 

3.7.3查询语句是字符串变量

declare
  type rc_emp is ref cursor; --声明ref游标类型
  vrc_emp  rc_emp; --声明游标变量
  v_ename  emp.ename%type;
  v_sal    emp.sal%type;
  v_deptno emp.deptno%type := 10;
  v_sql    clob;

begin
  v_sql := 'select e.ename, e.sal from emp e where e.deptno = :deptno';
  open vrc_emp for v_sql
    using v_deptno; --打开游标变量，并赋值。
  loop
    fetch vrc_emp
      into v_ename, v_sal; --提取记录
    exit when vrc_emp%notfound;
    dbms_output.put_line(v_ename || '的工资是' || v_sal);
  end loop;
  close vrc_emp; ---关闭游标变量

end;

4.游标表达式

游标表达式返回一个嵌套游标，使用CUSOR操作符表示。可以使用游标表达式从一个或者多个表中提取庞大的复杂的记录集。

可以在下面的场合使用游标表达式：

• 显示游标声明
• REF CURSOR声明和REF CURSOR变量
• 动态SQL查询

 

游标表达式语法：

CURSOR (subquery)

当父游标提取数据时，嵌套游标就会隐式地打开。嵌套游标在以下这些时刻关闭：

• 用户显示地关闭嵌套游标
• 父游标再次执行
• 父游标关闭
• 父游标取消
• 从父游标提取数据时抛出了异常。嵌套游标会和父游标一起关闭

使用游标表达式，查询各个部门的名称、地址以及该部门下的员工：

declare
  cursor cur_dept_emp is
    select d.dname,
           d.loc,
           cursor (select * from emp e where e.deptno = d.deptno) nc_emp --嵌套游标
      from dept d;

  type rc_emp is ref cursor return emp%rowtype;
  vrc_emp rc_emp;
  vrt_emp emp%rowtype;
  v_dname dept.dname%type;
  v_loc   dept.loc%type;

begin
  open cur_dept_emp;
  loop
    fetch cur_dept_emp
      into v_dname, v_loc, vrc_emp; --提取父游标数据，并自动打开嵌套游标
    exit when cur_dept_emp%notfound;
    dbms_output.put_line(v_loc || '的' || v_dname || '部门的员工信息：');
    loop
      fetch vrc_emp
        into vrt_emp; --从嵌套游标提取数据
      exit when vrc_emp%notfound;
      dbms_output.put_line(vrt_emp.ename || '的工资' || vrt_emp.sal);
    end loop;
    dbms_output.put_line('');
  end loop;
  close cur_dept_emp; --关闭父游标，同时也关闭了嵌套游标

end;

 

5.游标FOR循环

游标FOR循环的迭代变量不需要事先声明。这是一个%ROWTYPE记录，其字段名称匹配查询的列名，而且只能存在于循环中。数据库自动打开、提取、关闭游标FOR循环。即使在循环中使用EXIT语句、GO语句或者抛出异常，数据库都会自动关闭该游标。游标FOR循环可以分为：隐式游标FOR循环和显示游标FOR循环。

5.1隐式游标FOR循环

begin
  --隐式游标FOR循环
  for vrc_emp in (select * from emp e where e.deptno = 10) loop
    dbms_output.put_line(vrc_emp.ename || '的工资' || vrc_emp.sal);
  end loop;
end;

 

5.2显示游标FOR循环

declare
  cursor cur_emp is
    select * from emp e where e.deptno = 10;
begin
  --隐式游标FOR循环
  for vrc_emp in cur_emp loop
    dbms_output.put_line(vrc_emp.ename || '的工资' || vrc_emp.sal);
  end loop;
end;

 

6.WHERE CURRENT OF语句

PL/SQL为游标的UPDATE和DELETE语句提供了WHERE CURRENT OF语句。当你声明一个在UPDATE或者DELETE语句的CURRENT OF子句引用的游标时，必须使用FOR UPDATE语句获取独立的行级锁。

 

要修改最新取出来的记录的列：

UPDATE table_name

  SET set_clause

 WHERE CURRENT OF cursor_name;

 

要删除最新取出的记录：

DELETE FORM table_name WHERE CURRENT OF cursor_name;

 

declare
  cursor cur_emp is
    select * from emp for update nowait;
  vrt_emp emp%rowtype;
begin
  open cur_emp;
  loop
    fetch cur_emp
      into vrt_emp;
    exit when cur_emp%notfound;
    if vrt_emp.ename = 'SCOTT' then
      update emp e set e.sal = 6000 where current of cur_emp; --注意current of 后面子游标，而不是记录
    end if;
    if vrt_emp.ename = 'CHICLEWU' then
      delete from emp where current of cur_emp;
    end if;
  end loop;
end;