Oracle游标Cursor一些例子

declare
  type myref is REF CURSOR RETURN dept%rowtype;
  mycur myref;
  deptrec dept%rowtype;
begin
  open mycur for select * from dept;
  loop
    FETCH mycur into deptrec;
    EXIT when mycur%notfound;
    dbms_output.put_line(deptrec.deptno||' '||deptrec.dname||' '||deptrec.loc);
  end loop;
end;

--输出emp表中，sal>2000的人的ename、job、sal
declare
  type emprec is RECORD(ename emp.ename%type,job emp.job%type,sal emp.sal%type);
  type myref is REF CURSOR RETURN emprec;
  mycur myref;
  myemprec emprec; --myemprec mycur%rowtype
begin
  open mycur for select ename,job,sal from emp where sal>2000;
  loop
    FETCH mycur into myemprec;
    EXIT when mycur%notfound;
    dbms_output.put_line(myemprec.ename||' '||myemprec.job||' '||myemprec.sal);
  end loop;
end; 

declare
  type myref is REF CURSOR;
  mycur myref;
  e emp%rowtype;
  d dept%rowtype;
  inputchar char(1);
begin
  inputchar:=lower('&no'); --无论大小写都转化为小写
  if inputchar='e' then
    dbms_output.put_line('输出emp表');
    open mycur for select * from emp;
    loop
      FETCH mycur into e;
      EXIT when mycur%notfound;
      dbms_output.put_line(e.ename||' '||e.job||' '||e.deptno);
    end loop;
  elsif inputchar='d' then
    dbms_output.put_line('输出dept表');
    open mycur for select * from dept;
    loop
      FETCH mycur into d;
      EXIT when mycur%notfound;
      dbms_output.put_line(d.deptno||' '||d.dname||' '||d.loc);
    end loop;
  else
    dbms_output.put_line('输入错误');
  end if;
  IF mycur%isopen then  --最后判断是否打开了游标，如果打开了关闭
    CLOSE mycur;
  END IF;
  EXCEPTION
    when value_error then
      dbms_output.put_line('输入多于一个字符，不合法');
end;