问将sql server存储过程转换为MySQL(xampp)

EN

BEGIN TRAN
GO
GO
create PROC calcPro
(
@p_Date DATE 

)
AS
BEGIN

INSERT INTO calcula (PID) 
SELECT PID FROM products WHERE PID NOT IN (SELECT PID FROM calcula)

CREATE TABLE  #tmpCalc-- IF NOT EXISTs-->
(
PID INT, Date1 DATE, Date2 DATE,SaleDate DATE,[Days] INT,total_days int,depreciation NUMERIC(20,2), 
depcur_value NUMERIC(20,2),next_diffdays int,salestatus int, FLAG CHAR(1)
)
INSERT INTO #tmpCalc (PID, Date1, Date2,total_days,depreciation,SaleDate,salestatus)
SELECT A.pid, A.added_date, @p_Date, c.category_life, A.depre, sale_Date, A.sale_status
FROM products A, calcula B,categories c
WHERE A.pid=B.pid and a.cid=c.cid

UPDATE #tmpCalc SET [Days]=datediff(dd,Date1,Date2)

UPDATE #tmpCalc SET [Days]=0,next_diffdays=datediff(dd,Date1,SaleDate) where [Days] <0 
--UPDATE #tmpCalc SET next_diffdays=0 where Date1 < '2018-03-31' 

UPDATE #tmpCalc SET depcur_value=((CONVERT(numeric(20,2),depreciation)/total_days)*[Days])

--UPDATE #tmpCalc SET wtvalue=((CONVERT(numeric(20,2),b.depre)/c.category_life)*a.[Days])
--FROM #tmpCalc A, products B, categories C
--WHERE A.pid=b.pid and b.cid=c.cid

UPDATE calcula SET yEnd=A.[Days],rem_days = A.total_days - [Days],cur_depre = A.depcur_value,cur_wrvalue = A.depreciation-A.depcur_value
FROM #tmpCalc A, calcula B
WHERE A.pid=B.pid

UPDATE calcula SET yEnd=0,rem_days=0,cur_wrvalue =0 WHERE yEnd <= 0

DECLARE @SaleDt INT, @YendDt INT, @L_PID INT, @Date2 DATE, @DepValue1 NUMERIC(20,2), @DepValue2 NUMERIC(20,2), 
@DepValue NUMERIC(20,2)

SELECT @SaleDt=COUNT(*) FROM calcula WHERE sale_status='1'
SELECT @YendDt=COUNT(*) FROM calcula WHERE sale_status='0'


WHILE @SaleDt > 0
    BEGIN

        SELECT @L_PID=PID,@Date2=Date1 FROM #tmpCalc WHERE salestatus='1' AND FLAG IS NULL

        SELECT @DepValue1=A.depreciation,@DepValue2=B.cur_wrvalue FROM #tmpCalc A, calcula B WHERE A.PID=B.pid AND A.PID=@L_PID

        IF @DepValue2='0'
            BEGIN
                SET @DepValue=@DepValue1
            END
        ELSE
        IF @DepValue2!='0'
            BEGIN
                SET @DepValue=@DepValue2
            END

        IF @Date2<='2018-03-31'
            BEGIN
                SET @Date2='2018-04-01'
            END

        UPDATE #tmpCalc SET next_diffdays=datediff(dd,@Date2,SaleDate) where pid=@L_PID
        UPDATE calcula SET next_wtvalue=(@DepValue-next_depre) where pid=@L_PID

        SET @SaleDt=@SaleDt-1
        UPDATE #tmpCalc SET FLAG='Y' WHERE PID=@L_PID
        SET @L_PID=''
        SET @Date2=''
        SET @DepValue2='0'
        SET @DepValue1='0'
        SET @DepValue='0'

    END


WHILE @YendDt > 0
    BEGIN
        SELECT @L_PID=PID,@Date2=Date1 FROM #tmpCalc WHERE salestatus='0' AND FLAG IS NULL

        SELECT @DepValue1=A.depreciation,@DepValue2=B.cur_wrvalue FROM #tmpCalc A, calcula B WHERE A.PID=B.pid AND A.PID=@L_PID

        IF @DepValue2='0'
            BEGIN
                SET @DepValue=@DepValue1
            END
        ELSE
        IF @DepValue2!='0'
            BEGIN
                SET @DepValue=@DepValue2
            END

        IF @Date2<='2018-03-31'
            BEGIN
                SET @Date2='2018-04-01'
            END

        UPDATE #tmpCalc SET next_diffdays=datediff(dd,@Date2,'2019-03-31') where pid=@L_PID
        UPDATE calcula SET next_wtvalue=(@DepValue-next_depre) where pid=@L_PID

        SET @YendDt=@YendDt-1
        UPDATE #tmpCalc SET FLAG='Y' WHERE PID=@L_PID
        SET @L_PID=''
        SET @Date2=''
        SET @DepValue2='0'
        SET @DepValue1='0'
        SET @DepValue='0'
    END

UPDATE calcula SET next_depre = (A.depreciation/A.total_days) * A.next_diffdays,accumdepre=b.cur_depre+b.next_depre
FROM #tmpCalc A, calcula B
WHERE A.pid=B.pid




SELECT * FROM #tmpCalc 
--select p.pid,c.pid,p.product_name,p.depre,c.yEnd,c.rem_days,c.cur_depre from products p,calcula c where p.pid = c.pid
select * from calcula
END
GO
EXEC calcPro '2018-03-31'
GO
  --select * from calcula
  --select * from products
  --select * from categories
GO
--ROLLBACK TRAN
commit