1、编写一个过程 暂时在想 2、SELECT lpad(empno,' ',LEVEL*length(empno)) FROM emp CONNECT BY PRIOR empno = mgr; 3、 create trigger emp_tri before insert or update on emp for each row declare num number; begin select sum(Sal) into num from emp where deptno=:new.deptno; if num+:new.sal>20000 then raise_application_error(-20000,'超出预算'); end if; end; / 以是触发报错,可以使用如下: create view v_emp as select * from emp; / create trigger v_emp_tri instead of insert or update v_emp for each row declare num number; begin select sum(sal) into num from emp where deptno=:new.deptno; if num+:new.sal>20000 then raise_application_error(-20000,'超出预算'); end if; end; / 哪以后就直接对v_emp作插入与更新.
empno ename Job Mgr Hiredate Sal Comm deptno
7844 TUNER Sales 7678 08/09/81 1500 0 30
...
2、不用游标可能没有办法实现。
3、为表emp增加一个触发器。
暂时在想
2、SELECT lpad(empno,' ',LEVEL*length(empno))
FROM emp
CONNECT BY PRIOR empno = mgr;
3、
create trigger emp_tri
before insert or update on emp
for each row
declare
num number;
begin
select sum(Sal) into num from emp where deptno=:new.deptno;
if num+:new.sal>20000 then
raise_application_error(-20000,'超出预算');
end if;
end;
/
以是触发报错,可以使用如下:
create view v_emp as select * from emp;
/
create trigger v_emp_tri
instead of insert or update v_emp
for each row
declare
num number;
begin
select sum(sal) into num from emp where deptno=:new.deptno;
if num+:new.sal>20000 then
raise_application_error(-20000,'超出预算');
end if;
end;
/
哪以后就直接对v_emp作插入与更新.