17,078
社区成员
发帖
与我相关
我的任务
分享
create or replace trigger tr_test
BEFORE insert on test
for each row
declare
ECNAME VARCHAR2(1000);
e_invalid_id exception;
begin
SELECT MAX(ENAME) INTO ECNAME FROM EMP WHERE ID= :NEW.ID;
if (:new.ENAME != ECNAME) then
raise e_invalid_id;
end if;
exception
when e_invalid_id then
dbms_output.put_line ('ERR!');
end;
--存储过程就简单了,先把要插入的值用参数传进来,判断是否符合条件,符合条件的插入就可以了
create or replace procedure pr_test1 (v_id varchar2,v_name varchar2,v_tid varchar2)
as
e_invalid_id exception;
begin
if (v_id='1' and v_name='bb') then --写判断数据的条件,不符合就抛出错误信息
raise e_invalid_id;
else
insert into test (id,name,tid) values (v_id,v_name,v_tid); --符合条件就插入数据
commit;
end if;
exception
when e_invalid_id then
dbms_output.put_line ('数据错误!,请检查数据!');
end;
--调用存储过程
begin
pr_test1('1','aa','cc');
end;