Oracle自增主键的函数

CREATE OR REPLACE FUNCTION GET_CHAR_ID(I_TABLE_NAME IN VARCHAR2, I_COLUMN_NAME IN VARCHAR2, I_LENGTH IN NUMBER) RETURN VARCHAR2 IS V_SQL VARCHAR2(1000) := ''; V_NEW_ID VARCHAR2(50) := ''; TYPE REF_CUR IS REF CURSOR; --公共游标 V_CUR REF_CUR; BEGIN V_SQL := 'SELECT NVL(MAX(TO_NUMBER(' || I_COLUMN_NAME || '))+1,1) AS NEW_ID FROM ' || I_TABLE_NAME; OPEN V_CUR FOR V_SQL; LOOP FETCH V_CUR INTO V_NEW_ID; EXIT WHEN V_CUR%NOTFOUND; END LOOP; RETURN NUMTOSTRING(V_NEW_ID, I_LENGTH); END; 

 

create or replace function NumToString(v_num in number, v_length in number) return varchar2 is/** * 作用：将数字转换成指定长度的字符串,不足位用零补齐 * 作者：** * 日期：2010-05-21 */ Result varchar2(20);begin Result := to_char(v_num); while length(Result) < v_length loop Result := '0'||Result; end loop; return(Result);end NumToString;