oracle事务自治

函数加上如下语句表示事务自治

 

  PRAGMA AUTONOMOUS_TRANSACTION;

 

 

 

例：

 

create or replace function func_made_applycode(schoolcode in varchar2,spe_level in integer) return varchar2 is
  Result varchar2(20);
  /*生成报名号*/
  v_yearno year_semester.year_value%type;
  v_semesterno year_semester.semester_value%type;
  v_liushui number;
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  select a.year_value,a.semester_value into v_yearno,v_semesterno from year_semester a where a.isused =1 and rownum = 1;
  select nvl(max(to_number(substr(a.apply_code,9,5))),0) into v_liushui from apply_info a
  where substr(a.apply_code,1,4) = v_yearno 
    and substr(a.apply_code,5,1) = v_semesterno 
    and substr(a.apply_code,6,2)= schoolcode;
  v_liushui := v_liushui+1;
  Result := v_yearno||v_semesterno||schoolcode||case spe_level when 103001 then 1 when 103002 then 7 when 103003 then 4 end||lpad(v_liushui,5,0);
  
  return(Result);
end func_made_applycode;