Automatic Generation of document numbers based on format strings (ORACLE Edition)

Automatic Generation of document numbers based on format strings (ORACLE version) This code automatically generates document numbers for strings with specific keywords. Www.2cto.com @ SJ --- the format string (which is the keyword meaning below) "% Y" indicates the full year, for example, 2012 "% y" indicates the short year, for example: 12 "% M" indicates two months. For example, 01 "% m" indicates the abbreviated month. For example, 1 "% D" indicates the day. For example, 08 "% d" indicates the abbreviated day. For example: 8 "% W" or "% w" indicates the week (52 weeks of the year), for example, 05 (fifth week of the year) "% H" indicates the complete hour, for example: 08 "% h" indicates the abbreviation of hour. For example, 8 "% N" indicates the complete minute. For example, 08 "% n" indicates the abbreviation of minute. For example, 8 "% S" indicates the complete second. For example: 08 "% s" indicates the abbreviation of seconds. For example, 8 "% K" indicates the complete millisecond. For example, 008 "% 04X" indicates the serial number www.2cto.com 04 that generates the document number. For example: the sequential number 0005% X indicates that other non-specific keywords of the wildcard can be arbitrarily combined @ TableName --- database table for document number @ ColName ---- is the document number column for the Data Table @ Remove ----- to Remove the automatically generated document number arrays such The format is "1, 2, 3, 4, 5," @ Mantissa ---- to control whether to remove include or match; "1" indicates to include excluded data; "0" indicates removing the matching document number @ Where --- the query condition for the automatic generation of document numbers @ ZD_date --- the document date generated for the document number @ SSN ---- example of the production document number: format String: Complaint Form % Y % M % 06 XAAAsdsA automatically generated document No.: Complaint Form 201206000001 AAAsdsA code 1: Determine whether a numeric string www.2cto.com Code 2: the function create or replace function SN_ISNUMBER (MyStr VARCHAR2) return numberis str VARCHAR2 (400); isnum number; num number; BEGIN -- returns 0, not a NUMBER. 1, which is the number ISNUM: = 0; STR: = TRIM (MyStr); -- if the input value is null, the return value is not a number. If trim (STR) is not null then begin num: = TO_NUMBER (STR); ISNUM: = 1; exception when INVALID_NUMBER then null; when others then null; end if; return isnum; END; create or replace function SN_PROCESSAUTOSN (SJ VARCHAR2, TABLENAME VARCHAR2, COLNAME VARCHAR2, REMOVE VARCHAR2, mantissa int, WHERES VARCHAR2, ZZD_DATE DATE) RETURN VARCHAR2 IS -- automatically generate document number -- SJ Sequential Number Format String -- TableName table name -- ColName column name -- Stored Procedure Parameter ZD_DATE DATE; SN VARCHAR2 (500); YEARS VARCHAR2 (4 ); MINYEAR VARCHAR2 (2); MONTHS VARCHAR2 (2); DAYS VARCHAR2 (2); HOUR VARCHAR2 (2); SSN VARCHAR2 (1000); Q VARCHAR2 (2 ); MINUTE VARCHAR2 (2); WEEK VARCHAR2 (2); SECONDS VARCHAR2 (2); MSECONDS VARCHAR2 (10); SL VARCHAR2 (500); ZERO VARCHAR2 (500 ); XZERO VARCHAR2 (500); xcount int; LSQL VARCHAR2 (4000); LMAX VARCHAR2 (500); lstart int; lend int; llength int; IMAX VARCHAR2 (500); lmax int; mstart int; stat int; RES VARCHAR2 (500); RMOVE VARCHAR2 (5000); BEGIN/* TABLENAME: = 'oi _ CONTRACT_HEADER '; COLNAME: = 'och _ CONTRACT_ID '; -- complaint 1009001 SJ: = '% y % M % 046'; ZD_DATE: = to_date ('2017-01-01 ', 'yyyy-MM-dd '); */IF (ZZD_DATE is null) THEN ZD_DATE: = SYSDATE; ELSE ZD_DATE: = ZZD_DATE; end if; SN: = SJ; YEARS: = TO_CHAR (ZD_DATE, 'yyyy '); MINYEAR: = SUBSTR (YEARS, 3, 2); MONTHS: = TO_CHAR (ZD_DATE, 'mm'); DAYS: = TO_CHAR (ZD_DATE, 'dd'); WEEK: = TO_CHAR (ZD_DATE, 'ww '); HOUR: = TO_CHAR (ZD_DATE, 'hh24'); MINUTE: = TO_CHAR (ZD_DATE, 'mi'); Q: = TO_CHAR (ZD_DATE, 'q'); SECONDS: = TO_CHAR (ZD_DATE, 'ss'); SN: = REPLACE (SN, '% y', YEARS); SN: = REPLACE (SN, '% y', MINYEAR); SN: = REPLACE (SN,' % m', SUBSTR ('00' | TO_CHAR (MONTHS ), LENGTH ('00' | TO_CHAR (MONTHS)-1, 2); SN: = REPLACE (SN, '% d ', SUBSTR ('00' | TO_CHAR (DAYS), LENGTH ('00' | TO_CHAR (DAYS)-1, 2); SN: = REPLACE (SN, '% m', TO_NUMBER (MONTHS); SN: = REPLACE (SN,' % d', TO_NUMBER (DAYS); SN: = REPLACE (SN, '% W', TO_NUMBER (WEEK); SN: = REPLACE (SN,' % W', SUBSTR ('00' | TO_CHAR (WEEK ), LENGTH ('00' | TO_CHAR (WEEK)-1, 2); SN: = REPLACE (SN, '% H ', SUBSTR ('00' | TO_CHAR (HOUR), LENGTH ('00' | TO_CHAR (HOUR)-1, 2); SN: = REPLACE (SN, '% H', TO_NUMBER (HOUR); SN: = REPLACE (SN,' % n', SUBSTR ('00' | TO_CHAR (MINUTE ), LENGTH ('00' | TO_CHAR (MINUTE)-1, 2); SN: = REPLACE (SN, '% n', TO_NUMBER (MINUTE); SN: = REPLACE (SN, '% s', SUBSTR ('00' | TO_CHAR (SECONDS), LENGTH ('00' | TO_CHAR (SECONDS)-1, 2); SN: = REPLACE (SN, '% s', TO_NUMBER (SECONDS); SN: = REPLACE (SN,' % K ', SUBSTR ('000' | TO_CHAR (MSECONDS), LENGTH ('000' | TO_CHAR (MSECONDS)-2, 3); SN: = REPLACE (SN, '% k', TO_NUMBER (MSECONDS); SN: = REPLACE (SN,' % Q', SUBSTR ('00' | TO_CHAR (Q ), LENGTH ('00' | TO_CHAR (Q)-1, 2); SN: = REPLACE (SN, '% Q', TO_NUMBER (q); ZERO: = REGEXP_SUBSTR (SN, '% [0-9] {1, 10} x'); XZERO: = REPLACE (ZERO,' % ', ''); XZERO: = REPLACE (XZERO, 'x', ''); XZERO: = REPLACE (XZERO, 'x',''); IF (zero is null) then begin ssn: = SN; return ssn; end if; select count (T. TABLE_NAME) into xcount from USER_TABLES t where t. TABLE_NAME = TABLENAME; if xcount <1 then begin ssn: = SN; return ssn; END; else begin select count (T. TABLE_NAME) into xcount from USER_TAB_COLUMNS t where t. TABLE_NAME = tablename and t. COLUMN_NAME = COLNAME; if xcount <1 then begin ssn: = SN; return ssn; end if; DBMS_OUTPUT.PUT_LINE (SL); LSTART: = REGEXP_INSTR (SN, '% [0-9] {1, 10} x'); LEND: = LSTART + TO_NUMBER (XZERO); LLENGTH: = LENGTH (SN)-LENGTH (ZERO) + TO_NUMBER (XZERO); LSQL: = 'substr ('| COLNAME |', '| TO_CHAR (LSTART) |', '| TO_CHAR (XZERO) | ')'; LSQL: = 'select max (case when sn_isnumber ('| LSQL | ') = 1 then' | LSQL | 'else' '| ZERO | ''' end) from '| TABLENAME | 'where length (' | COLNAME | ') =' | TO_CHAR (LLENGTH); LSQL: = LSQL | 'and substr (' | COLNAME | ', 1,' | TO_CHAR (LSTART-1) | ') = substr (''' | SN | ''', 1, '| TO_CHAR (LSTART-1) |') '; IF (LLENGTH-LEND)> 0 then lsql: = LSQL | 'and substr (' | COLNAME | ',' | TO_CHAR (LEND) | ',' | TO_CHAR (LLENGTH-LEND) | ') = substr (''' | SN | ''',' | TO_CHAR (LSTART + LENGTH (ZERO) | ', '| TO_CHAR (LLENGTH-LEND) |') '; end if; if wheres is not null then begin lsql: = LSQL |' and '| WHERES; END; end if; execute immediate lsql into imax; if imax is null or imax = zero then imax: = '0'; end if; MSTART: = TO_NUMBER (IMAX) + 1; IF (remove is not null) then begin rmove: = REMOVE; IF (mantissa is not null and mantissa = 0) then begin <A> STAT: = 0; RMOVE: = REMOVE; WHILE (INSTR (RMOVE, ',')> 0) loop res: = SUBSTR (RMOVE, 1, INSTR (RMOVE, ',')-1 ); DBMS_OUTPUT.PUT_LINE (RES); if instr (TO_CHAR (MSTART), TO_CHAR (RES)> 0 then stat: = 1; end if; RMOVE: = SUBSTR (RMOVE, INSTR (RMOVE, ',') + 1, LENGTH (RMOVE)-INSTR (RMOVE, ','); end loop; if stat = 1 then begin mstart: = TO_NUMBER (MSTART) + 1; goto a; end if; MSTART: = TO_NUMBER (MSTART)-1; END; else begin while (INSTR (REMOVE, TO_CHAR (MSTART)> 0) loop mstart: = MSTART + 1; -- dbms_output.put_line (MSTART | 'A'); end loop; end if; SSN: = LPAD (TO_CHAR (MSTART), XZERO, '0'); SN: = REPLACE (SN, ZERO, SUBSTR (SSN, LENGTH (SSN)-XZERO, XZERO )); SSN: = SN; return ssn; END;