oracle在触发器中调用函数,oracle中的函数与触发器的使用

oracle中函数与触发器的使用，与sql中函数与触发器的使用功能是一致的。

--从临时表中查找指定的专家记录是否存在

-- drop function F_EXPERT_EXIST_IN_TEMP

create or replace function   "XHPORTAL".F_EXPERT_EXIST_IN_TEMP(

nExpertId in NUMBER,

nSqlType in NUMBER,

nTrs_flag in NUMBER

)

return NUMBER is

v_count_expert NUMBER;

CURSOR c_CountExperts is select count(*) from Expert_timer$_temp  where EXPERTID = nExpertId and SQL_TYPE = nSqlType and TRS_FLAG = nTrs_flag ;

begin

OPEN c_CountExperts;

LOOP

FETCH c_CountExperts INTO v_count_expert;

EXIT WHEN  c_CountExperts%NOTFOUND;

END LOOP;

CLOSE c_CountExperts;

return v_count_expert;

end F_EXPERT_EXIST_IN_TEMP;

--工作经历触发器(只有专家才有工作经历)

--新增

create or replace TRIGGER WorkHistory_I_TRIG AFTER INSERT ON XHPORTAL.XWCMWORKHISTORY

FOR EACH ROW

declare nExpertCount number;

BEGIN

select "XHPORTAL".F_EXPERT_EXIST_IN_TEMP(:new.OBJID,1,0) into nExpertCount from dual;

if(nExpertCount =0) then

INSERT INTO Expert_timer$_temp VALUES(Expert_timer$_SEQ.NEXTVAL,:new.OBJID,1,0);

end if;

END;

--修改

create or replace TRIGGER WorkHistory_U_TRIG AFTER UPDATE ON XHPORTAL.XWCMWORKHISTORY

FOR EACH ROW

declare nExpertCount number;

BEGIN

select "XHPORTAL".F_EXPERT_EXIST_IN_TEMP(:old.OBJID,3,0) into nExpertCount from dual;

if(nExpertCount =0) then

INSERT INTO Expert_timer$_temp VALUES(Expert_timer$_SEQ.NEXTVAL,:old.OBJID,3,0);

end if;

select "XHPORTAL".F_EXPERT_EXIST_IN_TEMP(:new.OBJID,1,0) into nExpertCount from dual;

if(nExpertCount =0) then

INSERT INTO Expert_timer$_temp VALUES(Expert_timer$_SEQ.NEXTVAL,:new.OBJID,1,0);

end if;

END;

--删除

create or replace TRIGGER WorkHistory_D_TRIG AFTER DELETE ON XHPORTAL.XWCMWORKHISTORY

FOR EACH ROW

declare nExpertCount number;

BEGIN

select "XHPORTAL".F_EXPERT_EXIST_IN_TEMP(:old.OBJID,3,0) into nExpertCount from dual;

if(nExpertCount =0) then

INSERT INTO Expert_timer$_temp VALUES(Expert_timer$_SEQ.NEXTVAL,:old.OBJID,3,0);

end if;

END;