Question

编写PL/SQL块，获得每个部门的平均工资，如果平均工资大于5000，视为自定义异常，提示该部门工资太高。

工资列为 sal
部门编号为deptno

Answer 1

declare
emp_no_temp exception ; 声明
e_sal number;
begin
select avg(sal) into e_sal from xxx where deptno=xxx ;
if e_sal>5000 then
raise emp_no_temp;
end if;

exception
when emp_no_temp exception then

dbms_output.put_line("该部门工资太高");
end;

__________________________________________
declare
e_sal number;
begin
select avg(sal) into e_sal from xxx where deptno=xxx ;
if e_sal>5000 then
raise_application_error(-20001,'该部门工资太高');

end if;

Answer 2

CREATE OR REPLACE PROCEDURE avg_sal
IS
ex_dept_sal EXCEPTION;
avg_sal NUMBER(10,2);
deptno varchar(10);
BEGIN
FOR t IN(select deptno,avg(sal) avg_sal from dept group by deptno) LOOP
IF t.avg_sal > 5000
THEN
avg_sal := t.deptno;
RAISE ex_dept_sal;
END IF;
END LOOP;

EXCEPTION WHEN ex_dept_sal
THEN
dbms_output.put_line(avg_sal||'部门工资太高');
WHEN
OTHERS THEN
NULL;
END;