转载：  http://blog.163.com/lyq_163_2009/blog/static/134082696201071210464738/

//错误处理

declare 

  v_temp number(4);

begin

  select empno into v_temp from emp where deptno=10;

exception

  when too_many_rows then

    dbms_output.put_line('太多记录了...');

  when others then

    dbms_output.put_line('error...');

end;

/

 

 

//错误处理

declare

  v_temp number(4);

begin

  select empno into v_temp from emp where empno=2222;

exception

  when no_data_found then

    dbms_output.put_line('没有数据...');

  when others then

    dbms_output.put_line('error...');

end;

/

 

 

//游标

declare

  cursor c is select * from emp;

  v_emp c%rowtype;

begin 

  open c;

  fetch c into v_emp;

  dbms_output.put_line(v_emp.ename);

  close c;

end;

/

 

 

//游标

declare

  cursor c is select * from emp;

  v_emp c%rowtype;

begin

  open c;

  loop

    fetch c into v_emp;

    exit when(c%notfound);

      dbms_output.put_line(v_emp.ename);

  end loop;

  close c;

end;

/

 

 

//游标

declare

  cursor c is select * from emp;

  v_emp c%rowtype;

begin

  open c;

  fetch c into v_emp;

  while(c%found)loop

    dbms_output.put_line(v_emp.ename);

    fetch c into v_emp;

  end loop;

  close c;

end;

/

 

 

//游标

declare

  cursor c is select * from emp;

begin

  for v_emp in c loop

   dbms_output.put_line(v_emp.ename);

  end loop;

end;

/

 

//带参数的游标

declare

  cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is 

    select ename,sal from emp where deptno=v_deptno and job=v_job;

  --v_temp c%rowtype;

begin

  --open c(30,'clerk');

  for v_temp in c(30,'CLERK') loop

    dbms_output.put_line(v_temp.ename || ' ' || v_temp.sal);

  end loop;

end;

/

 

 

//可以更新的游标

declare

  cursor c is select * from emp2 for update;

  --v_temp c%rowtype;

begin 

  for v_temp in c loop

    if(v_temp.sal < 2000)then

      update emp2 set sal=sal*2 where current of c;

    elsif(v_temp.sal =5000)then

      delete from emp2 where current of c;

    end if;

  end loop;

  commit;

end;

/

 

//创建表

create table emp2 as(

   select * from emp

);

//删除表

drop table emp2;