oracle 游标练习

--地定义异常
declare
       myException exception;
       v_emp emp%rowtype;
begin
     select * into v_emp
            from emp
                 where empno=7369;
     if v_emp.sal<1000 then
        raise myException;
     end if;
     dbms_output.put_line('正常');
exception
         when myException then
              dbms_output.put_line('自定义异常');
              update emp s
                     set sal=1500
                        where empno=7369;
         when no_data_found then
              dbms_output.put_line('无数据');
         when others then
              dbms_output.put_line('其他异常');
end;
------------------------------------------------------
--游标
declare
       my_emp emp%rowtype;
       cursor my_cur is
              select *
                     from emp
                          where deptno=30;
begin
     open my_cur;
     fetch my_cur into my_emp;
     dbms_output.put_line(my_emp.empno||'   '||my_emp.ename);
     close my_cur;
end;

-----------------------------------------------
--for循环游标
declare
       my_emp emp%rowtype;
       cursor my_cur is
              select *
                     from emp
                          where sal>1000;
begin
     for my_emp in my_cur loop
         dbms_output.put_line(my_emp.empno||'   '||my_emp.ename);
     end loop; 
end;

--
declare
       my_emp emp%rowtype;
       cursor my_cur is
              select *
                     from emp
                          where sal>1000;
begin
     open my_cur;
     loop
         fetch  my_cur into my_emp
         exit when my_cur%notfound;
         dbms_output.put_line(my_emp.empno||'   '||my_emp.ename);
     end loop;
     close my_cur;
end;

----------------------------------------------------------------
--带参数游标
declare
       cursor c1 is select deptno from dept;
       cursor c2(nn number)is select * from emp where deptno=10;
       c1rec c1%rowtype;
       --c2rec c2%rowtype;
begin
     for cclrec in c1 loop
         dbms_output.put_line(cclrec.deptno);
         for cc2rec in c2(clrec.deptno) loop
             dbms_output.put_line(cc2rec.empno||'   '||cc2rec.ename||'    '||c2rec.deptno);
         end loop;
     end loop;
end;
--------------------------------------------------------------------
--ref游标
declare
       type ref_cur is ref cursor;
       --type curl is ref cursor return emp%rowtype;
       cura ref_cur;
       c1rec emp%rowtype;
       c2rec dept%rowtype;
begin
     dbms_output.put_line('输出员工');
     open cura for select * from emp;
     loop
          fetch cura into c1rec;
          exit when cura%notfound;
          dbms_output.put_line(c1rec.ename);
     end loop;
     dbms_output.put_line('输出部门');
     open cura for select * from dept;
     loop
          fetch cura into c2rec;
          exit when cura%notfound;
          dbms_output.put_line(c2rec.dname);
     end loop;   
end;

declare
       type ref_cur is ref cursor;
       type curl is ref cursor return emp%rowtype;
       cura curl;
       c1rec emp%rowtype;
       c2rec dept%rowtype;
begin
     dbms_output.put_line('输出员工');
     open cura for select * from emp;
     loop
          fetch cura into c1rec;
          exit when cura%notfound;
          dbms_output.put_line(c1rec.ename);
     end loop;      
end;