oracle中怎么给一个非自增的平常字段加一个唯一id规则，可以是按年月日拼上一个3位数或者六位数



写的两个函数放在一个包里，先声明包以及里面的函数

create or replace package Pkg_Generate_Num is

  -- Author  : ADMINISTRATOR
  -- Created : 2012-12-28 7:18:36
  -- Purpose :

   -- 获取单据唯一编号
  Function getVoucherNum(a_VoucherType varchar2) Return Varchar2;
  Function getVoucherNum(a_VoucherType varchar2,a_len integer) Return Varchar2;
  --技改工程保修中使用生成唯一编号
  Function getVoucherNumBYJG(a_VoucherType varchar2) Return Varchar2;
end Pkg_Generate_Num;

下面就是包体了：

create or replace package body Pkg_Generate_Num is

   -- 获取记录唯一ID  生成如这样qz_201406120000000001
 function getVoucherNum(a_VoucherType varchar2) return Varchar2 Is
    pragma autonomous_transaction;
    mN_LastUID  Varchar2(30);
    mS_BillType Varchar2(30);
  Begin
    mS_BillType := a_VoucherType || to_char(Sysdate, 'yyyymmdd');

    select Max_UID
      into mN_LastUID
      from sjt_MAXUID
     Where bill_day = mS_BillType
       for update;

    mN_LastUID := mN_LastUID + 1;

    UPDATE sjt_MAXUID
       SET max_uid = mN_LastUID
     Where bill_day = mS_BillType;
    commit;
    return mS_BillType || lpad(mN_LastUID, 10, '0');
  exception
    when NO_DATA_FOUND Then
        Begin
            Insert Into sjt_MAXUID
              (bill_day, max_uid)
            Values
              (mS_BillType, 1);

            commit;
            return mS_BillType || lpad('1', 10, '0');
        Exception
            When Others Then
              Rollback;
              sys.dbms_lock.sleep(ceil(dbms_random.value*5));
              Return getVoucherNum(a_VoucherType);
        End;
  end;



 -- 获取记录唯一ID //输入的参数一个是前面添加的字符， 后面添加的是要生成几位数字
  function getVoucherNum(a_VoucherType varchar2,a_len integer) return Varchar2 Is
    pragma autonomous_transaction;
    mN_LastUID  Varchar2(30);
    mS_BillType Varchar2(30);
    mS_len integer;
  Begin
    mS_BillType := a_VoucherType ;

    select Max_UID
      into mN_LastUID
      from sjt_MAXUID
     Where bill_day = mS_BillType
       for update;

    mN_LastUID := mN_LastUID + 1;

    if a_len - length(mN_LastUID) < 0 then
      mS_len := length(mN_LastUID);
    else
      mS_len := a_len;
    end if;

    UPDATE sjt_MAXUID
       SET max_uid = mN_LastUID
     Where bill_day = mS_BillType;
    commit;
    return mS_BillType || lpad(mN_LastUID, mS_len, '0');
  exception
    when NO_DATA_FOUND Then
        Begin
            Insert Into sjt_MAXUID
              (bill_day, max_uid)
            Values
              (mS_BillType, 1);

            commit;
            return mS_BillType || lpad('1', a_len, '0');
        Exception
            When Others Then
              Rollback;
              sys.dbms_lock.sleep(ceil(dbms_random.value*5));
              Return getVoucherNum(a_VoucherType);
        End;
  end;
  
   -- 获取记录唯一ID生成如这样 qz_0001
  function getVoucherNumBYJG(a_VoucherType varchar2) return Varchar2 Is
    pragma autonomous_transaction;
    mN_LastUID  Varchar2(30);
    mS_BillType Varchar2(30);
  Begin
    mS_BillType := a_VoucherType;

    select Max_UID
      into mN_LastUID
      from sjt_MAXUID
     Where bill_day = mS_BillType
       for update;

    mN_LastUID := mN_LastUID + 1;

    UPDATE sjt_MAXUID
       SET max_uid = mN_LastUID
     Where bill_day = mS_BillType;
    commit;
    return mS_BillType || '_' || lpad(mN_LastUID, 4, '0');
  exception
    when NO_DATA_FOUND Then
        Begin
            Insert Into sjt_MAXUID
              (bill_day, max_uid)
            Values
              (mS_BillType, 1);

            commit;
            return mS_BillType|| '_' || lpad('1', 4, '0');
        Exception
            When Others Then
              Rollback;
              sys.dbms_lock.sleep(ceil(dbms_random.value*5));
              Return getVoucherNum(a_VoucherType);
        End;
  end;

begin
  -- Initialization
  null;
end Pkg_Generate_Num;

这里用到了一张中间表，表结构如下：

-- Create table  SJT_MAXUID
create table SJT_MAXUID
(
  bill_day VARCHAR2(20) not null,
  max_uid  NUMBER(30)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SJT_MAXUID
  add constraint PK_SJT_MAXUID_MAXUID primary key (BILL_DAY)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

这个就先整理到这里了，这个适合在写好一个字段后用触发器或者程序调用给另一个字段赋值。很方便，也很实用。