Oracle的游标使用

静态游标

显示游标

-- 显示游标的创建和使用(基本语法，步骤较多容易出错 不推荐使用)
declare
  -- 声明变量和游标
  name varchar2(50);
  dept_name varchar2(20);
  cursor emp_cur is
  select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
begin
  -- 打开游标
  open emp_cur;
  --循环提取游标
  loop
    fetch emp_cur into name,dept_name;
    exit when emp_cur%notfound;
    dbms_output.put_line(name||' 在 '||dept_name);
  end loop;
  --关闭游标
  close emp_cur;
end;

-- 实例：根据员工的入职年限为员工加薪，每超过一年加100 最高加1000
declare
  -- 声明变量和游标
  hire_date date;--入职时间
  e_id number;--员工ID
  cursor emp_cur is
  select empno,hiredate from emp;
begin
  -- 打开游标
  open emp_cur;
  --循环提取游标
  loop
    fetch emp_cur into e_id,hire_date;
    exit when emp_cur%notfound;
    if 100*(2013-to_char(hire_date,'yyyy'))<1000 then
      update emp set sal=sal+100*(2013-to_char(hire_date,'yyyy')) where empno=e_id;
    else
      update emp set sal=sal+1000 where empno=e_id;
    end if;
  end loop;
  --关闭游标
  close emp_cur;
end;

此外 使用循环游标可以简化游标的读取(推荐使用)

declare
cursor emp_cur is
select e.ename,d.dname from emp e,dept d
where e.deptno=d.deptno;

begin
  for emp_record in emp_cur loop    --注意 这里的emp_record 在For循环中是自动匹配游标类型的，如果不在For循环里面 需要先声明！
    dbms_output.put_line(
    emp_record.ename||'在'|| emp_record.dname);
    end loop;
end;

隐式游标

使用时前面要加上SQL%

sql%rowcount 常用于判断insert  update  delete语句是否操作成功 如果成功游标返回1；

declare
 name varchar2(50);
 dept_name varchar2(20);

begin
  select e.ename,d.dname into name,dept_name from emp e,dept d
  where e.deptno=d.deptno and empno=7890;
  dbms_output.put_line(sql%rowcount);    --注意  这里使用的隐式游标一定要在commit之前读取
  commit;
end;

REF动态游标

动态游标可以与不同的语句关联，用于处理多行的查询结果集

打开游标时绑定 是REF类型

通常用在查询语句需要运行时动态确定的情况

——强类型动态游标使用方法

declare
 type refcur_t is ref cursor    ——定义游标类型(强类型)
 return emp%rowtype;
 refcur refcur_t;           ——用游标类型声明游标
 v_emp emp%rowtype;   ——定义变量

begin
  open refcur for
  select * from emp;
 
  loop
    fetch refcur into v_emp;
    exit when refcur%notfound;
    dbms_output.put_line(refcur%rowcount||' '||v_emp.ename);  ——输出行号和员工姓名
  end loop;
  close refcur;
end;

——弱类型动态游标使用方法

declare
 type refcur_t is ref cursor;  ——①定义游标类型。因为没有return类型，因此变量不能声明为表类型 只能单独定义
 refcur refcur_t;   ——声明游标
 e_name varchar2(50);
 id number;

begin
  open refcur for
  select empno,ename from emp;——查询时要查具体的列
  fetch refcur into id,e_name;
  while refcur%found loop    ——循环提取游标信息
    dbms_output.put_line('#'||id||':'||e_name);
    fetch refcur into id,e_name;
  end loop;
  close refcur;
end;

——弱类型动态游标使用实例  根据用户输入得到信息

declare

 type refcur_t is ref cursor;
 refcur refcur_t;
 e_name varchar2(50);
 id number;
 selection varchar2(1):=upper(substr('&tab',1,1));--获得用户的输入，截取第一个字符

begin
  if selection='E' then  --如果用户输入E，那么打开游标关联查询雇员表
    open refcur for
    select empno,ename from emp;
    dbms_output.put_line('********雇员信息********');
  elsif selection='D' then  --如果用户输入D，那么打开游标关联查询部门表
    open refcur for
    select deptno,dname from dept;
    dbms_output.put_line('********部门信息********');
  else
    dbms_output.put_line('请输入雇员信息(E)或部门信息(D)');
    return;
  end if;
  fetch refcur into id,e_name;  --循环提取游标信息
  while refcur%found loop
    dbms_output.put_line('#'||id||':'||e_name);
    fetch refcur into id,e_name;
  end loop;
  close refcur;
end;