220224 MSSQL 프로시저 01

stored procedure : 스크립트를 서버에 저장해 놓은 것.

 

1.

1)  프로시저 생성하기

 

1. 이름은 동작을 설명하는 이름을 붙이되 주로 Get, Cal, Update 같은 동사로 이름을 붙인다.

2. 주로 SP_접두 를 붙인다.

3.  호환성을 고려하여 IS 보단 AS 를 적는다!

3. END 에도 프로시저 이름을 적어줄 수 있다 : 기능 요소 X. 여러 프로시저를 정의할 때 구분요소.

 

1. 기본형

CREATE PROCEDURE 이름(인수목록)
AS 또는 IS 
	변수선언
BEGIN
	본체
END [이름];

 

 

---

2. MSSQL

1) 프로시저 생성

- 가변길이 타입은 char로만 쓸수는 없고 char(10) 으로 길이를 다 밝혀야 한다.

- 변수에 대입할 필요없이 select 문으로 바로 출력할 수 있다.

create procedure proc_getCityPopu
@p_name char(10)
as
begin
	select popu from tCity where name = @p_name
end

2)

프로시저 호출 : EXECUTE 는 EXEC로 줄일수 있다.

-- MSSQL 아래 3구문 모두 가능. 

EXEC PROC_GetCityPopu @p_name ='서울';

EXEC PROC_GetCityPopu '서울';

RPOC_GetCityPopu '서울';

 

3)

결과 : 

 

 

프로시저 클릭으로 조회하기

1)

2)

 

 

프로시저 수정하기

- 원론 : drop procedure 명령으로 삭제한 뒤 새로 만드는 것

- 공식 : alter procedure > SQL SERVER 에서만 가능.

alter procedure proc_getCityPopu
	@p_name char(10)
as
	declare
		@popu int
begin
	select @popu = popu from tCity where name = @p_name;
	print @popu
end

 

 

인수와 리턴

tMember 테이블

create procedure PROC_GrantBonus
 @p_member char(20)
 @p_bonus int = 100
as
	begin
		update tMember set money  = money + @p_bonus where member = @p_member;
	end;

- 두 개 의 인수를 받는다.

- p_bonus 는 디폴트값이 100

- 두 인수를 순서대로 다 전달

- 뒤쪽 인수는 생략해도 디폴트값 100이 적용된다.

 

▶ 순서대로 전달

PROC_GrantBonus '춘향',2000;

▶ 뒤쪽 인수를 생략시, 디폴트 값 전달

PROC_GrantBonus '춘향';

 

▶ 인수의 이름을 밝히면 순서에 상관없이 전달이 가능하다.

[dbo].[proc_GrantBonus] @p_member ='춘향', @p_bonus=2000;

 

 

▶ 출력용 인수 뒤에는 output 키워드를 붙인다.

create procedure proc_OutCityPopu
	@p_name char(10),
	@p_popu int output
as
begin
	select @p_popu = popu from tCity where name = @p_name;
end;

▶ 호출할 때는 변수를 선언하여 출력용 인수로 전달하되 output 키워드를 밝힌다.

declare @popu int;
execute proc_OutCityPopu '서울', @popu output;
print '서울의 인구는' + cast(@popu as varchar(10)) + '만명입니다';

 

▶ 정수값 하나를 리턴할 때는 return 문이 간편하다.

- 프로시저의 나머지 명령을 무시하고 즉시 종료.

- 정수값 하나를 리턴 (정수만 리턴 가능)

- 결과값 리턴보다는 에러 처리용으로 사용 (에러코드를 정의하고 에러상황시 코드를 리턴)

create procedure proc_RetCityPopu
	@p_name char(10)
as
declare
	@popu int;
begin
	select @popu = popu from tCity where name= @p_name;
	return @popu;
end;

▶ 호출. 프로시저의 실행문 자체를 변수에 대입하여 사용.

declare @popu int;
execute @popu = proc_RetCityPopu '서울';
print '서울의 인구는'+ cast(@popu as varchar(10)) +'만명입니다';

 

▶ 에러 발생시 메시지를 출력하자.

@@ERROR 시스템 함수는 최후 실행한 쿼리의 결과값이며 성공시는 0.

오류 발생시는 에러 번호값을 가진다.

CREATE procedure [dbo].[proc_InsertSeoul]
as 
begin
	insert into tCity values('서울',605,974,'y','경기');
	if @@error != 0
	begin
		print('새 레코드를 삽입하지 못했습니다');
	end
end
GO

---

출처 : 김상형의 SQL 정복