Oracle生成流水号函数(DJJT12090600003):
例如当前最新的流水号为:DJJT12090600003,下一流水号:如果是当天生成的为:DJJT12090600004,如果是隔天生成的为:DJJT12090700001
CREATE OR REPLACE FUNCTION fn_no_make(v_type VARCHAR2, v_number_col VARCHAR2, v_table_name VARCHAR2) /* * 编码示例:DJJT12090600003 * author: Rock.et * create date: 2012/09/06 * 参数说明: * v_type: 编码前缀 * v_number_col:编码所在列名 * v_table_name:编码所在表名 */ RETURN VARCHAR2 IS v_old_no VARCHAR2(50); --原编码 v_old_num NUMBER; -- 原编码后五位编号 v_new_num VARCHAR2(10); --新编码后五位编号 v_maked_no VARCHAR2(50); --新编码 v_date_no VARCHAR2(20); --当前日期编号 v_sql VARCHAR2(4000); BEGIN v_sql := 'SELECT MAX(' || v_number_col || ') FROM ' || v_table_name; EXECUTE IMMEDIATE v_sql INTO v_old_no; v_sql := 'SELECT SUBSTR(TO_CHAR(SYSDATE,''YYMMDD''), 1, 6) AS DATE_NO FROM DUAL'; EXECUTE IMMEDIATE v_sql INTO v_date_no; v_old_num := to_number(substr(v_old_no, 11, 5)); v_new_num := to_char(v_old_num + 1); WHILE length(v_new_num) < 5 LOOP v_new_num := '0' || v_new_num; END LOOP; IF v_old_no IS NULL OR substr(v_old_no, 5, 6) <> v_date_no THEN v_maked_no := v_type || v_date_no || '00001'; ELSE v_maked_no := v_type || v_date_no || v_new_num; END IF; RETURN(v_maked_no); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END fn_no_make;