CREATE TABLE [dbo].[TableWater]( [Tb_id] [int] IDENTITY(1,1) NOT NULL, [Vc_table_name] [nvarchar](90) NULL, [Num_water_no] [varchar](100) NULL, CONSTRAINT [PK_TableWater] PRIMARY KEY CLUSTERED ( [Tb_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
测试表
测试参数
DECLARE @return_value int, @vmax_waterno bigint
EXEC @return_value = [dbo].[get_waterno] @vtableName = N'testtable', @vcnt = 1, @vmax_waterno = @vmax_waterno OUTPUT
SELECT @vmax_waterno as N'@vmax_waterno'
SELECT 'Return Value' = @return_value
SQL Server 实现：create PROCEDURE [dbo].[get_waterno] (@vtableName VARCHAR(90), --表名@vcnt INT ,--流水号@vmax_waterno bigint output --最大流水号)asBEGIN declare @Vcount int; set @vmax_waterno=0; SELECT @Vcount=count(1) FROM TableWater WHERE vc_table_name=@vtableName ; IF (@Vcount= 0 or @Vcount is null) BEGIN INSERT INTO TableWater(vc_table_name,num_water_no) VALUES(@vtableName, @vcnt ); set @vmax_waterno=@vcnt; --return; END; ELSE BEGIN DECLARE @i bigint; set @i= 1; WHILE (@i<= 1000) begin UPDATE TableWater SET num_water_no=num_water_no+@vcnt WHERE vc_table_name =@vtableName ; IF (@Vcount > 0 ) BEGIN SELECT @vmax_waterno=num_water_no FROM TableWater WHERE vc_table_name=@vtableName; -- return ; break; end; ELSE BEGIN set @i= @i + 1; SELECT @vmax_waterno=num_water_no FROM TableWater WHERE vc_table_name=@vtableName and num_water_no=@vmax_waterno ; set @vmax_waterno= NULL; --return ; break; end; end; end; end;
SQL 调用
---调用存储过程 生成结果表流水号 exec get_waterno "testtable",1,@p_tmp_water_num output; set @p_tmp_water_str= replicate('0',10)+ltrim (@p_tmp_water_num);
Oracle 实现：
PROCEDURE spr_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; --select max_waterno return;-- max_waterno; 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 ;-- and num_water_no = max_waterno; commit; IF (Vcount > 0 ) THEN BEGIN SELECT num_water_no INTO vmax_waterno FROM TableWater WHERE vc_table_name = vtableName; -- SET max_waterno = max_waterno + cnt; -- select max_waterno; 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; --select max_waterno; return ; END; END IF; END loop; vmax_waterno:= NULL; return; END; end; END IF; COMMIT; END;
oracle 调用
---调用存储过程 生成结果表流水号 spr_get_waterno('testtable',1,p_tmp_water_num); p_tmp_water_str:= LPAD(p_tmp_water_num,20,'0');