本文结合一个实例讲述一个复杂的mysql存储过程

1.需求

有三张数据表：物料表、bom表、bom详情表

• 物料表：里面有三种类型物料，分别是，产品、半成品、物料
• bom表：由物料表中产品类型组成，bom是树形结构，根节点是产品，叶子节点是物料，中间的是半成品
• bom详情表：和bom表通过产品关联

现在需要在bom表中查询一个产品对应的所有物料，即半成品需要全部展开成物料。

2.实现存储过程

2.1创建数据表

物料数据表，MA_SpreedType='M’是产品，MA_SpreedType='A’是半成品，MA_SpreedType='B’是物料，

CREATE TABLE `material` (
  `MA_Id` int(11) NOT NULL AUTO_INCREMENT,
  `MA_Num` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `MA_Name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `MA_Desc` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `MA_SpreedType` varchar(6) COLLATE utf8_bin DEFAULT NULL,
  `MA_RegTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`MA_Id`),
  UNIQUE KEY `MA_Num` (`MA_Num`),
  KEY `ix_basic_material_MA_RegTime` (`MA_RegTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

产品bom表，bom是树形结构，根节点是产品，叶子节点是物料，中间的是半成品

CREATE TABLE `bom` (
  `BO_Id` int(11) NOT NULL AUTO_INCREMENT,
  `BO_Parent` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `BO_Status` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  `BO_Version` varchar(12) COLLATE utf8_bin DEFAULT NULL,
  `BO_RegTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`BO_Id`),
  KEY `ix_basic_bom_BO_RegTime` (`BO_RegTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

产品bom详情表，BOD_Parent字段与bom表中BO_Parent字段关联

CREATE TABLE `bomdetail` (
  `BOD_Id` int(11) NOT NULL AUTO_INCREMENT,
  `BOD_Parent` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `BOD_Version` varchar(12) COLLATE utf8_bin DEFAULT NULL,
  `BOD_Num` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `BOD_Name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `BOD_Desc` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `BOD_RegTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`BOD_Id`),
  KEY `ix_basic_bomdetail_BOD_RegTime` (`BOD_RegTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.2创建存储过程

DROP PROCEDURE IF EXISTS `get_bom_list`;
DELIMITER ;;
CREATE PROCEDURE `get_bom_list`(IN MaterNum char(30))
BEGIN
	  DECLARE Count,TmpId,TmpLevel INT;
      DECLARE TMPNum,TMPName,TMPDesc,TMPParent,TMPVer,TMPType VARCHAR(255);
      DECLARE done BOOLEAN DEFAULT 0;
      DECLARE curid CURSOR FOR SELECT TMP_Id,TMP_MA_Num,TMP_MA_Name,TMP_MA_Desc,TMP_BOD_Parent,TMP_BOD_Version,
																		  TMP_Spread_Type,TMP_Level FROM tmp_bom_list_table ORDER BY TMP_Level;
	  # 定义游标																  
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; #CONTINUE HANDLER 是在条件出现时执行的代码，SQLSTATE '02000' 表没有找到更多的行（MySQL错误代码）
		# 创建临时表，保存查询结果
		DROP TABLE IF EXISTS tmp_bom_list_table;
		CREATE TEMPORARY TABLE tmp_bom_list_table(  
			TMP_Id int(11) NOT NULL AUTO_INCREMENT,
			TMP_MA_Num VARCHAR(30),
			TMP_MA_Name VARCHAR(255),
			TMP_MA_Desc VARCHAR(255),
			TMP_BOD_Parent VARCHAR(30),
			TMP_BOD_Version VARCHAR(12),
			TMP_Spread_Type VARCHAR(6),
			TMP_Level int(11),
			PRIMARY KEY (`TMP_Id`)
		);
			# 递归循环查询，碰到半成品（半成品属性为A）就继续展开
			# 根据产品编码递归查找下一级物料的ID，如果没有下一级，那么递归查找结束
			SELECT GROUP_CONCAT(BOD_Id) INTO @strAllSubIds FROM bomdetail WHERE BOD_Parent=MaterNum AND 
						 BOD_Version in (SELECT BO_Version FROM bom WHERE BO_Parent=BOD_Parent AND BO_Status=1);
			SET @sqlStr = CONCAT("SELECT GROUP_CONCAT(BOD_Num),GROUP_CONCAT(BOD_Parent) INTO @BOD_Num,@BOD_Parent 
														FROM bomdetail WHERE FIND_IN_SET(BOD_Id,@strAllSubIds)");

			SET Count = 1;
			SET @TmpLevel = 0;
			WHILE Count=1 DO
				PREPARE stmt FROM @sqlStr;
				EXECUTE stmt;
				DEALLOCATE PREPARE stmt; #释放资源

				IF @BOD_Num IS NULL THEN
					SET Count = 0;
				ELSE
					INSERT INTO tmp_bom_list_table (TMP_MA_Num,TMP_MA_Name,TMP_MA_Desc,TMP_BOD_Parent,TMP_BOD_Version,
					TMP_Spread_Type,TMP_Level) SELECT MA_Num,MA_Name,MA_Desc,BOD_Parent,BOD_Version,MA_SpreedType,
					@TmpLevel FROM (SELECT BOD_Parent,BOD_Version,BOD_Num FROM bomdetail WHERE FIND_IN_SET(BOD_Num,@BOD_Num) 
					AND FIND_IN_SET(BOD_Parent,@BOD_Parent)) t,material WHERE BOD_Num=MA_Num AND BOD_Version in 
					(SELECT BO_Version FROM bom WHERE BO_Parent=BOD_Parent AND BO_Status=1);
					
					SET @TmpLevel = @TmpLevel+1;
				END IF;
				SET @sqlStr = CONCAT("SELECT GROUP_CONCAT(BOD_Num),GROUP_CONCAT(BOD_Parent) INTO @BOD_Num,@BOD_Parent 
															FROM material,bomdetail WHERE BOD_Parent=MA_Num AND 
															FIND_IN_SET(BOD_Parent,@BOD_Num) AND MA_SpreedType='A'");
			END WHILE;

			OPEN curid; #打开游标
				REPEAT
					FETCH curid INTO TmpId,TMPNum,TMPName,TMPDesc,TMPParent,TMPVer,TMPType,TmpLevel;
					IF done = 0 then
							# SELECT TmpId,TMPNum,TMPName,TMPDesc,TMPParent,TMPVer,TMPType,TmpLevel;
							IF TMPType = 'M' THEN  # 产品处理分支
								select '产品'; # 处理分支
							ELSEIF TMPType = 'A' THEN # 半成品处理分支
								select '半成品';
							ELSE  # 物料处理分支
								select '物料';
							END IF;

					END IF;

				UNTIL done 
				END REPEAT;
			CLOSE curid; #关闭游标

    END
;;
DELIMITER ;

2.3调用存储过程

CALL get_bom_list('A-B032-2-019'); 

查询运行结果：

SELECT * FROM tmp_bom_list_table ORDER BY TMP_Level;