使用触发器创建复杂ID

Question

在我们的许多应用程序中，我们使用标识列来生成唯一的数字，例如customer_id。

我们的内部审计人员认为这可能违反了安全规定，我们希望使用更复杂的方法。

我在base34下面找到了一个函数，我想传递SYS_Guid的连接，TIMESTAMP的一部分和序列号来创建一个更复杂的ID。

下面是我的测试用例。是否有一种方法，我可以使用base34函数前插入触发器与上述连接，而不改变base34函数来实现这个任务。

例如，假设我有以下表格:

CREATE TABLE CUSTOMERS (
customer_id VARCHAR2 (20),
first_name VARCHAR2 (20),
last_name VARCHAR2 (20));

我想让触发器填充customer_id

提前感谢您的时间和专业知识。

create table t ( pk number);
create sequence seq start with 1000000 minvalue 1000000 maxvalue 9999999 cycle;
begin
for i in 1 .. 10 loop
insert into t values (            to_number(trunc(dbms_random.value(1000,9999))||                      to_char(systimestamp,'FFSS')||
seq.nextval));
end loop;
end;
/
create or replace function base34(p_num number) return varchar2 is
l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
l_num number := p_num;
l_str varchar2(38);
begin
loop
l_str := substr(l_dig,mod(l_num,34)+1,1) || l_str ;
l_num := trunc(l_num/34);
exit when l_num = 0;
end loop;
return l_str;
end;
/

create or replace function dec34(p_str varchar2) return number is
l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
l_num number := 0;
begin
for i in 1 .. length(p_str) loop
l_num := l_num * 34 + instr(l_dig,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end;
/
select base34(pk) from t where rownum <= 10;
select to_char(pk) from t where rownum = 1
union all
select base34(pk) from t where rownum = 1
union all
select to_char(dec34(base34(pk))) from t where rownum = 1;

Answer 1

CREATE TABLE CUSTOMERS (
customer_id VARCHAR2 (20),
first_name VARCHAR2 (20),
last_name VARCHAR2 (20));

create sequence seq start with 1000000 minvalue 1000000 maxvalue 9999999 cycle;
create or replace function base34(p_num number) return varchar2 is
l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
l_num number := p_num;
l_str varchar2(38);
begin
loop
l_str := substr(l_dig,mod(l_num,34)+1,1) || l_str ;
l_num := trunc(l_num/34);
exit when l_num = 0;
end loop;
return l_str;
end;

CREATE OR REPLACE TRIGGER customer_trg
BEFORE    INSERT 
ON    customers 
FOR EACH ROW
BEGIN
:NEW.customer_id := base34(to_number(trunc(dbms_random.value(1000,9999))||                      to_char(systimestamp,'FFSS')||
seq.nextval));
END;
begin
for i in 1 .. 100 loop
INSERT into customers (first_name, last_name) VALUES ('John', 'Doe');
end loop;
end;
/