oracle开发常用,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;

原文：http://www.cnblogs.com/yangpeng-jingjing/p/4556310.html