以下是根据整个开发过程整理出的完整、可独立部署的BOM层级码检查方案代码：
-- ============================================
-- BOM层级码检查系统完整代码
-- 版本: 1.0
-- 最后更新: 2024-12-08
-- 功能: 检查BOM层级码的格式、结构、逻辑正确性
-- 支持: 点分隔符(.), 多层级检查, 多种错误类型检测
-- 表名: tabDIYTable12755
-- 字段: F111932(序号), F111935(层级码), F111971(总成产品编码), F111933(BOM编辑单号)
-- ============================================
-- 1. 创建检查结果表
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'BOM_LevelCode_Check_Results')
BEGIN
CREATE TABLE [dbo].[BOM_LevelCode_Check_Results] (
CheckID INT IDENTITY(1,1) PRIMARY KEY,
CheckTime DATETIME DEFAULT GETDATE(),
总成产品编码 NVARCHAR(50), -- 对应F111971
序号 INT, -- 对应F111932
BOM编辑单号 NVARCHAR(50), -- 对应F111933
层级码 NVARCHAR(100), -- 对应F111935
ErrorType NVARCHAR(50), -- 错误类型
ErrorMessage NVARCHAR(500), -- 错误信息
Severity INT, -- 严重程度: 1=低, 2=中, 3=高
父层级码 NVARCHAR(100), -- 父层级码
当前深度 INT, -- 当前层级深度
父节点是否存在 BIT, -- 父节点是否存在
建议的层级码 NVARCHAR(100), -- 建议的正确编码
检查点 NVARCHAR(100) -- 检查点描述
)
-- 创建索引
CREATE INDEX IX_BOM_Check_Time ON [dbo].[BOM_LevelCode_Check_Results] (CheckTime DESC)
CREATE INDEX IX_BOM_Check_Error ON [dbo].[BOM_LevelCode_Check_Results] (ErrorType, Severity)
CREATE INDEX IX_BOM_Check_Code ON [dbo].[BOM_LevelCode_Check_Results] (总成产品编码, 层级码)
PRINT '✓ 创建BOM层级码检查结果表: BOM_LevelCode_Check_Results'END
ELSE
BEGIN
PRINT '✓ BOM层级码检查结果表已存在'
END
GO
-- 2. 创建字符串分割函数
CREATE OR ALTER FUNCTION [dbo].[fn_SplitString]
(
@InputString NVARCHAR(MAX),
@Delimiter NVARCHAR(1) = ';'
)
RETURNS @Result TABLE (Item NVARCHAR(MAX), ItemIndex INT)
AS
BEGIN
DECLARE @StartIndex INT = 1
DECLARE @EndIndex INT
DECLARE @Index INT = 1
-- 如果输入为空，返回空表
IF @InputString IS NULL OR LEN(@InputString) = 0
RETURN
-- 循环分割字符串
WHILE @StartIndex > 0
BEGIN
SET @EndIndex = CHARINDEX(@Delimiter, @InputString, @StartIndex)
IF @EndIndex = 0
BEGIN
INSERT INTO @Result (Item, ItemIndex)
VALUES (SUBSTRING(@InputString, @StartIndex, LEN(@InputString) - @StartIndex + 1), @Index)
BREAK
END
ELSE
BEGIN
INSERT INTO @Result (Item, ItemIndex)
VALUES (SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex), @Index)
SET @StartIndex = @EndIndex + 1
SET @Index = @Index + 1
END
END
RETURNEND
GO
PRINT '✓ 创建字符串分割函数: fn_SplitString'
GO
-- 3. 主存储过程：BOM层级码检查
CREATE OR ALTER PROCEDURE [dbo].[usp_Check_BOM_LevelCode]
@参数 NVARCHAR(200) -- 格式: "BOM单号;成品编码;是否清除历史(0/1)"
AS
BEGIN
SET NOCOUNT ON
-- 1. 解析参数
PRINT '======= 参数解析 ======='
PRINT '输入参数: ' + ISNULL(@参数, '空')
DECLARE @BOM单号 NVARCHAR(50) = NULL
DECLARE @成品编码 NVARCHAR(50) = NULL
DECLARE @是否清除历史 BIT = 0
-- 使用字符串分割函数解析参数
IF @参数 IS NOT NULL AND LEN(@参数) > 0
BEGIN
DECLARE @ParamParts TABLE (Item NVARCHAR(200), ItemIndex INT)
INSERT INTO @ParamParts
SELECT Item, ItemIndex FROM [dbo].[fn_SplitString](@参数, ';')
SELECT @BOM单号 = CASE WHEN ItemIndex = 1 AND NULLIF(Item, '') IS NOT NULL THEN Item ELSE NULL END FROM @ParamParts WHERE ItemIndex = 1
SELECT @成品编码 = CASE WHEN ItemIndex = 2 AND NULLIF(Item, '') IS NOT NULL THEN Item ELSE NULL END FROM @ParamParts WHERE ItemIndex = 2
SELECT @是否清除历史 = CASE WHEN ItemIndex = 3 AND ISNUMERIC(Item) = 1 THEN CAST(Item AS BIT) ELSE 0 END FROM @ParamParts WHERE ItemIndex = 3
END
-- 验证参数
IF @BOM单号 IS NULL OR @BOM单号 = ''
BEGIN
PRINT '❌ 参数格式错误，正确格式: "BOM单号;成品编码;是否清除历史(0/1)"'
PRINT '示例: "BOM001;PROD001;1" 或 "BOM001;;0" 或 "BOM001"'
PRINT '当前解析结果:'
PRINT ' BOM单号: ' + ISNULL(@BOM单号, '空')
PRINT ' 成品编码: ' + ISNULL(@成品编码, '空')
PRINT ' 清除历史: ' + CAST(@是否清除历史 AS VARCHAR(1))
RETURN
END
-- 参数信息
PRINT '解析结果:'
PRINT ' BOM单号: ' + ISNULL(@BOM单号, '全部')
PRINT ' 成品编码: ' + ISNULL(@成品编码, '全部')
PRINT ' 清除历史: ' + CASE WHEN @是否清除历史 = 1 THEN '是' ELSE '否' END
PRINT ''
-- 2. 初始化变量
DECLARE @StartTime DATETIME = GETDATE()
DECLARE @TotalRecords INT = 0
DECLARE @ErrorCount INT = 0
-- 3. 清空之前的检查结果
IF @是否清除历史 = 1
BEGIN
DELETE FROM [dbo].[BOM_LevelCode_Check_Results]
PRINT '✓ 已清除之前的检查结果'
END
-- 4. 创建临时表存储原始数据
CREATE TABLE #TempData (
RowNum INT,
LevelCode NVARCHAR(100),
ProductCode NVARCHAR(50),
BOMCode NVARCHAR(50),
LevelDepth INT,
LevelArray XML,
ParentCode NVARCHAR(100)
)
-- 5. 从原始表获取数据
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
INSERT INTO #TempData (RowNum, LevelCode, ProductCode, BOMCode, LevelDepth, LevelArray, ParentCode)
SELECT
F111932,
F111935,
F111971,
F111933,
CASE
WHEN ISNULL(F111935, '''') = ''0'' THEN 1
WHEN ISNULL(F111935, '''') = '''' THEN 0
ELSE LEN(F111935) - LEN(REPLACE(F111935, ''.'', '''')) + 1
END,
CASE
WHEN ISNULL(F111935, '''') = '''' THEN CAST('''' AS XML)
ELSE CAST('''' + REPLACE(F111935, ''.'', ''
'') + ''
'' AS XML)
END,
CASE
WHEN ISNULL(F111935, '''') = '''' THEN NULL
WHEN ISNULL(F111935, '''') = ''0'' THEN NULL
WHEN CHARINDEX(''.'', REVERSE(F111935)) = 0 THEN NULL
ELSE LEFT(F111935, LEN(F111935) - CHARINDEX(''.'', REVERSE(F111935)))
END
FROM [dbo].[tabDIYTable12755]
WHERE 1=1 '
IF @BOM单号 IS NOT NULL
BEGIN
SET @SQL = @SQL + N' AND F111933 = @BOM单号 '
END
IF @成品编码 IS NOT NULL
BEGIN
SET @SQL = @SQL + N' AND F111971 = @成品编码 '
END
SET @SQL = @SQL + N' ORDER BY F111971, F111932'
-- 执行查询
IF @BOM单号 IS NOT NULL AND @成品编码 IS NULL
BEGIN
EXEC sp_executesql @SQL, N'@BOM单号 NVARCHAR(50)', @BOM单号
END
ELSE IF @BOM单号 IS NULL AND @成品编码 IS NOT NULL
BEGIN
EXEC sp_executesql @SQL, N'@成品编码 NVARCHAR(50)', @成品编码
END
ELSE IF @BOM单号 IS NOT NULL AND @成品编码 IS NOT NULL
BEGIN
EXEC sp_executesql @SQL, N'@BOM单号 NVARCHAR(50), @成品编码 NVARCHAR(50)', @BOM单号, @成品编码
END
ELSE
BEGIN
EXEC sp_executesql @SQL
END
SET @TotalRecords = @@ROWCOUNT
PRINT '✓ 从tabDIYTable12755表加载 ' + CAST(@TotalRecords AS VARCHAR(10)) + ' 条BOM记录'
-- 6. 检查格式错误
PRINT '6.1. 检查格式错误...'
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 检查点, 建议的层级码
)
SELECT
ProductCode, RowNum, BOMCode, LevelCode,
'格式错误' AS ErrorType,
CASE
WHEN LevelCode LIKE '.%' THEN '层级码以点"."开头: ' + ISNULL(LevelCode, 'NULL')
WHEN LevelCode LIKE '%.' THEN '层级码以点"."结尾: ' + ISNULL(LevelCode, 'NULL')
WHEN LevelCode LIKE '%..%' THEN '层级码包含连续点"..": ' + ISNULL(LevelCode, 'NULL')
WHEN LevelCode LIKE '%[^0-9.]%' THEN '层级码包含非法字符(只允许数字和点): ' + ISNULL(LevelCode, 'NULL')
WHEN LevelCode = '' OR LevelCode IS NULL THEN '层级码为空'
ELSE '未知格式错误: ' + ISNULL(LevelCode, 'NULL')
END AS ErrorMessage,
3 AS Severity,
'格式检查' AS 检查点,
LevelCode AS 实际编码
FROM #TempData
WHERE LevelCode LIKE '.%'
OR LevelCode LIKE '%.'
OR LevelCode LIKE '%..%'
OR LevelCode LIKE '%[^0-9.]%'
OR LevelCode = '' OR LevelCode IS NULL
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个格式错误'
-- 7. 检查数字合法性
PRINT '6.2. 检查层级码数字合法性...'
;WITH NonNumericParts AS (
SELECT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode, t.ParentCode, t.LevelDepth,
p.value AS PartValue
FROM #TempData t
CROSS APPLY (
SELECT p.value('.', 'NVARCHAR(10)') as value
FROM t.LevelArray.nodes('/p') AS T(p)
) p
WHERE p.value IS NOT NULL AND p.value != ''
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 当前深度, 检查点, 建议的层级码
)
SELECT DISTINCT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode,
'数字错误' AS ErrorType,
'层级码部分包含非数字字符: "' + p.PartValue + '", 完整编码: ' + t.LevelCode AS ErrorMessage,
3 AS Severity,
t.ParentCode AS 父层级码,
t.LevelDepth AS 当前深度,
'数字合法性检查' AS 检查点,
t.LevelCode AS 实际编码
FROM NonNumericParts p
INNER JOIN #TempData t ON p.ProductCode = t.ProductCode AND p.RowNum = t.RowNum
WHERE ISNUMERIC(p.PartValue) = 0
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个数字错误'
-- 8. 检查重复层级码
PRINT '6.3. 检查重复层级码...'
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 检查点, 建议的层级码
)
SELECT DISTINCT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode,
'重复编码' AS ErrorType,
'在' + t.ProductCode + '中出现重复编码: ' + t.LevelCode +
', 出现次数: ' + CAST(COUNT(*) OVER (PARTITION BY t.ProductCode, t.LevelCode) AS VARCHAR(10)) AS ErrorMessage,
3 AS Severity,
'唯一性检查' AS 检查点,
t.LevelCode AS 实际编码
FROM #TempData t
WHERE EXISTS (
SELECT 1
FROM #TempData t2
WHERE t2.ProductCode = t.ProductCode
AND t2.LevelCode = t.LevelCode
AND t2.RowNum <> t.RowNum
)
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个重复编码'
-- 9. 检查父节点是否存在
PRINT '6.4. 检查父节点是否存在...'
;WITH MissingParents AS (
SELECT DISTINCT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode, t.ParentCode, t.LevelDepth
FROM #TempData t
WHERE t.ParentCode IS NOT NULL
AND t.ParentCode != ''
AND NOT EXISTS (
SELECT 1
FROM #TempData p
WHERE p.ProductCode = t.ProductCode
AND p.LevelCode = t.ParentCode
)
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 父节点是否存在, 当前深度, 检查点, 建议的层级码
)
SELECT
mp.ProductCode, mp.RowNum, mp.BOMCode, mp.LevelCode,
'缺失父节点' AS ErrorType,
'层级码: ' + mp.LevelCode + ' 的父层级码 "' + mp.ParentCode + '" 不存在' AS ErrorMessage,
2 AS Severity,
mp.ParentCode AS 父层级码,
0 AS 父节点是否存在,
mp.LevelDepth AS 当前深度,
'结构完整性检查' AS 检查点,
mp.LevelCode AS 实际编码
FROM MissingParents mp
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个缺失父节点错误'
-- 10. 检查层级码排序连续性
PRINT '6.5. 检查层级码排序连续性...';
;WITH LevelNumbers AS (
SELECT
t.ProductCode,
t.RowNum,
t.LevelCode,
t.ParentCode,
t.LevelDepth,
t.BOMCode,
-- 获取最后一个部分作为序号
CASE
WHEN t.LevelCode = '0' THEN 0
WHEN t.ParentCode IS NULL THEN NULL
ELSE
CAST(
CASE
WHEN LEN(t.LevelCode) - ISNULL(LEN(t.ParentCode) + 1, 0) > 0
THEN RIGHT(t.LevelCode, LEN(t.LevelCode) - ISNULL(LEN(t.ParentCode) + 1, 0))
ELSE t.LevelCode
END
AS INT)
END AS LevelNumber
FROM #TempData t
),
LevelSequence AS (
SELECT
ProductCode,
ParentCode,
LevelDepth,
MIN(RowNum) AS FirstRowNum,
MAX(RowNum) AS LastRowNum,
MIN(LevelNumber) AS MinNumber,
MAX(LevelNumber) AS MaxNumber,
COUNT(*) AS ActualCount
FROM LevelNumbers
WHERE LevelNumber IS NOT NULL
GROUP BY ProductCode, ParentCode, LevelDepth
),
MissingSequences AS (
SELECT
ls.ProductCode,
ls.ParentCode,
ls.LevelDepth,
ls.MinNumber,
ls.MaxNumber,
ls.ActualCount,
(ls.MaxNumber - ls.MinNumber + 1) AS ExpectedCount
FROM LevelSequence ls
WHERE (ls.MaxNumber - ls.MinNumber + 1) > ls.ActualCount
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 当前深度, 检查点, 建议的层级码
)
SELECT DISTINCT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode,
'序号不连续' AS ErrorType,
'在父层级码 "' + ISNULL(t.ParentCode, '根') + '" 下序号不连续' AS ErrorMessage,
1 AS Severity,
t.ParentCode AS 父层级码,
t.LevelDepth AS 当前深度,
'连续性检查' AS 检查点,
t.LevelCode AS 实际编码
FROM #TempData t
INNER JOIN MissingSequences ms ON t.ProductCode = ms.ProductCode
AND (t.ParentCode = ms.ParentCode OR (t.ParentCode IS NULL AND ms.ParentCode IS NULL))
AND t.LevelDepth = ms.LevelDepth
CROSS APPLY (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN ms.MinNumber AND ms.MaxNumber
) m(MissingNum)
WHERE NOT EXISTS (
SELECT 1
FROM LevelNumbers ln
WHERE ln.ProductCode = t.ProductCode
AND (ln.ParentCode = t.ParentCode OR (ln.ParentCode IS NULL AND t.ParentCode IS NULL))
AND ln.LevelDepth = t.LevelDepth
AND ln.LevelNumber = m.MissingNum
)
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个序号不连续错误'
-- 11. 检查深度突变
PRINT '6.6. 检查深度突变...';
;WITH DepthSequence AS (
SELECT
ProductCode,
RowNum,
LevelCode,
BOMCode,
LevelDepth,
ParentCode,
ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY RowNum) AS RowOrder
FROM #TempData
),
DepthWithPrev AS (
SELECT
ds.*,
LAG(ds.LevelDepth) OVER (PARTITION BY ds.ProductCode ORDER BY ds.RowOrder) AS PrevDepth,
LAG(ds.LevelCode) OVER (PARTITION BY ds.ProductCode ORDER BY ds.RowOrder) AS PrevLevelCode
FROM DepthSequence ds
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 当前深度, 检查点, 建议的层级码
)
SELECT
ds.ProductCode, ds.RowNum, ds.BOMCode, ds.LevelCode,
'深度突变' AS ErrorType,
'深度从' + ISNULL(CAST(ds.PrevDepth AS VARCHAR(10)), '0') +
' 直接变为 ' + CAST(ds.LevelDepth AS VARCHAR(10)) + ', 层级码: ' + ds.LevelCode AS ErrorMessage,
2 AS Severity,
ds.ParentCode AS 父层级码,
ds.LevelDepth AS 当前深度,
'结构检查' AS 检查点,
ds.LevelCode AS 实际编码
FROM DepthWithPrev ds
WHERE ds.PrevDepth IS NOT NULL
AND ABS(ds.LevelDepth - ds.PrevDepth) > 1
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个深度突变错误'
-- 12. 检查层级码数字格式
PRINT '6.7. 检查层级码数字格式...';
;WITH LeadingZeros AS (
SELECT DISTINCT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode, t.ParentCode, t.LevelDepth,
p.value AS PartValue
FROM #TempData t
CROSS APPLY (
SELECT p.value('.', 'NVARCHAR(10)') as value
FROM t.LevelArray.nodes('/p') AS T(p)
) p
WHERE p.value IS NOT NULL
AND p.value != ''
AND ISNUMERIC(p.value) = 1
AND p.value LIKE '0%'
AND LEN(p.value) > 1
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 当前深度, 检查点, 建议的层级码
)
SELECT
t.ProductCode, t.RowNum, t.BOMCode, t.LevelCode,
'格式不规范' AS ErrorType,
'层级码部分包含前导零: "' + t.PartValue + '", 完整编码: ' + t.LevelCode AS ErrorMessage,
1 AS Severity,
t.ParentCode AS 父层级码,
t.LevelDepth AS 当前深度,
'格式规范检查' AS 检查点,
t.LevelCode AS 实际编码
FROM LeadingZeros t
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个格式不规范错误'
-- 13. 检查最大深度限制
PRINT '6.8. 检查最大深度限制...';
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 父层级码, 当前深度, 检查点, 建议的层级码
)
SELECT
ProductCode, RowNum, BOMCode, LevelCode,
'深度过大' AS ErrorType,
'层级深度: ' + CAST(LevelDepth AS VARCHAR(10)) + ' 超过最大深度限制(5级), 层级码: ' + LevelCode AS ErrorMessage,
2 AS Severity,
ParentCode AS 父层级码,
LevelDepth AS 当前深度,
'深度检查' AS 检查点,
LevelCode AS 实际编码
FROM #TempData
WHERE LevelDepth > 5
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个深度过大错误'
-- 14. 检查根节点
PRINT '6.9. 检查根节点...';
;WITH MissingRoot AS (
SELECT DISTINCT
t.ProductCode
FROM #TempData t
WHERE NOT EXISTS (
SELECT 1 FROM #TempData r
WHERE r.ProductCode = t.ProductCode
AND r.LevelCode = '0'
)
)
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 当前深度, 检查点, 建议的层级码
)
SELECT
mr.ProductCode, NULL, NULL, '0',
'缺失根节点' AS ErrorType,
'缺少根节点(层级码应为0), 总成产品编码: ' + mr.ProductCode AS ErrorMessage,
2 AS Severity,
1 AS 当前深度,
'结构完整性检查' AS 检查点,
'0' AS 期望的编码
FROM MissingRoot mr
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
PRINT ' 发现 ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' 个根节点错误'
-- 15. 计算检查总时间
DECLARE @EndTime DATETIME = GETDATE()
DECLARE @Duration INT = DATEDIFF(SECOND, @StartTime, @EndTime)
-- 16. 插入总结
INSERT INTO [dbo].[BOM_LevelCode_Check_Results] (
总成产品编码, 序号, BOM编辑单号, 层级码, ErrorType, ErrorMessage, Severity, 检查点
)
VALUES (
'SYSTEM',
0,
'检查总结',
'0',
'检查统计',
'本次检查共扫描' + CAST(@TotalRecords AS VARCHAR(10)) + '条记录，发现' + CAST(@ErrorCount AS VARCHAR(10)) + '个问题，耗时' + CAST(@Duration AS VARCHAR(10)) + '秒',
1,
'检查总结'
)
-- 17. 输出结果
PRINT ''
PRINT '======= 检查完成 ======='
PRINT '开始时间: ' + CONVERT(VARCHAR(19), @StartTime, 120)
PRINT '结束时间: ' + CONVERT(VARCHAR(19), @EndTime, 120)
PRINT '总耗时: ' + CAST(@Duration AS VARCHAR(10)) + ' 秒'
PRINT '总记录数: ' + CAST(@TotalRecords AS VARCHAR(10)) + ' 条'
PRINT '发现问题: ' + CAST(@ErrorCount AS VARCHAR(10)) + ' 个'
PRINT '========================================'
-- 18. 返回错误统计
SELECT
'检查统计' AS 统计类型,
'总记录数' AS 项目,
CAST(@TotalRecords AS VARCHAR(10)) AS 数量
UNION ALL
SELECT
'检查统计',
'发现问题数',
CAST(@ErrorCount AS VARCHAR(10))
UNION ALL
SELECT
'检查统计',
'耗时(秒)',
CAST(@Duration AS VARCHAR(10))
-- 19. 返回各类型错误数量
SELECT
ErrorType AS 错误类型,
COUNT(*) AS 数量,
CAST(COUNT(*) * 100.0 / @ErrorCount AS DECIMAL(5,1)) AS 占比百分比
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckID = (SELECT MAX(CheckID) FROM [dbo].[BOM_LevelCode_Check_Results])
GROUP BY ErrorType
ORDER BY COUNT(*) DESC
-- 20. 清理临时表
DROP TABLE #TempData
END
GO
PRINT '✓ 创建BOM层级码检查存储过程: usp_Check_BOM_LevelCode'
GO
-- 4. 创建快捷检查存储过程
CREATE OR ALTER PROCEDURE [dbo].[usp_QuickCheck_BOM_LevelCode]
@参数 NVARCHAR(200) -- 格式: "BOM单号;成品编码;是否清除历史(0/1)"
AS
BEGIN
SET NOCOUNT ON
PRINT '======= 快速检查 ======='
EXEC [dbo].[usp_Check_BOM_LevelCode] @参数
PRINT ''
PRINT '======= 详细信息 ======='
PRINT '详细结果查询:'
PRINT 'SELECT TOP 100 * FROM [dbo].[BOM_LevelCode_Check_Results] WHERE ErrorType <> ''检查统计'' ORDER BY CheckTime DESC, Severity, 总成产品编码, 序号'
END
GO
PRINT '✓ 创建快捷检查存储过程: usp_QuickCheck_BOM_LevelCode'
GO
-- 5. 创建结果查询视图
CREATE OR ALTER VIEW [dbo].[vw_BOM_Check_Results]
AS
SELECT
CheckID,
CheckTime,
总成产品编码,
序号,
BOM编辑单号,
层级码,
ErrorType AS 错误类型,
ErrorMessage AS 错误信息,
CASE Severity
WHEN 1 THEN '低'
WHEN 2 THEN '中'
WHEN 3 THEN '高'
ELSE '未知'
END AS 严重程度,
父层级码,
当前深度 AS 层级深度,
父节点是否存在,
建议的层级码 AS 期望编码,
检查点
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE ErrorType <> '检查统计'
GO
PRINT '✓ 创建BOM检查结果视图: vw_BOM_Check_Results'
GO
-- 6. 创建历史数据清理过程
CREATE OR ALTER PROCEDURE [dbo].[usp_Clear_Check_History]
@DaysToKeep INT = 30
AS
BEGIN
SET NOCOUNT ON
DECLARE @Count INT
DECLARE @MinCheckTime DATETIME = DATEADD(DAY, -@DaysToKeep, GETDATE())
DELETE FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime < @MinCheckTime
SET @Count = @@ROWCOUNT
SELECT
'清理结果' AS 信息,
'清理的记录数' AS 项目,
CAST(@Count AS VARCHAR(10)) AS 数量
UNION ALL
SELECT
'清理结果',
'保留天数',
CAST(@DaysToKeep AS VARCHAR(10))
UNION ALL
SELECT
'清理结果',
'清理前的日期',
CONVERT(VARCHAR(20), @MinCheckTime, 120)
PRINT '已清理 ' + CAST(@Count AS VARCHAR(10)) + ' 条' + CAST(@DaysToKeep AS VARCHAR(10)) + '天前的历史记录'
END
GO
PRINT '✓ 创建历史数据清理存储过程: usp_Clear_Check_History'
GO
-- 7. 创建结果汇总存储过程
CREATE OR ALTER PROCEDURE [dbo].[usp_Get_Check_Summary]
@最近天数 INT = 7
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartTime DATETIME = DATEADD(DAY, -@最近天数, GETDATE())
SELECT
'总体统计' AS 类别,
'总检查次数' AS 项目,
COUNT(DISTINCT CheckID) AS 数值
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime >= @StartTime
AND ErrorType = '检查统计'
UNION ALL
SELECT
'总体统计',
'总检查记录数',
SUM(CAST(REPLACE(REPLACE(ErrorMessage, '本次检查共扫描', ''), '条记录，发现', '') AS INT))
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime >= @StartTime
AND ErrorType = '检查统计'
UNION ALL
SELECT
'总体统计',
'总发现问题数',
SUM(CAST(SUBSTRING(ErrorMessage, CHARINDEX('发现', ErrorMessage) + 2, CHARINDEX('个问题', ErrorMessage) - CHARINDEX('发现', ErrorMessage) - 2) AS INT))
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime >= @StartTime
AND ErrorType = '检查统计'
-- 错误类型统计
SELECT
ErrorType AS 错误类型,
COUNT(*) AS 数量,
CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(5,1)) AS 占比百分比
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime >= @StartTime
AND ErrorType <> '检查统计'
GROUP BY ErrorType
ORDER BY COUNT(*) DESC
-- 最近检查记录
SELECT TOP 10
CheckTime AS 检查时间,
总成产品编码,
ErrorType AS 错误类型,
ErrorMessage AS 错误信息,
层级码,
父层级码,
当前深度
FROM [dbo].[BOM_LevelCode_Check_Results]
WHERE CheckTime >= @StartTime
AND ErrorType <> '检查统计'
ORDER BY CheckTime DESC, Severity
END
GO
PRINT '✓ 创建结果汇总存储过程: usp_Get_Check_Summary'
GO
-- 8. 使用示例
PRINT ''
PRINT '======= 使用说明 ======='
PRINT ''
PRINT '1. 基本检查:'
PRINT ' EXEC [dbo].[usp_Check_BOM_LevelCode] ''BOM单号;成品编码;是否清除历史(0/1)'' '
PRINT ''
PRINT '2. 示例调用:'
PRINT ' -- 检查指定BOM单号，不清除历史'
PRINT ' EXEC [dbo].[usp_QuickCheck_BOM_LevelCode] ''DJ0001;;0'' '
PRINT ''
PRINT ' -- 检查指定BOM单号和成品编码，不清除历史'
PRINT ' EXEC [dbo].[usp_QuickCheck_BOM_LevelCode] ''DJ0001;CP01;0'' '
PRINT ''
PRINT ' -- 检查指定BOM单号，清除历史'
PRINT ' EXEC [dbo].[usp_QuickCheck_BOM_LevelCode] ''DJ0001;;1'' '
PRINT ''
PRINT '3. 查询结果:'
PRINT ' SELECT * FROM [dbo].[vw_BOM_Check_Results] '
PRINT ' SELECT * FROM [dbo].[BOM_LevelCode_Check_Results] '
PRINT ''
PRINT '4. 清理历史:'
PRINT ' EXEC [dbo].[usp_Clear_Check_History] 30 '
PRINT ''
PRINT '5. 查看统计:'
PRINT ' EXEC [dbo].[usp_Get_Check_Summary] 7 '
PRINT ''
PRINT '======= 功能说明 ======='
PRINT '1. 支持点(.)分隔的层级码格式检查'
PRINT '2. 支持参数过滤: BOM单号, 成品编码, 是否清除历史'
PRINT '3. 检查类型: 格式, 数字, 重复, 父节点, 连续性, 深度, 根节点等'
PRINT '4. 错误等级: 1=低, 2=中, 3=高'
PRINT '5. 结果保存在: BOM_LevelCode_Check_Results 表'
PRINT '6. 通过视图查看: vw_BOM_Check_Results'
PRINT ''
PRINT '======= 层级码示例 ======='
PRINT '正确格式: 0, 1, 1.1, 1.2, 1.2.1, 1.2.1.1'
PRINT '错误格式: .1, 1., 1..2, 1.2a, 1.-2, 1.2.1.1.1.1 (深度过大)'
PRINT ''
PRINT '======= 部署完成 ======='
GO
完整方案已创建完成，包括以下组件：
1. 结果表 - BOM_LevelCode_Check_Results
2. 主存储过程 - usp_Check_BOM_LevelCode
3. 辅助函数 - fn_SplitString
4. 快捷调用 - usp_QuickCheck_BOM_LevelCode
5. 结果视图 - vw_BOM_Check_Results
6. 清理过程 - usp_Clear_Check_History
7. 统计查询 - usp_Get_Check_Summary
功能特性：
• ✅ 支持点分隔符层级码检查
• ✅ 单参数调用模式："BOM单号;成品编码;是否清除历史"
• ✅ 9种检查类型
• ✅ 详细的错误报告
• ✅ 分级错误严重程度
• ✅ 完整的错误追踪
• ✅ 历史记录管理
• ✅ 统计分析功能
使用方法：
-- 示例调用
EXEC [dbo].[usp_QuickCheck_BOM_LevelCode] 'BOMZB2025000004;;0'
-- 查看结果
SELECT * FROM [dbo].[vw_BOM_Check_Results]
ORDER BY 严重程度 DESC, 检查时间 DESC
-- 查看统计
EXEC [dbo].[usp_Get_Check_Summary] 7
-- 清理历史
EXEC [dbo].[usp_Clear_Check_History] 30
此方案可以有效地检查BOM层级码的正确性，确保数据质量。
