本代码为通过特定关键字的字符串自动生成单据号;解决编程中频繁编写代码自动生成单据号。
- @SJ ---为格式字符串(以下是关键字含义)
- "%Y"表示完整年 如:2012
- "%y"表示简写年 如:12
- "%M"表示两位月份 如:01
- "%m"表示简写月份 如:1
- "%D"表示天 如:08
- "%d"表示简写天 如:8
- "%W" 或者 "%w"表示周(全年52周) 如:05(全年第五周)
- "%H"表示完整小时 如:08
- "%h"表示简写小时 如:8
- "%N"表示完整分钟 如:08
- "%n"表示简写分钟 如:8
- "%S"表示完整秒 如:08
- "%s"表示简写秒 如:8
- "%K"表示完整毫秒 如:008
- "%04X"表示生成单据号的流水号
- 04表示4为数字 如:流水号0005
- % X 表示通配符
- 其他非特定关键字可以任意组合
- @TableName ---为单据号的数据库表
- @ColName ----为数据表的单据号列
- @Remove -----为剔除自动生成单据号的数组 如:格式为"1,2,3,4,5,"
- @Mantissa ----为控制是否剔除包含还是匹配;"1"表示包含剔除的数据;"0"表示剔除完全匹配的单据号
- @Where ---为自动生成单据号的查询条件
- @ZD_date ---为单据号生成的单据日期
- @SSN ----为生产的单据号
例子:
格式字符串:投诉单%Y%M%06XAAAsdsA
自动生成的单据号:投诉单201206000001AAAsdsA
代码1:判断是否数值型字符串
代码2:自动生成序列号的函数
CREATE OR REPLACE FUNCTION SN_ISNUMBER(MyStr VARCHAR2) RETURN NUMBER IS STR VARCHAR2(400); ISNUM NUMBER; NUM NUMBER; BEGIN --返回0,不是数字。1,是数字 ISNUM:=0; STR:=TRIM(MyStr); --如果输入值为空,就返回不是数字。 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; 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 --自动生成单据号 --SJ 流水号格式字符串 --TableName 表名 --ColName 列名 --存储过程参数 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'; --投诉单1009001 SJ := '%y%M%046'; ZD_DATE:=to_date('2009-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; 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; END IF; END; 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; 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; END IF; END; END IF; SSN := LPAD(TO_CHAR(MSTART), XZERO, '0'); SN := REPLACE(SN, ZERO, SUBSTR(SSN, LENGTH(SSN) - XZERO, XZERO)); SSN := SN; RETURN SSN; END;