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;