可以写一个函数实现,参数为序列的名称 FUNCTION GetSeqValue(P_SeqName in varchar2) RETURN number IS V_RESULT NUMBER; BEGIN LOOP EXECUTE IMMEDIATE 'SELECT '||TRIM(P_SeqName)||'.NEXTVAL FROM DUAL' INTO V_RESULT; EXIT WHEN INSTR(TO_CHAR(V_RESULT) ,'4')<=0; END IF; RETURN V_RESULT; END;
换了个方法,这个应该不存在效率问题了 FUNCTION GetSeqValue(P_SeqName in varchar2) RETURN number IS V_TEMP NUMBER; V_RESULT varchar2(400); BEGIN EXECUTE IMMEDIATE 'SELECT '||TRIM(P_SeqName)||'.NEXTVAL FROM DUAL' INTO V_TEMP; LOOP EXIT WHEN V_TEMP=0; SELECT (CASE WHEN MOD(V_TEMP,9)<4 THEN MOD(V_TEMP,9) ELSE MOD(V_TEMP,9)+1 END)||V_RESULT INTO V_RESULT FROM DUAL; V_TEMP:=TRUNC(V_TEMP/9); END LOOP; RETURN to_number(V_RESULT); END;
FUNCTION GetSeqValue(P_SeqName in varchar2) RETURN number IS
V_RESULT NUMBER;
BEGIN
LOOP
EXECUTE IMMEDIATE 'SELECT '||TRIM(P_SeqName)||'.NEXTVAL FROM DUAL' INTO V_RESULT;
EXIT WHEN INSTR(TO_CHAR(V_RESULT) ,'4')<=0;
END IF;
RETURN V_RESULT;
END;
FUNCTION GetSeqValue(P_SeqName in varchar2) RETURN number IS
V_TEMP NUMBER;
V_RESULT varchar2(400);
BEGIN
EXECUTE IMMEDIATE 'SELECT '||TRIM(P_SeqName)||'.NEXTVAL FROM DUAL' INTO V_TEMP;
LOOP
EXIT WHEN V_TEMP=0;
SELECT (CASE WHEN MOD(V_TEMP,9)<4 THEN MOD(V_TEMP,9) ELSE MOD(V_TEMP,9)+1 END)||V_RESULT INTO V_RESULT FROM DUAL;
V_TEMP:=TRUNC(V_TEMP/9);
END LOOP;
RETURN to_number(V_RESULT);
END;