SQL存储过程展易飞BOM

来源：互联网发布：淘宝展现词为什么是0 编辑：程序博客网时间：2024/05/24 01:46

create TABLE ZBOMCB1
(
--BOM展开表算表
CB001 CHAR(20) NOT NULL,
CB005 CHAR(20) NOT NULL,
CB008 numeric(16) NOT NULL
)
CREATE PROCEDURE ZGetBOM3Level
as
       --使用游标展算BOM插ZBOMCB1
delete from ZBOMCB1
declare @cb001 char(60),@cb002 char(60)--,@MQ003 CHAR(23),@LA011 int--,@cb001_P char (60);
--INSERT INTO ZBOMCB1 SELECT CB001,CB005,CB008 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001='211-001-3003-03-02' AND MB025='P' AND CB022='Y'
begin
     declare my CURSOR --定义游标
   --获取游标集合
--for (select a.MQ003, a.LA001,a.LA011 from zfunTable('20150301','20160229') a left join INVMB b on a.LA001=b.MB001 where MB109='Y'AND MB025 !='P')
--for(SELECT CB001,CB005,CB008 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001='211-001-3003-03-02' AND MB025='M' AND CB022='Y' AND MB109='Y' )
for(SELECT MB001 FROM INVMB WHERE MB025='M' AND MB109='Y' )
open my--打开游标
fetch next from my into @cb002--读取第一行数据赋值给变量
   while @@FETCH_STATUS=0
begin
--插入BOM一阶
INSERT INTO ZBOMCB1 SELECT @cb002,CB005,CB008 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb002 AND MB025='P'
          begin --开始BOM二阶
               if(exists(SELECT CB005 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb002 AND MB025='M'))
               declare @cb001_1 char(60)
               declare my1 CURSOR
               FOR( SELECT CB005 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb002 AND MB025='M')
               open my1
               fetch next from my1 into @cb001_1
               while @@FETCH_STATUS=0
                    begin
                      --插入BOM二阶
                        INSERT INTO ZBOMCB1 SELECT @cb002,CB005,CB008 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb001_1 AND MB025='P'
                                   begin --开始循环三阶
                                       if(exists(SELECT CB005 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb001_1 AND MB025='M'))
                                       declare @cb001_2 char(60)
                                       declare my2 CURSOR
                                       FOR( SELECT CB005 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb001_1 AND MB025='M')
                                       open my2
                                       fetch next from my2 into @cb001_2
                                       while @@FETCH_STATUS=0
                                          begin
                                          --开始插入三阶
                                               INSERT INTO ZBOMCB1 SELECT @cb002,CB005,CB008 FROM BOMCB LEFT JOIN INVMB ON MB001=CB005 WHERE CB001=@cb001_2 AND MB025='P'
                                                fetch next from my2 into @cb001_2
                                           end
                                        close my2
                                        deallocate my2
                                     end
                         fetch next from my1 into @cb001_1
                    end
               close my1
               deallocate my1
            end
   fetch next from my into @cb002
end
      close my --关闭游标
      deallocate my --释放游标
      end
      go

0 0