Oracle自定义编码规则

来源：互联网发布：ubuntu安装mac os 编辑：程序博客网时间：2024/05/17 22:30

Oracle自定义编码规则的实现

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

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

1、创建编码的客户化表

-- Create tablecreate 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 indexescreate 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;

0 0