在Oracle写一个Function 供前台作调用
1.用0-9,A-Z ,其中I,O,X 不能使用才产生流水号 ,如001,002...00A,00B...ZZZ
2.第一次产生流水码是前面加CE ,如:CE001...CEZZZ,当后三位流水码产生到ZZZ时,换用CO ,如:CO001...COZZZ,然后前两位再使用CE ,依次循环本人新学oracle ,求解~~,谢谢各位高手帮忙~~
1.用0-9,A-Z ,其中I,O,X 不能使用才产生流水号 ,如001,002...00A,00B...ZZZ
2.第一次产生流水码是前面加CE ,如:CE001...CEZZZ,当后三位流水码产生到ZZZ时,换用CO ,如:CO001...COZZZ,然后前两位再使用CE ,依次循环本人新学oracle ,求解~~,谢谢各位高手帮忙~~
序列的产生应基于一个sequence,这样产生的序列不会出现重复....
简单写了一个如下:--创建一个SEQUENCE,获取序列,在此基础上进行加工
create sequence SEQ_TEXT
minvalue 0
maxvalue 10000000
start with 0
increment by 1
cache 20
order;CREATE OR REPLACE FUNCTION getSequence
RETURN VARCHAR2
AS
N_SHANG NUMBER(10);
N_YUSHU NUMBER(10);
HEXRES VARCHAR2(10);
V_FLAG VARCHAR2(4);
BEGIN
--使用序列获取序列值
N_SHANG :=SEQ_TEXT.NEXTVAL;
--获取生成序列的前缀,奇数次循环为CE,偶数次循环为CO
SELECT DECODE(MOD(TRUNC(N_SHANG/(35*POWER(36,0)+35*POWER(36,1) + 35*POWER(36,2)+1)),2),1,'CO','CE')
INTO V_FLAG
FROM DUAL;
--对ZZZ取模,方便转换
N_SHANG := N_SHANG MOD (35*POWER(36,0)+35*POWER(36,1) + 35*POWER(36,2)+1);
--特殊情况返回000
IF N_SHANG = 0 THEN
HEXRES := '000';
END IF;
--对36进制处理
WHILE N_SHANG>0 LOOP
N_YUSHU:=N_SHANG MOD 36;
SELECT CONCAT(CASE WHEN N_YUSHU >9 THEN CHR(N_YUSHU+55) ELSE TO_CHAR(N_YUSHU) END,HEXRES)
INTO HEXRES
FROM DUAL;
N_SHANG := TRUNC(N_SHANG/36);
END LOOP;
--返回结果
RETURN CONCAT(V_FLAG,LPAD(HEXRES,3,0));
END;
前台调用方式:13:23:46 ChenZw> select getSequence() as seq from dual;SEQ
-------------------------------------------------------------
CE9XI
Create or Replace Function F_33(i_num in int)
Return varchar2 AS
v_num int;
v_str varchar2(10):='';
type Table_type is Table Of char Index By Binary_Integer;
t_s Table_type;
begin
for i in 0..9 loop
t_s(i):=to_char(i);
end loop;
for i in 10..17 loop
t_s(i):=chr(i+55);
end loop;
for i in 18..22 loop
t_s(i):=chr(i+56);
end loop;
for i in 23..30 loop
t_s(i):=chr(i+57);
end loop;
for i in 31..32 loop
t_s(i):=chr(i+58);
end loop;
v_num:=i_num;
while v_num>32 loop
v_str:=t_s(mod(v_num,33))||v_str;
v_num:=trunc(v_num/33);
end loop;
return (substr('00'||t_s(v_num)||v_str,-3));
end f_33;
jz int := 33;
v_ret varchar2(10); v_prefix varchar2(2);
v1 varchar2(1);
v2 varchar2(1);
v3 varchar2(1);
begin select decode(mod(floor(i_dec / jz / jz / jz), 2), 0, 'CE', 1, 'CO')
into v_prefix
from dual; v1 := Substr(charbase, mod(floor(i_dec / jz / jz), jz) + 1, 1);
v2 := Substr(charbase, mod(floor(i_dec / jz), jz) + 1, 1);
v3 := Substr(charbase, mod(i_dec, jz) + 1, 1);
v_ret := v_prefix || v1 || v2 || v3;
return v_ret;end;
CREATE OR REPLACE FUNCTION TRANSSCALETOINT(PRM_SCALE NUMBER, --进制
V_VALUE VARCHAR2) --传入值
RETURN NUMBER
AS
N_NUM NUMBER(20);
BEGIN
N_NUM :=0;
FOR I IN (SELECT CASE
WHEN REGEXP_LIKE(SUBSTR(V_VALUE, LEVEL, 1), '[[:digit:]]') THEN
--挑出是0-9数字的
TO_NUMBER(SUBSTR(V_VALUE, LEVEL, 1))
ELSE
--转换A-Z,没有对取值范围做校验!
ASCII(UPPER(SUBSTR(V_VALUE, LEVEL, 1))) - 55
END AS NUM,
--所在位
LENGTH(V_VALUE) - LEVEL AS POW
FROM DUAL
CONNECT BY LEVEL <= LENGTH(V_VALUE))
LOOP
--乘以权重
N_NUM:=N_NUM + I.NUM*POWER(PRM_SCALE,I.POW);
END LOOP;
RETURN N_NUM;
END;
633
ABCDEFJHIJ
0123456789
按正常来说,J是19,但是是所以是33进制,因为我有几个字母是未用的,比如:I,O,X 而您的程式验证下来这餐个字母是在其中的呢
CREATE OR REPLACE FUNCTION TRANSSCALETOINT(PRM_SCALE NUMBER, V_VALUE VARCHAR2)
RETURN NUMBER
AS
CONSSTR CONSTANT VARCHAR2(40) :='0123456789ABCDEFGHJKLMNPQRSTUVWYZ';
N_NUM NUMBER(20);
BEGIN
N_NUM :=0;
FOR I IN (SELECT CASE
WHEN REGEXP_LIKE(SUBSTR(V_VALUE, LEVEL, 1), '[[:digit:]]') THEN
TO_NUMBER(SUBSTR(V_VALUE, LEVEL, 1))
ELSE
INSTR(CONSSTR,SUBSTR(V_VALUE, LEVEL, 1))-1
END AS NUM,
LENGTH(V_VALUE) - LEVEL AS POW
FROM DUAL
CONNECT BY LEVEL <= LENGTH(V_VALUE))
LOOP
N_NUM:=N_NUM + I.NUM*POWER(PRM_SCALE,I.POW);
END LOOP;
RETURN N_NUM;
END;SELECT TRANSSCALETOINT(33,'0J6') FROM DUAL;TRANSSCALETOINT(33,'0J6')
600