[MSSQL] 저장 프로시저(STORED PROCEDURE)

SELECT, INSERT, UPDATE 등의 일반 SQL 명령어가 순차적으로 호출되어야 하는 로직

 

* 일반 SQL 처리방식

- 각 명령별로 DBMS에 명령을 전송하고 처리된 결과를 다시 PC로 리턴 받는 과정을 반복적으로 거침

-> PC에 데이터를 전달하기 위해 매번 네트워크를 사용

-> 네트워크에 SQL코드가 노출로 인해 해커들의 표적이 될 가능성 있음

    PC에 매번 데이터를 처리해야 하기 때문에 복잡도가 높음

 

* 내장 프로시저(SP) 처리방식

 - 데이터베이스에 프로시저(SP)를 개발(순차적으로 호출되어야할 명령어 포함)

 -> 미리 컴파일되었기 때문에 상대적으로 속도 빠름

 -> PC는 서버에 저장된 프로시저만 호출하고 데이터베이스가 내부적으로 처리후 결과만 제공할 수 있어 매우 단순해짐

 -> 이름만 호출하기때문에 SQL명령어가 네트워크에 노출되지 않아 보안 측면에서 유리

 -> 여러개의 테이블을 직접 INSERT, UPDATE 처리하면서 원하는 자료를 조회가능

 -> 다른 저장 프로시저, 함수를 호출할 수 있고 반복전인 처리나 예외적인 처리도 가능

---

[프로시저 생성]

-- 생성문법
CREATE PROCEDURE [SP이름]
(
	[입력변수1 자료형],
	[입력변수2 자료형],
	[외부변수1 자료형 OUTPUT],
	[외부변수2 자료형 OUTPUT]
)
AS
BEGIN
	
	[SQL 명령어 또는 PROCEDURE 로직]
	
	RETURN([코드값])

END

-- 예시
CREATE PROCEDURE SP06_100_생성테스트
(
	@IN_제품코드	NVARCHAR(50),
	@IN_제품명		NVARCHAR(100)
)

AS
BEGIN
	
		SELECT 제품코드 = @IN_제품코드
				,제품명 = @IN_제품명

		RETURN(1)

END

* RETURN문

 - 뒤에 다른 문장이 있더라도 무조건 종료

 - 종료시 숫자를 반환받을 수 있음. 만약 RETURN 뒤에 숫자가 없거나 RETURN 없이 종료된 경우 [0]이 반환

 

* SP개발시 리턴 값으로 정상여부, 오류유형, 위치를 관리

 예 - [1] : 정상종료

       [2] ~ [999] : 오류발생 (오류위치나 유형별로 숫자로 구분 관리 가능)

       [0] : RETURN문을 만나지 못하고 종료([0]도 비정상 종료 인식)

---

[프로시져 실행]

USE [DB101]
GO

-- 실행방법 1 : 결과 및 리턴값 체크
-- 입력/처리업무 등 SP리턴값을 필수적으로 확인이 필요한 경우 권장/사용
DECLARE @return_value int

EXEC @return_value = [dbo].[SP06_100_생성테스트]
	@IN_제품코드	= N'A1',
	@IN_제품명	= N'사과'
    
SELECT 'Return Value' = @return_value

GO

-- 실행방법 2 : 결과만 확인(매개변수명 명시)
-- 조회업무 등 SP리턴값 확인이 필요없는 경우 권장 사용
EXEC [dbo].[SP06_100_생성테스트]
	@IN_제품코드	= 'A1',
	@IN_제품명	= '사과'

-- 실행방법 3 : 결과만 확인(매개변수명 생략)
-- 사용 권장하지 않음(SP입력값 변경 대응 어려움)
EXEC [dbo].[SP06_100_생성테스트] 'A1', '사과'

결과 (실행방법 1, 실행방법 2, 실행방법 3)

저장 프로시저(SP)를 실행 하기 위해 EXEC 명령을 사용할 때에는 가급적 RETURN 값을 전달 받을 수 있고 저장 프로시저에 값을 전달할 이름을 명확하게 표시한 방법을 사용 하기 권장

 

* [USE]

 : 데이터베이스가 관리하는 여러 개의 DB중에서 실제 사용할 DB를 지정할 때 사용

 

* [GO]

 : 윗줄에 SQL 명령어들이 있을 경우 [GO]를 만나면 위의 SQL 명령어들이 일괄적으로 실행

   실행 후에는 관련 실행 환경이 초기화

   마지막까지 [GO]가 없을 경우에는 맨 마지막에 [GO]가 있는 것으로 간주하여 전체 SQL문장을 일괄적 실행

   프로그래밍에 큰 영향을 주기 않음

 

* [N]

 : 다국어를 입력 처리 하겠다는 의미

   환경설정에 따라 [N]생략 시 비정상 값이 입력될 수도 있음                                                                           

---

[프로시저 이름 변경]

EXEC SP_RENAME 'SP06_100_생성테스트', 'SP06_생성테스트_이름변경'

---

[프로시저 제거]

DROP PROCEDURE [SP이름]

DROP PROCEDURE SP06_생성테스트

 

 

 

* 도서 'MSSQL Server 기본에서 실무까지' 참조