PLSQL总结——17.游标

/*
create table EMPLOYEE
(
  employee_number NUMBER,
  employee_name   VARCHAR2(30),
  salary          NUMBER(7,2),
  department_id   NUMBER
)
*/

declare
  /*声明强类型的变量游标*/
  type emp_curtype is ref cursor return employee%rowtype;
  emp_cur emp_curtype;
  rec     emp_cur%rowtype; --弱类型变量游标不能这样声明

  /*打开游标过程*/
  procedure open_cur(p_code varchar2,
                     p_cur  out emp_curtype) is
  begin
    /*按照p_code来打开不同类型的游标*/
    if p_code = 'employee_number'
    then
      open p_cur for
        select * from employee order by employee_number desc; --按employee_number排序
    elsif p_code = 'employee_name'
    then
      open p_cur for
        select * from employee order by employee_name desc; --按employee_name排序
    end if;
  end open_cur;

begin
  open_cur('employee_number', emp_cur); --打开游标
  loop
    fetch emp_cur into rec;
    exit when emp_cur%notfound;
  
    <<output_block>>
    begin
      /*假如第一条，输出头*/
      if emp_cur%rowcount = 1
      then
        dbms_output.put_line('--------------------------------------------------');
        dbms_output.put_line('employee_number|employee_name|salary|department_id');
      end if;
      dbms_output.put_line(rpad(rec.employee_number, 15, ' ') || '|' ||
                           rpad(rec.employee_name, 13, ' ') || '|' ||
                           rpad(rec.salary, 6, ' ') || '|' ||
                           rpad(rec.department_id, 13, ' '));
    end;
  
  end loop;
  dbms_output.put_line('--------------------------------------------------');

  close emp_cur; --关闭游标

exception
  when others then
    null;
end;