Oracle 游标

--无论是隐式游标，还是显示游标，均有%ISOPEN,%FOUND,%NOTFOUND,%ROWCOUNT四个属性
--隐式游标
/*
       所有DML语句(主要用在update/delete)为隐式游标，通过隐式游标属性可以获取SQL语句信息。
*/
--%FOUND : 用来判断当前游标是否指向有效的一行，若是则为true，否则为false

begin
  delete from t_emp where empno = 7369;
  if sql%found then
     dbms_output.put_line('7369用户信息删除成功');
  else
     dbms_output.put_line('7369用户信息不存在');
  end if;
end;
--%NOTFOUND: 与%FOUND类似，但其值确刚好相反
begin
  delete from t_emp where empno = 99999;
  if sql%notfound then
     dbms_output.put_line('99999用户信息不存在');
  end if;
end;
--%rowcount 记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号
begin
  delete from t_emp where empno > 10;
     dbms_output.put_line('受影响的行数:'||sql%rowcount);
end;

--显式游标
/*
 用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
*/

declare
   --定义复合(emp行类型)类型变量
   rec emp%rowtype;
   --声明显示游标
   cursor cur_emp is
   select * from emp where sal>2500; 
begin
  open cur_emp; -- 开启游标
  loop -- 循环提取游标
    fetch cur_emp into rec; -- 提取游标的一行数据赋值给rec
    exit when cur_emp%notfound; -- 当没有找到记录时退出循环
    dbms_output.put_line(cur_emp%rowcount||'.雇员名称:'||rec.ename||'工资:'||rec.sal);
 end loop;
--%isopen 判断游标是否打开，若是则为true，否则为false
 if cur_emp%isopen then -- 判断游标是否打开，如果是则关闭游标
    close cur_emp; --关闭游标
 end if;
end;

-- 参数游标

declare
   --定义复合(emp行类型)类型变量
   rec emp%rowtype;
   --部门编号
   v_deptno number;
   --声明显示游标
   cursor cur_emp(p_deptno number) is --根据部门编号查询
   select * from emp where deptno = p_deptno; 
begin
  v_deptno:='&请输入部门编号';
  open cur_emp(v_deptno); -- 开启游标
  loop -- 循环提取游标
    fetch cur_emp into rec; -- 提取游标的一行数据赋值给rec
    exit when cur_emp%notfound; -- 当没有找到记录时退出循环
    dbms_output.put_line(cur_emp%rowcount||'.雇员名称:'||rec.ename||'工资:'||rec.sal);
 end loop;
--%isopen 判断游标是否打开，若是则为true，否则为false
 if cur_emp%isopen then -- 判断游标是否打开，如果是则关闭游标
    close cur_emp; --关闭游标
 end if;
end;

--REF游标(有称为动态游标,隐式游标和显示声明游标又称为静态游标)
/*
 动态关联结果集
 给指定部门下的员工涨薪水20%

*/

 

declare 
     type cus_cur is ref cursor; -- 声明一个ref游标类型
     var_cur cus_cur; -- 声明ref游标类型的变量
     v_emprow emp%rowtype; -- 定义emp 行类型变量
     v_deptno number := &v_deptno;
begin
     -- 打开游标,并指定查询结果集
     open var_cur for 'select * from t_emp where deptno = :v_deptno_var' --:v_deptno_var 设置参数
     using v_deptno; -- 通过using 替换参数
     loop -- 循环游标
       fetch var_cur into v_emprow; -- 提取游标
       exit when var_cur%notfound; -- 如果提取到了最后一行则退出循环
       update t_emp set sal = sal + sal * 0.2 where empno = v_emprow.empno; -- 修改员工薪水
       --打印加薪信息
       dbms_output.put_line('员工姓名:'||v_emprow.ename || ' 已经加薪');
     end loop;
     close var_cur; -- 关闭游标
commit;
end;

使用显示游标的方式输出：员工编号、员工姓名、部门名称，职务、薪水(数据使用:emp、dept表)

方法一：先创建视图，然后通过游标输出

create or replace view vi_emp_dept(empno,ename,dname,ejob,esal)
as select e.empno,e.ename,d.dname,e.job,e.sal from emp e,dept d
where e.deptno=d.deptno;

declare
       v_emp_dept scott.vi_emp_dept%rowtype;
       cursor c_emp is select * from vi_emp_dept;      
begin
       open c_emp;
       loop
         fetch c_emp into v_emp_dept;
         exit when c_emp%notfound;
         dbms_output.put_line(v_emp_dept.empno||' '||v_emp_dept.ename||' '||v_emp_dept.dname||' '||v_emp_dept.ejob||' '||v_emp_dept.esal);    
       end loop;
       if c_emp%isopen then 
         close c_emp;
       end if;
end;

方法二：定义一个类型变量，然后用游标输出

declare
  type t_emp_dept is record(
       empno scott.emp.empno%type,
       ename scott.emp.ename%type,
       dname scott.dept.dname%type,
       ejob  scott.emp.job%type,
       esal  scott.emp.sal%type
       );      
       v_emp_dept t_emp_dept;
       cursor c_emp is select e.empno,e.ename,d.dname,e.job,e.sal from emp e,dept d where e.deptno=d.deptno;      
begin
       open c_emp;
       loop
         fetch c_emp into v_emp_dept;
         exit when c_emp%notfound;
         dbms_output.put_line(v_emp_dept.empno||' '||v_emp_dept.ename||' '||v_emp_dept.dname||' '||v_emp_dept.ejob||' '||v_emp_dept.esal);    
       end loop;
       if c_emp%isopen then 
         close c_emp;
       end if;
end;