存储过程第一步

存储过程第一步

特色：
1.存储过程传入list参数（ arrayStr 和 qtyNum 都是）
2.递归查询插入临时表

CREATE DEFINER=`root`@`%` PROCEDURE `sp_sdm_bom_many_select`(in arrayStr MEDIUMTEXT,IN qtyNum MEDIUMTEXT,IN tenantCodeValue VARCHAR(100))
BEGIN
	declare i int default 0;
	
	set @arrayLength = 1 + (length(arrayStr) - length(REPLACE(arrayStr,',','')));
	
		DROP TEMPORARY TABLE IF EXISTS many_bom_result;
    -- 创建临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS many_bom_result(
				rootcode varchar(100),-- 根物料
				matcode varchar(100),-- 物料号
				matcode_parent varchar(100),-- 上级物料号
				qty decimal(18,2),-- 数量
				unit varchar(255),-- 单位
				class_code varchar(255)-- 停用时间
		);
    -- 清空临时表数据
    DELETE FROM many_bom_result;
    -- 发起调用
	while i < @arrayLength
 
	DO
 
	set i = i+1;
	
	set @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(arrayStr,',',i)),',',1));
 
-- 	添加语句
-- 	位置截取: SUBSTRING_INDEX(@result,',',1)  SUBSTRING_INDEX(@qtyNum,',',1) 
				insert into many_bom_result(rootcode,matcode,matcode_parent,qty,unit,class_code) 
				SELECT SUBSTRING_INDEX(@result,',',1),bom_list_res.* FROM ( WITH RECURSIVE cte_sdm_bom_relation AS(
				SELECT * FROM sdm_bom_relation WHERE matcode_parent = SUBSTRING_INDEX(@result,',',1) AND   tenant_code = tenantCodeValue
				UNION ALL			SELECT u.* FROM sdm_bom_relation u, cte_sdm_bom_relation t WHERE u.matcode_parent = t.matcode)
				SELECT matcode,matcode_parent ,qty*SUBSTRING_INDEX(@qtyNum,',',1) as qty,unit,
				(SELECT classification_code FROM sbm_materiel m WHERE cte_sdm_bom_relation.matcode = m.matcode) as class_code 
				FROM cte_sdm_bom_relation ) as  bom_list_res;
				
  end while;
	SELECT rootcode,matcode,matcode_parent AS 'matcodeparent',qty,unit,class_code AS 'classCode' FROM many_bom_result;
END