oracle 指定类型和指定位数创建序列号的代码详解

-- Create table

create table LDMAXNO

(

   NOTYPE  VARCHAR2(17) not null ,

   NOLIMIT VARCHAR2(12) not null ,

   MAXNO   INTEGER not null

);

-- Add comments to the table

comment on table LDMAXNO

   is '产生最大的流水号，所有的号码从1开始' ;

-- Add comments to the columns

comment on column LDMAXNO.NOTYPE

   is '含义描述:1、号码类型' ;

comment on column LDMAXNO.NOLIMIT

   is '含义描述:1、号码限制条件' ;

comment on column LDMAXNO.MAXNO

   is '含义描述:1、当前最大值' ;

-- Create/Recreate primary, unique and foreign key constraints

alter table LDMAXNO

   add constraint PK_LDMAXNO primary key (NOTYPE, NOLIMIT);

   create or replace function CreateMaxNos(cNoType  in ldmaxno.notype%type,

                                        cNoLimit in ldmaxno.nolimit%type)

   return integer is

   pragma autonomous_transaction;

   tMaxNo integer := 0; --初始化赋值等于0，定义返回变量

begin

   --最大数加1

   update LDMaxNo

      set MaxNo = MaxNo + 1

    where NoType = cNoType

      and NoLimit = cNoLimit

   Returning MaxNo Into tMaxNo; --取出最大数

   If (Sql%Notfound) then

     --第一次向数据库中插入最大数为 1 的记录

     Insert Into LDMaxNo

       (NOTYPE, NOLIMIT, MAXNO)

     values

       (cNoType, cNoLimit, 1);

     tMaxNo := 1;

   End If;

   commit ;

   return (tMaxNo); --返回结果

end CreateMaxNos;

/

<? xml version = "1.0" encoding = "UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

< mapper namespace = "com.gblfy.business.mapper.DullMapper" >

     < select id = "getMaxNo" resultType = "java.lang.String" >

          select createmaxno(#{cNoType},#{cNoLength}) from dual

     </ select >

</ mapper >

package com.gblfy.business.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import org.apache.ibatis.annotations.Param;

public interface DullMapper extends BaseMapper {

     /**

      * 功能：产生指定长度的流水号，一个号码类型一个流水

      * @param cNoType 流水号的类型

      * @param cNoLength 流水号的长度

      * @return 返回产生的流水号码

      */

     String getMaxNo( @Param ( "cNoType" ) String cNoType, @Param ( "cNoLength" ) int cNoLength);

}

package com.gblfy.business.service;

public interface SysMaxNoService {

     /**

      * 功能：产生指定长度的流水号，一个号码类型一个流水

      *

      * @param cNoType   流水号的类型

      * @param cNoLength 流水号的长度

      * @return 返回产生的流水号码

      */

     String createMaxNo(String cNoType, int cNoLength);

}

package com.gblfy.business.service.impl;

import com.gblfy.business.mapper.DullMapper;

import com.gblfy.business.service.SysMaxNoService;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.stereotype.Service;

import javax.annotation.Resource;

import java.math.BigInteger;

@Service

public class SysMaxNoServiceImpl implements SysMaxNoService {

     private final static Logger logger = LoggerFactory.getLogger(SysMaxNoServiceImpl. class );

     @Resource

     private DullMapper dullMapper;

     /**

      * 功能：产生指定长度的流水号，一个号码类型一个流水

      *

      * @param cNoType   流水号的类型

      * @param cNoLength 流水号的长度

      * @return 返回产生的流水号码

      */

     @Override

     public String createMaxNo(String cNoType, int cNoLength) {

         if ((cNoType == null ) || (cNoType.trim().length() <= 0 ) ||

                 (cNoLength <= 0 )) {

             logger.info( "NoType长度错误 {} NoLength错误" , cNoType, cNoLength);

             return null ;

         }

         cNoType = cNoType.toUpperCase();

         String tReturn = "" ;

         String cNoLimit = "SN" ;

         BigInteger tMaxNo = new BigInteger( "0" );

         tReturn = cNoLimit;

         try {

             String result = dullMapper.getMaxNo(cNoType, cNoLength);

             tMaxNo = new BigInteger(result);

         } catch (Exception e) {

             e.printStackTrace();

             logger.info( "生成流水号出现异常，请核实！" );

         }

         String tStr = tMaxNo.toString();

         //将生成的流水号进行加工处理

         tStr = LCh(tStr, "0" , cNoLength);

         tReturn = tStr.trim();

         return tReturn;

     }

     /**

      * 将生成的流水号进行加工处理

      * <p>

      * 1.判断是否满足指定长度，如果不满足前面用0来补位

      * 2.将生成的流水号进行去空格处理

      * 3.将最终的流水号进行字符串拼接

      * </P>

      *

      * @param sourString

      * @param cChar

      * @param cLen

      * @return

      */

     private String LCh(String sourString, String cChar, int cLen) {

         int tLen = sourString.length();

         int i, iMax;

         String tReturn = "" ;

         if (tLen >= cLen) {

             return sourString;

         }

         //1.判断是否满足指定长度，如果不满足前面用0来补位

         iMax = cLen - tLen;

         for (i = 0 ; i < iMax; i++) {

             tReturn += cChar;

         }

         //2.将生成的流水号进行去空格处理

         //3.将最终的流水号进行字符串拼接

         tReturn = tReturn.trim() + sourString.trim();

         return tReturn;

     }

}

package com.gblfy.business.controller;

import com.gblfy.business.service.SysMaxNoService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

/**

  * 生成指定类型+位数的流水号

  *

  * @Author gblfy

  * @Date 2022-05-16 20:13

  **/

@RestController

public class SysMaxNoController {

     @Autowired

     private SysMaxNoService maxNoService;

     /**

      * 生成指定类型+位数的流水号

      *

      * @param cNoType

      * @param cNoLength

      * @return

      */

     @GetMapping ( "/generate/serial/number" )

     public String generateSerialNumber( @RequestParam (name = "cNoType" , required = false , defaultValue = "cNoType" ) String cNoType,

                                        @RequestParam int cNoLength) {

         return maxNoService.createMaxNo(cNoType, cNoLength);

     }

}