Oracle自定义编码规则

Oracle自定义编码规则的实现

在编写程序的过程中，我们经常会用到对一些记录进行编码的实现，有时候定义的编码规则并不能很好的实现需求，比如使用lpad（rpad）函数进行补充的时候，有个长度的限制，这时候就影响了编码的正常使用。

在下面提供了一个公用的扩展性比较好的编码规则

1、创建编码的客户化表

-- Create table
create table CUX.CUX_DOC_SEQUENCES
(
  doc_type          VARCHAR2 (30 ),
  pk1_value         VARCHAR2 (50 ),
  pk2_value         VARCHAR2 (50 ),
  pk3_value         VARCHAR2 (50 ),
  pk4_value         VARCHAR2 (50 ),
  pk5_value         VARCHAR2 (50 ),
  next_seq_number   NUMBER ,
  created_by        NUMBER ,
  creation_date     DATE ,
  last_updated_by   NUMBER ,
  last_update_date  DATE ,
  last_update_login NUMBER
)
tablespace APPS_TS_TX_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 128 K
    next 128 K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate indexes
create unique index CUX.CUX_DOC_SEQUENCES_U1 on CUX.CUX_DOC_SEQUENCES (DOC_TYPE, PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE)
  tablespace APPS_TS_TX_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128 K
    next 128 K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

2、定义生成编码的function

FUNCTION next_seq_number(p_doc_type    IN VARCHAR2 ,
                           p_pk1_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk2_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk3_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk4_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk5_value   IN VARCHAR2 DEFAULT NULL ,
                           p_init_number IN NUMBER DEFAULT 1 ) RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION ;
 
    CURSOR c_seq IS
      SELECT ds.rowid row_id, ds.next_seq_number
        FROM cux_doc_sequences ds
       WHERE ds.doc_type = p_doc_type
         AND ds.pk1_value = nvl(p_pk1_value, '-1' )
         AND ds.pk2_value = nvl(p_pk2_value, '-1' )
         AND ds.pk3_value = nvl(p_pk3_value, '-1' )
         AND ds.pk4_value = nvl(p_pk4_value, '-1' )
         AND ds.pk5_value = nvl(p_pk5_value, '-1' )
         FOR UPDATE NOWAIT;
 
    l_row_id          VARCHAR2 (18 );
    l_next_seq_number NUMBER ;
  BEGIN
    OPEN c_seq;
    FETCH c_seq
      INTO l_row_id, l_next_seq_number;
    IF c_seq%NOTFOUND THEN
      l_next_seq_number := nvl(p_init_number, 1 );
      INSERT INTO cux_doc_sequences
        (doc_type,
         pk1_value,
         pk2_value,
         pk3_value,
         pk4_value,
         pk5_value,
         next_seq_number,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by,
         last_update_login)
      VALUES
        (p_doc_type,
         nvl(p_pk1_value, '-1' ),
         nvl(p_pk2_value, '-1' ),
         nvl(p_pk3_value, '-1' ),
         nvl(p_pk4_value, '-1' ),
         nvl(p_pk5_value, '-1' ),
         l_next_seq_number,
         SYSDATE ,
         fnd_global.user_id,
         SYSDATE ,
         fnd_global.user_id,
         fnd_global.login_id);
    ELSE
      l_next_seq_number := l_next_seq_number + 1 ;
      UPDATE cux_doc_sequences
         SET next_seq_number   = l_next_seq_number,
             last_update_date  = SYSDATE ,
             last_updated_by   = fnd_global.user_id,
             last_update_login = fnd_global.login_id
       WHERE ROWID = l_row_id;
    END IF ;
    CLOSE c_seq;
    COMMIT ;
 
    RETURN l_next_seq_number;
  END next_seq_number;

  FUNCTION next_seq_number(p_doc_type    IN VARCHAR2 ,
                           p_doc_prefix  IN VARCHAR2 ,
                           p_seq_length  IN NUMBER DEFAULT 0 ,
                           p_pk1_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk2_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk3_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk4_value   IN VARCHAR2 DEFAULT NULL ,
                           p_pk5_value   IN VARCHAR2 DEFAULT NULL ,
                           p_init_number IN NUMBER DEFAULT 1 ) RETURN VARCHAR2 IS
    l_next_seq_number NUMBER ;
    l_doc_number      VARCHAR2 (150 );
  BEGIN
    l_next_seq_number := next_seq_number(p_doc_type    => p_doc_type,
                                         p_pk1_value   => p_pk1_value,
                                         p_pk2_value   => p_pk2_value,
                                         p_pk3_value   => p_pk3_value,
                                         p_pk4_value   => p_pk4_value,
                                         p_pk5_value   => p_pk5_value,
                                         p_init_number => p_init_number);
    IF p_seq_length IS NULL OR p_seq_length = 0 THEN
      l_doc_number := p_doc_prefix || l_next_seq_number;
    ELSE
      IF length(l_next_seq_number) >= p_seq_length THEN
        l_doc_number := p_doc_prefix || l_next_seq_number;
      ELSE
        l_doc_number := p_doc_prefix ||
                        lpad(l_next_seq_number, p_seq_length, '0' );
      END IF ;
    END IF ;
    RETURN l_doc_number;
  END next_seq_number;

3、调用function生成编码

例子：

FUNCTION get_receipt_number(p_org_id IN NUMBER, p_receipt_date DATE )
  RETURN VARCHAR2 IS
  l_receipt_number VARCHAR2 (30 );
BEGIN
  l_receipt_number := cux_doc_sequence_utl.next_seq_number(p_doc_type   => 'POS_AR_RECEIPT_NUMBER' ,
                                                            p_doc_prefix => 'POS_' ||
                                                                            to_char(p_receipt_date,
                                                                                    'YYYYMM' ),
                                                            p_seq_length => 5 ,
                                                            p_pk1_value  => p_org_id,
                                                            p_pk2_value  => to_char(p_receipt_date,
                                                                                    'YYYYMM' ));
  RETURN l_receipt_number;
EXCEPTION
  WHEN no_data_found THEN
    RETURN NULL ;
    cux_conc_utl.log_msg( 'FUNCTION get_receipt_number ERROR:' ||
                          'NO_DATA_FOUND' );
    --RAISE fnd_api.g_exc_error;
  WHEN too_many_rows THEN
    RETURN NULL ;
    cux_conc_utl.log_msg( 'FUNCTION get_receipt_number ERROR:' ||
                          'TOO_MANY_ROWS' );
    --RAISE fnd_api.g_exc_error;
  WHEN OTHERS THEN
    RETURN NULL ;
    cux_conc_utl.log_msg( 'FUNCTION get_receipt_number ERROR:' || SQLERRM );
    --RAISE fnd_api.g_exc_error;
END get_receipt_number;