oracle 函数生成流水号

code_base 表

create table CODE_BASE
(
  code  VARCHAR2(20),
  vdate VARCHAR2(20),
  seq   NUMBER
)

CREATE OR REPLACE FUNCTION create_code
(p_code varchar2,p_length number) return VARCHAR2 as PRAGMA AUTONOMOUS_TRANSACTION; 
v_count number;
v_code varchar2(40);
v_lock code_base%rowtype;
begin
  select count(1)
    into v_count
    from code_base c
   where c.code = p_code
     and c.vdate = to_char(sysdate, 'yymmdd');




dbms_output.put_line(v_count) ;    
if v_count < 1 then
  insert into code_base(code,vdate,seq)
  values(p_code,to_char(sysdate, 'yymmdd'),1);
end if;
 select * into v_lock from code_base c where c.code = p_code
     and c.vdate = to_char(sysdate, 'yymmdd') for update;
     

 
 select c.code||c.vdate||lpad(c.seq, p_length, 0) into v_code from code_base c where c.code = p_code
     and c.vdate = to_char(sysdate, 'yymmdd'); 

 update code_base c
 set c.seq=c.seq+1
 where c.code = p_code and c.vdate = to_char(sysdate, 'yymmdd'); 
commit;
return v_code;        


end;