-- ============================================================
|
-- 生产订单完整删除函数
|
-- 适用场景：待开始、进行中、已完成三种状态的生产订单删除
|
-- 生成日期：2026-06-11
|
-- ============================================================
|
|
DELIMITER $$
|
|
DROP FUNCTION IF EXISTS fn_delete_production_order$$
|
|
CREATE FUNCTION fn_delete_production_order(
|
p_order_id BIGINT,
|
p_force_delete TINYINT DEFAULT 0 -- 0=安全模式(已生产不允许删除), 1=强制删除
|
) RETURNS VARCHAR(500)
|
DETERMINISTIC
|
READS SQL DATA
|
BEGIN
|
DECLARE v_order_status INT DEFAULT 0;
|
DECLARE v_complete_quantity DECIMAL(20,4) DEFAULT 0;
|
DECLARE v_quantity DECIMAL(20,4) DEFAULT 0;
|
DECLARE v_nps_no VARCHAR(100) DEFAULT '';
|
DECLARE v_has_pick_record INT DEFAULT 0;
|
DECLARE v_has_product_main INT DEFAULT 0;
|
DECLARE v_has_quality_inspect INT DEFAULT 0;
|
DECLARE v_has_stock_record INT DEFAULT 0;
|
DECLARE v_deleted_count INT DEFAULT 0;
|
DECLARE v_result VARCHAR(500) DEFAULT '';
|
|
-- 查询订单基本信息
|
SELECT status, complete_quantity, quantity, nps_no
|
INTO v_order_status, v_complete_quantity, v_quantity, v_nps_no
|
FROM production_order
|
WHERE id = p_order_id;
|
|
-- 订单不存在
|
IF v_order_status IS NULL THEN
|
RETURN CONCAT('删除失败：生产订单不存在，ID=', p_order_id);
|
END IF;
|
|
-- 安全模式下检查是否允许删除
|
IF p_force_delete = 0 THEN
|
-- 状态检查：只有待开始(1)和已取消(4)允许删除
|
IF v_order_status = 2 THEN
|
RETURN CONCAT('删除失败：订单[', v_nps_no, ']正在进行中，不允许删除');
|
END IF;
|
|
IF v_order_status = 3 THEN
|
RETURN CONCAT('删除失败：订单[', v_nps_no, ']已完成，不允许删除');
|
END IF;
|
|
IF v_order_status = 5 THEN
|
RETURN CONCAT('删除失败：订单[', v_nps_no, ']已结束，不允许删除');
|
END IF;
|
END IF;
|
|
-- ========== 第一步：检查关联数据是否存在 ==========
|
|
-- 检查领料记录
|
SELECT COUNT(*) INTO v_has_pick_record
|
FROM production_order_pick_record
|
WHERE production_order_id = p_order_id;
|
|
-- 检查报工记录
|
SELECT COUNT(*) INTO v_has_product_main
|
FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id;
|
|
-- 检查质检记录
|
SELECT COUNT(*) INTO v_has_quality_inspect
|
FROM quality_inspect qi
|
INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id;
|
|
-- 检查库存记录（通过报工入库）
|
SELECT COUNT(*) INTO v_has_stock_record
|
FROM stock_in_record sir
|
INNER JOIN production_product_main ppm ON sir.record_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
AND sir.record_type IN ('2', '5'); -- 生产报工入库
|
|
-- ========== 第二步：删除关联数据（从叶子到根） ==========
|
|
-- 【第1层】删除质检附件
|
DELETE FROM quality_inspect_file
|
WHERE inspect_id IN (
|
SELECT qi.id FROM quality_inspect qi
|
INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第2层】删除质检参数
|
DELETE FROM quality_inspect_param
|
WHERE inspect_id IN (
|
SELECT qi.id FROM quality_inspect qi
|
INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第3层】删除不合格品记录
|
DELETE FROM quality_unqualified
|
WHERE inspect_id IN (
|
SELECT qi.id FROM quality_inspect qi
|
INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第4层】删除质检记录（过程检验、出厂检验）
|
DELETE FROM quality_inspect
|
WHERE product_main_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第5层】删除库存入库记录（生产报工入库、报废入库）
|
DELETE FROM stock_in_record
|
WHERE record_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
) AND record_type IN ('2', '5', '7'); -- 生产报工入库、报废入库
|
|
-- 删除质检入库记录
|
DELETE FROM stock_in_record
|
WHERE record_id IN (
|
SELECT qi.id FROM quality_inspect qi
|
INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
) AND record_type = '6'; -- 质检入库
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第6层】删除库存出库记录（生产报工出库）
|
DELETE FROM stock_out_record
|
WHERE record_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
) AND record_type = '13'; -- 生产报工出库
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第7层】删除报工产出
|
DELETE FROM production_product_output
|
WHERE production_product_main_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第8层】删除报工投入
|
DELETE FROM production_product_input
|
WHERE production_product_main_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第9层】删除生产核算
|
DELETE FROM production_account
|
WHERE production_product_main_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第10层】删除报工工序参数（通过production_product_main_id关联）
|
DELETE FROM production_order_routing_operation_param
|
WHERE production_product_main_id IN (
|
SELECT ppm.id FROM production_product_main ppm
|
INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
|
WHERE pot.production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第11层】删除报工主表
|
DELETE FROM production_product_main
|
WHERE production_operation_task_id IN (
|
SELECT id FROM production_operation_task
|
WHERE production_order_id = p_order_id
|
);
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第12层】删除领料记录
|
DELETE FROM production_order_pick_record
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第13层】删除领料入库记录（退料）
|
DELETE FROM stock_in_record
|
WHERE record_id IN (
|
SELECT id FROM production_order_pick
|
WHERE production_order_id = p_order_id
|
) AND record_type IN ('20', '22'); -- 领料退料入库
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第14层】删除领料单
|
DELETE FROM production_order_pick
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第15层】删除生产工单
|
DELETE FROM production_operation_task
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第16层】删除工序参数（订单级别）
|
DELETE FROM production_order_routing_operation_param
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第17层】删除工序
|
DELETE FROM production_order_routing_operation
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第18层】删除工艺路线
|
DELETE FROM production_order_routing
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第19层】删除BOM结构
|
DELETE FROM production_bom_structure
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第20层】删除订单BOM
|
DELETE FROM production_order_bom
|
WHERE production_order_id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 【第21层】删除生产计划关联（回退计划已下发数量）
|
-- 注意：这里需要根据业务逻辑回退production_plan的quantity_issued和status
|
-- 由于production_plan_ids是JSON格式[1,2,3]，需要特殊处理
|
-- 此处仅删除订单，计划回退逻辑建议在应用层处理
|
|
-- 【第22层】删除生产订单主表
|
DELETE FROM production_order
|
WHERE id = p_order_id;
|
SET v_deleted_count = v_deleted_count + ROW_COUNT();
|
|
-- 返回结果
|
SET v_result = CONCAT(
|
'删除成功：订单[', v_nps_no, ']，',
|
'状态=', CASE v_order_status
|
WHEN 1 THEN '待开始'
|
WHEN 2 THEN '进行中'
|
WHEN 3 THEN '已完成'
|
WHEN 4 THEN '已取消'
|
WHEN 5 THEN '已结束'
|
ELSE '未知'
|
END, '，',
|
'共删除', v_deleted_count, '条关联数据'
|
);
|
|
RETURN v_result;
|
|
END$$
|
|
DELIMITER ;
|
|
-- ============================================================
|
-- 批量删除生产订单存储过程
|
-- ============================================================
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_batch_delete_production_order$$
|
|
CREATE PROCEDURE sp_batch_delete_production_order(
|
IN p_order_ids TEXT, -- 订单ID列表，逗号分隔，如 "1,2,3"
|
IN p_force_delete TINYINT -- 0=安全模式, 1=强制删除
|
)
|
BEGIN
|
DECLARE v_order_id BIGINT;
|
DECLARE v_done INT DEFAULT FALSE;
|
DECLARE v_cursor CURSOR FOR
|
SELECT CAST(TRIM(value) AS UNSIGNED)
|
FROM JSON_TABLE(CONCAT('[', p_order_ids, ']'), '$[*]' COLUMNS(value VARCHAR(20) PATH '$')) AS jt;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
|
|
DECLARE v_success_count INT DEFAULT 0;
|
DECLARE v_fail_count INT DEFAULT 0;
|
DECLARE v_result_msg VARCHAR(500);
|
|
-- 创建临时表记录结果
|
DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;
|
CREATE TEMPORARY TABLE tmp_delete_result (
|
order_id BIGINT,
|
result VARCHAR(500)
|
);
|
|
OPEN v_cursor;
|
|
read_loop: LOOP
|
FETCH v_cursor INTO v_order_id;
|
IF v_done THEN
|
LEAVE read_loop;
|
END IF;
|
|
-- 调用删除函数
|
SET v_result_msg = fn_delete_production_order(v_order_id, p_force_delete);
|
|
-- 记录结果
|
INSERT INTO tmp_delete_result (order_id, result) VALUES (v_order_id, v_result_msg);
|
|
-- 统计
|
IF v_result_msg LIKE '删除成功%' THEN
|
SET v_success_count = v_success_count + 1;
|
ELSE
|
SET v_fail_count = v_fail_count + 1;
|
END IF;
|
|
END LOOP;
|
|
CLOSE v_cursor;
|
|
-- 输出结果
|
SELECT
|
CONCAT('批量删除完成：成功', v_success_count, '个，失败', v_fail_count, '个') AS summary;
|
|
SELECT * FROM tmp_delete_result;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;
|
|
END$$
|
|
DELIMITER ;
|
|
-- ============================================================
|
-- 使用示例
|
-- ============================================================
|
|
-- 示例1：安全模式删除单个订单（只能删除待开始/已取消状态）
|
-- SELECT fn_delete_production_order(123, 0);
|
|
-- 示例2：强制删除单个订单（忽略状态检查）
|
-- SELECT fn_delete_production_order(123, 1);
|
|
-- 示例3：批量删除订单
|
-- CALL sp_batch_delete_production_order('123,124,125', 0);
|
|
-- 示例4：批量强制删除
|
-- CALL sp_batch_delete_production_order('123,124,125', 1);
|
