我们经常需要用一个流水号来唯一表示一条数据，我们有时采用队列来自动生成一个唯一的流水号，但是采用队列经常不能满足我们的需求，比如说，这个队列只能设定一个最小值，最大值，然后进行累加，不能将产生这个流水号的日期包括今这个流水号中；一种类型就要新建一个队列等。
下面这个存储过程可以产生一个流水号，它的格式是当前日期（格式YYYYMMDD）＋6位的流水号数字，不同的类型只要给出一个不同的类型名称就可以了。在使用这个存储过程前，要先创建一个表来保存不同的类型，表结构如下：
create table T_GENID
(
  CLASS   NUMBER(2) not null,
  GENDATE CHAR(8) not null,
  ID      VARCHAR2(12) not null
)
tablespace SERVICE_MAIN_DAT
  pctfree 10
  pctused 80
  initrans 1
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table T_GENID
  add constraint PK_GENID primary key (CLASS,ID)
  using index
  tablespace SERVICE_MAIN_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

存储过程如下：
CREATE OR REPLACE PROCEDURE p_GetSerialNo
(
  i_Type                  IN     VARCHAR2,           --流水号的类型
  o_SerialNo              OUT    VARCHAR2            --返回流水号
) IS
  v_Dat                          VARCHAR2(10);
  v_SerialNum                    NUMBER(7);
BEGIN
  --取系统日期生成需要的字符串
  v_Dat := TO_CHAR(SYSDATE,('YYYYMMDD'));

  --生成产生流水号的序号
  BEGIN
    SELECT Decode(GENDATE,v_Dat,(ID+1),1)
      INTO v_SerialNum
      FROM T_GENID WHERE CLASS=i_Type FOR UPDATE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_SerialNum:= 1;
      INSERT INTO T_GENID(CLASS,GENDATE,ID)
        VALUES (i_Type,v_Dat,v_SerialNum);

    WHEN TOO_MANY_ROWS THEN
      SELECT NVL(MAX(ID)+1,1)
        INTO v_SerialNum
        FROM T_GENID WHERE CLASS=i_Type;

      DELETE t_AGT_GENID WHERE CLASS=i_Type;
      INSERT INTO T_GENID(CLASS,GENDATE,ID)
        VALUES (i_Type,v_Dat,v_SerialNum);
  END;

  --修改流水号参数
  UPDATE T_GENID
     SET GENDATE = v_Dat,
         ID = v_SerialNum WHERE CLASS=i_Type;
  COMMIT;
  --得到需要的流水号
  o_SerialNo := v_Dat||Lpad(v_SerialNum,6,'0');
EXCEPTION
  WHEN OTHERS THEN
    o_SerialNo := NULL;
END;
/

使用方法很简单，就是在pl/sql中调用这个存储过程，给其设置一个类型就行了，如下：
CREATE OR REPLACE PROCEDURE p_Test
(
)
IS
  v_serId VARCHAR2(14);
BEGIN
  P_Agt_GetSerialNo ('test',v_serId);   --生成流水号，放到变量v_serId中,第一个参数就是给其设置的类型了
 
  INSERT INT t_test (testid) VALUES(v_serId);--将产生的流水号插入表中
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;
/

该文章在 2011/3/3 16:59:00 编辑过