oracle 异常 sql,Oracle SQL 异常处理 | 码农网

①建表myemp。该表内容与emp一致；

②建存储过程。存储过程要的参数，和表里的字段一一对应。比如，表里有empno，存储过程就要有一个参数对应这字段i_empno，类型肯定和empno一样，如果你知道类型是number(4)，就直接写成(i_empno in number(4),...)以此类推.

③功能实现，根据empno判断，如果myemp表里已经有这个empno，你就根据你传入的信息把empno的信息更新了,如果没有，就把你这些传入的字段，插入到表里,

eg:我只用两个字段来说明：empno、sal

入参1：123,1000，经过��断，myemp表里没有123这个empno,那么执行完存储过程，这个信息要插入到表里；

入参2:7369,2000,经判断，表里已经有这个编号，但sal为800，那么执行完存储过程，7369的sal更新为2000；

create or replace procedure store_info

(v_empno in myemp.empno%type,

v_ename in myemp.ename%type,

v_job in myemp.job%type,

v_mgr in myemp.mgr%type,

v_hiredate in myemp.hiredate%type,

v_sal in myemp.sal%type,

v_comm in myemp.comm%type,

v_deptno in myemp.deptno%type

)

IS

v_id myemp.empno%type:=0;

BEGIN

select count(*) into v_id

from myemp

where myemp.empno = v_empno;

if (v_id=0) then

insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

values (v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);

else

update myemp

set myemp.ename=nvl(v_ename,myemp.ename) , myemp.job=nvl(v_job,myemp.job),

myemp.mgr=nvl(v_mgr,myemp.mgr) , myemp.hiredate=nvl(v_hiredate,myemp.hiredate),

myemp.sal=nvl(v_sal,myemp.sal) , myemp.comm=nvl(v_comm,myemp.comm),

myemp.deptno=nvl(v_deptno,myemp.deptno)

where myemp.empno = v_empno ;

end if;

END store_info;

begin

store_info(7369,null,null,null,null,2000,null,null);

end;

结果

【注意】：

为什么要把这一题关于存储过程的题放到这里？

因为我起初用异常处理部分来写这一题......，这是不规范的。

本题中用到 count() 函数，count() 是用来计算满足条件的行数的，count(*) 计算所有的行，包括空值。

用异常处理来写本题的代码：

create table myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

as

select empno, ename, job, mgr, hiredate, sal, comm, deptno

from emp;

create or replace procedure store_info

(v_empno in myemp.empno%type,

v_ename in myemp.ename%type,

v_job in myemp.job%type,

v_mgr in myemp.mgr%type,

v_hiredate in myemp.hiredate%type,

v_sal in myemp.sal%type,

v_comm in myemp.comm%type,

v_deptno in myemp.deptno%type

)

IS

v_id myemp.empno%type;

BEGIN

select myemp.empno into v_id

from myemp

where myemp.empno = v_empno;

update myemp

set myemp.ename=v_ename, myemp.job=v_job,myemp.mgr=v_mgr,

myemp.hiredate=v_hiredate, myemp.sal=v_sal,

myemp.comm=v_comm,myemp.deptno=v_deptno

where myemp.empno = v_id;

EXCEPTION

when no_data_found then

insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

values (v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);

END store_info;

3.

编写PL/SQL块，使用SELECT语句将管理者编号为空的员工的姓名及工作编号显示出来，如果符合条件的员工多于一人，则返回字符串“最高管理者人员过多！”字符串，如果找到没有符合条件的记录，则返回字符串“没有最高管理者，请指定”

代码：

declare

o_ename emp.ename%type;

o_empno emp.empno%type;

v_id emp.empno%type;

begin

select emp.empno into v_id

from emp

where emp.mgr is null;

select emp.ename into o_ename from emp where emp.empno = v_id;

select emp.empno into o_empno from emp where emp.empno = v_id;

dbms_output.put_line('员工姓名：'||o_ename||','|| '员工编号：'||o_empno);

exception

when no_data_found then

dbms_output.put_line('没有最高管理者，请指定');

when too_many_rows then

dbms_output.put_line('最高管理者人员过多');

end;

4.获得每个部门的平均工资，如果平均工资大于2000，视为用户定义的异常，提示“该部门的平均工资过高”。

declare

cursor cemp

is

select dept.dname,avg(sal)

from emp,dept

where emp.deptno = dept.deptno

group by emp.deptno ,dept.dname;

v_dname dept.dname%type ;

v_asal emp.sal%type ;

too_high_sal exception;

begin

open cemp;

loop --打开循环

fetch cemp into v_dname,v_asal;

exit when cemp%notfound;

begin --这里写了一个 PL/SQL 代码块，里面可以做异常处理

if v_asal > 2000 then

raise too_high_sal;

end if;

exception --异常处理，会终止此代码块。进入下一次循环

when too_high_sal then

dbms_output.put_line(v_dname||'该部门工资过高');

end;

end loop;

close cemp;--注意end loop 与 close cemp 的先后顺序。必须是先结束循环，再关闭游标。

end;