Oracle根据表生成系统流水号

1.建表tablewater

create table TABLEWATER
(
  tb_id         INTEGER not null,
  vc_table_name VARCHAR2(90),
  num_water_no  NUMBER(30)
)
vc_table_name 字段值为参数表table1、table2、、、、

2.存储过程实现

PROCEDURE   get_waterno 
    (
    
   vtableName IN  VARCHAR2, --表名
   vcnt       IN  INT ,--流水号
   vmax_waterno OUT  number --最大流水号
    
    )
    is
  Vcount int;
 
  BEGIN

     vmax_waterno:= 0;
     
    SELECT count(1) into Vcount FROM  TableWater WHERE vc_table_name = vtableName ;
    
    IF (Vcount= 0 or Vcount is null)
       THEN
    BEGIN        
          INSERT INTO  TableWater(vc_table_name,num_water_no) VALUES(vtableName, vcnt );
           vmax_waterno:= vcnt;
           
          return;
commit; END; ELSE BEGIN DECLARE i INTEGER; begin i:= 1; WHILE i<= 1000 loop UPDATE TableWater SET num_water_no = num_water_no + vcnt WHERE vc_table_name = vtableName ; commit; IF (Vcount > 0 ) THEN BEGIN SELECT num_water_no INTO vmax_waterno FROM TableWater WHERE vc_table_name = vtableName; return ; END ; ELSE BEGIN i:= i + 1; ------------------------------------------------------------------------------- SELECT num_water_no INTO vmax_waterno FROM TableWater WHERE vc_table_name = vtableName and vmax_waterno = num_water_no ; ------------------------------------------------------------------------ vmax_waterno:= NULL; return ; END; END IF; END loop; vmax_waterno:= NULL; return; END; end; END IF; COMMIT; END;
原文地址:https://www.cnblogs.com/Warmsunshine/p/5258640.html