Oracle自动生成编号的函数

来源：互联网发布：asp工资管理系统源码编辑：程序博客网时间：2024/04/25 19:45

第一次用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;