Tips_018：使用Trigger实现不重复的流水号

需求如下：
（1）书籍的编码由字符串"ISBN-"加9位数字，其中9位数字部分不满9位的填充"0"。
（2）9位数字部分不能重复，且必须连续，不能“浪费”。

解决方案：
（1）“不能重复，且必须连续”的要求基本排除了使用DB Sequence来实现的可能性。
（2）考虑使用一个代码表，专门存储当前的数字。
（3）考虑使用Trigger在Insert数据时，使用TO_CHAR()函数更新相应字段。

1. 创建表
CREATE TABLE book (
book_id     NUMBER PRIMARY KEY,
book_code   VARCHAR2(14 BYTE),
book_name   VARCHAR2(20 BYTE),
book_number NUMBER
);

2. 创建Sequence，用于填充主键：book_id
CREATE SEQUENCE book_seq START WITH 1 INCREMENT BY 1;

3. 创建代码表，存储对应代码的当前的数字
CREATE TABLE code_value(    
   code  VARCHAR2(4 BYTE) PRIMARY KEY,
   value NUMBER
);

code_value表内容如下：
PRODUCT_CODE 0
CUSTOMER_CODE 0
EMPLOYEE_CODE 0
BOOK_CODE 0

4. 创建Trigger
CREATE OR REPLACE
TRIGGER book_trigger  
BEFORE INSERT ON book  
FOR EACH ROW  
BEGIN  
  IF INSERTING THEN
    IF :NEW.book_id IS NULL THEN
      SELECT book_seq.nextval INTO :NEW.book_id FROM dual FOR UPDATE;
      SELECT NVL(VALUE,0) + 1 INTO :NEW.book_number FROM code_value WHERE code='BOOK_CODE';
      SELECT 'ISBN-'||TRIM(TO_CHAR(NVL(VALUE,0) + 1, '000000009'))  INTO :NEW.book_code FROM code_value WHERE code='BOOK_CODE';
      UPDATE code_value SET VALUE=NVL(VALUE,0) + 1 WHERE code='BOOK_CODE' ;
    END IF;
  END IF;
END ;

说明：

（1）SELECT book_seq.nextval INTO :NEW.book_id FROM dual FOR UPDATE;

把Sequence的下一个值赋值给book_id，FOR UPDATE是为了保证并发情况下不会有重复的流水号。

（2）SELECT NVL(VALUE,0) + 1 INTO :NEW.book_number FROM code_value WHERE code='BOOK_CODE';

从代码表中获取当前的数字值，并加1后，赋值给book_number。

（3）SELECT 'ISBN-'||TRIM(TO_CHAR(NVL(VALUE,0) + 1, '000000009'))  INTO :NEW.book_code FROM code_value WHERE code='BOOK_CODE';

从代码表中获取当前的数字值，加1后，并使用TO_CHAR()函数后，赋值给book_code。

其中 '000000009'，0表示如果没有值，就填充0；9表示任意数字。

5. 测试

添加如下数据：

INSERT INTO book(book_name) VALUES ('aa'); 

INSERT INTO book(book_name) VALUES ('bb');

INSERT INTO book(book_name) VALUES ('cc'); 

INSERT INTO book(book_name) VALUES ('dd');

COMMIT;

查看数据库，其中Book表数据如下：

参考文献：

1. http://www.52rs.net/ArticleView.aspx?gID=71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d