[MSSQL] 저장 프로시저 만들기 실습

서버 탐색기를 이용한 저장 프로시저 사용하기에 이어

상품 이름으로 PID 검색 저장 프로시저

 

 이번에는 상품 이름으로 PID를 검색하는 저장 프로시저를 작성해 봅시다. 여기에서는 상품 이름을 인자로 받아서 검색한 후에 PID를 알려주어야 합니다. PID처럼 수행 결과를 알려 주려면 OUTPUT 유형의 매개 변수로 지정하세요. 참고로 여기에서는 검색한 상품이 없을 때 PID 값을 -1로 지정하는 것으로 설정하였습니다.

 

ALTER PROCEDURE dbo.FindPIDByName              (              @PNAME varchar(50),              @PID int OUTPUT              )            AS              SET @PID = -1              Select @PID = PID from Product              where PNAME = @PNAME              RETURN

 

 그리고 OUTPUT 유형의 매개 변수가 있는 저장 프로시저를 실행할 때는 저장 프로시저 실행 창에서 임의의 값을 설정하여 실행해야 출력 창에 OUTPUT 유형의 매개 변수의 변화가 나타납니다.

 [그림] OUTPUT 유형의 매개 변수의 값도 설정

 

 

고객 이름으로 CID 검색 저장 프로시저

 

ALTER PROCEDURE dbo.FindCIDByName              (              @CNAME varchar(50),              @CID int OUTPUT              ) AS              SET @CID = -1               Select @CID = CID from Custom              where CNAME = @CNAME              RETURN

 

상품 이름으로 판매 개수 확인 저장 프로시저

 

 이번에는 상품 이름으로 판매 개수를 확인하는 저장 프로시저를 작성해 봅시다.

 

 특정 상품의 판매 개수는 판매 테이블에 데이터를 저장하였습니다. 그런데 판매 테이블에는 상품 이름이 없기 때문에 먼저 상품 이름으로 PID를 얻어와야 판매 테이블에서 판매 개수를 확인할 수 있습니다. 앞에서 상품 이름으로 PID를 검색하는 저장 프로시저를 작성하였는데 여기에서는 이를 이용하는 저장 프로시저를 만들어서 사용할 것입니다.

 

 저장 프로시저에서 다른 저장 프로시저를 호출할 때는 EXEC 구문을 이용합니다.

 

EXEC [저장 프로시저 이름] [인자 리스트]

 

 EXEC 구문에 인자 리스트를 열거할 때는 출력 유형의 매개 변수는 OUTPUT 키워드를 명시해야 합니다. 그리고 저장 프로시저 내부에서 변수를 선언할 때는 DECLARE 구문을 이용하여 변수명과 형식을 명시합니다.

 

ALTER PROCEDURE dbo.GetCountByPname              (              @PNAME varchar(50),              @COUNT int OUTPUT              ) AS     DECLARE @PID int              EXEC FindPIDByName @PNAME, @PID output              SELECT @COUNT =SUM(COUNT) FROM SALE where PID = @PID              RETURN

 

상품 이름으로 상품 제거 저장 프로시저

 

 이번에는 상품 이름으로 상품을 제거하는 저장 프로시저를 만들어 봅시다. 상품 테이블의 PID는 판매 테이블의 PID와 관계가 있기 때문에 상품 테이블에 상품 데이터를 제거하기 위해서는 판매 테이블에서 해당 상품의 PID인 판매 데이터를 먼저 제거해야 합니다. 이에 PID를 인자로 받아 판매 데이터를 제거하는 저장 프로시저를 먼저 작성합시다.

 

ALTER PROCEDURE dbo.RemoveSaleByPID              (              @PID int              ) AS              delete from Sale where PID = @PID RETURN

 

 상품 제거 저장 프로시저는 입력 인자로 상품 이름을 받는데 판매 테이블에서 판매 데이터를 제거하기 위해서는 PID를 알아야 하므로 앞에서 만든 상품 이름으로 PID를 구하는 FindPIDByName 저장 프로시저를 사용해야겠죠.

 

ALTER PROCEDURE dbo.RemoveProduct (              @PNAME varchar(50),              @Result int OUTPUT ) AS declare @PID int              Exec FindPIDByName           @PNAME,@PID OUTPUT              if @PID = -1              begin                            set @Result = 0              end              else              begin                            Exec RemoveSaleByPID @PID                            delete from Product where PID = @PID                            set @Result = 1              end RETURN

 

고객 이름으로 고객 정보 제거 저장 프로시저

 

 고객 이름으로 고객 정보를 제거하는 저장 프로시저도 고객 테이블의 고객 정보를 제거하기 전에 판매 테이블에 있는 해당 고객의 CID인 판매 정보를 제거해야 합니다.

 

ALTER PROCEDURE dbo.RemoveSaleByCID              (              @CID int              ) AS              delete from Sale where CID = @CID RETURN

 

ALTER PROCEDURE dbo.RemoveCustom (              @CNAME varchar(50),              @Result int OUTPUT ) AS declare @CID int              Exec FindCIDByName           @CNAME,@CID OUTPUT              if @CID = -1              begin                            set @Result = 0              end              else              begin                            Exec RemoveSaleByCID @CID                            delete from Custom where CID = @CID                            set @Result = 1              end RETURN

  

판매 데이터 추가 저장 프로시저

 

 이번에는 판매 데이터를 추가하는 저장 프로시저를 작성해 봅시다. 판매 데이터는 PID, CID, COUNT 정보가 있어야 합니다. 그리고 이미 판매 테이블에 PID와 CID가 같은 판매 데이터가 있다면 COUNT를 추가하면 되겠죠. 따라서 먼저 판매 테이블에 PID와 CID가 같은 판매 데이터가 있는지 확인이 필요합니다.

 

Exists (Select * from SALE where PID = @PID and CID = @CID )

 

 그리고 이미 PID와 CID가 같은 판매 데이터가 있다면 COUNT 정보를 얻어와서 입력 인자로 받은 COUNT 값과 더한 값으로 변경해야 합니다. 여기에서는 현재 판매 데이터의 COUNT 값을 NOWCOUNT 변수에 얻어올게요.

 

DECLARE @NOWCOUNT int

SELECT @NOWCOUNT = COUNT from SALE where PID = @PID and CID=@CID

 

 그리고 입력 인자로 전달받은 COUNT와 NOWCOUNT의 값을 더하여 기존 판매 데이터의 COUNT 값을 변경해야겠죠.

 

SET @COUNT = @COUNT + @NOWCOUNT

UPDATE Sale SET COUNT = @COUNT where PID = @PID and CID=@CID

 

 물론, 판매 테이블에 PID와 CID가 같은 판매 데이터가 없다면 판매 테이블에 추가하면 되겠죠. 그런데 판매 테이블에 판매 데이터를 추가하려면 판매 일시도 필요한데 이는 MS SQL 내장 함수인 now를 이용하기로 할게요.

 

insert into sale values(@CID, @PID, @Count, {fn now()})

 

ALTER PROCEDURE dbo.AddSale              (              @PID int,              @CID int,              @COUNT int OUTPUT              ) AS IF Exists (Select * from SALE where PID = @PID and CID = @CID )              Begin              DECLARE @NOWCOUNT int              SELECT @NOWCOUNT = COUNT from SALE where PID = @PID and CID=@CID              SET @COUNT = @COUNT + @NOWCOUNT              UPDATE Sale SET COUNT = @COUNT where PID = @PID and CID=@CID              End else              Begin              insert into sale values(@CID, @PID, @Count, {fn now()})              End RETURN

 

 이번에는 상품 이름과 고객 이름, 판매 개수를 인자로 하는 판매 추가 저장 프로시저를 작성해 봅시다. 이미 앞에서 상품 아이디와 고객 아이디로 판매를 추가하는 저장 프로시저는 AddSale 저장 프로시저로 만들었죠. 여기에서는 상품 이름으로 상품 아이디를 얻어오고 고객 이름으로 고객 아이디를 얻어와서 AddSale 저장 프로시저를 사용합시다.

 

ALTER PROCEDURE dbo.AddSale2                    (              @PNAME varchar(50),              @CNAME varchar(50),              @COUNT int OUTPUT              )            AS              declare @PID int              declare @CID int              Exec FindCIDByName @CNAME,@CID output              IF @CID = -1                            BEGIN                            set @COUNT = -1                            RETURN                            END              Exec FindPIDByName @PNAME,@PID output              IF @PID = -1                            BEGIN                            set @COUNT = -1                            RETURN                            END              Exec AddSale @PID, @CID, @COUNT output              RETURN