Oracle游标遍历%rowtype中的记录

た入场券 2021-06-26 16:06 518阅读 0赞

那么我们使用`Oracle`游标

游标分为：静态游标和引用游标（动态游标）

静态游标：由用户定义（隐式游标、显示游标）结果集不变

引用游标游标：结果集变化

隐式游标：用`DML`操作时，自动使用隐式游标。我们可以使用隐式游标判断`SQL`语句执行结果

自动声明和处理的。在`Session`会话区，开启游标。处理后自动关闭。可以返回单行查询。

隐式游标使用：

declare 
     %NOTFOUND  －－ 执行行没有找到。 
     %FOUND  －－执行行找到 
     %ROWCOUNT －－游标影响行数 
     %ISOPEN －－ 当前游标是否打开

我们现在通过游标来看看上篇文章的例子  
通过循环来遍历数据：

## 1、`loop when`循环 ##

declare 
    cursor myCur is select * from hr.jobs; 
    oneRow hr.jobs%rowtype; 
    begin 
           open myCur; 
      loop 
           fetch myCur into oneRow; 
      dbms_output.put_line(oneRow.job_id ||' ' ||onerow.job_title); 
      exit when myCur%notFound; 
           end loop; 
      close myCur; 
    end;

## 2、`while` 循环 ##

declare 
    cursor myCur is select * from hr.jobs; 
    oneRow hr.jobs%rowtype; 
    begin 
    open myCur; 
    fetch myCur into oneRow; 
    while (myCur%found) 
      loop 
      dbms_output.put_line(oneRow.job_id ||' ' ||onerow.job_title); 
      fetch myCur into oneRow; 
      end loop; 
      close myCur; 
    end;

## 3、`for` 循环 ##

declare 
    cursor myCur is select * from hr.jobs; 
    oneRow hr.jobs%rowtype; 
    begin for oneRow in myCur loop dbms_output.put_line(oneRow.job_id ||' ' ||onerow.job_title); 
       end loop; 
    end;

结果如下：

AD_PRES    President 
    AD_VP    Administration Vice President 
    AD_ASST    Administration Assistant 
    FI_MGR    Finance Manager 
    FI_ACCOUNT    Accountant 
    AC_MGR    Accounting Manager 
    AC_ACCOUNT    Public Accountant 
    SA_MAN    Sales Manager 
    SA_REP    Sales Representative 
    PU_MAN    Purchasing Manager 
    PU_CLERK    Purchasing Clerk 
    ST_MAN    Stock Manager 
    ST_CLERK    Stock Clerk 
    SH_CLERK    Shipping Clerk 
    IT_PROG    Programmer 
    MK_MAN    Marketing Manager 
    MK_REP    Marketing Representative 
    HR_REP    Human Resources Representative 
    PR_REP    Public Relations Representative