Mysql Numbering SP Sample

최근 채번관련 가이드할일 있어서 열어본 기록. 작년에 만들었던 놈이다.

---

■ 채번용 Table

CREATE TABLE `t_order_seq` (
  `SEQ` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`SEQ`)
) ENGINE=INNODB

INNODB 이더라도 UNIQUE NUMBERING 이 보장되는것으로 보임.

---

■ SP

DELIMITER //

USE tastedb//

/*================================================================================
SP_GET_ORDER_SEQ
---------------------------------------
GET ID FOR t_order_mst.ORDER_ID, t_order_temp.ORDER_ID
e.g)

CALL SP_GET_ORDER_SEQ('Y', @R_RET);
SELECT @R_RET;
---------------------------------------
order id example : 2011010212345678 ( yyyymmdd + 8자리)
================================================================================*/
DROP PROCEDURE IF EXISTS SP_GET_ORDER_SEQ//
CREATE PROCEDURE SP_GET_ORDER_SEQ
(
    IN I_PRINT_YN           CHAR(1)
    ,OUT O_RTN_VAL           CHAR(16)
)

BEGIN

    DECLARE V_SEQ BIGINT;
    DECLARE V_VAL_MAX   BIGINT DEFAULT 99999999;
   
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET O_RTN_VAL = '9999123199999999';
    END;

    -- MAIN PART ===================================
    INSERT INTO t_order_seq VALUES (0);

    SELECT LAST_INSERT_ID() INTO V_SEQ;
   
    -- SELECT V_SEQ;
   
    IF V_SEQ > V_VAL_MAX THEN
       
        TRUNCATE TABLE t_order_seq;  -- 이 구문은... 채번이 많이 될 것으로 예상하지 않는다면, 뺀다음 SP가 아닌 펑션으로 쓰는 것도 괜찮음. 허나 그런건 깔끔하지 않으므로 나는 비선호함.
       
        INSERT INTO t_order_seq VALUES (0);
        SELECT LAST_INSERT_ID() INTO V_SEQ;
       
    END IF;

    DELETE FROM t_order_seq WHERE SEQ < V_SEQ;

    SET O_RTN_VAL =
        CONCAT( DATE_FORMAT(CURDATE(), '%Y%m%d')
                , LPAD(V_SEQ, 8, '0')
                );

    IF I_PRINT_YN = 'Y' THEN
        SELECT O_RTN_VAL;
    END IF;

END//

DELIMITER ;