Oracle Cursor 游标

Oracle 游标


一、游标的概述
当在PL/SQL块中执行select和dml语句时，Oracle会为其分配上下文区，游标是指向上下文去的指针。


二、游标的分类
1、Oracle中的游标分为显式游标和隐式游标：
显式游标：用cursor...is 命令定义的游标，它可以对select语句返回的多条记录进行处理。
隐式游标：在执行insert、delete、update和返回单条记录的查询select into语句时由pl/sql自动定义，隐式游标也叫sql游标。


2、Oracle显式游标分为普通游标、参数游标、游标变量。
其中普通游标和参数游标也被称为静态游标，游标变量被称为动态游标。


一般所说的游标是指显式游标，因为隐式并不能被操作者控制，只能获取他的属性，本篇先讲述最常用的显式游标，隐式游标在最后简述。


三、显式游标的一般使用步骤
定义游标：cursor cursor_name is select_sql; (注意，游标的定义只能用使关键字IS，它与AS不通用)
打开游标：open cursor_name;
提取数据：
语法1 fetch cursor_name into variable1[,variable2,...];
其中，fetch into 每次只能提取一行数据，批量数据需使用循环；variable指定接收游标数据的变量,select_sql的字段有几个，就有几个variable。
语法2 fetch cursor_name bulk collect into collect1,collect2,...[limit rows];
其中，collect指定接收游标结果的集合变量，这种语法9i后支持，比较耗内存。


显示游标(游标的四个属性)：
%isopen:检测游标是否已经打开，已经打开则返回true。
%found:检测游标结果集是否存在数据，存在返回true。如果打开游标后没有使用fetch推进，则返回null.
%notfound:检测游标结果集是否不存在数据，不存在返回true。
%rowcount:返回到当前行为止已提取的实际行数。


关闭游标：close cursor_name;


到底哪种类型可以把一行的数据都装进来，使用ROWTYPE类型，此类型表示可以把一行的数据都装进来。
打开一个已经打开的游标也是合法的。当第二次打开时，plsql先自动关闭，再打开，一次打开游标也是允许的。
关闭一个已经关闭的游标回报ORA-10001错误。


四、显式游标的三种类型(普通游标、参数游标、游标变量)
其中普通游标和参数游标被称为静态游标，游标变量被被称为动态游标。


1、普通游标
说明：最普通的游标。本例列出的是带变量v_cnt的普通游标


1.1、最普遍的方法fetch...into
说明：每次只能处理一行，为了处理多行，必须使用循环语句。
declare
  v_cnt number :=20;
  cursor emp_cursor is select ename,job,sal from emp where deptno>=v_cnt;
  vname emp.ename%type;
  vsal emp.sal%type;
  vjob emp.job%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into vname,vjob,vsal;
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名' || vname || '岗顶' || vjob || '工资' || vsal);
  end loop;
  close emp_cursor;
end;


declare
  v_cnt number :=20;
  cursor emp_cursor is select ename,job,sal from emp where deptno>=v_cnt;
  emp_record emp_cursor%rowtype;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名' || emp_record.ename || '岗顶' || emp_record.job || '工资' || emp_record.sal);
  end loop;
  close emp_cursor;
end;


2）、9i后新增的方法fetch...bulk collect into
说明：一次就可以提取结果的所有数据，需要使用PL/SQL集合数据类型。PL/SQL集合数据类型分为PL/SQL表(别名索引表)、嵌套表、VARRAY。
declare
  v_cnt number :=20;
  cursor emp_cursor is select ename from emp where deptno>=v_cnt;
  type ename_table_type is table of varchar2(10);  --定义 嵌套表
  ename_table ename_table_type;                    --自定义一个此数据类型的变量
begin
  open emp_cursor;
  fetch emp_cursor bulk collect into ename_table;
  for i in 1..ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
close emp_cursor;
end;


3）、9i后新增的方法fetch...bulk collect into...limit
说明：提起部分数据
declare
  type name_array_type is varray(5) of varchar2(10);  --定义 VARRAY
  name_array name_array_type;
  cursor emp_cursor is select name from emp;
  rows int:=5;
  v_count int:=0;
begin
  open emp_cursor;
  loop 
    fetch emp_cursor bulk collect into name_array limit rows;
    dbms_output.put('雇员名');
    for i in 1..(emp_cursor%rowcount-v_count) loop
      dbms_output.put(name_array(i));
    end loop;
    dbms_output.new_line;
    v_count:=emp_cursor%rowcount;
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
end;


2、参数游标
说明：参数只指定数据类型，不能指定长度，而且必须在where子句中引用参数，与带变量的普通游标相比，进一步缩小了变量的作用范围。
在打开游标前，应先为绑定在游标中的变量赋值，当使用不同参数值多次打开游标时，可以生产不同的结果集，
但如果打开游标后，再为该变量赋值，查询结果集不发生任何改变，这是因为结果集在游标打开时被确定，同时指向结果集的指针也被确认。


1）、普通参数游标
declare
  v_cnt number :=2;
  cursor ec(p_dno number) is select ename,job from emp where deptno>=p_dno;
begin
  for erec in ec(v_cnt) loop
    dbms_output.put_line('姓名' || erec.ename || '岗位' || erec.job);
  end loop;
end;


2）、cursor表达式
说明：9i新增，用于返回嵌套游标。
declare
  type myis_refcursor is ref cursor;
  empcur myis_refcursor;
  cursor dept_cursor(no number) is select a.dname,cursor(select ename,sal from emp where deptno=a.deptno) from dept a where a.deptno=no;
  v_dname dept.dname%type;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  open dept_cursor(&no);
  loop
    fetch dept_cursor into v_dname,empcur;
    exit when dept_cursor%notfound;
    loop
      fetch empcur into v_ename,v_sal;
      exit when empcur%notfound;
    end loop;
  end loop;
  close dept_cursor;
end;


3、游标变量
说明：游标变量是基于ref cursor类型所定义的变量，它实际上是指向内存地址的指针。
使用显式游标只能定义静态游标，而通过使用游标变量可以在打开游标时指定其对应的select语句，从而实现动态游标。
结构：TYPE type_name is ref cursor [return return_type];
其中type_name是游标变量类型名，return_type是一个记录类型，这个记录类型指定了游标变量最终返回的查询结果集类型，
可以是用户自定义的记录类型或%rowtype定义的记录类型。


1）使用无返回类型的游标变量
说明：如果不指定return子句，那么打开游标时可以指定的select语句。
declare
  v_col1 varchar2(20):='empno';
  v_col2 varchar2(20):='ename';
  v_tablename varchar2(20):='emp';
  v_cond varchar2(20):='deptno=10 and cnt>=5000';
  type ref_cursor_type is ref cursor; --定义
  rc ref_cursor_tye; --声明
  v1 number(6);
  v2 varchar2(10);
begin
  open rc for select v_col1,v_col2 from v_tablename where v_cond; --用open for来打开一个查询，需要注意的是它可以多次使用,用来打开不同的查询。
  loop
    fetch rc into v1,v2;
    exit when rc%notfound;
    dbms_output.put_line('col1=' || v1 || ',col2=' || v2);
  end loop;
  close rc;
end;


2）使用有返回类型的游标变量
说明：对于有返回类型的游标变量，打开游标open查询列表必须与游标的返回类型匹配，且列也要一一对应。
注意：在打开游标，open查询语句时不能使用for ypdate，在dml语句中也不能使用where current of，这与静态游标不同。
我们可以为不同的查询多次打开一个游标变量，在重新打开这个游标变量之前不必先关闭它，这与静态游标不同。
当为一个不同的查询再次打开该游标时，前面的查询就丢失。


例一(用rowtype定义记录类型)：
declare
  dno number:=20;
  type emp_cursor_type is ref cursor return emp%rowtype;  --%rowtype定义的记录类型
  ec emp_cursor_type;
  er emp%rowtype;
begin
  open ec for select * from emp where deptno=dno; --
  loop
    fetch ec into er;
    exit when ec%not found;
    dbms_output.put_line('姓名' || er.ename ||  '工资' || er.sal);
  end loop;
  close ec;
end;


例二(自定义记录类型)：
declare
type t_coderecord is record(au_code article.au_code%type,ar_code article.ar_code%type);--自定义一个记录类型
v_code t_coderecord;--声明一个变量
type t_coderef is ref cursor return t_coderecord;
type t_coderef2 is ref cursor return v_code%type;
v_code t_coderef;


五、显式游标的三种循环策略LOOP循环、WHILE循环、FOR循环。
一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用，检查它的%found或%notfound属性，属性才有值(ture\false)，否则得到的结果是null。 


1、LOOP循环游标
说明：loop循环开始，先执行fetch语句，紧跟接着exit when语句的%notfound判断，然后再进行数据处理动作。
declare
  v_cnt number :=20;
  cursor emp_cursor is select ename,job,sal from emp where deptno=v_cnt;  --游标的定义
  vname emp.ename%type;
  vsal emp.sal%type;
  vjob emp.job%type;
begin
  open emp_cursor;  --打开游标
  loop
    fetch emp_cursor into vname,vjob,vsal;  --检索一条记录
    exit when emp_cursor%notfound;  
--exit when语句在fetch语句之后，这样在最后一行被检索后，emp_cursor%notfound变为true，循环结束。exit when语句同时放在数据处理语句前，这样做能确保不会重复处理同一行（检索到的最后一行）。
    dbms_output.put_line('姓名' || vname || '岗顶' || vjob || '工资' || vsal);
  end loop;
  close emp_cursor;  --关闭游标
end;


2、while循环游标
说明：使用while 循环时，需要在循环之前进行一次fetch动作，而且数据处理动作必须放在循环体内的fetch方法之前，循环体内的fetch方法要放在最后。否则就会多处理一次，这一点也要非常的小心。
declare
  v_cnt number :=20;
  cursor emp_cursor is select ename,job,sal from emp where deptno=v_cnt;
  vname emp.ename%type;
  vsal emp.sal%type;
  vjob emp.job%type;
begin
  open emp_cursor;
  fetch emp_cursor into vname,vjob,vsal;
  while emp_cursor%found loop
    dbms_output.put_line('姓名' || vname || '岗顶' || vjob || '工资' || vsal);
    fetch emp_cursor into vname,vjob,vsal;
  end loop;
  close emp_cursor;
end;


3、FOR循环游标
说明：使用游标FOR循环时，Oracle会隐含的自动打开游标、关闭游标，并自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。


1）、使用游标FOR循环
declare
  v_cnt number :=3;
  cursor c_ec is select ename,hiredate from emp order by hiredate desc;
begin                     --开始游标for循环，隐含地打开c_ec游标
  for v_erec in c_ec loop   --v_erec这个变量无需在循环外进行声明，无需为其指定数据类型，for循环隐含地声明了。
                          -- 一个隐含的fetch语句在这里被执行
    dbms_output.put_line('姓名' || v_erec.ename || '工作时间' || v_erec.hiredate);
    exit when c_ec%rowcount=v_cnt;
                               -- 循环继续前，一个隐含的c_ec%notfound被检查
  end loop;--循环结束，c_ec游标的一个隐含close操作被执行。
end;


2）、在游标for循环中直接使用子查询
说明：如果在使用for循环时不需要使用任何游标属性，那么可以直接在游标for循环中使用子查询。
begin
  for emp_record in (select ename,sal from emp) loop
    dbms_output.put_line(emp_record.ename);
  end loop;
end;


综上，for循环游标的方法最高效简洁安全，while循环最复杂，而实际使用中最常用的是loop循环。
另外，如果在update语句中使用current of cursor子句时，必须先声明游标的select语句中有for update of语句。


六、接收显式游标数据的数据类型选择


1.使用普通变量接收游标数据(上面的例子中已经普遍引用)
declare
  v_cnt number :=20;
  cursor emp_cursor is select ename,job,sal from emp where deptno=v_cnt;
  vname emp.ename%type;
  vsal emp.sal%type;
  vjob emp.job%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into vname,vjob,vsal;
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名' || vname || '岗顶' || vjob || '工资' || vsal);
  end loop;
  close emp_cursor;
end;


2.使用PL/SQL记录变量接收游标数据：简化单行数据处理
declare
  v_cnt number :=5;
  cursor ecur is select ename,sal from emp order by sal desc; --游标
  erec ecur%rowtype; --定义声明一个行级的PL/SQL记录变量erec
begin
  open ecur;
  loop
    fetch ecur into erec;
    exit when ecur%notfound or ecur%rowcount>v_cnt;
    dbms_output.put_line('姓名' || erec.ename ||  '工资' || erec.sal);
  end loop;
  close ecur;
end;


3.使用PL/SQL集合变量接收游标数据，简化多行多列数据处理
declare
  v_cnt number :=5;
  cursor ec is select ename,sal from emp where lower(job)=lower(v_cnt); --游标
  type eetype is table of ec%rowtype index by binary_intefer; --定义一个复合数据类型PL/SQL表变量eetype
  et eetype; --声明etype
  i int;
begin
  open ec;
  loop
    i:=ec%rowcount+1;
    fetch ec into et(i);
    exit when ec%notfound;
    dbms_output.put_line('姓名' || et(i).ename ||  '工资' || et(i).sal);
  end loop;
  close ec;
end;


七、通过游标更新、删除表的数据
说明：必须要带for update子句。
语法：cursor cursor_name(parameter_name datatype) is select_statement for update [of column_reference] [nowait]
其中，for update用于在游标结果集加共享锁。
当select语句引用多张表示，使用of子句可以确定哪些表要加锁，没有则select语句所引用的全部表加上锁。
nowait用于指定不等待锁，当其他会话已经锁表之后，默认是当前会话一直等待释放，但指定了NOWAIT后，
如果表已经被其它会话加锁，则抛出异常并退出当前块。
注意，为了更新或删除当前游标行数据，必须在update或delete语句中引用where current of子句。
例子：
cursor c_name is select emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno for update;--全部加共享锁
cursor c_name is select emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno for update of emp.deptno;--只在emp表加共享锁
cursor c_name is select sal from emp for update nowait;


declare
  cursor emp_cursor is select ename,sal,deptno from emp for update;--默认所有表均加上共享锁。
  dno int:=1001;
begin
  for emp_record in emp_cursor loop
    if emp_record.deptno=dno then
      update emp set sal=sal*1.1 where current of emp_cursor; --更新操作
      delete from emp where current of emp_cursor; --删除操作
    end if;
  end loop;
end;


八、显式游标在开发中不多见的应用


2.通过游标更新、删除表的数据对于多表的情况（使用for子句在特定表上加共享锁）
declare
  cursor emp_cursor is select a.dname,b.ename from dept a join emp b on a.deprno=b.deptno;
  name varchar2(20):='sales';
begin
  for emp_record in emp_cursor loop
    if emp_record.dname=name then
      delete from emp where current of emp_cursor;
    end if;
  end loop;
end;


3.使用fetch...bulk collect批量提取的用法
说明：批量提起所有数据
declare
  v_name varchar2(20):='clerk';
  cursor ec is select * from emp where job=v_name;
  type etype is table of emp%rowtype; --定义一个复合数据类型嵌套表etype
  et etype;
begin
  open ec;
  fetch ec bulk collect into et;
  close ec;
  for i in 1..et.count loop
    dbms_output.put_line('姓名' || et(i).ename || '工资' || et(i).sal);
  end loop;
end;


4.使用fetch...bulk collect批量提取 + limit子句限制提取行数的应用
declare
  v_cnt number:=4;
  cursor ec is select * from emp;
  type emp_array_type is varray(5) of emp%rowtype; --定义一个复合数据类型varray变成数组
  ea emp_array_type;
begin
  open ec;
  loop
    fetch ec bulk collect into ea limit v_cnt;
    for i in 1..ea.count loop
      dbms_output.put_line('姓名' || et(i).ename || '工资' || et(i).sal); --结果只会输出4行
    end loop;
    exit when ec%notfound;
  end loop;
  close ec;
end;


5.游标中保存着游标的应用
declare
  v_dno number:=5010;
  cursor dept_cursor(no number) is select a.dname,cursor(select * from emp where deptno=a.deptno) from dept a where a.deptno=no;
  type ref_cursor_type is ref cursor;
  ec ref_cursor_type;
  er emp%rowtype;
  vdname dept.dname%type;
begin
  open dept_cursor(v_dno);
  loop
    fetch dept_cursor into vdname,ec;
    exit when dept_cursor%notfound;
    dbms_output.put_line('部门' || vdname);
    loop
      fetch ec into er;
      exit when ec%notfound;
      dbms_output.put_line('雇员' || er.ename || '岗位' || er.job); --一个部门有多名员工
    end loop;
  end loop;
  close dept_cursor;
end;


九、隐式游标
1、介绍
(1)、显式游标仅仅是用来控制返回多行的select语句。而隐式游标是指向处理所有的sql语句的环境区域的指针，隐式游标也叫sql游标。
(2)、与显式游标不同的是，sql游标不能通过专门的命令打开或关闭。pl/sql隐式地打开sql游标，并在它内部处理sql，然后关闭它。
(3)、sql游标用来处理insert、update、delete、以及返回一行的select...into语句。一个sql游标不管是打开还是关闭，open、fetch和close命令都不能操作它。


2、隐式游标的四个属性
(1)、%found
说明：当使用insert,delete,update语句处理一行或多行，或执行select into 语句返回一行是，%found属性返回true,否则为false.
注意：当select into语句返回多行数据报too_meny_rows异常时，%found并不会返回true；select into语句返回0行数据报no_data_found异常时，%found也不会返回false，因为已经转到异常处理部分。
(2)、%notfound
说明：与%found属性相反。
(3)、%isopen
说明：在执行dml语句之前后，Oracle会自动隐含式的打开和关闭sql游标，所以%isopen总为false.
(4)、%rowcount
说明：该属性返回执行insert,delete,update语句返回的行数，或返回执行select into语句时查询出的行数。如果insert,delete,update,select into语句返回行数为0，则%rowcount属性返回0。
注意：如果select into语句返回多行，则产生too_meny_rows异常，会转扫异常处理部分，而不会去判断%rowcount.


3、应用例子
(1)、%notfound
begin
  update auths set entry_date_time=sysdate where author_code='A00017';
--如果update语句中修改的行不存在(sql%notfound返回值为true)
--则向auths表中插入一行
  if sql%notfound then
    insert into auths(author_code,name) values('A00017','照');
  end if;
end;


(1)、%rowcount
begin
  update auths set entry_date_time=sysdate where author_code='A00017';
--如果update语句中修改的行不存在(sql%rowcount=0)
--则向auths表中插入一行
  if sql%rowcount=0 then
    insert into auths(author_code,name) values('A00017','照');
  end if;
end;