解决一个有关问题,随便写了一个存储过程
解决一个问题,随便写了一个存储过程
DELIMITER $$ DROP PROCEDURE IF EXISTS wms_batch_add_packing_list; CREATE PROCEDURE wms_batch_add_packing_list() BEGIN DECLARE done INT DEFAULT 0; DECLARE key_uuid VARCHAR(100); DECLARE short_uuid VARCHAR(100); DECLARE boxNo VARCHAR(100); DECLARE box_imei_nums INTEGER; DECLARE box_sku_code VARCHAR(32); DECLARE detail_key_uuid VARCHAR(100); DECLARE imei_box_no VARCHAR(100); DECLARE imei_sku_code VARCHAR(100); DECLARE imei_Code VARCHAR(100); DECLARE imei_Code2 VARCHAR(100); DECLARE imei_uuid VARCHAR(100); DECLARE rs_cursor CURSOR FOR SELECT c.imei_code, c.imei2 FROM wms_imei_center c WHERE c.box_code IN ('2015100336584'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SELECT replace(uuid(), '-', ''), UUID_SHORT(), FN_SEQ_NEXT_VAL('sequence.box.no') INTO key_uuid, short_uuid, boxNo; # 插入箱子 INSERT INTO wms_packing_list (id, packing_list_no, warehouse_code, packing_state, box_no, origin_box_no, box_state, creator_id, creator_name, create_time, packer_id, packer_name, pack_time) VALUES (key_uuid, short_uuid, '62000021', 1, boxNo, '', '1', '系统', '系统', now(), '系统', '系统', now()); SELECT count(*), c.sku_code INTO box_imei_nums, box_sku_code FROM wms_imei_center c WHERE c.box_code IN ('2015100336584'); SELECT replace(uuid(), '-', '') INTO detail_key_uuid; INSERT INTO wms_packing_list_detail (id, packing_list_no, box_no, origin_box_no, sku_code, sku_barcode, qty) VALUES (detail_key_uuid, short_uuid, boxNo, '', box_sku_code, '', box_imei_nums); -- 定义游标 OPEN rs_cursor; REPEAT FETCH rs_cursor INTO imei_Code, imei_Code2; IF NOT done THEN SELECT replace(uuid(), '-', '') INTO imei_uuid; INSERT INTO wms_packing_list_detail_metadata (id, packing_list_no, detail_id, imei_code, imei2) VALUES (imei_uuid, short_uuid, detail_key_uuid, imei_Code, imei_Code2); END IF; UNTIL done END REPEAT; END; $$ DELIMITER ; CALL wms_batch_add_packing_list();