oracle存储过程生成流水号,存储过程生成流水号

格式如A20110915001，第1位写死A，2-5位为年，6-7位为月，8-9位为日，最后三位为流水号.

/****** Object:  StoredProcedure [dbo].[usp_SaleRetrun_CreateAutoApplyBatchId]    Script Date: 02/07/2012 09:55:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE  PROCEDURE [dbo].[usp_CreateAutoBatchId] AS BEGIN SET NOCOUNT ON; declare @oldValue varchar(12) declare @newValue varchar(12) declare @newCode varchar(3) declare @oldCode varchar(3) declare @oldYearMonthDay varchar(9) declare @newYearMonthDay varchar(9) set @newYearMonthDay = CONVERT(varchar(8), GETDATE(), 112) set @newYearMonthDay ='R'+@newYearMonthDay select @oldValue = pValue from Parameter with (rowlock) where pName='Program_Batch_Id' and pActive='Y' set @oldYearMonthDay = substring(@oldValue,1,9) set @oldCode = substring(@oldValue,10,3) if @newYearMonthDay = @oldYearMonthDay--如果新的年月日编号与旧的相同 begin -- 旧序号＋1 set @newCode = cast(cast(@oldCode as int)+1 as varchar(3)) while len(@newCode)<3 begin set @newCode = '0' + @newCode end set @newValue = @oldYearMonthDay + @newCode end else--采用新的年月编号，序号从"001"开始 begin set @newCode = '001' set @newValue = @newYearMonthDay + @newCode end     Begin transaction           --开始事务 update Parameter with (rowlock) set pValue = @newValue  where pName='Program_Batch_Id' and pActive='Y' if @@ERROR > 0 ROLLBACK Else COMMIT END GO