Mysql Numbering Function Sample

2012/02/24 - [ IT , Digital/Database] - Mysql Numbering SP Sample

의 SP와 이 페이지의 펑션을 비교하여 얘기하자면

SP --- 특정일에 YYYYMMDD001 ~ YYYYMMDD099 까지 채번됐다면, 다음날은 YYYYMMDD100이 된다.
FUNCTION --- 매일마다 YYYYMMDD001 로 새로 채번이 된다.

---

■ 채번용 Table

CREATE TABLE `T_SEQUENCE` (`NAME` VARCHAR(20) NOT NULL`SEQUENCE` INT(11) NULL DEFAULT NULL,PRIMARY KEY (`NAME`))ENGINE=MyISAM;

MyISAM으로 해야함.
추측컨대 AP단에서 채번과 다른 DB작업들을 트랜잭션 한방으로 처리할 경우에도... 문제는 없을것 같음. (테스트 안해봄)

이 Table은 여러 가지의 채번을 관리하는 테이블임.
채번 용도별 일자별 한 건이 발생함.
Name 컬럼 값 예 : ORDER-20100802

---

■ Function

CREATE DEFINER=`tastedb`@`%` FUNCTION `SF_GET_SEQ`(`prefix_name` VARCHAR(20))
RETURNS varchar(100)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE curr_de     VARCHAR(8)  DEFAULT "";
DECLARE seq_name    VARCHAR(20) DEFAULT "";
DECLARE i_seq       INT         DEFAULT 0;
DECLARE str_seq     VARCHAR(5)  DEFAULT "";
DECLARE idx         VARCHAR(20) DEFAULT "";
DECLARE ret_seq     VARCHAR(20) DEFAULT "";

SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO curr_de;
SET seq_name = CONCAT(prefix_name, "-", curr_de);

UPDATE T_SEQUENCE
SET SEQUENCE = LAST_INSERT_ID(SEQUENCE+1)
WHERE NAME = seq_name;

SET i_seq := LAST_INSERT_ID();
IF i_seq = 0 THEN
SET i_seq := 1;
INSERT INTO T_SEQUENCE(NAME, SEQUENCE)
VALUES(seq_name, 1);
END IF;

SET idx := LENGTH(i_seq);
WHILE (idx < 5) DO
SET str_seq := CONCAT('0', str_seq);
SET idx := idx + 1;
END WHILE;

SET ret_seq := CONCAT(curr_de, str_seq, i_seq);
RETURN ret_seq;
END

---

■ LAST_INSERT_ID()

예1) 일반적으로 쓰는 용도. -- AUTO_INCREMENT 컬럼에 대해
INSERT INTO order_seq VALUES (0);
SELECT LAST_INSERT_ID() INTO V_SEQ;

예2) 이렇게도 사용이 가능함. --- AUTO_INCREMENT 컬럼 아닌거에 대해서도 가능
UPDATE order_mst
SET ORDER_PRICE = LAST_INSERT_ID( ORDER_PRICE + 1000 )
, CANCEL_AMOUNT = CANCEL_AMOUNT + 100
WHERE ORDER_ID = 100;

SELECT LAST_INSERT_ID(); --- 기존 ORDER_PRICE 값이 0 이었다면 여기서 1000을 리턴함.