IT工程師數位筆記本

create table Goods
(
    Id int not null PRIMARY key auto_increment,
    Code varchar(50) comment '編碼',
    Name varchar(20) comment '名稱',
    Count int comment '數量',
    Brand varchar(20) comment '品牌'
) default charset=utf8 comment '商品表';

create table GoodDetails
(
    Id int not null PRIMARY key auto_increment,
    GId int not null comment 'Goods表Id',
　　 Name varchar(20) comment '名稱',
    Code varchar(50) comment '編碼明細',
    Remark varchar(100) comment '備注'
) default charset=utf8 comment '商品明細';

delimiter $
drop PROCEDURE if EXISTS p_autocreate;
CREATE  PROCEDURE `p_autocreate`(IN g_ids VARCHAR(1000), IN nolength INT)
BEGIN
 DECLARE res_code INT;

 DECLARE res_msg VARCHAR (50);

 /*臨時表的條數*/
 DECLARE t_count INT;

 /**游標內使用變量**begin**/
 DECLARE g_id INT;

 DECLARE g_code VARCHAR (50);

 DECLARE d_code VARCHAR (50);

 DECLARE g_count INT (11);

 DECLARE g_name VARCHAR (20);
 /**游標內使用變量**end**/

 /**游標的位置**/
 DECLARE v_index INT DEFAULT 1;

 DECLARE done BIT DEFAULT 0;

 /*聲明游標*/
 DECLARE g_cursor CURSOR FOR SELECT id, CODE, NAME, COUNT FROM temp_goods;

 /*游標查詢時, 如果找不到下一個了, 會將done置為1*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 /**創建臨時表**begin**/
 DROP TABLE IF EXISTS temp_goods;
 CREATE TEMPORARY TABLE temp_goods (
  Id INT NOT NULL, --  PRIMARY key auto_increment,
  CODE VARCHAR (50),
  NAME VARCHAR (20),
  COUNT INT
 ) DEFAULT CHARSET = utf8;
 /**創建臨時表**end**/

 /**初始化返回值**begin**/
 SET res_code := "-99";

 SET res_msg := "OK";
 /**初始化返回值**end**/

 IF (g_ids IS NOT NULL OR LENGTH(g_ids) > 0) THEN

      SET @v_sql := CONCAT("INSERT INTO  temp_goods(Id,Code,Name,Count) ",
                    " select Id,Code,Name,Count from Goods ",
                    " where ",
                    " find_in_set(id, ",
                    CHAR(34),
                    g_ids,
                    CHAR(34),
                    ")>0 ;");

 /*預編譯此動態sql, 并存入stmt中*/
 PREPARE stmt FROM @v_sql;

 /*執行此動態sql, 此動態sql的作用, 是從Goods中提取有效數據*/
 EXECUTE stmt;

 /*釋放此資源*/
 DEALLOCATE PREPARE stmt;

 SELECT COUNT(1) INTO t_count FROM temp_goods;

 START TRANSACTION; -- 開始事務

 IF (t_count > 0) THEN
      /*打開游標*/
      OPEN g_cursor;

      REPEAT 
                        /*這里的順序要與之前的順序保持一致*/
            FETCH g_cursor INTO g_id, g_code, g_name, g_count;

            IF NOT done THEN

               SET v_index := 1;

               IF (IFNULL(g_count, 0) > 0) THEN

                  WHILE (v_index <= g_count)
                  DO
                     SET d_code := CONCAT(g_code, LPAD(v_index, nolength, "0"));

                     INSERT INTO GoodDetails(GId, NAME, CODE) VALUES (g_id, g_name, d_code);

                     SET v_index := v_index + 1;
                  END WHILE;
               END IF;
            END IF;
         UNTIL done END REPEAT; -- 結束repeat循環
         CLOSE g_cursor; /*關閉游標*/

         COMMIT; -- 提交事務

      ELSE

         ROLLBACK; -- 回滾事務

         SET res_code := "10";

         SET res_msg := "系統中不存在相關記錄.";
      END IF;
   ELSE
      SET res_code := "5";

      SET res_msg := "請選擇要生成的記錄";
   END IF;

   DROP TABLE IF EXISTS temp_goods;

   SELECT res_msg;
END $
delimiter ;

call p_autocreate('1,2,3,4', 3);