ORACLE开发：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;
/

2.2. 接口
DullMapper.java

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);

}


2.3. api接口

package com.gblfy.business.service;

public interface SysMaxNoService {

    /**

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

     *

     * @param cNoType   流水号的类型

     * @param cNoLength 流水号的长度

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

     */

    String createMaxNo(String cNoType, int cNoLength);

}

2.4. api实例

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;

    }

}


2.5. 控制层

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);

    }

}


三、测试

3.1. 效果图


到此这篇关于oracle 指定类型和指定位数创建序列号的文章就介绍到这了,更多相关oracle创建序列号内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！