• sql server 存储过程递归,寻找叶子


    USE [DB1]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_JDE_FetchBomDetail]    Script Date: 07/28/2011 09:47:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_JDE_FetchBomDetail](
        @ParentItem nvarchar(30),
        @ParentQty real,
        @OrignItem nvarchar(30), 
        @isLastLeaf int output) AS
    BEGIN
       
        DECLARE @tmpVar int
        DECLARE @strParentItem nvarchar(30)
        DECLARE @p_item nvarchar(30)
        DECLARE @p_uom nvarchar(30)
        DECLARE @s_itm real
        DECLARE @s_litm nvarchar(30)
        DECLARE @s_atim nvarchar(30)
        DECLARE @s_uom nvarchar(10)
        DECLARE @s_qty real
        DECLARE @p_OUom nvarchar(10)
        DECLARE @S_Oum nvarchar(10)
        DECLARE @isLastLeaf2 int
       
        SET @tmpVar = 0;
        SET @isLastLeaf =0;
        SET @strParentItem = @ParentItem;
       
        DECLARE @rowcount AS int

        DECLARE mycur2 CURSOR Local FOR
       
            select ixkitl, cpuom, ixitm, ixlitm, ixaitm, csum, isnull(csqty,0), ixuom, ixum
            from costbom_Z1 where rtrim(ltrim(ixkitl)) =rtrim(ltrim(@strParentItem));
           
        OPEN mycur2;

        FETCH NEXT FROM mycur2
        INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
       
    --PRINT 'Count:  '+cast(@ROWCOUNT AS nvarchar(20))

        IF @@FETCH_STATUS <>0
        BEGIN
            SET @isLastLeaf=1
           
            CLOSE mycur2
            DEALLOCATE mycur2
           
            RETURN
        END
       
        ELSE BEGIN
    --PRINT 'isLeastLeaf21:'+cast(isnull(@isLastLeaf2,'') AS nvarchar(20))   
            WHILE @@FETCH_STATUS = 0
            BEGIN
                DECLARE @qty real
                SET @qty=@s_qty*@ParentQty
               
                EXEC dbo.sp_JDE_FetchBomDetail @s_litm,@qty, @OrignItem, @isLastLeaf2 output;
               
    --PRINT 'isLeastLeaf22:'+cast(@isLastLeaf2 AS nvarchar(20))   
           
                IF @isLastLeaf2=1
                BEGIN
                    insert into  costbom_z3 (ixkitl, ixitm, ixlitm, ixaitm,csum, csqty, cpuom, ixum, ixuom )
                    values(@OrignItem,  @s_itm, @s_litm, @s_atim , @s_uom , @s_qty*@ParentQty, @p_uom, @s_Oum, @P_Ouom);
                END
               
                FETCH NEXT FROM mycur2
                INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
            END
        END

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Factorial_ap](@Number Int,@RetVal Int OUTPUT)AS
    begin
    DECLARE @In Int
    DECLARE @Out Int
    IF @Number <> 1 BEGIN
        set @In = @Number-1
        EXEC Factorial_ap @In, @Out OUTPUT
        SELECT @RetVal = @Number * @Out
    END
    ELSE BEGIN
        SELECT @RetVal = 1
    END
    print @RetVal
    RETURN
    end
    -- exec dbo.[Factorial_ap] 5,''
        CLOSE mycur2;
        DEALLOCATE mycur2; 


    END

  • 相关阅读:
    基于K-means聚类算法的图像分割 和 基于机器学习的图像二元分类
    .off文件三维数据读取并显示
    滑动窗口&Region Proposal&Selective Search&KMeans&二元分类(空间分割)
    ENVI+IDL遥感图像处理
    OpenCV与图像分割 边界检测
    SublimeText3追踪函数工具CTags设置及使用
    岗位要求
    bash shell脚本如何获取脚本所在目录
    mongodb获取到的可能已经沦为肉鸡的云服务器地址
    wireshark查看包显示:Packet size limitedduring capture
  • 原文地址:https://www.cnblogs.com/songrun/p/2119171.html
Copyright © 2020-2023  润新知