Oracle Cursor详解与实例

rrt44

摘要：详细介绍oracle数据库中关于游标的定义和使用。通过实例操作来深入了解cursor的用法和用处。

一：相关概念

        1、concept

        When Oracle Database executes aSQL statement , it stores the result set and processing information in anunnamed private SQL area . A pointer to this unnamed area , called a cursor ,let you retrieve the rows of the result set one at a time . Cursor attributesreturn information about the state of the cursor .

        2、概念：

        游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。Cursor类型：静态游标——分为显式（explicit）游标和隐式（implicit）游标、REF游标——动态游标、是一种引用类型、类似于指针。

二：具体类型及使用

        1、implicit cursor

        1） explanation：Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. You can get information about this cursor from itsattributes, but you cannot control it. After the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.
        2） implicit cursor（隐式游标）由系统自动打开和关闭、当我们执行一个SQL DML时、系统会自动打开一个cursor、当执行完毕之后系统会关闭cursor、我们不能直接控制cursor、但是却可以通过implicit Cursor的属性来了解操作的状态和结果、从而达到流程的控制——Cursor的属性包括：
                i、SQL%ROWCOUNT整形——代表DML语句成功执行的行数
                ii、SQL%FOUND布尔型——值为true时代表插入、删除、更新或查询操作成功
                iii、SQL%NOTFOUND布尔型——与上面相反
                v、SQL%ISOPEN布尔型——DML执行过程中为真、否则为假
        3）示例：

begin  
  update student set sname='chy' WHERE sno='1';  
  if sql%isopen then  
     dbms_output.put_line('cursor is opening !');  
  else  
     dbms_output.put_line('cursor is closed !');  
  end if;  
  if sql%found then  
     dbms_output.put_line('DML is successed !');  
  else  
     dbms_output.put_line('DML is failed !');  
  end if;  
  if sql%notfound then  
     dbms_output.put_line('DML is failed !');  
  else  
     dbms_output.put_line('DML is successed !');  
  end if;  
      dbms_output.put_line(sql%rowcount||' is the number of result !');  
  exception   
      when no_data_found then  
           dbms_output.put_line('Sorry No data');  
      when too_many_rows then  
           dbms_output.put_line('Too Many rows');  
end;  

        2、explicit cursor

        1）explanation：PL/SQLalso lets you declare explicit cursors. An explicit cursor has a name and isassociated with a query (SQLSELECT statement)—usually one that returns multiplerows. After declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement), and close the cursor (with the CLOSE statement). After closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.
很直白的说明了显示游标的用处、以及用法。
        2）explicit  cursor的属性包含：
        游标的属性返回值类型意义
        %ROWCOUNT 整型获得FETCH语句返回的数据行数
        %FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假
        %NOTFOUND 布尔型与%FOUND属性返回值相反
        %ISOPEN 布尔型游标已经打开时值为真，否则为假
        3）对于explicit Cursor使用分四个步骤：
                a 定义游标——Cursor [ Cursor Name[param_name, param_type]] IS select xxx from xxxwhere xxx;
                b 打开游标——Open [ Cursor Name[varialbe_value] ] ;
                c 操作游标——Fetch [ Cursor Name ];
                d 关闭游标——Close [ Cursor Name ] ;
        4）具体使用显示游标,遍历循环游标步骤：
                a）使用显示游标
                     i、声明游标：划分存储区域，注意此时并没有执行Select语句。CURSOR游标名(参数列表)   [返回值类型]   IS  Select 语句；
                ii、打开游标：执行Select语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。open游标名(参数列表);
                iii、获取记录：移动游标取一条记录  fetch 游标名 into 临时记录或属性类型变量；
   v、关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
    close  游标名；
                b）遍历循环游标
  i、for循环游标
   循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
         ……
     for  变量名  In 游标名
     loop
      数据处理语句；
     end loop；
   ii、loop循环游标
    ……
    loop
     fetch  游标名 into 临时记录或属性类型变量；
     exit  when   游标名%notfound；
end   loop；
  iii、while循环
       ……
       open 游标名
    fetch 游标名into临时记录或属性类型变量；
       while 游标名%foundloop
              -- do something

              fetch 游标名into临时记录或属性类型变量；
end loop;
……
close 游标名

        5）常见显式Cursor用法：
               i、使用for循环来使用cursor：

declare   
  cursor cur is select * from t_user where age = 22;  
  userinfo t_user%rowtype;  
begin  
  for userinfo in cur loop  
    exit when cur%notfound;  
    dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);  
  end loop;  
  exception   
    when others then  
      dbms_output.put_line(sqlerrm);  
end;        

               ii、使用fetch来使用cursor：     exp2

declare   
  cursor cur is select * from t_user where age = 22;  
  userinfo t_user%rowtype;  
begin  
  open cur;  
  loop  
     exit when cur%notfound;  
     fetch cur into userinfo;  
     dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);  
  end loop;  
  exception  
     when others then  
          dbms_output.put_line(sqlerrm);  
close cur;  
end;   

               iii、使用fetch结合while使用cursor：exp3

declare  
  cursor cur is select * from t_user where age = 23;  
  userinfo t_user%rowtype;  
begin  
  open cur;  
  fetch cur into userinfo;  
  if cur%isopen then  
    while cur%found loop  
          dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);  
          fetch cur into userinfo;  
    end loop;  
    dbms_output.put_line('totle result : ' || cur%rowcount);  
  else  
    dbms_output.put_line('cursor is closed!');  
  end if;   
  close cur;  
  exception  
     when others then  
          dbms_output.put_line(sqlerrm);  
close cur;  
end;     

               v、使用cursor实现数据的修改（带参数的cursor）、下面三种作用是一样的、只是内部实现有点区别

-- 给工作为CLERK的员工加薪  

--one  
declare  
   cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;  
   ef emp1%rowtype;  
   c_sal emp1.sal%type;  
begin  
  for ef in cur('CLERK') LOOP  
    EXIT WHEN CUR%NOTFOUND;  
    IF EF.SAL < 1000 THEN  
      C_SAL := EF.SAL*1.2;  
    ELSIF EF.SAL < 2000 THEN  
      C_SAL := EF.SAL*1.5;  
    ELSIF EF.SAL < 3000 THEN  
      C_SAL := EF.SAL*2;  
    ELSE  
      C_SAL := EF.SAL*2.2;  
    END IF;  
    UPDATE EMP1 SET EMP1.SAL=C_SAL WHERE CURRENT OF CUR;  
  END LOOP;  
  EXCEPTION  
    WHEN OTHERS THEN  
     dbms_output.put_line(sqlerrm);  
END;  
--two  
declare  
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;  
  EF emp1%rowtype;  
  C_SAL emp1.sal%type;  
begin  
  open cur('CLERK');  
  fetch cur into EF;  
  while cur%found loop   
    EXIT WHEN CUR%NOTFOUND;  
    IF EF.SAL < 1000 THEN  
      C_SAL := EF.SAL*1.2;  
    ELSIF EF.SAL < 2000 THEN  
      C_SAL := EF.SAL*1.5;  
    ELSIF EF.SAL < 3000 THEN  
      C_SAL := EF.SAL*2;  
    ELSE  
      C_SAL := EF.SAL*2.2;  
    END IF;  
    update emp1 set emp1.sal=C_SAL where current of cur;  
    fetch cur into EF;  
  end loop;  
  close cur;  
end;   
--three  
declare  
  --define the cursor Note: the select sql is not excuted!  
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;  
  ef emp1%rowtype;  
  c_sal emp1.sal%type;  
begin  
  open cur('CLERK');  
  fetch cur into ef;  

  while cur%found loop  
    exit when cur%notfound;  
    case  
      when ef.sal < 1000   
      then c_sal := ef.sal*1.2;  
      when ef.sal < 2000   
      then c_sal := ef.sal*1.4;  
      when ef.sal < 3000   
      then c_sal := ef.sal*1.6;  
    end case;  
    update emp1 set emp1.sal = c_sal where current of cur;  
    fetch cur into ef;  
  end loop;  
  close cur;  
end;  

                vi、使用cursor实现数据的删除：

--use cursor to delect date  
create table emp3 as select * from emp;  

--delete the date of emp3 where the job is 'CLERK';  
declare  
  cursor cur(c_job varchar2) is select * from emp3 where emp3.job=c_job for update;  
  ef emp3%rowtype;  
begin  
  for ef in cur('CLERK') loop  
    exit when cur%notfound;  
    delete from emp3 where current of cur;  
  end loop;  
end;  

补充：
        ref cursor会在下一个笔记中出现、这里补充一个循环时使用的判断条件if的东西。
注意看下面两段代码：

IF EF.SAL < 1000 THEN  
      C_SAL := EF.SAL*1.2;  
    <span style="color:#ff0000;">ELSIF</span> EF.SAL < 2000 THEN  
      C_SAL := EF.SAL*1.5;  
    <span style="color:#ff0000;">ELSIF</span> EF.SAL < 3000 THEN  
      C_SAL := EF.SAL*2;  
    ELSE  
      C_SAL := EF.SAL*2.2;  
    END IF;  

IF EF.SAL < 1000 THEN  
      C_SAL := EF.SAL*1.2;  
    <span style="color:#ff0000;">ELSE IF</span> EF.SAL < 2000 THEN  
      C_SAL := EF.SAL*1.5;  
    <span style="color:#ff0000;">ELSE IF</span> EF.SAL < 3000 THEN  
      C_SAL := EF.SAL*2;  
    ELSE  
      C_SAL := EF.SAL*2.2;  
    END IF;  

当我们使用多个if条件的时候写成后则就会出错、必须要写成前面的elsif来结合if多条件的情况！