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;
/
|