求一个Function, 用 0~9,A~F 这几个数字及字母,产生流水号, 求各位帮助 ,产生的数据如下 格式:
00000,00001,00002,00003,00004,00005,00006,00007,00008,00009,0000A,0000B,0000C,0000D,0000E,0000F,00010....
00000,00001,00002,00003,00004,00005,00006,00007,00008,00009,0000A,0000B,0000C,0000D,0000E,0000F,00010....
下边是一个算法,你如果想建函数的话,变一下就可以
declare
v_number number;
v_length number;
v_char varchar2(10);
begin
select mw_app.mwq_upc_struct.nextval into v_number from dual;
select to_char(v_number, 'xxxxx') into v_char from dual;
v_char := trim(v_char);
v_length := length(v_char);
v_length := 5 - v_length;
for i in 1 .. v_length loop
v_char := '0' || v_char;
end loop;
dbms_output.put_line('五位的十六进制数为:' || v_char);
end;
1、创建SEQ,产生十进制的序列号:CREATE SEQUENCE SEQ_HEX
MINVALUE 0
MAXVALUE 10000000
START WITH 0
INCREMENT BY 1
CACHE 20
CYCLE
ORDER;2、写一个函数,返回0-9 A-F的结果:CREATE OR REPLACE FUNCTION getHexSequence
RETURN VARCHAR2
AS
N_SHANG NUMBER(10);
N_YUSHU NUMBER(10);
HEXRES VARCHAR2(10);
BEGIN
N_SHANG :=seq_hex.NEXTVAL;
IF N_SHANG = 0 THEN
RETURN '00000';
END IF;
WHILE N_SHANG>0 LOOP
N_YUSHU:=N_SHANG MOD 16;
SELECT CONCAT(DECODE(N_YUSHU,'10','A','11','B','12','C','13','D','14','E','15','F',N_YUSHU),HEXRES) INTO HEXRES FROM DUAL;
N_SHANG := TRUNC(N_SHANG/16);
END LOOP;
RETURN LPAD(HEXRES,5,0);
END;3、程序中的调用:SELECT getHexSequence FROM dual;4、产生的结果如下:SQL> SELECT getHexSequence FROM dual;GETHEXSEQUENCE
----------------------------------------
000ADSQL>