PL/SQL中项目编号的流水号生成的代码

function judge_prj_number return varchar2 is
v_old_number varchar(50);
v_new_number varchar(50);
v_new_date varchar(30);
v_new_num varchar(30); --新编码后四位
v_old_num varchar(30); --原编码后四位
v_number varchar(50);
–v_count number; --流水号

begin
–找出最大的
select max(project_number) into v_old_number from prj_project_22119;
–将当前日期取出
select substr(to_char(sysdate, ‘yyyymmdd’),1,8) into v_new_date from dual;
v_old_num := to_number(substr(v_old_number, -4, 4)); --取出旧编码后四位
v_new_num :=to_char(v_old_num + 1); --将旧编码后四位 +1然后将值赋给新的编码
WHILE length(v_new_num) < 4
LOOP
v_new_num := ‘0’ || v_new_num;
END LOOP;
select count(project_number) into v_number from prj_project_22119; --查询表中project_number的个数
if v_number > 0 then
v_new_number := ‘PRJ’ || v_new_date || v_new_num; --返回完整的新编码
ELSE
v_new_number := ‘PRJ’ || v_new_date || ‘0001’;
END IF;
return v_new_number;
exception
when others then
—例外返回null
return null;
end judge_prj_number;