不同的单据根据日期和单据流水自动生成顺序的单号。
解决办法:
create
or
replace
procedure
proc_id_builder(id
varchar2
, sn out
varchar2
)
AS
rowt int ;
upperID varchar2 ( 64 );
begin
upperID : = upper (id);
select count ( * ) into rowt from ts_parameter where parameterid = upperID;
if rowt = 0 then
sn : = to_char(sysdate, ' YYMMDD ' ) || ' 000001 ' ;
insert into ts_parameter values (upperID, sn, '' );
commit ;
return ;
end if ;
select parametervalue into sn from ts_parameter where parameterid = upperID for update ;
if substr(sn, 1 , 6 ) > to_char(sysdate, ' YYMMDD ' ) then
sn : = '' ;
return ;
end if ;
-- 生成单号
if substr(sn, 1 , 6 ) < to_char(sysdate, ' YYMMDD ' ) then
sn : = lpad(to_char(sysdate, ' YYMMDD ' ) || ' 000001 ' , 12 , ' 0 ' );
else
sn : = lpad(to_char(to_number(sn) + 1 ), 12 , ' 0 ' );
end if ;
-- 更新单号
update ts_parameter set parametervalue = sn where parameterid = upperID;
commit ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
lfguser.proc_log_ora_exception(p_err_Num => SQLCODE,
p_err_Msg => SQLERRM,
p_err_Source => ' proc_ticketid_builder ' );
end ;
rowt int ;
upperID varchar2 ( 64 );
begin
upperID : = upper (id);
select count ( * ) into rowt from ts_parameter where parameterid = upperID;
if rowt = 0 then
sn : = to_char(sysdate, ' YYMMDD ' ) || ' 000001 ' ;
insert into ts_parameter values (upperID, sn, '' );
commit ;
return ;
end if ;
select parametervalue into sn from ts_parameter where parameterid = upperID for update ;
if substr(sn, 1 , 6 ) > to_char(sysdate, ' YYMMDD ' ) then
sn : = '' ;
return ;
end if ;
-- 生成单号
if substr(sn, 1 , 6 ) < to_char(sysdate, ' YYMMDD ' ) then
sn : = lpad(to_char(sysdate, ' YYMMDD ' ) || ' 000001 ' , 12 , ' 0 ' );
else
sn : = lpad(to_char(to_number(sn) + 1 ), 12 , ' 0 ' );
end if ;
-- 更新单号
update ts_parameter set parametervalue = sn where parameterid = upperID;
commit ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
lfguser.proc_log_ora_exception(p_err_Num => SQLCODE,
p_err_Msg => SQLERRM,
p_err_Source => ' proc_ticketid_builder ' );
end ;