DOC/3设计阶段/DB/02_function/nextval.sql

DELIMITER $$


DROP FUNCTION IF EXISTS nextval$$

CREATE FUNCTION nextval(v_seq_name VARCHAR (50)) RETURNS VARCHAR(50) CHARSET utf8
BEGIN
  
  DECLARE V_CURRENT VARCHAR (20) ;
  DECLARE V_MAX VARCHAR (20) ;
  DECLARE V_MIN VARCHAR (20) ;
  SET V_CURRENT = '' ;
  SET V_MAX = '' ;
  SET V_MIN = '' ;
  SELECT 
    CURRENT_VAL,
    MAX_VAL,
    MIN_VAL INTO V_CURRENT,
    V_MAX,
    V_MIN 
  FROM
    SEQUENCE 
  WHERE SEQ_NAME = v_seq_name ;
  
  IF V_CURRENT = V_MAX 
  OR V_CURRENT = '' 
  THEN 
  UPDATE 
    sequence 
  SET
    CURRENT_VAL = V_MIN 
  WHERE SEQ_NAME = v_seq_name ;
  
  ELSE 
  UPDATE 
    sequence 
  SET
    CURRENT_VAL = (CURRENT_VAL + 0) + (INCREMENT_VAL + 0) 
  WHERE SEQ_NAME = v_seq_name ;
  END IF ;
  RETURN currval (v_seq_name) ;
END$$

DELIMITER ;