Oracle (PLSQL) generates serial number _oracle

Copy Code code as follows:

CREATE OR REPLACE FUNCTION fn_no_make (V_prefix VARCHAR2,
V_table_name VARCHAR2,
V_number_col VARCHAR2)
/*
* V_prefix: Code prefix two bits
* V_table_name: Table name encoded
* V_number_col: Name of the column where the encoding is located
*/
Return VARCHAR2 is
V_old_no VARCHAR2 (50); --Original code
V_new_no VARCHAR2 (50); --New Code
V_old_num number; --Three digit number after original code
V_new_num VARCHAR2 (10); --three digit number after new code
V_date_no VARCHAR2 (10); --Current date number
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 to_char (sysdate, ' YYYYMMDD ') from DUAL ';
EXECUTE IMMEDIATE v_sql into V_date_no;

V_old_num: = To_number (substr (V_old_no, 12, 3));
V_new_num: = Lpad (To_char (v_old_num+1), 3, ' 0 ');

IF V_old_no is NULL OR substr (V_old_no, 3, 8) <> V_date_no THEN
V_new_no: = V_prefix | | V_date_no | | '-' || ' 001 ';
ELSE
V_new_no: = V_prefix | | V_date_no | | '-' || V_new_num;
End IF;

return v_new_no;
EXCEPTION
When others THEN
Dbms_output.put_line (SQLERRM);
End Fn_no_make;