SQL SERVER 2008 存储过程使用记录 游标

USE [LH]--数据库名称
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[SGMRAA123]--起的存储过程名称
AS
DECLARE @j int
DECLARE @SqlStr NVARCHAR(MAX) --最终拼接执行的sql语句
BEGIN
SET @j=0;
--创建一个表#A存储需要的工单数据
CREATE TABLE #A(
idx int IDENTITY(1,1),
RAA001 NVARCHAR(100),
RAB003 NVARCHAR(100),
RAB007 DECIMAL(28,6),
RAB007S DECIMAL(28,6),
)
CREATE TABLE #B(
RAA001 NVARCHAR(100),
)
--创建临时库存表
CREATE TABLE #R(
LOA001 NVARCHAR(100),--品号
KRAB DECIMAL(28,6) --库存结余
)
BEGIN
--数据插入工单表
INSERT INTO #A
SELECT RAA001,RAB003,ISNULL(RAB007,0)RAB007,ISNULL(RAB007,0)RAB007S FROM SGMRAB LEFT JOIN SGMRAA ON RAA001=RAB001 WHERE RAA024='T' AND RAA020='N' AND RAA965='T' AND RAB003 NOT LIKE '2%' AND RAA015 LIKE '3%' ORDER BY RAA001
END
--数据插入库存表
INSERT INTO #R
SELECT LOA001, SUM(ISNULL(LOA003,0))LOA003 FROM JSKLOA LEFT JOIN TPADDA ON DDA001=LOA002 WHERE DDA002='1' GROUP BY LOA001
END

DECLARE @UserId varchar(50) --定义接收值
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT idx FROM #A) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @UserId;--读取第一行数据 @UserId次数就是第一个idx
WHILE @@FETCH_STATUS = 0
BEGIN

--从表中把库存做扣减 从小到大依次减 扣库存

UPDATE #A SET RAB007S=C.A3-C.A2 FROM (
SELECT B.RAB003,case when #R.KRAB<0 then 0 else #R.KRAB end A2, ISNULL(B.RAB007,0) A3 FROM #A B left join (select * from #R where ISNULL(#R.KRAB,0)>=0)#R on #R.LOA001=B.RAB003 WHERE idx=@UserId) C WHERE #A. idx=@UserId
update #R set KRAB=ISNULL(#R.KRAB,0)-ISNULL(RAB007,0) from #A where #R.LOA001=#A.RAB003 and #A.idx=@UserId
FETCH NEXT FROM My_Cursor INTO @UserId;--读取下一行数据@UserId=1 在循环一直到idx结束
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
--删0051《=0的
delete from #A where RAB007S<=0
BEGIN
INSERT INTO #B
SELECT DISTINCT RAA001 FROM SGMRAA LEFT JOIN SGMRAB ON RAA001=RAB001 WHERE RAA024='T' AND RAA020='N' AND RAA965='T' AND RAB003 NOT LIKE '2%' AND RAA015 LIKE '3%' and RAA001 not in(select distinct RAA001 from #A)

END

--select * from #A

-- select * from #R
SET @SqlStr= 'SELECT * FROM #B'
EXEC(@SqlStr);