根据格式字符串自动生成单据号(ORACLE版)(二)
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
<>
STAT := 0;
RMOVE := REMOVE;
WHILE (INSTR(RMOVE, ',') > 0) LOOP
RES := SUBSTR(RMOVE, 1, INSTR(RMOVE, ',') - 1);
DBMS_