SQL> create sequence sq_1 minvalue 1 maxvalue 99999999 start with 1;Sequence createdSQL> select substr('AB00000000',1,10-length(sq_1.nextval)) || sq_1.nextval STR from dual;STR -------------------------------------------------- AB00000001SQL> select substr('AB00000000',1,10-length(sq_1.nextval)) || sq_1.nextval STR from dual;STR -------------------------------------------------- AB00000002SQL>
lpad length 用这两个函数
CREATE OR REPLACE FUNCTION ABCDE RETURN VARCHAR2 IS SN NUMBER(8); STR VARCHAR2(20); BEGIN SELECT SEQ.NEXTVAL INTO SN FROM DUAL; STR := 'AB' || RPAD(SN,8,'0'); RETURN STR; END;
--------------------------------------------------
AB00000001SQL> select substr('AB00000000',1,10-length(sq_1.nextval)) || sq_1.nextval STR from dual;STR
--------------------------------------------------
AB00000002SQL>
length
用这两个函数
RETURN VARCHAR2
IS
SN NUMBER(8);
STR VARCHAR2(20);
BEGIN
SELECT SEQ.NEXTVAL INTO SN FROM DUAL;
STR := 'AB' || RPAD(SN,8,'0');
RETURN STR;
END;