• 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

  • 相关阅读:
    Volatile的作用---http://www.cnblogs.com/xing901022/p/7840684.html
    基于JDBC持久化的事务管理-https://www.cnblogs.com/xing901022/p/4272420.html
    Class的isAssignableFrom方法--其他博主的博客
    深入并发二 ThreadLocal源码与内存泄漏相关分析 https://www.cnblogs.com/qmlingxin/p/9412061.html
    Beta阶段项目总结
    Alpha阶段项目总结
    Alpha版总结会议——班级派
    第二冲刺阶段——站立会议第十四天6月7日
    第二冲刺阶段——站立会议第十三天6月6日
    第二冲刺阶段——站立会议第十二天6月5日
  • 原文地址:https://www.cnblogs.com/songrun/p/2119171.html
Copyright © 2020-2023  润新知