プロシジャで採番を行う。

採番されてるべきコードの抜けが発生したテーブルを
単純に番号を割り振っていくプロシジャ、テーブル名や、コードのけた数など修正すれば、
使い回しができるプロシジャを書いてみた。

この例は、items というテーブルに、７桁のコード：０’ゼロ’埋めされてるコードで、抜けがあるものに、
強制的に指定する番号範囲から空いたコードを振って更新していく。

DELIMITER //
DROP PROCEDURE IF EXISTS sp_itemcode_asign
//
CREATE PROCEDURE sp_itemcode_asign(IN fromcode VARCHAR(7), IN tocode VARCHAR(7))
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE item_id   INT;
   DECLARE device_item_machine_no INT;
   DECLARE codevalue    INT;
   DECLARE maxcodevalue INT;
   DECLARE seqno        INT DEFAULT 1;
   DECLARE codecharcter VARCHAR(7);
   -- カーソル定義
   DECLARE cur CURSOR FOR
      SELECT id FROM items WHERE COALESCE(jcode, '') = '' ORDER BY id ASC;
   -- ハンドラ宣言
   DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

   -- シーケンスにコードを振る TEMPORARY TABLE
   SET codevalue = CAST(fromcode AS SIGNED);
   SET maxcodevalue = CAST(tocode AS SIGNED);
   DROP TABLE IF EXISTS temp_codes;
   CREATE TEMPORARY TABLE temp_codes (vcode VARCHAR(7), code_value INT);
   WHILE codevalue <= maxcodevalue DO
      INSERT INTO temp_codes SELECT LPAD(codevalue, 7, '0'), codevalue;
      SET codevalue = codevalue + 1;
   END WHILE;
   -- 空きコードの　TEMPORARY TABLE 。id でシーケンスに参照可能
   DROP TABLE IF EXISTS temp_seq;
   CREATE TEMPORARY TABLE temp_seq (id INT NOT NULL, vcode VARCHAR(7), code_value INT);
   INSERT INTO temp_seq
   SELECT @i:=@i+1, temp.vcode, temp.code_value
   FROM (select @i:=0) AS dummy, temp_codes temp LEFT JOIN items i ON temp.vcode = i.jcode
   WHERE COALESCE(jcode, '') = '';
   -- 空きコードセット
   OPEN cur;
   REPEAT
      FETCH cur INTO item_id;
      IF NOT done THEN
         SELECT vcode INTO codecharcter FROM temp_seq WHERE id = seqno;
         UPDATE items SET jcode = codecharcter WHERE id = item_id;
         SET seqno = seqno + 1;
      END IF;
   UNTIL done END REPEAT;
   CLOSE cur;
END
//
DELIMITER ;


呼び出し例

 call  sp_itemcode_asign( '0001200' , '0009999' );