ORACLE存储过程自动生成编号

Oracle之DBMS_RANDOM 包的使用例子

create or replace procedure PRO_AUTO__PM(CODE_NO_OUT OUT VARCHAR2) is
--自动生成编号，规则（年月+5位随机数+7位流水号）
  codeNum    number;
  codeNumLen number;
  codeNumPC  VARCHAR2(7);

begin
  select case
           when t.cutNum = 0 then
            1
           else
            (select to_number(p.code_no) + 1
               from YW_PM p
              where p.date_time = to_char(sysdate, 'yyyyMM'))
         end
    INTO codeNum
    from (select count(1) cutNum from YW_PM p where  p.date_time = to_char(sysdate, 'yyyyMM')) t;

  IF codeNum = 1 then
    insert into YW_PM
      (DATE_TIME, VALID_CODE, CODE_NO)
    VALUES
      (to_char(sysdate, 'yyyyMM'),
       (DBMS_RANDOM.STRING('X', 4) || trunc(dbms_random.value(1, 9))),
       '0000001');
       commit;
  Elsif codeNum > 1 then
    codeNumLen := length(TO_CHAR(codeNum));
    while codeNumLen < 7 loop
      codeNumPC  := codeNumPC || '0';
      codeNumLen := codeNumLen + 1;
    end loop;
    UPDATE YW_PM
       SET CODE_NO   =
           (codeNumPC || TO_CHAR(codeNum)),
           VALID_CODE =
           (DBMS_RANDOM.STRING('X', 4) || trunc(dbms_random.value(1, 9)))
     WHERE DATE_TIME = to_char(sysdate, 'yyyyMM');
     commit;
  End IF;
  select to_char(p.date_time) || to_char(p.valid_code) ||
         to_char(p.code_no)
    into CODE_NO_OUT
    from YW_PM p where  p.date_time = to_char(sysdate, 'yyyyMM');


Exception
  When others then
    Rollback;
    Dbms_Output.put_line(Sqlerrm);

end PRO_AUTO_PM;