本代码为通过特定关键字的字符串自动生成单据号;解决编程中频繁编写代码自动生成单据号。
- @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;