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

2020.01.26 00:51

졸리운_곰 조회 수:1667

[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 유형의 매개 변수의 값도 설정

 [그림] 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

 



출처: https://ehclub.co.kr/2100 [언제나 휴일]

 

 

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

 

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

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

 

 이번에는 상품 이름으로 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 유형의 매개 변수의 값도 설정

 [그림] 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

 



출처: https://ehclub.co.kr/2100 [언제나 휴일]

 

 

 

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
공지 오라클 기본 샘플 데이터베이스 졸리운_곰 2014.01.02 86103
공지 [SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE 가을의 곰을... 2013.02.10 78620
공지 [G_SQL] Sample Database 가을의 곰을... 2012.05.20 95334
72 [R library] library(XML) # install.packages("XML") 인스톨 에러 졸리운_곰 2023.05.06 1177
71 [R 데이터 분석] Titanic: Machine Learning from Disaster (타이타닉 생존 예측) file 졸리운_곰 2023.04.29 1178
70 [R 데이터 분석] R 유명한 패키지 정리 졸리운_곰 2023.04.24 1903
69 [R 데이터 분석] Shiny : 대시보드 배포하기 file 졸리운_곰 2023.03.19 1414
68 [stat(통계) R 언어] 유명하고 많이 사용하는 R 패키지 정리 졸리운_곰 2022.04.19 1295
67 [R 데이터 분석] anaconda에서 R 사용하기 file 졸리운_곰 2022.01.16 1307
66 [R 데이터 분석] Using C/C++ in R , R언어에서 C/C++ 사용하기 졸리운_곰 2021.11.21 1646
65 [데이터 통계분석 R언어] Shiny 웹앱 개발 졸리운_곰 2021.02.14 1388
64 How to Deploy Interactive R Apps with Shiny Server file 졸리운_곰 2021.02.13 1213
63 [통계, 데이터분석 언어 R] R Shiny Server를 사용해 보자. 졸리운_곰 2021.02.13 1415
62 통계분석툴 R & MongoDB 연동 방법 file 졸리운_곰 2020.12.12 1821
61 Reading OECD.Stat into R file 졸리운_곰 2019.04.04 1870
60 Scrape OECD Data by R lang : R언어로 OECD.net 데이터 가져오기 졸리운_곰 2019.04.04 1485
59 윈도우에서 통계 분석 프로그램 R Project 활용 방안 file 졸리운_곰 2018.12.24 1699
58 C#과 R을 연동하기 file 졸리운_곰 2018.12.24 1555
57 Use R in C# : C#에서 R 사용 졸리운_곰 2018.12.24 1548
56 R 언어_ 범주형 자료(factor) 함수 사용 file 졸리운_곰 2018.12.22 1665
55 R - 도수분포표/교차표, table( )함수 file 졸리운_곰 2018.12.22 1428
54 주피터 노트북에 R 커널 설치하기 + 아나콘다 설치 file 졸리운_곰 2018.12.21 2310
53 R기초입문_r_book_mac_v3.pdf file 졸리운_곰 2018.12.20 1587
쓰기
태그
대표 김성준 주소 : 경기 용인 분당수지 U타워 등록번호 : 142-07-27414
통신판매업 신고 : 제2012-용인수지-0185호 출판업 신고 : 수지구청 제 123호 개인정보보호최고책임자 : 김성준 sjkim70@stechstar.com
대표전화 : 010-4589-2193 [fax] 02-6280-1294 COPYRIGHT(C) stechstar.com ALL RIGHTS RESERVED