今天需要写一个存储过程来展开某个主件料号的多阶BOM。因为BOM结构在数据库中都是单阶存储的。一想这个不就是递归的典型使用场景吗,哈哈,动手!
先码个存储过程:
CREATE PROCEDURE extract_bom
@partno varchar(100),
@level VARCHAR(100)
AS
BEGIN
declare @slave_cnt int
declare @md003 varchar(100)
declare @md002 varchar(100)
SET NOCOUNT ON
set @slave_cnt=0;
select @slave_cnt = count(*) from BOMMD
where MD001=@partno;
if(@slave_cnt>0)
begin
INSERT INTO IT_BOM_EXT ([CREATE_DATE],[MODIFIER],[MODI_DATE],[MD001],[MD002],[MD003],[MD004],[MD005],[MD006],[MD007],[MD008],[MD009],[MD010],
[MD011],[MD012],[MD013],[MD014],[MD015],[MD016],[MD017],[MD018],[MD032],SEQ,INSERTDATE)
SELECT [CREATE_DATE],[MODIFIER],[MODI_DATE],@partno as [MD001],[MD002],[MD003],[MD004],[MD005],[MD006],[MD007],[MD008],[MD009],[MD010],
[MD011],[MD012],[MD013],[MD014],[MD015],[MD016],[MD017],[MD018],[MD032],trim(@level)+ '-' + trim([MD002]),getdate()
FROM BOMMD
where MD001=@partno
end
DECLARE slave_list_cursor CURSOR FOR
SELECT MD002,MD003 FROM BOMMD
WHERE MD001=@partno ORDER BY MD002
--打开游标
OPEN slave_list_cursor
--读取第一条数据
FETCH NEXT FROM slave_list_cursor INTO @md002,@md003
--遍历查询到的表名
WHILE @@FETCH_STATUS = 0
BEGIN
set @md002=@level+@md002
--当前表则读取其信息插入到表格中
EXECUTE extract_bom @md003,@md002
--读取下一条数据
FETCH NEXT FROM slave_list_cursor INTO @md002,@md003
END
--释放游标
CLOSE slave_list_cursor
DEALLOCATE slave_list_cursor
END
GO
编译顺利通过
接着进行调用:
declare @seq varchar(100)
set @seq='-';
EXEC extract_bom @partno,@seq
GO
一运行不对啊,提示游标已存在。明明执行完毕游标都释放了啊
回头检查,发现游标在递归外面,而SQL SERVER 中游标默认是全局的。难怪出现这个错误。
查询资料,想到了办法。申明游标时 ,加上local 关键字,把游标申明为局部:
DECLARE slave_list_cursor CURSOR local FOR ... .....
再次编译,运行。OK !!