项目中要依据车牌号码、日期和随机码生成运单号,所以写了函数和创建了历史码存储表来保证运单号的不重复。
运单号=车牌号码+日期+四位随机码
1、创建历史运单号存储表(用来比对新生成的随机号是否已被使用)
-- Create table
create table RANDOM_NUMBER
(
RANDOMNUMBER NVARCHAR2(50) not null,
PLATENNUMBER NVARCHAR2(50) not null,
ADDDATE DATE default SYS_EXTRACT_UTC(SYSTIMESTAMP) not null
)
tablespace WMWHSE1_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
--------------------------------------------------------------------------
2、创建生成随机码函数
CREATE OR REPLACE FUNCTION fn_ifrandom (plateNnumber1 IN VARCHAR2)
RETURN varchar2
IS --生成不重复的随机数
PRAGMA AUTONOMOUS_TRANSACTION;
randomchar varchar2(50); --最终返回的不重复的随机数
randomchar1 varchar2(50);
ifrandom number; --循环生成随机数时,判断是否是重复随机数
BEGIN
--判断是否是重复随机数,是则ifrandom>0,否则ifrandom=0
select plateNnumber1||to_char(sysdate,'yymmdd')||lpad(round(dbms_random.value(1,1000)),4,0) into randomchar from dual;
select count(*) into ifrandom from Random_Number t where t.platennumber = plateNnumber1
and t.randomnumber = randomchar
and to_char(ADDDATE,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');
if ifrandom>0 then --重复了,再生成 递归
randomchar := fn_ifrandom(plateNnumber1);
else
insert into Random_Number(RandomNumber,plateNnumber,ADDDATE)values(randomchar,plateNnumber1,sysdate);
commit;
end if;
RETURN randomchar;
END fn_ifrandom;
--------------------------------------------------------------------------------
3、调用函数
select fn_ifrandom(’车牌号码‘) from dual
第二种Oracle生成随机数的方式,可以通过Oracle序列来生成
第一步创建序列
create sequence QC_setting_detail_Seq
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
第二步生成指定位数的序列
select Lpad(QC_setting_detail_Seq.Nextval,10,0) from dual;