- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
MS SQLserver [MSSQL] 저장 프로시저 만들기 실습
2020.01.26 00:51
[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 |
출처: https://ehclub.co.kr/2100 [언제나 휴일]
[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 |
출처: https://ehclub.co.kr/2100 [언제나 휴일]
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
