SQL_SERVER存储过程笔记

-- 示例 刷新库存表中可用库存

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'updateSqty'))
    drop proc updateSqty
go


create procedure updateSqty
AS
BEGIN
	DECLARE @sNo VARCHAR(90);
	DECLARE @partNo VARCHAR(90);
	DECLARE @pno INT;
	DECLARE @fno DECIMAL(9,3);
	DECLARE @ino DECIMAL(9,3);
	DECLARE @ono DECIMAL(9,3);
	DECLARE @bono DECIMAL(9,3);
	DECLARE @uqty DECIMAL(13,3); -- 可用库存
	
	DECLARE scr CURSOR
	FOR SELECT store_no,part_no, proc_no,first_number,in_number,out_number, book_number
	FROM dbo.storage_tz_bills WHERE fiscal_month='2021.12';
	OPEN scr;
	FETCH NEXT FROM scr INTO @sNo, @partNo, @pno, @fno, @ino, @ono, @bono;
	WHILE @@fetch_status = 0
	BEGIN
		-- 计算出实际可用的库存
		SET @uqty = @fno + @ino - @ono + @bono;
		
		UPDATE dbo.storage_tz_bills
		SET usable_number = @uqty
		WHERE 
		store_no = @sNo AND
		part_no = @partNo AND
		proc_no = @pno AND
		fiscal_month = '2021.12';
		
		FETCH NEXT FROM scr INTO @sNo, @partNo, @pno, @fno, @ino, @ono, @bono;
	END
	CLOSE scr;
	DEALLOCATE scr;

END
GO


--调用,执行存储过程
exec updateSqty;

***************************************************************************************************
 1 create procedure UpdateHKUNo    --存储过程里面放置游标
 2 as
 3 begin
 4 
 5     declare UpdateHKUNoCursor cursor    --声明一个游标，查询满足条件的数据
 6         for select psn_code from person where type='E' and hku_no is null
 7     
 8     open UpdateHKUNoCursor    --打开
 9     
10     declare @noToUpdate varchar(20)    --声明一个变量，用于读取游标中的值
11         fetch next from UpdateHKUNoCursor into @noToUpdate
12     
13     while @@fetch_status=0    --循环读取
14         begin
15         --print @noToUpdate
16         update person set hku_no=dbo.GetExtUserHKUNo() where psn_code=@noToUpdate
17         fetch next from UpdateHKUNoCursor into @noToUpdate
18         end
19     
20     close UpdateHKUNoCursor    --关闭
21     
22     deallocate UpdateHKUNoCursor    --删除
23     
24 end
25