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;

 

Oracle开发常用函数

标签：

本文系统来源：http://www.cnblogs.com/yangpeng-jingjing/p/4556310.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！