--地定义异常
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;