SQL过程初学，用于物料结帐

来源：互联网发布：网约车软件下载排行编辑：程序博客网时间：2024/05/23 00:06

组内有一个牛逼的DBA，所以DB方面基本上都求助于它，最近发现如果用SQL过程，可以大节省code

USE [TestIT]GO/****** Object:  StoredProcedure [dbo].[Study4]    Script Date: 10/28/2015 15:02:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Study4]ASBEGINSET NOCOUNT ON;    Declare @markname varchar(50)    Declare @partnumber varchar(50)    Declare @bond varchar(5)    Declare @reqty int    Declare @id int      if  exists (select * from MASTER.dbo.syscursors where cursor_name='markrecords') /*查找markrecords结果集，如果有就关闭*/      begin        close markrecords        deallocate markrecords         end              Declare markrecords cursor for select Req_Id,Market_Name,Part_Number,Nbonded,Req_Qty from Market_Record where Req_State=2 and  Update_Flag=0  open markrecords  /*创建markrecords结果集*/  FETCH NEXT FROM markrecords INTO @id ,@markname,@partnumber,@bond,@reqty /*第一次获取markrecords结果集记录，用于断定是否有数据*/      while @@FETCH_STATUS=0  /*循环markrecords结果集，直至结束*/     Begin                   if  exists (SELECT  Id, Market_Name, Market_Loc, Part_Number, Nbonded, Min_Qty, Max_Qty, On_Hand FROM Market_List  WHERE  Market_Name = @markname AND Part_Number = @partnumber AND Nbonded = @bond)            begin               print cast(@id as varchar(15))+@markname +@partnumber+ @bond+cast(@reqty as varchar(5)) /*更新数据*/               UPDATE  Market_List SET On_Hand =On_Hand+@reqty WHERE Market_Name=@markname and Part_Number=@partnumber and Nbonded=@bond  /*更新数据*/               UPDATE Market_Record SET Update_Flag =1 WHERE Req_Id=@id           End           FETCH NEXT FROM markrecords INTO @id ,@markname,@partnumber,@bond,@reqty   /*获取markrecords结果集记录*/     End   close markrecords deallocate markrecordsEND

0 0