转载：
//错误处理
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;