Oracle 根据规则生成流水号

1.业务需求
不同企业生成不同的供应商编码，考虑并发情况。编码规则：前缀 + 5位流水号，特此写一通用函数。

2.代码展示
/*
1. 适用于含有企业编号的表，且主键为纯数字，并发低或无并发的场景
*/
FUNCTION f_getkeyid_number(pi_table_name VARCHAR2,
pi_column_name VARCHAR2,
pi_company_id VARCHAR2) RETURN VARCHAR2 IS
p_length INT;
p_id NUMBER(38);
p_sql VARCHAR2(4000);
p_result VARCHAR2(50);
BEGIN
SELECT MAX(decode(data_type, 'NUMBER', data_precision, data_length))
INTO p_length
FROM user_tab_columns
WHERE table_name = upper(pi_table_name)
AND column_name = upper(pi_column_name);

p_sql := 'select max(' || pi_column_name || ') from ' || pi_table_name ||
         ' where company_id=''' || pi_company_id ||
         '''and regexp_like(' || pi_column_name ||
         ' ,''^[0-9]+[0-9]$'') ';
EXECUTE IMMEDIATE p_sql
  INTO p_id;
IF length(p_id) > p_length THEN
  RETURN NULL;
END IF;

RETURN to_char(p_id + 1);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(p_sql);
raise_application_error(-20002, SQLERRM);
RETURN NULL;
END;

/============================================

Author : CZH
Created : 2020-09-12 16:57:42
ALERTER :
ALERTER_TIME :
Purpose : 2. 获取不同企业的供应商编码编码规则：前缀 + 流水号
Obj_Name : F_GETKEYCODE
Arg_Number : 5
PI_TABLE_NAME : 表名
PI_COLUMN_NAME : 列名
PI_COMPANY_ID : 公司编号
PI_PRE : 前缀
PI_SERAIL_NUM : 流水号长度
============================================/

FUNCTION f_getkeycode(pi_table_name VARCHAR2, --表名
pi_column_name VARCHAR2, --列名
pi_company_id VARCHAR2, --公司编号
pi_pre VARCHAR2, --前缀
pi_serail_num NUMBER) RETURN VARCHAR2 IS
--流水号长度
/* pi_table_name VARCHAR2(250) := 't_supplier_info';
pi_column_name VARCHAR2(250) := 'supplier_code';
pi_company_id VARCHAR2(250) := 'a972dd1ffe3b3a10e0533c281cac8fd7';
pi_pre VARCHAR2(250) := 'C';
pi_serail_num NUMBER := 5;*/
p_length INT;
p_id NUMBER(38);
p_sql VARCHAR2(4000);
p_result VARCHAR2(50);
BEGIN
SELECT MAX(decode(data_type, 'NUMBER', data_precision, data_length))
INTO p_length
FROM user_tab_columns
WHERE table_name = upper(pi_table_name)
AND column_name = upper(pi_column_name);

--dbms_output.put_line(p_length);

p_sql := 'SELECT nvl(MAX(v.tcode), 0)

FROM (SELECT DISTINCT MAX(to_number(substr(' ||
pi_column_name || ',
nvl(length(''' ||
pi_pre ||
'''), 0) + 1,
length(''' ||
pi_column_name || ''')))) over(PARTITION BY substr(' ||
pi_column_name || ', 0, nvl(length(''' || pi_pre ||
'''), 0))) tcode
FROM ' || pi_table_name || '
WHERE company_id = ''' || pi_company_id || '''
AND ' || pi_column_name || ' IS NOT NULL
AND substr(' || pi_column_name ||
', 0, nvl(length(''' || pi_pre || '''), 0)) = ''' || pi_pre || '''
AND regexp_like(substr(' || pi_column_name || ',
nvl(length(''' || pi_pre ||
'''), 0) + 1,
length(''' || pi_column_name ||
''')),' || '''^[0-9]+[0-9]$''' || ')) v';

-- dbms_output.put_line(p_sql);

EXECUTE IMMEDIATE p_sql
  INTO p_id;

IF (length(pi_pre) +
   length(lpad(to_char(p_id + 1), pi_serail_num, '0'))) > p_length THEN
  dbms_output.put_line('超出字段長度');
END IF;

/*dbms_output.put_line(pi_pre ||
lpad(to_char(p_id + 1), pi_serail_num, '0'));*/

p_result := pi_pre || lpad(to_char(p_id + 1), pi_serail_num, '0');

RETURN p_result;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(p_sql);
raise_application_error(-20002, SQLERRM);
RETURN NULL;
END f_getkeycode;