文章详情页_Oracle 游标

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

游标概念

为了处理 SQL 语句，Oracle 必须分配一片名叫上下文（context area）的区域（即缓冲区）来处理所必须的信息，其中包括要处理的数目、一个指向语句被解析后的表示形式的指针以及查询的活动集（active set）。

游标是一个指向上下文的句柄（Handle）或指针。

隐式游标

隐式游标是 Oracle 数据库中最常见的一种 Session Cursor，它无处不在，我们在 SQL*PLUS 或者在 PL/SQL 代码中直接执行 SQL 语句时，Oracle 实际上都帮我们自动创建了隐式游标来作为这些 SQL 语句执行的载体。

之所以称之为 “隐式游标”，是因为它的生命周期管理（即对 Open、Parse、Bind、Execute、Fetch 和 Close 的控制）全部是由 SQL 引擎或 PL/SQL 引擎自动完成，所以我们常常感觉不到它的存在。

隐式游标的生命周期管理全部是由 SQL 引擎或 PL/SQL 引擎自动完成的，这意味着我们在使用隐式游标时不需要写任何额外的代码，Oracle 会帮我们全部搞定，但同时也意味着我们失去了对隐式游标的控制权。

虽然不能控制隐式游标的 Open、Parse、Bind、Execute、Fetch 和 Close，但是我们通过隐式游标的一些属性来了解与之对应的刚执行过的目标 SQL 语句的一些信息。

隐式游标的属性

Oracle 数据库中隐式游标有如下这四个最常用的属性。

SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQL%ROWCOUNT

SQL%FOUND 属性

SQL%FOUND 表示一条 SQL 语句被执行成功后受其影响而改变的记录数是否大于 1 或等于 1。既然是要改变记录，这意味着 SQL%FOUND 通常适用于那些执行 INSERT、UPDATE 和 DELETE 操作的 DML 语句，当然，它其实也可以用于 SELECT INTO 语句。

在一条 DML 被执行前，SQL%FOUND 的值是 NULL。当这条 DML 语句被执行并且成功改变了一条或者一条以上记录的时候，又或者 SELECT INTO 语句成功返回一条或一条以上记录的时候，SQL%FOUND 的值是 TRUE，否则为 FALSE。

# 使用 SQL%FOUND 属性实现，只有当成功删除表 DEPT 中的一条指定记录后，才会新插入一条记录

declare
  v_deptno  number;
begin
  v_deptno  := 50;
  delete from dept where deptno = v_deptno;
  if sql%found then 
    insert into dept values(100, 'Databse', 'ShangHai');
  end if;
  commit;
end;

注：在 PL/SQL 代码中使用 SELECT INTO 语句时，当且仅当对应的 SELECT 语句的返回结果只有一条记录时 Oracle 才不会报错。如果对应的 SELECT 语句的返回结果为 0，则 Oracle 会报错 “NO_DATA_FOUND”；如果对应的 SELECT 语句的返回结果大于 1，则 Oracle 会报错 “TOO_MANY_ROWS”。

SQL%NOTFOUND 属性

SQL%NOTFOUND 表示一条 SQL 语句被执行成功后受其影响而改变的记录数是否为 0。和 SQL%FOUND 一样，SQL%NOTFOUND 也适用于那些执行 INSERT、UPDATE 和 DELETE 操作的 DML 语句，当然，它也可以用于 SELECT INTO 语句。

在一条 DML 语句被执行前，SQL%NOTFOUND 的值是 NULL。SQL%NOTFOUND 逻辑上的含义和 SQL%FOUND 相反，当一条 DML 语句被执行且这条 DML 语句没有改变任何记录的时候，又或者 SELECT INTO 语句没有返回任何记录的时候，SQL%NOTFOUND 的值是 TRUE，否则为 FALSE。

SQL%ISOPEN 属性

SQL%ISOPEN 表示隐式游标是否处于 Open 状态。

对于隐式游标而言，SQL%ISOPEN 的值永远是 FALSE。因为 Oracle 一旦执行完隐式游标所对应的 SQL 语句后就会自动 Close 该隐式游标。

SQL%ROWCOUNT 属性

SQL%ROWCOUNT 表示一条 SQL 语句成功执行后受其影响而改变的记录的数量。和 SQL%FOUND、SQL%NOTFOUND 一样，SQL%ROWCOUNT 即适用于那些执行 INSERT、UPDATE 和 DELETE 操作的 DML 语句，也适用于 SELECT INTO 语句。

当与 DML 语句联用时，SQL%ROWCOUNT 表示该 DML 语句被执行后受其影响而改变的记录数。当一条 SQL 语句被执行后没有改变任何记录，又或者 SELECT INTO 语句所对应的 SELECT 语句没有返回任何记录的时候，SQL%ROWCOUNT 的值是 0。

# SQL%ROWCOUNT 表示被成功删除的记录数

declare
  v_deptno  number;
begin
  v_deptno  := 50;
  delete from dept where deptno = v_deptno;
  dbms_output.put_line('Number of dept deleted: ' || to_char(sql%rowcount));
  commit;
end;

注：当前 SQL%ROWCOUNT 的值仅仅代表最近一次执行的 SQL 的 SQL%ROWCOUNT 值，后续执行的 SQL 的 SQL%ROWCOUNT 值会覆盖之前执行的 SQL 所对应的 SQL%ROWCOUNT 值。如果代码里需要用到某条 SQL 的 SQL%ROWCOUNT 值，那么在该 SQL 执行完后把它所对应的 SQL%ROWCOUNT 值保存在一个变量里就可以了。

显示游标

显示游标是 Oracle 数据库中另外一种类型的 Session Cursor，它通常用于 PL/SQL 代码（如存储过程、函数、包等）中，之所以称为 “显示游标”，是因为其定义和生命周期管理中的 Open、Fetch 和 Close 均由我们在 PL/SQL 代码中显示控制。

显示游标的属性

Oracle 数据库中显示游标最常用的属性有如下四个（这里的 cursor_name 表示我们在 PL/SQL 代码中自定义的显示游标的名称）。

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ISOPEN
cursor_name%ROWCOUNT

cursor_name%FOUND 属性

cursor_name%FOUND 属性表示指定的显示游标是否至少有一条记录被 Fetch 了。

当一个显示游标被 Open 以后，如果还一次都没有被 Fetch，那么此时 cursor_name%FOUND 的值是 NULL。当这个显示游标被 Fetch 后，cursor_name%FOUND 的值为 TRUE，直到全部 Fetch 完毕。而全部 Fetch 完毕后，如果再次执行一次 Fetch 操作，Oracle 并不会报错，只是此时 cursor_name%FOUND 的值为 False。

如果一个显示游标还没有被 Open，这时候如果试图使用 cursor_name%FOUND 属性，则 Oracle 会报错 “INVALID_CURSOR”。

declare
  cursor c1 is select ename, sal from emp where rownum < 11;
  v_ename   emp.ename%type;
  v_sal     emp.sal%type;
begin
  open c1;
  loop
    fetch c1 into v_ename, v_sal;
    if c1%found then 
      dbms_output.put_line('name = ' || v_ename || ', salary = ' || v_sal);
    else
      exit;
    end if;
  end loop;
  close c1;
end;    
/

name = SMITH, salary = 800
name = ALLEN, salary = 1600
name = WARD, salary = 1250
name = JONES, salary = 2975
name = MARTIN, salary = 1250
name = BLAKE, salary = 2850
name = CLARK, salary = 2450
name = SCOTT, salary = 3000
name = KING, salary = 5000
name = TURNER, salary = 1500

从上述 PL/SQL 代码可以看出，显示游标 c1 是我们自定义的，而且对应的定义 SQL 语句为 “select ename, sal from emp where rownum < 11”。我们在使用属性 c1%found 之前已经先对 c1 执行了 Open 操作，并且随后对 c1 执行的 Fetch 和 Close 操作均由我们自己在 PL/SQL 代码中显示控制。

cursor_name%NOTFOUND 属性

cursor_name%NOTFOUND 属性表示指定的显示游标是否已经 Fetch 完毕了。

当一个显示游标被 Open 后，如果还一次都没有被 Fetch，那么 cursor_name%NOTFOUND 属性的值是 NULL。当这个显示游标被 Fetch 后，cursor_name%NOTFOUND 属性的值为 FALSE，直到全部 Fetch 完毕。全部 Fetch 完毕后，如果再执行一次 Fetch 操作，Oracle 并不会报错，只是此时 cursor_name%NOTFOUND 属性的值为 TRUE。

和 cursor_name%FOUND 属性一样，如果一个显示游标还没有被 Open，这时候试图使用 cursor_name%NOTFOUND 属性，则 Oracle 也会报错 “INVALID_CURSOR”。

declare
  cursor c1 is select ename, sal from emp where rownum < 11;
  v_ename   emp.ename%type;
  v_sal     emp.sal%type;
begin
  open c1;
  loop
    fetch c1 into v_ename, v_sal;
    if c1%notfound then 
      exit;
    else 
      dbms_output.put_line('name = ' || v_ename || ', salary = ' || v_sal);
    end if;
  end loop;
  close c1;
end;
/

name = SMITH, salary = 800
name = ALLEN, salary = 1600
name = WARD, salary = 1250
name = JONES, salary = 2975
name = MARTIN, salary = 1250
name = BLAKE, salary = 2850
name = CLARK, salary = 2450
name = SCOTT, salary = 3000
name = KING, salary = 5000
name = TURNER, salary = 1500

cursor_name%ISOPEN 属性

cursor_name%ISOPEN 属性表示指定的显示游标是否被 Open 了。

这个属性通常被用于标准的 Exception 处理流程中，用于 Close 那些由于发生了 Exception 而导致有显示游标没有被正常关闭的情形。

declare
  cursor c1 is select ename, sal from emp where rownum < 11;
  c_ename   emp.ename%type;
  c_sal     emp.sal%type;
begin
  open c1;
  fetch c1 into c_ename, c_sal;
  dbms_output.put_line('name = ' || c_ename || ', salary = ' || c_sal);
exception
  when others then 
    if c1%isopen = true then 
      close c1;
    end if;
    return;
end;
/

name = SMITH, salary = 800

cursor_name%ROWCOUNT 属性

cursor_name%ROWCOUNT 属性表示指定的显示游标迄今为止一共 Fetch 了多少行记录。

当一个显示游标被 Open 后，如果还一次都没有被 Fetch，那么 cursor_name%ROWCOUNT 的值是 0。当一个显示游标被 Open 后，如果 Fetch 后返回的结果集是空的，那么 cursor_name%ROWCOUNT 的值也是 0。只要这个显示游标被 Fetch 过一次且返回的结果集不是空的，那么 cursor_name%ROWCOUNT 的值就不是 0 了。并且，随着每一次 Fetch，cursor_name%ROWCOUNT 的值都会递增，它的当前值就表示指定的显示游标迄今为止一共 Fetch 了多少行记录。

和 cursor_name%FOUND、cursor_name%NOTFOUND 属性一样，如果一个显示游标还没有被 Open，这时候试图使用 cursor_name%ROWCOUNT 属性，则 Oracle 也会报错 “INVALID_CURSOR”。

declare
  cursor c1 is select ename from emp where rownum < 10;
  v_ename   emp.ename%type;
begin
  open c1;
  loop
    fetch c1 into v_ename;
    if c1%found then 
      dbms_output.put_line(c1%rowcount || ': ' || 'name = ' || v_ename);
    else
      exit;
    end if;
  end loop;
end;
/

1: name = SMITH
2: name = ALLEN
3: name = WARD
4: name = JONES
5: name = MARTIN
6: name = BLAKE
7: name = CLARK
8: name = SCOTT
9: name = KING

从上述执行结果可以看出，随着对显示游标 c1 的每一次 Fetch 操作，c1%rowcount 的值确实会递增。

处理显示游标

处理显式游标需要如下四个 PL/SQL 步骤

定义游标

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

CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;

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

打开游标

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

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

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

提取游标数据

就是检索结果集合中的数据行，放入指定的输出变量中。

FETCH cursor_name INTO {variable_list | record_variable };

执行 FETCH 语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行 FETCH 语句，游标属性 %NOTFOUND 将会变成 TRUE。所以每次执行完 FETCH 语句后，检查游标属性 %NOTFOUND 就可以判断 FETCH 语句是否执行成功并返回了一个数据行，以便确定是否给对应的变量赋了值。

关闭游标

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

CLOSE cursor_name;

显示游标应用案例

查询薪水前十名员工的信息

SQL> conn hr/hr@localhost/orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as hr@localhost/orcl


declare
  cursor c1 is 
    select ename, salary from (
      select first_name || ' ' || last_name ename, salary from employees order by salary desc
    ) where rownum < 11;
  v_ename   employees.first_name%type;
  v_salary  employees.salary%type;
begin
  open c1;
  loop
    fetch c1 into v_ename, v_salary;
      if c1%found then 
        dbms_output.put_line(c1%rowcount || '： ' || rpad(v_ename, 20, ' ') || ', ' || v_salary);
      else
        exit;
      end if;
  end loop;
end;
/

1： Steven King         , 24000
2： Neena Kochhar       , 17000
3： Lex De Haan         , 17000
4： John Russell        , 14000
5： Karen Partners      , 13500
6： Michael Hartstein   , 13000
7： Nancy Greenberg     , 12000
8： Shelley Higgins     , 12000
9： Alberto Errazuriz   , 12000
10： Lisa Ozer           , 11500

给工资低于 3000 的员工增加工资 50

declare
  cursor c1 is select employee_id, salary from employees;
  v_empno   employees.employee_id%type;
  v_sal     employees.salary%type;
begin
  open c1;
  loop
    fetch c1 into v_empno, v_sal;
    exit when c1%notfound;
    if v_sal < 3000 then 
      update employees set salary = salary + 50 where employee_id = v_empno;
      dbms_output.put_line('编号为' || v_empno || '的员工工资已调整...');
    end if;
  end loop;
  close c1;
  commit;
end;
/

编号为116的员工工资已调整...
编号为117的员工工资已调整...
编号为118的员工工资已调整...
编号为119的员工工资已调整...
编号为126的员工工资已调整...
编号为127的员工工资已调整...
编号为128的员工工资已调整...
编号为130的员工工资已调整...
编号为131的员工工资已调整...
编号为132的员工工资已调整...
编号为134的员工工资已调整...
编号为135的员工工资已调整...
编号为136的员工工资已调整...
编号为139的员工工资已调整...
编号为140的员工工资已调整...
编号为143的员工工资已调整...
编号为144的员工工资已调整...
编号为182的员工工资已调整...
编号为183的员工工资已调整...
编号为190的员工工资已调整...
编号为191的员工工资已调整...
编号为195的员工工资已调整...
编号为198的员工工资已调整...
编号为199的员工工资已调整...

游标参数的传递方法

declare
  cursor c1 is select department_name, location_id from departments where department_id <= 30;
  cursor c2(v_deptno number default 10) is select department_name, location_id from departments where department_id <= v_deptno;
  cursor c3(v_deptno number default 10) is select * from departments where department_id <= v_deptno;
  v_deptrec  departments%rowtype;
  v_deptname departments.department_name%type;
  v_deptloc  departments.location_id%type;
begin
  open c1;
  loop
    fetch c1 into v_deptname, v_deptloc;
    exit when c1%notfound;
      dbms_output.put_line('c1：' || v_deptname || '---' || v_deptloc);
  end loop;
  close c1;

  open c2(v_deptno => 30);
  loop
    fetch c2 into v_deptname, v_deptloc;
    exit when c2%notfound;
      dbms_output.put_line('c2：' || v_deptname || '---' || v_deptloc);
  end loop; 
  close c2;

  open c3(v_deptno => 20);
  loop
    fetch c3 into v_deptrec;
    exit when c3%notfound;
      dbms_output.put_line('c3：' || v_deptrec.department_id || '---' || v_deptrec.department_name || '---' || v_deptrec.location_id);  
  end loop;  
  close c3;
end;
/

c1：Administration---1700
c1：Marketing---1800
c1：Purchasing---1700
c2：Administration---1700
c2：Marketing---1800
c2：Purchasing---1700
c3：10---Administration---1700
c3：20---Marketing---1800

不带参数且没有返回值的游标

declare
  cursor c1 is select * from employees where rownum < 11;
  v_emprec employees%rowtype;
begin
  open c1;
  loop
    fetch c1 into v_emprec;
    if c1%found then 
      dbms_output.put_line(v_emprec.first_name || '的岗位是：' || v_emprec.job_id);
    else
      dbms_output.put_line('已经处理完结果集了...');  
      exit;
    end if;
  end loop;
  close c1;
end;
/

Steven的岗位是：AD_PRES
Neena的岗位是：AD_VP
Lex的岗位是：AD_VP
Alexander的岗位是：IT_PROG
Bruce的岗位是：IT_PROG
David的岗位是：IT_PROG
Valli的岗位是：IT_PROG
Diana的岗位是：IT_PROG
Nancy的岗位是：FI_MGR
Daniel的岗位是：FI_ACCOUNT
已经处理完结果集了...

带参数且没有返回值的游标

alter session set nls_date_format = 'YYYY-MM-DD';

declare
  cursor c1(v_deptno number, v_jobno varchar2) is 
    select first_name, hire_date from employees where department_id = v_deptno and job_id = v_jobno;
  v_fname employees.first_name%type;
  v_hdate employees.job_id%type;
begin
  open c1(v_deptno => 90, v_jobno => 'AD_VP');  --打开游标，传递参数值
  loop
    fetch c1 into v_fname, v_hdate;
    exit when c1%notfound;
      dbms_output.put_line(v_fname || '的雇佣日期是：' || v_hdate);
  end loop;
  close c1;
end;
/

Neena的雇佣日期是：1989-09-21
Lex的雇佣日期是：1993-01-13

带参数且有返回值的游标

alter session set nls_date_format = 'YYYY-MM-DD';

declare
  type emp_record is record(v_fname employees.first_name%type, v_hdate employees.hire_date%type);
  v_emp_record    emp_record;
  cursor c1(v_deptno number, v_job varchar2) return emp_record is 
    select first_name, hire_date from employees where department_id = v_deptno and job_id = v_job;
begin
  open c1(v_deptno => 90, v_job => 'AD_VP');
  loop
    fetch c1 into v_emp_record;
    if c1%found then 
      dbms_output.put_line(v_emp_record.v_fname || '的雇佣日期是：' || v_emp_record.v_hdate);
    else
      dbms_output.put_line('已经处理完结果集了...');
      exit;
    end if;
  end loop;
  close c1;
end;
/

Neena的雇佣日期是：1989-09-21
Lex的雇佣日期是：1993-01-13
已经处理完结果集了...

参考游标

和显示游标一样，参考游标通常也用于 PL/SQL 代码（如存储过程、函数、包等）中，参考游标的定义和其生命周期管理中的 Open、Fetch 和 Close 也是由我们在 PL/SQL 代码中显示控制的。

参考游标的属性

Oracle 数据库中参考游标最常用的属性有如下四个（这里的 cursor_name 表示我们在 PL/SQL 代码中自定义的参考游标的名称）。

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ISOPEN
cursor_name%ROWCOUNT

参考游标的上述四种属性与显示游标的对应属性的内容和含义是一模一样的。具体内容详见显示游标。

参考游标应用案例

参考游标是 Oracle 数据库中最灵活的一种 Session Cursor，它的灵活性表现在如下这三个方面。

参考游标的定义方式非常灵活

--第一种定义方式
type ref_cur_emp is ref cursor return emp%rowtype;
cur_emp ref_cur_emp;

--第二种定义方式
type ref_result is record(ename emp.ename%type, sal emp.sal%type)
type ref_cur_strong is ref cursor return ref_result;
cur_emp ref_cur_strong

--第三种方式
type ref_cur_week is ref cursor;
cur_emp ref_cur_week;

--第四种方式
cur_emp SYS_REFCURSOR;

上述 PL/SQL 代码用四种不同的方式分别定义了同一个参考游标 cur_emp。

参考游标可以作为存储过程的输入参数

declare
  type ref_cur_emp is ref cursor return emp%rowtype;
  cur_emp ref_cur_emp;
  procedure p_emp_cv(i_emp_cv in ref_cur_emp) is 
    v_person emp%rowtype;
  begin
    dbms_output.put_line('-----');
    loop
      fetch i_emp_cv into v_person;
      exit when i_emp_cv%notfound;
      dbms_output.put_line('name = ' || v_person.ename || 'salary = ' || v_person.sal);
    end loop;
  end;    
begin
  open cur_emp for select * from emp where rownum < 11;
  p_emp_cv(cur_emp);
  close cur_emp;
  open cur_emp for select * from emp where ename like 'C%';
  p_emp_cv(cur_emp);
  close cur_emp;
end;
/

-----
name = SMITHsalary = 800
name = ALLENsalary = 1600
name = WARDsalary = 1250
name = JONESsalary = 2975
name = MARTINsalary = 1250
name = BLAKEsalary = 2850
name = CLARKsalary = 2450
name = SCOTTsalary = 3000
name = KINGsalary = 5000
name = TURNERsalary = 1500
-----
name = CLARKsalary = 2450

从上述 PL/SQL 代码可以看出，参考游标 cur_emp 被作为内嵌存储过程 p_emp_cv 的输入参数，并且我们对参考游标 cur_emp 执行了两次 Open 操作，这两次 Open 操作所对应的 SQL 语句是不一样的。

参考游标的 Open 方式非常灵活

create or replace package pkg_refcursor_open_demo as 
  type gencurtyp is ref cursor;
  procedure open_cv (generic_cv in out gencurtyp, choice int);
end pkg_refcursor_open_demo;
/

create or replace package body pkg_refcursor_open_demo as 
  procedure open_cv (generic_cv in out gencurtyp, choice int) as 
  begin
    if choice = 1 then 
      open generic_cv for select * from emp;
    elsif choice = 2 then 
      open generic_cv for select * from dept;
    end if;
  end;
end pkg_refcursor_open_demo;
/

从上述 PL/SQL 代码可以看出，参考游标 generic_cv 会随着存储过程 open_cv 的输入参数 choice 的不同值而执行不同的 Open 操作，这些 Open 操作所对应的 SQL 语句都是不一样的。

参考游标额外用法

参考游标在执行 Fetch 操作时，除了常规的一次 Fetch 一条数据外，还可以一次 Fetch 一批数据

declare 
  type ref_cur_emp is ref cursor;
  type namelist is table of emp.ename%type;
  emp_cv ref_cur_emp;
  names  namelist;
begin
  open emp_cv for select ename from emp where rownum < 11;
  fetch emp_cv bulk collect into names;
  close emp_cv;

  for i in names.first .. names.last loop
    dbms_output.put_line('name = ' || names(i));
  end loop;
end;
/

name = SMITH
name = ALLEN
name = WARD
name = JONES
name = MARTIN
name = BLAKE
name = CLARK
name = SCOTT
name = KING
name = TURNER

参考游标和显示游标是可以嵌套的

declare
  type ref_cur_emp is ref cursor;
  emp_cur   ref_cur_emp;
  dept_name dept.dname%type;
  emp_name  emp.ename%type;
  cursor c1 is select dname, cursor (select ename from emp e where e.deptno = d.deptno) employees from dept d;
begin
  open c1;
  loop
    fetch c1 into dept_name, emp_cur;
    exit when c1%notfound;
    dbms_output.put_line('department：' || dept_name);
    loop
      fetch emp_cur into emp_name;
      exit when emp_cur%notfound;
        dbms_output.put_line(' -- employee：' || emp_name);
    end loop;
  end loop;
  close c1;
end;
/

department：ACCOUNTING
 -- employee：CLARK
 -- employee：KING
 -- employee：MILLER
department：RESEARCH
 -- employee：SMITH
 -- employee：JONES
 -- employee：SCOTT
 -- employee：ADAMS
 -- employee：FORD
department：SALES
 -- employee：ALLEN
 -- employee：WARD
 -- employee：MARTIN
 -- employee：BLAKE
 -- employee：TURNER
 -- employee：JAMES
department：OPERATIONS

游标的 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 循环语句中的索引变量来访问这些列数据。

declare
  cursor c1 is select employee_id, first_name || last_name ename, salary from employees where rownum < 11;
begin
  for v_c in c1 loop
    dbms_output.put_line(v_c.employee_id || '---' || v_c.ename || '---' || v_c.salary);
  end loop;
end;
/

100---StevenKing---24000
101---NeenaKochhar---17000
102---LexDe Haan---17000
103---AlexanderHunold---9000
104---BruceErnst---6000
105---DavidAustin---4800
106---ValliPataballa---4800
107---DianaLorentz---4200
108---NancyGreenberg---12000
109---DanielFaviet---9000

当所声明的游标带有参数时，通过游标 FOR 循环语句为游标传递参数

declare
  cursor c1(v_deptno number) is select department_name, location_id from departments where department_id <= v_deptno;
begin
  for v_c in c1(30) loop
    dbms_output.put_line(v_c.department_name || '---' || v_c.location_id);
  end loop;
end;
/

Administration---1700
Marketing---1800
Purchasing---1700

%TYPE 与 %ROWTYPE

%TYPE

为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，Oracle 提供了 %TYPE 定义方式。当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改 PL/SQL 程序了。当不能确切地知道被参照的那个变量的数据类型时，就只能采用这种方法定义变量的数据类型。

%ROWTYPE

如果一个表有较多的列，使用 %ROWTYPE 来定义一个表示表中一行记录的变量，比分别使用 %TYPE 来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供 %ROWTYPE 定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

declare
  v_sal employees.salary%type;
  v_emp employees%rowtype;
begin
  select salary into v_sal from employees where employee_id = 107;
  select * into v_emp from employees where employee_id = 109;
  dbms_output.put_line(v_sal);
  dbms_output.put_line(v_emp.first_name || v_emp.last_name || '：' || v_emp.salary);
end;
/

4200
DanielFaviet：9000

参考资料

《基于 Oracle 的 SQL 优化 -- 崔华》

ORACLE PL/SQL 编程之四：把游标说透

ORACLE 游标概念讲解

ORACLE 中游标详细用法

原创文章，转载请注明出处：http://www.opcoder.cn/article/44/