oracle数据库sql语句09 PL SQL程序结构 异常

PL/SQL程序结构 




set serveroutput on




--异常处理




--no_data_found
declare
a emp%rowtype;
begin
select * into a from emp where empno=108;
dbms_output.put_line(a.ename||'   '||a.job);
exception
 when no_data_found then
  dbms_output.put_line('无数据记录');
end;
/






--声明一个异常
declare
e exception;
pragma exception_init(e, -02291);
begin
insert into emp(empno, ename,deptno) values(1112, 'sss', 50);
exception
 when e then
  dbms_output.put_line('部门编号不存在');
end;
/




--自定义异常 - sal<6000
declare
v_sal emp.sal%type;
e exception;
pragma exception_init(e, -02291);
begin
commit;
insert into emp(empno, ename,sal) values(1113, 'sss', 6100) returning sal into v_sal;
if v_sal > 6000 then
raise e;
end if;
exception
 when e then
  dbms_output.put_line('工资超过6000');
  rollback;
end;
/








1. 查询职位名称为SALESMAN的员工工资，如果该职位不存在，则输出“There is not such an job!”；如果存在多个的员工，则输出其员工号和工资。


declare
a emp%rowtype;
begin
select * into a from emp where job='SALESMAN';
exception
when no_data_found then
dbms_output.put_line('There is not such an job!');
when too_many_rows then
for i in (select * from emp where job='SALESMAN') loop
dbms_output.put_line(i.empno||'  '||i.sal);
end loop;
end;
/


2. 非预定义异常。 
设定一个程序段，功能为在emp表中添加一个员工记录，所在部门编号为50  
declare
e exception;
pragma exception_init(e, -02291);
begin
insert into emp(empno, ename, deptno) values(1114, 'sss', 50);
exception
 when e then
  dbms_output.put_line('部门编号不存在');
end;
/


3. 用户自定义异常 
设计一个程序段，功能为完成emp表中记录的插入，我们约定员工工资不允许超过8000 


declare
v_sal emp.sal%type;
e exception;
pragma exception_init(e, -02291);
begin
commit;
insert into emp(empno, ename,sal) values(1114, 'sss', 8100) returning sal into v_sal;
if v_sal > 8000 then
raise e;
end if;
exception
 when e then
  dbms_output.put_line('工资超过8000');
  rollback;
end;
/