快速產生唯一單據序號

Mysql版

DELIMITER $$
DROP PROCEDURE IF EXISTS `GetMailNO`$$
CREATE DEFINER=`lrmis`@`%` PROCEDURE `GetMailNO`(OUT mailNO BIGINT)
BEGIN
 DECLARE t_error INTEGER DEFAULT 0;
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  -- 遇到異常后繼續 即設定@t_error=1 然後回滾,返回0
 SET mailNO=0;
 START TRANSACTION;   
     SELECT mail_seq INTO mailNO FROM lrmailseq FOR UPDATE; --排它鎖 
     SET mailNO=mailNO+1;
     UPDATE lrmailseq SET mail_seq=mailNO; 
  IF t_error = 1 THEN  
       ROLLBACK;
  ELSE
      COMMIT;
  END IF;
END$$
DELIMITER ;

SqlServer版

CREATE Procedure [iemis].[GetMailNO]   
(  
 @seqNO bigint output  
)  
as   
begin  
     begin tran  
        set @seqNO=1  
     select @seqNO=seq_no from lrmailseq with (updlock)  
         set @seqNO=@seqNO+1  
     update lrmailseq set seq_no=@seqNO  
     commit tran  
     if @@error>0  
         rollback tran  
end