谢谢各位,总算是可以编译通过了,但不知道好用不好用。因为Sqlserver和Oracle在字符处理方面可能还有差异。
CREATE OR REPLACE
TRIGGER TRG_Mail
BEFORE INSERT OR UPDATE ON RELEX.ALERTHISTORY
referencing old as old new as new
for each row
declare
r_from int;--:所在的位置
r_len int;--长度
r_Identifier varchar2(4000);--Idnetity字符串
r_id int; --- Fracas.AlertHistory 的id
r_currentname varchar2(255);--当前标示符
r_prjname varchar2(255);--系统名称
r_new varchar2(4000);--新生成的字符串,带引号的标识符
r_Setid int;--系统id
r_solutionid int;---项目id
r_ssql varchar2(4000);--要执行的sql语句
s_SetId varchar2(255);--存放临时转换的字符串
BEGIN
--set serveroutput off;
--if (updating(RecordsMatched)) then
--取得记录
r_setid := :new.setid;
r_id := :new.id;
r_Identifier := :new.RecordsMatched;
--取得记录
-- cast(new.RecordsMatched as varchar2(4000)) into r_Identifier
--select new.setid into r_setid,new.id into r_id, new.RecordsMatched into r_Identifier from :new ;
---要去掉 r_identifier的右边空格
r_Identifier:=rtrim(r_identifier);
if (length(r_Identifier)<1) then
return;
end if;
--取得:的位置和总的长度
r_from:=instr(':', r_Identifier) ;
r_len:= length(r_Identifier);
--项目id
select solutionid into r_solutionid from Relex.DatabaseFiles
where id=r_setid;
--如果到:结束,就退出
if (r_from=r_len) then
return;
end if;
if (upper(substr(r_identifier,1,1))='I') then
-- 取得系统,从第5个开始中文,英文从11个开始
r_prjname:=substrc(r_Identifier,11,r_from-11);
else
r_prjname:=substr(r_Identifier,5,r_from-2-5);
end if;
r_setid:=0;
--取得setid
select id into r_setid
from Relex.DatabaseFiles
where filename=r_prjname and solutionid=r_solutionid;
--如果没有找到项目对应的id,退出
if (r_setid=0) then
return;
end if;
--取得总长度
r_len:=length(r_Identifier);
--取得标识符字符串
r_Identifier:=substr(r_Identifier,r_from+2,r_len-r_from-2);
--加引号的处理过程
r_new:='(';
while (instr(',',r_Identifier)>0)
Loop
r_len:=instr(',',r_Identifier);
r_currentname:=substr(r_Identifier,1,r_len-1);
--去掉左右字符串
r_currentname:=rtrim(ltrim(r_currentname));
r_new:=r_new+''''||r_currentname ||''''||',';
r_Identifier:=substr(r_Identifier,r_len+1,length(r_Identifier)-r_len) ;
end Loop;
r_new:=r_new+''''||ltrim(rtrim(r_Identifier))||'''';
r_new:=r_new ||')';
--更新语句的拼写
s_SetId:=rtrim(to_char(r_setid));
r_ssql:='update Relex.Incidents1 set IncUserBool3=1 where ((IncUserBool3 is null) or(IncUserBool3=0)) ';
r_ssql:=r_ssql||' and (setid='||s_setId||')';
r_ssql:=r_ssql||' and (Identifier in '||r_new||')';
---执行更新程序
execute immediate r_ssql;
Exception
when Others Then
return ;
END TRG_mail;