SQL_SERVER存储过程笔记

https://www.cnblogs.com/selene/p/4483612.html

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

--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 

带参数的存储过程

create  proc P_Titles_ByTitleID_SelectPrice2
@title_id  varchar (6),  --入参
@price money  output    --出参【出参加标识（output）】
as
select  @price=price  from  titles  where
title_id=@title_id
--出参的@在=左边
 
go
--1，先声明变量
declare  @price2 money
--2，之后在调用
exec  P_Titles_ByTitleID_SelectPrice2 
@title_id= 'BU1032' ,
@price=@price2  output
--3，再之后在查声明变量
select  @price2
--出参要声明，配参后面要加output标识，之后再查声明变量。