根据格式字符串自动生成单据号(ORCALE版)

本代码为通过特定关键字的字符串自动生成单据号；解决编程中频繁编写代码自动生成单据号。

• @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;