Oracle procedure递归方法生成有规律唯一不重复且连续的流水号(处理字母数字)

最新推荐文章于 2023-12-16 15:28:17 发布
最新推荐文章于 2023-12-16 15:28:17 发布
阅读量2.8k 收藏 1
点赞数 3
分类专栏： DB 文章标签： 数据库 sql oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/huahua000111/article/details/72622095
版权

由于业务需求,产品编号某些位数是字母,字母也是需要连续的，前2位固定,，不能出现"E","I","O"。比如客户指定产品编号类似是“0A00A0000A00” 字母位数是字母，不够进一，"A"--->"B" ，而数字进一就简单。

这里有两种方法。

1：拆分位数。

由于前2位固定。则可以将   0A00A0000A00拆分成：“00”,"A","0000","A","00" 5个单独的流水号。

然后规则是将排在最后的“00” +1，那么就是"01" ，不需要进一，则再和前面的拼凑起来，最后就是“0A00A0000A01”. 并插入到另一个数据库。

数据库HANDLE1_SERIAL_NO_Generate 是触发数据库，而拿生成好的数据是在另一个数据库HANDLE1_SERIAL_NO(表结构一样)

表结构：

-- Create table
create table HANDLE1_SERIAL_NO
(
  SERIAL_NO    VARCHAR2(20) not null,
  MACHINE_NO       VARCHAR2(20) not null,
  TIME    DATE not null,
  SERIAL_COUNT NUMBER--需要生成多少个流水号
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table HANDLE1_SERIAL_NO_PROD
  add constraint PK_HANDLE1_SERIAL_NO primary key (SERIAL_NO)


以下是存储过程代码

Create Or Replace Trigger TRG_HANDLE1_SERIAL_NO_GENERATE
  Before Insert Or Update On HANDLE1_SERIAL_NO_GENERATE For Each Row
Declare
  maxSerialNO   varchar2(20);
  currSerialNO  varchar2(20);
  SN            number;
  serialCount   number;
  serialNoChar1 varchar2(5);
  serialNoChar2 varchar2(5);
  serialNoChar3 varchar2(5);
  serialNoChar4 varchar2(5);
  serialNoChar5 varchar2(5);
Begin
  if inserting then
    if(:new.serial_no is null or length(:new.serial_no) < 12) then
      RAISE_APPLICATION_ERROR(-20998,'ERROR: the serial_no is illegal!');
    end if;

    serialCount:=0;
    while serialCount<:new.serial_count loop
      --if upper(substr(:new.serial_no,3,10)) = '00A0000A00' then
        select max(serial_no) into maxSerialNO from HANDLE1_SERIAL_NO;
        if maxSerialNO is null then
           currSerialNO := :new.serial_no;
        else
          serialNoChar1:=substr(maxSerialNO,3,2);
          serialNoChar2:=substr(maxSerialNO,5,1);
          serialNoChar3:=substr(maxSerialNO,6,4);
          serialNoChar4:=substr(maxSerialNO,10,1);
          serialNoChar5:=substr(maxSerialNO,11,2);
          if to_number(serialNoChar5)<99 then
            SN:=to_number(serialNoChar5)+1;
            serialNoChar5 := lpad(to_char(SN),2,'0');
          else
            serialNoChar5:='00';
            if ascii(serialNoChar4)+1 >ascii('Z') then
               serialNoChar4:='A';
               if to_number(serialNoChar3) +1>9999 then
                 serialNoChar3 := '0000';
                 if ascii(serialNoChar2)+1 >ascii('Z') then
                   serialNoChar2:='A';
                   serialNoChar1:=lpad(to_char(to_number(serialNoChar1)+1),2,'0');
                 else
                   if (ascii(serialNoChar2)+1 =ascii('E') or ascii(serialNoChar2)+1 =ascii('I') or ascii(serialNoChar2)+1 =ascii('O')) then
                     serialNoChar2:=chr(ascii(serialNoChar2)+2);
                   else
                     serialNoChar2:=chr(ascii(serialNoChar2)+1);
                   end if;
                 end if;
               else
                 serialNoChar3 := lpad(to_char(to_number(serialNoChar3) +1),4,'0');
               end if;
            else
              if (ascii(serialNoChar4)+1 =ascii('E') or ascii(serialNoChar4)+1 =ascii('I') or ascii(serialNoChar4)+1 =ascii('O')) then
                serialNoChar4 :=chr(ascii(serialNoChar4)+2);
              else
                serialNoChar4 :=chr(ascii(serialNoChar4)+1);
              end if;
            end if;
          end if;
         currSerialNO := substr(maxSerialNO,1,2)|| serialNoChar1||serialNoChar2||serialNoChar3||serialNoChar4||serialNoChar5;
        end if;
        --RAISE_APPLICATION_ERROR(-20998,currSerialNO);
        Insert into HANDLE1_SERIAL_NO(serial_no,tester,test_time) values(currSerialNO,:new.tester,:new.test_time);
        :new.serial_no := currSerialNO;
        serialCount:=serialCount+1;
    end loop;
  end if;
  
  exception
     when others then
       RAISE_APPLICATION_ERROR(-20998,'ERROR:Insert serial no failed in DB!');
End;

2：递归方法

我发现第一种方法虽然能够达到客户需求，但是不利于以后的扩展，并且整个存储过程比较臃肿。于是就是改进该方法，使用递归函数。

目前是前2位固定，但是这种方法的流水号的长度是可变的，只是前2为固定，并且字母所在的位也可以随意放置，比起第一种，以后如果流水号格式改变成”0A0000000000AAA“,第一种方法需要大变动，而这种方法可以继续使用(前2为固定"0A").

当然读者可以把前2位固定的可以变成参数传入，修改下函数，这样就更加灵活了。

触发器：

Create Or Replace Trigger HANDLE1_SERIAL_NO_Generate
  Before Insert Or Update On HANDLE1_SERIAL_NO_Generate For Each Row

declare
maxSerialNo varchar2(20);
SerialNoType varchar2(10);
currSerialNO varchar2(20);

begin
  /* serial no length can not less than 3 */
  if length(:new.serial_no)<4 then
    RAISE_APPLICATION_ERROR(-20999,'ERROR:serial no length is less than 3!');
  end if;
  
  if :new.serial_count>100 then 
    RAISE_APPLICATION_ERROR(-30001,'ERROR:!serial_count');
  end if;

  if inserting then
    /* serial_no:0A00A0000A00,fixed top 2 */
   for i in 1..:new.serial_count loop
     SerialNoType:=substr(:new.serial_no,1,2);
     select max(serial_no) into maxSerialNo from HANDLE1_SERIAL_NO where serial_no like SerialNoType || '%' and length(serial_no)=length(:new.serial_no);
   if maxSerialNO is null then
     currSerialNO:= :new.serial_no;
   else
     currSerialNO:=fun_GetHandleSerialNO(maxSerialNo);
     if instr(currSerialNO,'not enough')>0 then
       RAISE_APPLICATION_ERROR(-20302,'ERROR:SerialNo is not enough!');
     end if;
     if instr(currSerialNO,'error')>0 then
       RAISE_APPLICATION_ERROR(-20303,'error get serialno function!');
     end if;
    end if;

    Insert into HANDLE1_SERIAL_NO(serial_no,tester,test_time) values(currSerialNO,:new.tester,:new.test_time);
    :new.serial_no:= currSerialNO;
    end loop;
   end if;

  exception
     when others then
       RAISE_APPLICATION_ERROR(-20998,'ERROR:Insert serial no failed in DB!');
end;



递归函数： 

create or replace function fun_GetHandleSerialNO(SerialNo varchar2) return varchar2 is

SingleChar varchar2(5);
SingleCharacter varchar2(5);
SingleNum number;
NewSerialNo varchar2(20);

begin

  if substr(SerialNo,3,1)='Z' then
    return 'error digit is not enough';
  end if;

  SingleChar:=substr(SerialNo,length(SerialNo),1);
  /*ascii('0') = 48, ascii('9') = 57
    ascii('A') = 65, ascii('Z') = 90 */

  /*Number Type*/
  if ascii(SingleChar)>=48 and ascii(SingleChar)<=57 then
    SingleNum:=to_number(SingleChar)+1;
    if SingleNum>9 then
      NewSerialNo:=fun_GetHandleSerialNO(substr(SerialNo,1,length(SerialNo)-1));
      return NewSerialNo || '0';
    else
     return substr(SerialNo,1,length(SerialNo)-1) || SingleNum;
    end if;   
  /* Character Type */
  else 
    if ascii(SingleChar)>=65 and ascii(SingleChar)<=90 then
      SingleCharacter:=chr(ascii(SingleChar)+1);
      /* filtered character 'E','I','O' */
      if (SingleCharacter ='E' or SingleCharacter ='I' or SingleCharacter ='O') then
         SingleCharacter:=chr(ascii(SingleChar)+2);
      end if;
      if SingleCharacter>'Z' then
        NewSerialNo:=fun_GetHandleSerialNO(substr(SerialNo,1,length(SerialNo)-1));
        return NewSerialNo || 'A';
      else
       return substr(SerialNo,1,length(SerialNo)-1) || SingleCharacter;
      end if;
    end if;   
  end if;
EXCEPTION
  WHEN others THEN
    return 'error get serialno function';
end;

使用方法： 

insert into handle1_serial_no_generate
  (serial_no, tester, test_time, serial_count)
values
  ('0A00A0000A00', 'test1', sysdate, 10);


结果：

handle_serial_no_generate:

0A00AAAAA0009 test1 2017/5/22 11:39:2810


handle_serial_no:

0A00AAAAA0000 test1 2017/5/22 11:39:28
0A00AAAAA0001 test1 2017/5/22 11:39:28
0A00AAAAA0002 test1 2017/5/22 11:39:28
0A00AAAAA0003 test1 2017/5/22 11:39:28
0A00AAAAA0004 test1 2017/5/22 11:39:28
0A00AAAAA0005 test1 2017/5/22 11:39:28
0A00AAAAA0006 test1 2017/5/22 11:39:28
0A00AAAAA0007 test1 2017/5/22 11:39:28
0A00AAAAA0008 test1 2017/5/22 11:39:28
0A00AAAAA0009 test1 2017/5/22 11:39:28


当遇到"E","I"."O"自动进一,长度可变。

handle_serial_no_generate:

0A00AAAAA0000AAL test1 2017/5/22 11:48:0310


handle_serial_no:

0A00AAAAA0000AAA test1 2017/5/22 11:48:03

0A00AAAAA0000AAB test1 2017/5/22 11:48:03
0A00AAAAA0000AAC test1 2017/5/22 11:48:03
0A00AAAAA0000AAD test1 2017/5/22 11:48:03
0A00AAAAA0000AAF test1 2017/5/22 11:48:03
0A00AAAAA0000AAG test1 2017/5/22 11:48:03
0A00AAAAA0000AAH test1 2017/5/22 11:48:03
0A00AAAAA0000AAJ test1 2017/5/22 11:48:03
0A00AAAAA0000AAK test1 2017/5/22 11:48:03
0A00AAAAA0000AAL test1 2017/5/22 11:48:03


Nero_JUGG
关注
  • 3
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用 Kotlin 写一段函数式的递归遍历树结构，处理节点的代码
禅与计算机程序设计艺术
10-23 8702
fun visitNavigation(navData: List<NavigationNodeVO>?, visitor: (NavigationNodeVO) -> Unit) { navData?.map { visitor(it) val children = it.children visitNavigation(...
Oracle递归的实现
每天学习一点点
04-17 1万+
递归查询语法 WITH cte_name AS ( cte_query_initial --初始化部分 UNION ALL cte_query_iterative --递归部分 ) SELECT * FROM cte_name; 关键字WITH表示定义递归形式的通用表表达式（即递归CTE）。 递归CTE的定义包含两部分： 1.cte_query_initial是初始化查询语句，用于创建初始结果集； 2.cte_query_iterative是递归查询语句，可以对当前CTE
Oracle 中生成流水号的方法
09-10
主要介绍了Oracle 中生成流水号的方法 的相关资料,需要的朋友可以参考下
oracle 流水号不重复,获取一个连续不重复的流水号
weixin_42509293的博客
04-10 368
private static String channelCode="ABC";private static SimpleDateFormat ymdFormat=new SimpleDateFormat("yyyyMMdd");private static SimpleDateFormat hmsFormat=new SimpleDateFormat("HHmmssSSS");private s...
利用MySql函数生成不重复的流水号
qq_42382972的博客
03-11 1205
利用MySql函数生成不重复的流水号添加存储流水号的表MySql函数创建封装获取流水号的方法Mappermapper.xml结语 添加存储流水号的表 create table T_CODE( id varchar(50) character set utf8 collate utf8_general_ci not null, currentvalue int null default 1, max...
用数据库生成不重复的流水号
编程资料大全
03-06 342
导读： 　　'************************************************* 　　'函数名：getMaxOrder 　　'作 用：得到最大序列号 　　'参 数：fieldName ----在序列号表中的字段名 　　' tableName ----序列号所在表名 　　' fieldName ----在表中的字段名 　　'返回值：字段的最大值 　　'调用函数：idA...
oracle存储过程的递归调用
沉淀
08-30 8447
心里知道有这么回事，但很少在实践中用到，下面是一个查找父地址的过程。 CREATE OR REPLACE FUNCTION F_CALLPARENTARDADDRESS(V_ARDADDRESSID IN NUMBER,
【密码学】生成8位26个字母和数字的全排列（密码字典,密钥）c++代码（非递归高效直接）
热门推荐
Wu_Being 的CSDN 博客
10-01 1万+
生成8位26个字母和数字的全排列（密钥）代码（非递归高效直接） 用C语言或C++，生成一个8位的26个字母和数字的全排列的原代码，例如从：00000000到ZZZZZZZZ。 说明：生成的这个字典会非常大，有36^8条记录，估计要3T的硬盘，跑一个晚上看看         这个方法比递归更高效，更直接。                                                ...
MYSQL实现Oracle的Start with…Connect By递归树查询
itmyhome的专栏
12-08 7068
因项目迁移，使用的数据库也需要从Oracle迁移到MySQL 其中有个功能使用到了Oracle的Start with…Connect By递归树查询，而MySQL中没有此函数，但可以通过自定义函数的方式来解决这个问题 创建表 create table treeList( id varchar(10), -- 节点ID name varchar(10), -- 节点名称 p
MySql递归查询和Oracle递归查询
tracymcgrady1314的博客
07-07 2370
MySql的递归查询和 Oracle的递归查询
oracle递归 遍历子节点 查询父节点
Bang的博客
11-20 1万+
利用start with .. connect by prior 遍历子节点或者父节点
递归查询存储过程
12-11
递归查询存储过程，很实用哦
Oracle Form自动生成序号
05-18
讲解如何在Form(父子结构)中自动生成行号，并且行号可以修改，修改后的行号依然会按照最大的行号网上递增，类似采购订单Form的行号逻辑
java生成流水单号，带日期带流水号，唯一不重复
最新发布
Jerry的博客
12-16 2836
str:单号前缀，maxNum：当前最大单号（流水号）1、封装公共方法（直接复制使用，跑起来再看原理）//result：最终结果，需要的流水号。// 格式化当前时间。2、业务模块调用，生成唯一流水号。//流水号：prefix。//str：单号前缀。
mysql,oracle 存储过程使用递归
xihuanxiaoxin的专栏
01-24 473
给对机构树表修改treecode时用到了递归 记录一下 mysql： BEGIN  /* 定义变量 */   declare v_num int default 0; declare v_c_org_id VARCHAR(32); declare v_p_org_id VARCHAR(32);     declare done int default -1;  -- 用于控制循环是否结束  ...
如何生成连续的流水号
wmj2003的专栏
07-02 7598
工作中常常遇到流水号生成的问题，有的客户要求流水号必须连续，这个我们的开发工作提出了一个小难题。解决方法如下：方法一：采用最大号表的形式在数据库中创建一个表专门存放其他数据库的最大流水号，table_maxOrderID  {table1_max,table2_max};在向数据库中插入数据的时候，首先想table_maxOrderId表查询到当前表的最大号加1，先更新最大号表，然
oracle存储过程中的递归,18-Oracle学习_存储过程-递归-树状结构的存储与展示
weixin_31710909的博客
04-03 364
一, 表create table article(id number primary key,cont varchar2(4000),pid number,isleaf number(1), -- 0, 叶子结点; 1, 非叶子结点alevel number(2));二, 数据insert into article values(1, '蚂蚁大战大象', 0, 0, 0);insert int...
[原创]存储过程里面的递归
weixin_34410662的博客
12-07 133
表结构 CREATE TABLE #temptable ( [id] BIGINT , [dataKey] NVARCHAR(255) , [dataValue] NVARCHAR(MAX) , [displayOrder] INT , [dataTypeDesc] NVARCHAR(100) , [pid] BIGI...
oracle存储过程中的递归,递归存储过程中使用cursor
weixin_39645019的博客
04-03 346
递归存储过程中使用cursor可以用来向上遍历有向图，找出其直系父辈。注意定义成local类型的cursor ！[@more@]PROCEDURE sp_get_all_lineal_parent@node_id int,@lineal_path varchar(200) outputwith encryptionASSET NOCOUNT ONBEGINIF ISNULL(@lineal_pat...
使用递归的方法生成列表的全排列，列表中有重复元素
06-02
在递归的过程中，如果列表中有重复元素，可能会出现重复的全排列。为了避免这种情况，可以在每次递归时，判断当前元素是否已经出现过，如果已经出现过，则跳过当前元素。以下是生成一段对列表进行全排列的代码，其中包括了对重复元素的处理： ```python def permute(nums): if len(nums) == 0: return [] if len(nums) == 1: return [nums] res = [] visited = set() for i in range(len(nums)): if nums[i] in visited: continue visited.add(nums[i]) prefix = nums[i] suffix = nums[:i] + nums[i+1:] for p in permute(suffix): res.append([prefix] + p) return res lst = [1, 2, 2, 3] result = permute(lst) for r in result: print(r) ``` 输出结果为： ``` [1, 2, 2, 3] [1, 2, 3, 2] [1, 3, 2, 2] [2, 1, 2, 3] [2, 1, 3, 2] [2, 2, 1, 3] [2, 2, 3, 1] [2, 3, 1, 2] [2, 3, 2, 1] [3, 1, 2, 2] [3, 2, 1, 2] [3, 2, 2, 1] ``` 这段代码中，`visited` 用于记录已经出现过的元素，如果当前元素已经出现过，则跳过当前元素。这样就能保证在递归过程中不会出现重复的全排列。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值