第一次用oracle 函数,跟sql有太多的不同,涉及的知识点还是很多 函数创建、类型转换、if else的用法、必须要传参数、字符型相加的特殊写法、substr函数的用法等等
CREATE or replace FUNCTION fun_DelegateNo(currentdate in date)
RETURN VARCHAR2 IS v_result VARCHAR2(50);
begin
select max(DELEGATENO) INTO v_result from EXPORTDELEGATE
-- rownum=1 ;to_number(substr(,5,12))'yymmdd hh24:mi:ss'
where CreateTime
between to_date(to_char(currentdate,'yyyyMMdd')||' 00:00:00','yymmdd hh24:mi:ss') and
to_date(to_char(currentdate,'yyyyMMdd')||' 23:59:59','yymmdd hh24:mi:ss');
if v_result is null
then
v_result:= 'FLEC'||to_char(currentdate,'yyyyMMdd')||'0001';
else
v_result:= 'FLEC'|| to_char(currentdate,'yyyyMMdd')||substr(('000'||to_char(to_number(substr(v_result,-4,4))+1)),-4,4);
end if;
RETURN v_result;
end;