CREATE OR REPLACE FUNCTION fun_id RETURN VARCHAR2 AS v_id VARCHAR2(2); BEGIN SELECT MAX(id) INTO v_id FROM tt; IF (v_id='99') THEN return 'A0'; ELSIF (substr(v_id, 2, 1) = '9') THEN v_id := chr(ascii(substr(v_id, 1, 1)) + 1) || 0 ; RETURN v_id ; ELSE v_id:=substr(v_id,1,1)||(substr(v_id,2,1)+1); RETURN v_id ; END IF ; EXCEPTION WHEN OTHERS THEN RETURN('ee'); END;写了个函数帮助实现了这个功能 应该能满足你的需要
十进制-->十六进制
select to_char(254,'XX') from dual;
十六进制-->十进制
select to_number('FF','XX') from dual;
SELECT 'A3' FROM dual )
SELECT ltrim(DECODE(SUBSTR(MAX(TO_NUMBER(DECODE(SUBSTR(LPAD(A, 3, 0), 1, 2),'0A',10,'0B','11','00','00','01','01') || SUBSTR(LPAD(A, 3, 0), 3, 1)))+1,1,2),
'10','0A','11','0B','00','00','01','01') || SUBSTR(MAX(TO_NUMBER(DECODE(SUBSTR(LPAD(A, 3, 0), 1, 2),'0A',10,'0B','11','00','00','01','01') || SUBSTR(LPAD(A, 3, 0), 3, 1)))+1,3,1),'0')
FROM TEST
没想到什么好办法,这个算是实现了你的功能,你自己吧decode部分补充完整吧
select decode((substr('A0', 2, 1) + 1),
10,
chr(ascii(substr('A0', 1, 1)) + 1),
substr('A0', 1, 1)) ||
decode((substr('A0', 2, 1) + 1), 10, 0, substr('A0', 2, 1) + 1)
from dual;
select decode((substr('A9', 2, 1) + 1),
10,
chr(ascii(substr('A9', 1, 1)) + 1),
substr('A9', 1, 1)) ||
decode((substr('A9', 2, 1) + 1), 10, 0, substr('A9', 2, 1) + 1)
from dual;
CREATE OR REPLACE FUNCTION fun_id RETURN VARCHAR2 AS
v_id VARCHAR2(2);
BEGIN
SELECT MAX(id) INTO v_id FROM tt;
IF (v_id='99') THEN
return 'A0';
ELSIF (substr(v_id, 2, 1) = '9') THEN
v_id := chr(ascii(substr(v_id, 1, 1)) + 1) || 0 ;
RETURN v_id ;
ELSE
v_id:=substr(v_id,1,1)||(substr(v_id,2,1)+1);
RETURN v_id ;
END IF ;
EXCEPTION WHEN OTHERS
THEN
RETURN('ee');
END;写了个函数帮助实现了这个功能
应该能满足你的需要