Oracle：触发器中截取指定字符串并做处理实例

create or replace trigger tri_cdo_mstr_upd before insert or update of cdo_content on cdo_mstr
for each row
declare 
    var_start INT := 0;
    var_end   INT := 0;
    str       varchar2(4000);
    --str_flag1 varchar2(255) := '<span style=';
    str_flag1 varchar2(255) := '<span style="font-size: 8pt; color: gray">';
    str_flag2 varchar2(255) := '<span style="color:gray; font-size:8pt">';
    str_flag3 varchar2(255) := '</span>';
    var_cdo_content  varchar2(4000);
begin
   
   var_cdo_content := :new.cdo_content; 
    loop
      if instr(var_cdo_content,str_flag1) > 0 then
         var_start := instr(var_cdo_content,str_flag1);
      elsif instr(var_cdo_content,str_flag2) > 0 then
         var_start := instr(var_cdo_content,str_flag2);
      end if;

      if var_start > 0 then
        var_end := instr(var_cdo_content,str_flag3);
      end if;
      
      exit when (var_start=0 or var_end=0);

      if var_start > 0 and var_end > 0 then
        str := substr(var_cdo_content,var_start,var_end-var_start+length(str_flag3));
      end if;

      var_cdo_content := replace(var_cdo_content,str);
    end loop;
    
    :new.cdo_content:=var_cdo_content;

end tri_cdo_mstr_upd;

 

转载于:https://www.cnblogs.com/su1643/p/6723295.html