PL/SQL(八):编写游标

一、游标

    是oracle系统在内存中开辟的一个工作区，在其中存放select语句返回的查询结果。

二、游标分类

    1、隐式游标
        1）PL/SQL隐式建立并自动管理这一游标
        2）隐式游标属性
    SQL%ROWCOUNT     返回最近一条SQL语句所影响到的记录的数量（整数型）
    SQL%FOUND     布尔型属性，当游标或游标变量被打开但是在执行FETCH语
                         句之前时， %FOUND是NULL。其后，如果最后的FETCH语句
                         返回一行或多行记录，则%FOUND为TRUE，如果FETCH语句
                         没有返回记录，则%FOUND为FALSE。                                     
    SQL%NOTFOUND     布尔型属性，当游标或游标变量被打开但是在执行FETCH语
                         句之前时， %NOTFOUND是NULL。其后，如果最后的
                         FETCH语句返回一行记录，则%NOTFOUND为FALSE，如果
                         FETCH语句没有返回记录，则%NOTFOUND为TRUE。     

    SQL%ISOPEN         当游标或游标变量被打开时，为TRUE；否则为FALSE

   2、显示游标

        由用户显式声明,查询返回多行记录
        要访问查询结果的所有记录，可以通过fetch语句，进行指针的移动来实现
        使用游标进行操作，包括定义游标、打开游标、提取数据、关闭游标四步。

        不能对游标赋值，也不能在表达式中使用游标名 

        显示游标的属性                        属性类型描述
        %ISOPEN                  布尔如果游标是打开的，其值为TRUE
        %NOTFOUND          布尔如果FETCH 语句没有返回记录，其值为TRUE
        %FOUND                  布尔如果FETCH 语句返回一行记录，其值为TRUE；  与%NOTFOUND相反
        %ROWCOUNT        数量返回迄今为止已经从游标中取出的记录数目

三、游标处理

1、使用游标前，应首先打开游标
2、游标指针只能向下移动，不能回退

3、将提取的行值存入一个PL/SQL record 中能方便地处理活动集中的行

四、示例代码

1、隐式游标

--简单用法
begin
  for emprow in (select * from emp where deptno = 10) loop
      dbms_output.put_line(emprow.empno);
      dbms_output.put_line(emprow.ename);
  end loop;
end;
-- sql%rowcount ,最近执行的SQL 影响行数，部门号为20
begin 
  update emp set sal = sal where deptno = 10;
  update emp set sal = sal where deptno = 20;    
  dbms_output.put_line(sql%rowcount);   
end;

2、显示游标的标准用法

--1）loop方式
declare 
  --1.定义游标
  cursor emp_dept10_cur is
         select * from emp where deptno = 10;
  emprow emp%rowtype;
begin
  if emp_dept10_cur%isopen then             --未打开，不执行
    dbms_output.put_line(‘1游标已经打开‘);  
  end if;
  --2.打开游标
  open emp_dept10_cur ;
   if emp_dept10_cur%isopen then            --已打开，执行
    dbms_output.put_line(‘2游标已经打开‘);  
  end if;
  --3.读取行记录，先fetch 在读取单行记录
  loop
    fetch emp_dept10_cur into emprow ; 
    exit when emp_dept10_cur%notfound;
    dbms_output.put_line(emprow.empno);
    dbms_output.put_line(emprow.ename);
  end loop;
  --4.关闭游标
  close emp_dept10_cur;
end;

--2）whle 方式
declare
  cursor emp_dept10_cur is
         select * from emp where deptno = 10; 
  emprow emp%rowtype;
begin 
  open emp_dept10_cur;
  fetch emp_dept10_cur into emprow;
  
  while(emp_dept10_cur%found) loop   --emp_dept10_cur 有值 继续循环
    dbms_output.put_line(emprow.empno);
    dbms_output.put_line(emprow.ename);
     fetch emp_dept10_cur into emprow;
  end loop;
  
  close emp_dept10_cur;
end; 

3、显示游标

--游标定义 、打开、提取、关闭 一体化 
declare
  cursor emp_dept10_cur is
        select * from emp where deptno = 10;
begin
  for emprow in emp_dept10_cur loop
    dbms_output.put_line(emprow.empno);
    dbms_output.put_line(emprow.ename);
  end loop;
  --再次打开，遍历游标
  for emprow in emp_dept10_cur loop
    dbms_output.put_line(emprow.empno);
    dbms_output.put_line(emprow.ename);
  end loop;
end;

4、带参数的游标

--1、带参方式（一）
declare
  cursor emp_dept_cur is
         select * from emp where deptno = &dno;  
begin   
  for emprow in emp_dept_cur loop    
    dbms_output.put_line(emprow.ename); 
  end loop;   
end; 
--2、带参方式（二）
declare
  cursor emp_dept_cur( dno int ) is
         select * from emp where deptno = dno;   --有参数的游标
begin   
  for emprow in emp_dept_cur(&dno) loop    
    dbms_output.put_line(emprow.empno); 
  end loop;  
end; 

--3、显示开/关游标
declare 
  cursor emp_dept_cur(dno int) is
         select * from emp where deptno = dno;
  emprow emp%rowtype;
begin
  --选择10号部门的员工
  open emp_dept_cur(10);
  loop
    fetch emp_dept_cur into emprow;
    exit when emp_dept_cur%notfound;
    dbms_output.put_line(emprow.empno); 
    dbms_output.put_line(emprow.ename); 
  end loop;
  close emp_dept_cur;
  --选择20号部门的员工
  open emp_dept_cur(20);
  loop
    fetch emp_dept_cur into emprow;
    exit when emp_dept_cur%notfound;
    dbms_output.put_line(emprow.empno); 
    dbms_output.put_line(emprow.ename); 
  end loop;
  close emp_dept_cur;
end;

5、实例应用

/*
  在一个块中分别显示薪水低于2000，在2000与4000之间，以
  及大于4000的雇员名.
*/
declare 
  cursor emp_cursor(s_sal number,e_sal number) is
    select ename from emp where sal between s_sal and e_sal;
begin
  FOR  emp_record  IN emp_cursor(0,2000)  LOOP
    dbms_output.put_line(‘薪水低于2000的用户是：‘||emp_record.ename);
  end loop;
  dbms_output.put_line(‘===================================‘);
  FOR  emp_record  IN emp_cursor(2000,4000)  LOOP
    dbms_output.put_line(‘薪水大于2000，低于4000的用户是：‘||emp_record.ename);
  end loop;
  dbms_output.put_line(‘===================================‘);
  FOR  emp_record  IN emp_cursor(4000,1000000)  LOOP
    dbms_output.put_line(‘薪水大于4000的用户是：‘||emp_record.ename);
  end loop;  
end;