The sequential number function generated by Oracle (DJJT12090600003)

For example, if the latest sequential number is DJJT12090600003 and the next sequential number is: DJJT12090600003 generated on the current day, if the sequential number is DJJT12090600004, if it is generated on the next day: DJJT12090700001 www.2cto.com create or replace function fn_no_make (v_type VARCHAR2, v_number_col VARCHAR2, v_table_name VARCHAR2)/** encoding example: DJJT12090600003 * author: Rock. et * create date: 2012/09/06 * parameter description: * v_type: encoding prefix * v_number_col: name of the encoding column * v_table_name: name of the encoding table */www.2cto.com
RETURN VARCHAR2 IS v_old_no VARCHAR2 (50); -- original encoding v_old_num NUMBER; -- the last five digits of the original encoding v_new_num VARCHAR2 (10 ); -- the last five digits of the new encoding: v_maked_no VARCHAR2 (50); -- the new encoding: v_date_no VARCHAR2 (20); -- current date: 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; www.2cto.com 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 | '000000'; 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;