CREATE SEQUENCE seq_2; CREATE OR REPLACE FUNCTION GET_STR RETURN VARCHAR2 IS C_R VARCHAR2(9); I NUMBER; BEGIN SELECT SEQ_2.NEXTVAL INTO I FROM DUAL; IF (MOD(I, 1000) = 0) THEN SELECT SEQ_2.NEXTVAL INTO I FROM DUAL; END IF; C_R := 'D00' || LPAD(I, 6, '0'); RETURN(C_R); END GET_STR; /SELECT GET_STR() FROM dual;
楼主需求变了,原来是999后直接变成1001。 老需求用上面的。 如果是新的需求,用下面的。 CREATE SEQUENCE seq_2; CREATE OR REPLACE FUNCTION GET_STR RETURN VARCHAR2 IS C_R VARCHAR2(9); I NUMBER; BEGIN SELECT SEQ_2.NEXTVAL INTO I FROM DUAL; C_R := 'D00' || LPAD(I, 6, '0'); RETURN(C_R); END GET_STR; /SELECT GET_STR() FROM dual;
CREATE SEQUENCE seq_2;
CREATE OR REPLACE FUNCTION GET_STR RETURN VARCHAR2 IS
C_R VARCHAR2(9);
I NUMBER;
BEGIN
SELECT SEQ_2.NEXTVAL INTO I FROM DUAL;
IF (MOD(I, 1000) = 0) THEN
SELECT SEQ_2.NEXTVAL INTO I FROM DUAL;
END IF;
C_R := 'D00' || LPAD(I, 6, '0');
RETURN(C_R);
END GET_STR;
/SELECT GET_STR() FROM dual;
楼主需求变了,原来是999后直接变成1001。
老需求用上面的。
如果是新的需求,用下面的。
CREATE SEQUENCE seq_2;
CREATE OR REPLACE FUNCTION GET_STR RETURN VARCHAR2 IS
C_R VARCHAR2(9);
I NUMBER;
BEGIN
SELECT SEQ_2.NEXTVAL INTO I FROM DUAL;
C_R := 'D00' || LPAD(I, 6, '0');
RETURN(C_R);
END GET_STR;
/SELECT GET_STR() FROM dual;