问题简述

数据库中用来存储客户信息表的主键字段类型 **VARCHAR2(4)**。某天，值累计到9999，这时候系统出现异常 无法正常插入数据，综合其他模块来看，决定使用存储函数来解决主键字段不够用的情况，即使用子母结合数字的方式来生成主键序列，以下是具体的实现方式。

核心代码

create FUNCTION FN_CTPK_INCREASE
/********************************************************************
     [Function名称]: FN_CTPK_INCREASE
     [目 的]: 解决表字段不够用的问题
     [备 注]:
     [参 数]:
         # INPUT:
                  无
         # OUTPUT:
                  1. RTN_FIELD        返回处理好的PK值
  
     [使用示例]: SELECT FN_CTPK_INCREASE() FROM DUAL;
  
     [VERSION ]:
     |----------|-----------|----------------|-------------------|
     |版本       |日期       |制作/修改者       |说明                |
     |----------|-----------|----------------|-------------------|
     |1.0       |2019-11-09 |柒索            |                    |
     |----------|-----------|----------------|-------------------| 
 ********************************************************************/
    RETURN VARCHAR2 AS
  RTN_FIELD VARCHAR2(30);    /*最终返回值*/
  FILED_VAL  VARCHAR2(4);    /*表中主键的最大值*/
  FIELD_CNT NUMBER;          /*统计数量*/
  FILED_VAL1 VARCHAR2(1);    /*拆分最大值 第一位*/
  FILED_VAL2 VARCHAR2(1);    /*拆分最大值 第二位*/
  FILED_VAL3 VARCHAR2(1);    /*拆分最大值 第三位*/
  FILED_VAL4 VARCHAR2(1);    /*拆分最大值 第四位*/
BEGIN
    /*统计列中包含字母的值*/
    SELECT COUNT(1)
      INTO FIELD_CNT
      FROM CITY
     WHERE REGEXP_LIKE(CID, '([A-Z])');
  
    /*如果统计到列中包含字母的主键的值为0时  将返回值RTN_FIELD设置为A000*/
    IF FIELD_CNT = 0 THEN
      RTN_FIELD := 'A000';
    ELSE
      /*获取CITY表中的最大值*/
      SELECT NVL(MAX(CID), '0000')
        INTO FILED_VAL
        FROM CITY
       WHERE REGEXP_LIKE(CID, '([A-Z])');

      /*将拿到的值进行拆分*/
      SELECT SUBSTR(FILED_VAL, 1, 1),
             SUBSTR(FILED_VAL, 2, 1),
             SUBSTR(FILED_VAL, 3, 1),
             SUBSTR(FILED_VAL, 4, 1)
        INTO FILED_VAL1, FILED_VAL2, FILED_VAL3, FILED_VAL4
        FROM DUAL;
    
      /*
        ASCII编码中0、9、A、Z分别对应的数字如下：
        ASCII('0') = 48
        ASCII('9') = 57
        ASCII('A') = 65
        ASCII('Z') = 90
      */

      IF TO_NUMBER(ASCII(FILED_VAL4)) = 90 THEN
        FILED_VAL4 := '0';
        IF TO_NUMBER(ASCII(FILED_VAL3)) = 90 THEN
          FILED_VAL3 := '0';
          IF TO_NUMBER(ASCII(FILED_VAL2)) = 90 THEN
            FILED_VAL2 := '0';
            IF TO_NUMBER(ASCII(FILED_VAL1)) = 57 THEN
              FILED_VAL1 := 'A';
            ELSE
              FILED_VAL1 := CHR(TO_NUMBER(ASCII(FILED_VAL1)) + 1);
            END IF;
          ELSE
            IF TO_NUMBER(ASCII(FILED_VAL2)) = 57 THEN
              FILED_VAL2 := 'A';
            ELSE
              FILED_VAL2 := CHR(TO_NUMBER(ASCII(FILED_VAL2)) + 1);
            END IF;
          END IF;
        ELSE
          IF TO_NUMBER(ASCII(FILED_VAL3)) = 57 THEN
            FILED_VAL3 := 'A';
          ELSE
            FILED_VAL3 := CHR(TO_NUMBER(ASCII(FILED_VAL3)) + 1);
          END IF;
        END IF;
      ELSE
        IF TO_NUMBER(ASCII(FILED_VAL4)) = 57 THEN
          FILED_VAL4 := 'A';
        ELSE
          FILED_VAL4 := CHR(TO_NUMBER(ASCII(FILED_VAL4)) + 1);
        END IF;
      END IF;
      /*拼接处理结果 RETURN*/
      RTN_FIELD := FILED_VAL1||FILED_VAL2||FILED_VAL3||FILED_VAL4; 
    END IF;
  /*DBMS_OUTPUT.PUT_LINE('RETURN-:' || RTN_FIELD);*/
  RETURN RTN_FIELD;
END FN_CTPK_INCREASE;
/

测试调用

/* 测试： 循环插入数据 查看主键的变化*/
BEGIN
  FOR i IN 1 .. 1000 LOOP
        INSERT INTO SCOTT.CITY (CID, CNAME, CTYCODE, DISTRICT, INFO)
            VALUES (FN_CTPK_INCREASE(),
                    'Shanghai', 
                    'CHN', 
                    'Shanghai',
                    '{"Population": 9696300}');
    END LOOP;
END;

PS：原主键生成方式：


SELECT NVL((SELECT CID + 1 FROM CITY WHERE CID > ' ' AND ROWNUM = 1 ), '1000') FROM DUAL;

查看结果

SELECT * FROM CITY
WHERE ROWNUM  < 50
ORDER BY CID;
1000	Boston       	USA  	Massachusetts	{"Population": 589141}
9999	Panchiao     	CHN  	Taipei       	{"Population": 523850}
A000	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A001	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A002	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A003	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A004	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A005	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A006	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A007	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A008	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A009	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00A	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00B	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00C	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00D	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00E	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00F	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00G	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00H	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00I	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00J	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00K	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00L	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00M	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00N	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00O	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00P	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00Q	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00R	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00S	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00T	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00U	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00V	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00W	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00X	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00Y	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A00Z	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A010	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A011	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A012	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A013	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A014	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A015	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A016	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A017	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A018	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A019	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}
A01A	Shanghai     	CHN  	Shanghai     	{"Population": 9696300}

插入示例

INSERT INTO SCOTT.CITY (CID, CNAME, CTYCODE, DISTRICT, INFO)
	VALUES (FN_CTPK_INCREASE(), 
          'Shanghai', 
          'CHN', 
          'Shanghai', 
          '{"Population": 9696300}');

Done.