Oracle游标的使用

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户处理数据的过程中提供了很大方便。

在Oracle中，通过游标操作数据主要使用显式游标和隐式游标。另外，还包括具有引用类特性的REF游标。

1、显式游标

显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），使用它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤。

1.1 声明游标

声明游标主要包括游标名称和为游标提供结果集的SELECT语句。因此，在声明游标时，必须制定游标名称和游标所使用的SELECT语句，声明游标的语法格式如下：

cursor cur_name[(input_parameter1[,input_parameter2]…)]
[return ret_type]
is select_sentence;

cur_name：表示所声明的游标名称。

ret_type：表示执行游标操作后的返回值类型，这是一个可选项。

select_sentence：游标所使用的SELECT语句，它为游标的反复读取提供了结果集。

input_parameter1：作为游标的“输入参数”，可以有多个，这是一个可选项。它指定用户在打开游标后向游标中传递的值。

【实例】声明一个游标，用来读取emp表中职务为销售员（SALESMAN）的雇员信息。

declare
  cursor cur_emp(var_job in varchar2:='SALESMAN')
  is select empno,ename,sal
     from emp
     where job=var_job;

1.2 打开游标

open cur_name[(para_value1[,para_value2]…)];

cur_name：要打开的游标名称。

para_value1：指定“输入参数”的值。

【实例】紧接上一个例子中的代码，打开游标。

open cur_emp('MANAGER');

1.3 读取游标

当打开一个游标之后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。读取游标使用fetch…into语句，其语法格式如下：

fetch cur_name into {variable};

cur_name：要读取的游标名称。

variable：表示一个变量列表或“记录”变量（RECORD类型），Oracle使用“记录”变量来存储游标中的数据，要比使用变量列表方便得多。

1.4 关闭游标

游标使用完毕后需要关闭，以释放系统资源，比如SELECT语句返回的结果集等。它的语句格式。

close cur_name;

cur_name：表示要关闭的游标名称。

close cur_emp;

【实例】声明一个检索emp表中雇员信息的游标，然后打开游标，并指定检索职务是“MANAGER”的雇员信息，接着使用fetch…into语句和while循环读取游标中的所有雇员信息，并输出读取的雇员信息，最后使用close语句关闭游标。

declare
  /*声明游标，检索雇员信息*/
  cursor cur_emp(var_job in varchar2:='SALESMAN')
  is select empno,sal
     from emp
     where job=var_job;
     
  type record_emp is record       --声明一个记录类型（RECORD类型）
  (
     /*定义当前记录的成员变量*/
     var_empno emp.empno%type,var_ename emp.ename%type,var_sal emp.sal%type 
  );
  
  emp_row record_emp;             --声明一个record_emp类型的变量
begin
  open cur_emp('MANAGER');        --打开游标
  fetch cur_emp into emp_row;     --先让指针指向结果集中的第一行，并将值保存到emp_row中
  while cur_emp%found loop
    dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'，工资是'||emp_row.var_sal);
    fetch cur_emp into emp_row;   --让指针指向结果集中的下一行，并将值保存到emp_row中
  end loop;
  close cur_emp;                  --关闭游标 
end;

2、游标的属性

无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen和%rowcount四个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。下面对这4个属性的功能进行讲解。

（1）%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为fasle。

（2）%notfound：布尔型属性，与%found属性的功能相反。

（3）%rowcount：数字型属性，返回受SQL语句影响的行数。

（4）%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

【实例】声明一个游标，用于检索指定员工编号的雇员信息，然后使用游标的%found属性来判断是否检索到指定员工编号的雇员信息。

declare
  var_ename varchar2(50);                 --声明变量，用来存储雇员名称
  var_job varchar2(50);                   --声明变量，用来存储雇员的职务
  /*声明游标，检索指定员工编号的雇员信息*/  
  cursor cur_emp
  is select ename,job
    from emp
    where empno=7499;
begin
  open cur_emp;--打开游标
  fetch cur_emp into var_ename,var_job;   --读取游标，并存储雇员名和职务
  if cur_emp%found then                   --若检索到数据记录，则输出雇员信息
    dbms_output.put_line('编号是7499的雇员名称为：'||var_ename||'，职务是：'||var_job);
  else
    dbms_output.put_line('无数据记录');    --提示无记录信息
  end if;
end;

3、隐式游标

在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句（如，UPDATE、DELETE语句）的执行结果，当然特殊情况下，也可以处理SELECT语句的查询结果。由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——SQL。

在实际的PL/SQL编程中，经常使用隐式游标来判断更新数据行或删除数据行的情况。

【实例】在SCOTT模式下，把emp表中的销售员（即SALESMAN）的工资上调20%，然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量。

begin 
  update emp
  set sal=sal*(1+0.2)
  where job='SALESMAN';     --把销售员的工资上调20%
  if sql%notfound then      --若update语句没有影响到任何一行数据
    dbms_output.put_line('没有雇员需要上调工资');
  else                      --若update语句至少影响到一行数据        
    dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
  end if;
end;

4、通过for语句循环游标

在使用隐式游标或显式游标处理具有多行数据的结果集时，用户可以配合for语句来完成。在使用for语句遍历游标中的数据时，可以把它的计时器看做一个自动RECORD类型的变量。

（1）在for语句中遍历隐式游标中的数据时，通常在关键字“in”的后面提供由SELECT语句检索的结果集，在检索结果集的过程中，Oracle系统会自动提供一个隐式的游标sql。

【实例】使用隐式游标和for语句检索出职务是销售员的雇员信息并输出。

begin
  for emp_record in (select empno,sal from emp where job='SALESMAN')
  loop
    dbms_output.put('雇员编号：'||emp_record.empno);       --输出雇员编号
    dbms_output.put('；雇员名称：'||emp_record.ename);     --输出雇员名称
    dbms_output.put_line('；雇员工资：'||emp_record.sal);  --输出雇员工资
  end loop;
end;

（2）在for语句中遍历显式游标中的数据时，通常在关键字“in”的后面提供游标的名称，其语法格式如下：

for var_auto_record in cur_name loop
   plsqlsentence;
end loop;

var_auto_record：自动的RECORD类型的变量，可以是任意合法的变量名称。

cur_name：指定的游标名称。

plsqlsentence：PL/SQL语句。

declare
  cursor cur_emp is
  select * from emp
  where deptno=30;              --检索部门编号为30的雇员信息
begin
  for emp_record in cur_emp     --遍历雇员信息
  loop
    dbms_output.put('雇员编号：'||emp_record.empno);       --输出雇员编号
    dbms_output.put('；雇员名称：'||emp_record.ename);     --输出雇员名称
    dbms_output.put_line('；雇员工资：'||emp_record.sal);  --输出雇员工资
  end loop;
end;

在使用游标（包括显式和隐式）的for循环中，可以声明游标，但不用进行打开游标、读取游标和关闭游标等操作，这是由Oracle系统内部自动完成。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!