如何在sql中编写游标来比较两个表数据并从一个记录中找到fifocost [英] How to write a cursor in sql to compare two table data and finding fifocost from one by one record

问题描述

我想写一个光标来比较两个表数据并找到一个fifo成本。



我已经为光标编写了这个查询但是没有得到正确的成本。

Hi,

I want to write a cursor to compare two tables data and find a fifo cost.

I have written this query for a cursor but it is not getting correct cost.

USE [RISE_DB]
GO
/****** Object:  StoredProcedure [dbo].[SP_GETFIFOCOST]    Script Date: 09/11/2015 14:21:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
Begin
  Declare @qty decimal(18,2),@cost decimal(18,2),@line int
  Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
  Declare MyCur Cursor for 
    select QTY_IN,UNIT_COST,LINE 
    from STOCKDETAILS  
    where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') 
    and PRODUCT_NO=@pid order   by STOCK_DATE
  Declare @fqty decimal(18,2)
  Open MyCur
  Fetch next from mycur into @qty,@cost,@line
  While @@Fetch_Status=0
  Begin
    Declare MyCur1 Cursor for 
    select QTY_IN,UNIT_COST,LINE 
    from STOCKDETAILS  
    where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') 
    and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur1
    Fetch next from mycur1 into @qty1,@cost1,@line1
    While @@Fetch_Status=0
    Begin
      if @qty>=@qty1 
        set @qty=@qty-@qty1
      set @fqty=@fqty+@qty
      if @qty<@qty1 
        set @qty=@qty-@qty1
      set @fqty=@fqty+@qty-@qty1
      Fetch Next From MyCUr into @qty,@cost,@line
      Fetch Next From MyCUr1 into @qty1,@cost1,@line1
      while @fqty=0
      Begin
        update STOCKDETAILS 
        set FIFO_COST=@cost 
        where LINE=@line1
      End
    End
    Close Mycur
    Close Mycur1
    Deallocate Mycur
    Deallocate Mycur1
  End
End

Exec SP_GETFIFOCOST'F1'



我在前端写了相同的逻辑这样

Exec SP_GETFIFOCOST 'F1'

I have written same logic at the front end like this

b = table2.Rows.Count - 1;
                   try
                   {
                       for (a = table1.Rows.Count - 1; a >= 0; a--)
                       {
                           total();
                           while (fqty != 0 && fqty >= 0)
                           {
                               if (b >= 0)
                               {
                                   b = b - 1;
                                   total();
                               }
                               else
                               {
                                   fqty = 0;
                               }
                           }
                       }
                   }
                   catch (Exception EX) { }

void total()
       {
           try
           {
               fout = Convert.ToDecimal(table2.Rows[b]["QTY_OUT"].ToString());
           }
           catch (Exception EX) { fout = 0; }
           try
           {
               fin = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString());
           }
           catch (Exception EX) { fin = 0; }
           if (fin >= fout)
           {
               fqty = fin - fout;
               table1.Rows[a]["QTY_IN"] = fqty;
               try
               {
                   string fc = table1.Rows[a]["UNIT_COST"].ToString();
                   string sn = table2.Rows[b]["LINE"].ToString();
                   TRcmd.Connection = cn;
                   TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
                   TRcmd.CommandType = CommandType.Text;
                   TRcmd.ExecuteNonQuery();
               }
               catch (Exception EX) { }
               if (fqty == 0)
               {
                   b = b - 1;
               }

           }
           else
           {
               fqty = 0;
               table1.Rows[a]["QTY_IN"] = fqty;
               try
               {
                   string fc = table1.Rows[a]["UNIT_COST"].ToString();
                   string sn = table2.Rows[b]["LINE"].ToString();
                   TRcmd.Connection = cn;
                   TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
                   TRcmd.CommandType = CommandType.Text;
                   TRcmd.ExecuteNonQuery();
               }
               catch (Exception EX) { }
               a = a - 1;
               try
               {
                   table1.Rows[a]["QTY_IN"] = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString()) + (fin - fout);
               }
               catch (Exception EX) { }
               a = a + 1;
               b = b - 1;
           }

       }

请问我帮

Would u pls help me

推荐答案

我已经对sql进行了代码标记并添加了缩进。



这总是很好的做法，因为A：我们的眼睛很差，B：你可以发现像if语句一样的错误：


当前：

I have code-tagged the sql and added indents.

This is always good practice because A: our poor eyes and B: you can spot mistakes like your if statements:

Current:

if @qty>=@qty1
  set @qty=@qty-@qty1
set @fqty=@fqty+@qty
if @qty<@qty1
  set @qty=@qty-@qty1
set @fqty=@fqty+@qty-@qty1

我假设您在使用它之前不打算设置@fqty两次：

修正：

I assume you do not intend to set @fqty twice before using it so:
Fixed:

if @qty>=@qty1
BEGIN
  set @qty=@qty-@qty1
  set @fqty=@fqty+@qty
END
if @qty<@qty1
BEGIN
  set @qty=@qty-@qty1
  set @fqty=@fqty+@qty-@qty1
END

与C＃一样，没有语法包装，'if'语句仅适用于下一行。



我希望有所帮助^ _ ^

Andy





PS：这就是我发现的。我所知道的可能存在逻辑错误，但这个场景太复杂了，我无法复制测试。







更新：



我一直在玩C＃版本。一旦我可以简化它，那么光标将更容易编写。



这是我到目前为止：

Just as with C#, without a syntax wrapper an 'if' statement only applies to the next line.

I hope that helps ^_^
Andy


PS: This was just what I spotted. There may be logical errors for all I know but the scenario is too complex for me to replicate tests.



UPDATE:

I have been playing with the C# version first. Once I can simplify this down then the cursor will be much easier to write.

Here is what I have so far:

class Class1
{

    public DataTable Table1;
    public DataTable Table2;
    private decimal _quantity;
    private int _a, _b;

    public SqlConnection Cn;
    public SqlCommand Rcmd;

    public void Test()
    {
        _b = Table2.Rows.Count - 1;
        for (_a = Table1.Rows.Count - 1; _a >= 0; _a--)
        {
            Total();
            while (_quantity > 0)
            {
                if (_b >= 0)
                {
                    _b--;
                    Total();
                }
                else
                    _quantity = 0;
            }
        }
    }
    void Total()
    {
        decimal dIn, dOut;

        decimal.TryParse(Table2.Rows[_b]["QTY_OUT"].ToString(), out dOut);
        decimal.TryParse(Table2.Rows[_b]["QTY_IN"].ToString(), out dIn);

        Table1.Rows[_a]["QTY_IN"] = _quantity;

        UpdateStock(Table1.Rows[_a]["UNIT_COST"].ToString(), Table2.Rows[_b]["LINE"].ToString());

        if (dIn >= dOut)
            _quantity = dIn - dOut;
        else
        {
            _quantity = 0;
            Table1.Rows[_a - 1]["QTY_IN"] = Convert.ToDecimal(Table1.Rows[_a - 1]["QTY_IN"].ToString()) + (dIn - dOut);
        }
        if (_quantity == 0)
            _b--;
    }
    private void UpdateStock(string unitCost, string line)
    {
        Rcmd.Connection = Cn;
        Rcmd.CommandText = "update STOCKDETAILS set FIFO_COST=@UnitPrice where LINE=@Line";
        Rcmd.CommandType = CommandType.Text;

        Rcmd.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.VarChar) { Value = unitCost });
        Rcmd.Parameters.Add(new SqlParameter("@Line", SqlDbType.VarChar) { Value = line });

        Rcmd.ExecuteNonQuery();
    }
}

这与您的代码完全相同。注意我已经重构了UpdateStock进程并且为了简单起见摆脱了所有的try / catch块。



我想进一步简化这一点：对Table1的更新不出现在光标中。为了简化游标，我可以摆脱所有的DataTable更新吗？

This is precisely the same as your code. Notice I have refactored the UpdateStock process and got rid of all of the try/catch blocks for simplicity.

I want to simplify this further: The updates to Table1 do not appear in your cursor. For the purposes of simplifying for the cursor, can I get rid of all of the DataTable updates?

查找FIFO成本的代码

Code to Find FIFO COST

<pre lang="SQL"><pre lang="SQL"><pre lang="SQL">

USE [RISE_DB]

GO

/ ******对象：StoredProcedure [dbo]。[SP_GETFIFOCOST]脚本日期：09/15/2015 15:34:11 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

更改程序[dbo] 。[SP_GETFIFOCOST]（@ pid nvarchar（250））as

开始

声明@qty十进制（18,2），@ cost decimal（18,2），@ line int

声明@ qty1十进制（18,2），@ cost1十进制（18,2），@ line1 int

声明@fqty十进制（18,2）

设置@ fqty = 0

声明MyCur1光标，从STOCKDETAILS中选择QTY_OUT，UNIT_COST，LINE，其中STOCK_JR（'OU'，'IS'，'SJ'，'TR'，'SA'，'CS'，'AS'，'PR'， 'DO'）和PRODUCT_NO = @ pid order by STOCK_DATE

Open MyCur1

声明MyCur Cursor选择QTY_IN，UNIT_COST，LINE来自STOCKDETAILS，其中STOCK_JR位于（'IN'， 'GR'，'TR'，'SA'，'OS'，'SR'，'AS'，'OG'，'PO'）和PRODUCT_NO = @ pid order by STOCK_DATE

Open MyCur

如果@ fqty = 0

开始

从MyCur1获取Next到@ qty1，@ cost1，@ line1

从MyCur获取Next到@ qty，@ cost，@ line

结束

而@@ Fetch_Status = 0

开始

而@@ Fetch_Status = 0

开始

if @ qty> @ qty1

begin

设置@qty = @qty- @ qty1

更新STOCKDETAILS设置FIFO_COST = @ cost其中LINE = @ line1

设置@ fqty = @ fqty + @ qty

if @ fqty> 0

begin

Fetch Next从MyCur1到@ qty1，@ cost1，@ line1

set @ fqty = @ qty- @ qty1

update STOCKDETAILS set FIFO_COST = @ cost其中LINE = @ line1

结束

结束

if @ qty< @ qty1

begin

set @fqty = @ qty- @ qty1

更新STOCKDETAILS设置FIFO_COST = @ cost其中LINE = @ line1

if @ fqty< 0

begin

从MyCur获取下一个到@ qty，@ cost，@ line

从MyCur1获取Next到@ qty1，@ cost1，@ line1

set @ qty1 = @ fqty- @ qty1

set @ fqty = @ qty + @ qty1

if @ fqty> 0

begin

set @ qty = @ qty + @ qty1

end

update STOCKDETAILS set FIFO_COST = @ cost where LINE = @ line1

end

结束

如果@ fqty = 0

开始

从MyCur1获取下一个到@ qty1，@ cost1，@第1行

从MyCur获取下一个到@ qty，@ cost，@ line

结束

结束

结束

关闭MyCur1

关闭MyCur

Deallocate MyCur1

Deallocate MyCur

结束

USE [RISE_DB]
GO
/****** Object: StoredProcedure [dbo].[SP_GETFIFOCOST] Script Date: 09/15/2015 15:34:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
Begin
Declare @qty decimal(18,2),@cost decimal(18,2),@line int
Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
Declare @fqty decimal(18,2)
set @fqty=0
Declare MyCur1 Cursor for select QTY_OUT,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur1
Declare MyCur Cursor for select QTY_IN,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur
if @fqty=0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
While @@Fetch_Status=0
Begin
While @@Fetch_Status=0
Begin
if @qty>@qty1
begin
set @qty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
set @fqty=@fqty+@qty
if @fqty>0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @qty<@qty1
begin
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
if @fqty<0
begin
Fetch Next From MyCur into @qty,@cost,@line
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @qty1=@fqty-@qty1
set @fqty=@qty+@qty1
if @fqty>0
begin
set @qty=@qty+@qty1
end
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @fqty=0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
End
end
Close MyCur1
Close MyCur
Deallocate MyCur1
Deallocate MyCur
End

这篇关于如何在sql中编写游标来比较两个表数据并从一个记录中找到fifocost的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！