oracle事务自治

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

  PRAGMA AUTONOMOUS_TRANSACTION;

例：

Sql代码  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;  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;

原文章浏览量：1098

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-1069481