复制代码 代码如下:
create or replace function fn_no_make(v_prefix varchar2,
v_table_name varchar2,
v_number_col varchar2)
/*
* v_prefix: 编码前缀两位
* v_table_name：编码所在表名
* v_number_col：编码所在列名
*/
return varchar2 is
v_old_no varchar2(50); –原编码
v_new_no varchar2(50); –新编码
v_old_num number; –原编码后三位编号
v_new_num varchar2(10); –新编码后三位编号
v_date_no varchar2(10); –当前日期编号
v_sql varchar2(4000);
begin
v_sql := ‘select max(‘ || v_number_col || ‘) from ‘ || v_table_name;
execute immediate v_sql into v_old_no;
v_sql := ‘select to_char(sysdate,”yyyymmdd”) from dual’;
execute immediate v_sql into v_date_no;
v_old_num := to_number(substr(v_old_no, 12, 3));
v_new_num := lpad(to_char(v_old_num+1), 3, ‘0’);
if v_old_no is null or substr(v_old_no, 3, 8) <> v_date_no then
v_new_no := v_prefix || v_date_no || ‘-‘ || ‘001’;
else
v_new_no := v_prefix || v_date_no || ‘-‘ || v_new_num;
end if;
return v_new_no;
exception
when others then
dbms_output.put_line(sqlerrm);
end fn_no_make;