记一次解决Oracle数据库中表主键不够用的问题

问题简述

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

核心代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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;
/

测试调用

1
2
3
4
5
6
7
8
9
10
11
/* 测试: 循环插入数据 查看主键的变化*/
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:原主键生成方式:

1
2

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

查看结果

1
2
3
SELECT * FROM CITY
WHERE ROWNUM < 50
ORDER BY CID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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}

插入示例

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

Done.