Oracle开发常用函数

max 最大数 自动加 1

create or replace function fun_getmaxlot(

       vend in varchar2 ,

       domain IN VARCHAR2,

       tag in varchar2

)

 RETURN VARCHAR2

 AS

 MAXVAL     VARCHAR2(40);

 NEWIQC     VARCHAR2(40);

 TT         VARCHAR2(12);

BEGIN

     BEGIN

      select max(xsld_lot) into MAXVAL

    from xsld_det

    where xsld_lot like

    upper(tag)|| to_char(sysdate, 'yyMMdd') || Upper(vend)||'%'

    and UPPER(xsld_domain) = UPPER(domain);

      if(MAXVAL is null) THEN

         return upper(tag)||to_char(sysdate, 'yyMMdd') || Upper(vend)||'001';

      end if;

      NEWIQC := replace(MAXVAL , upper(tag)||to_char(sysdate, 'yyMMdd') || Upper(vend) ,'');

        TT := ( NEWIQC*1 + 1)||'';

        for m in LENGTH(TT)..(2) loop

            TT :=  '0' || TT;

        end loop;

        return upper(tag)||to_char(sysdate, 'yyMMdd') || Upper(vend) ||TT;

     EXCEPTION

     WHEN NO_DATA_FOUND THEN

         RETURN '';

     END;

END;