Oracle游标使用全解

游标：  定义    ①从表中检索出结果集，从中每次指向一条记录进行交互的机制。    ②关系数据库中的操作是在完整的行集合上执行的。         由SELECT 语句返回的行集合包括满足该语句的WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。         游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问API 的一部分而得以实现的软件。         用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，    以及是否能够在结果集中向前和/或向后移动（可滚动性）。         游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。    换句话说，游标从概念上讲基于数据库的表返回结果集。         由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。   %NotFound : 最后一条记录是否提取出True or False。  作用    ①指定结果集中特定行的位置。    ②基于当前的结果集位置检索一行或连续的几行。    ③在结果集的当前位置修改行中的数据。    ④可以以编程的方式访问数据库。    主要事项    因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。游标类型    ①静态游标：结果集已经确实（静态定义）的游标。分为隐式和显示游标。          ⑴隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。                一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。                * 插入操作：INSERT。* 更新操作：UPDATE。* 删除操作：DELETE。* 单行查询操作：SELECT ... INTO ...。                
隐式游标的属性 返回值类型   意    义  
SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功  
SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  
          ⑵显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。                显式游标对应一个返回结果为多行多列的SELECT语句。    ②REF游标：动态关联结果集的临时对象。游标属性    %Found ：Fetch语句（获取记录）执行情况True or False。    %NotFound : 最后一条记录是否提取出True or False。    %ISOpen ： 游标是否打开True or False。    %RowCount ：游标当前提取的行数 。遍历循环游标    ⑴For 循环游标          循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。              For  变量名  In  游标名                   Loop                      数据处理语句；                  End Loop；    ⑵Loop循环游标          Loop              Fatch  游标名InTo  临时记录或属性类型变量；              Exit  When   游标名%NotFound；          End   Loop；实例：-- 声明游标；CURSOR cursor_name IS select_statement--For 循环游标--（1）定义游标--（2）定义游标变量--（3）使用for循环来使用这个游标declare       --类型定义       cursor c_job       is       select empno,ename,job,sal       from emp       where job='MANAGER';       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型       c_row c_job%rowtype;begin       for c_row in c_job loop         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);       end loop;end;
--Fetch游标--使用的时候必须要明确的打开和关闭declare        --类型定义       cursor c_job       is       select empno,ename,job,sal       from emp       where job='MANAGER';       --定义一个游标变量       c_row c_job%rowtype;begin       open c_job;         loop           --提取一行数据到c_row           fetch c_job into c_row;           --判读是否提取到值，没取到值就退出           --取到值c_job%notfound 是false            --取不到值c_job%notfound 是true           exit when c_job%notfound;            dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);         end loop;       --关闭游标      close c_job;end;
--1,使用游标和loop循环来显示所有部门的名称--游标声明declare        cursor csr_dept       is       --select语句       select DNAME       from Depth;       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量       row_dept csr_dept%rowtype;begin       --for循环       for row_dept in csr_dept loop           dbms_output.put_line('部门名称:'||row_dept.DNAME);       end loop;end;
--2,使用游标和while循环来显示所有部门的的地理位置（用%found属性）declare       --游标声明       cursor csr_TestWhile       is       --select语句       select  LOC       from Depth;       --指定行指针       row_loc csr_TestWhile%rowtype;begin  --打开游标       open csr_TestWhile;       --给第一行喂数据       fetch csr_TestWhile into row_loc;         --测试是否有数据，并执行循环         while csr_TestWhile%found loop           dbms_output.put_line('部门地点：'||row_loc.LOC);           --给下一行喂数据           fetch csr_TestWhile into row_loc;         end loop;       close csr_TestWhile;end; select * from emp


--3,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  declare       CURSOR       c_dept(p_deptNo number)      is      select * from emp where emp.depno=p_deptNo;      r_emp emp%rowtype;begin        for r_emp in c_dept(20) loop            dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);        end loop;end;

--4：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来 declare        cursor        csr_Update        is        select * from  emp1 for update OF SAL;        empInfo csr_Update%rowtype;        saleInfo  emp1.SAL%TYPE;begin    FOR empInfo IN csr_Update LOOP      IF empInfo.SAL<1500 THEN        saleInfo:=empInfo.SAL*1.2;       elsif empInfo.SAL<2000 THEN        saleInfo:=empInfo.SAL*1.5;        elsif empInfo.SAL<3000 THEN        saleInfo:=empInfo.SAL*2;      END IF;      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;     END LOOP;END;
--5：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）declare    cursor crs_testComput    is    select * from emp1 order by HIREDATE asc;    --计数器    top_two number:=2;    r_testComput crs_testComput%rowtype;begin    open crs_testComput;       FETCH crs_testComput INTO r_testComput;          while top_two>0 loop             dbms_output.put_line('员工姓名：'||r_testComput.ENAME||' 工作时间：'||r_testComput.HIREDATE);             --计速器减一             top_two:=top_two-1;             FETCH crs_testComput INTO r_testComput;           end loop;     close crs_testComput;end;