MSSQL - 저장 프로시저 (PROCEDURE)

▶ 저장프로시저

- 실무에서는 프로그램에서 만들어 놓은 SQL문을 저장해 놓고, 필요할 때마다 호출해서 사용하는 방식으로 프로그램을 만든다.

 

저장 프로시저는 이러한 방식이 가능하도록하는 각 DBMS 에서 제공하는 프로그래밍 기능이다.

Oracle, MySQL 등 대부분의 DBMS 에서 제공하고 있는데, 오늘은 SQL Server에서 제공하는 저장 프로시저에 대해 정리할 생각이다.

 

저장 프로시저는 쿼리문들의 집합으로, 어떤 동작을 여러쿼리를 거쳐서 일괄적으로 처리할 때 사용한다.

 

여기서 이런 질문을 할 수도 있다.

 

"그럼 그냥 SQL 문 쓰면 되지 , 굳이 SP를 만들어 써야하나?"

 

저장 프로시저를 실무에서 사용하는 이유가 있다.

 

먼저, SQL Server의 성능을 향상 시킬 수 있다.

저장 프로시저를 처음에 실행하면 최적화, 컴파일 단계를 거쳐 그 결과가 캐시(메모리)에 저장되게 되는데, 이 후에 해당 SP를 실행하게 되면 캐시(메모리)에 있는 것을 가져와서 사용하므로 실행속도가 빨라지게 된다.

그렇기 때문에 일반 쿼리를 반복해서 실행하는 것보다 SP 를 사용하는게  성능적인 측면에서 좋다.

 

두번째, 유지보수 및 재활용 측면에서 좋다.

C#, Java등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수정요건이 발생할때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해진다. 

또한 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.

 

셋째, 보안을 강화할 수 있다.

사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있다.

실제 테이블에 접근하여 다양한 조작을 하는 것은 위험하기 때문에 실무에서는 실제로 개발자에게는 sp권한만 주는 방식을 많이 사용한다.

 

마지막으로, 네트워크의 부하를 줄일 수 있다.

클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 된다. 하지만 저장 프로시저를 이용한다면 저장프로시저의 이름, 매개변수 등 몇글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있다.

 

간단하게 설명하면 위에 글과 같습니다. 좀더 자세히 알고 싶으면 아래 링크를 참조하시면 됩니다.

https://devkingdom.tistory.com/323

[MSSQL] 저장 프로시저 (Stored Procedure) 란?

 

--프로시저 기본 형태--
CREATE PROC 프로시저명
(
  파라미터 데이터형태
          .
          .
          .
)
AS
BEGIN
 쿼리문
END

 

. 기본적인 형태가 위와 같은형식입니다.

. 파라미터는 조건식에 값을 넣기 위해 받아오는 값입니다. 크기는 테이블에 있는 컬럼의 데이터형태와 크기를 맞춰서 선언을 하여 사용을 해야합니다.

 

아래의 링크에서 데이터를 참고하여 만들었습니다.

https://alstn4973.tistory.com/151

MSSQL - INSERT 삽입

CREATE PROC SP_EMP
(
 @EMP_NO INT
 )
AS  
  
BEGIN  
  SELECT * FROM EMP WHERE empno = @EMP_NO
END

 

. 예시를 만들어 보여드리면 WHERE 조건식에 파라미터가 들어갑니다.

. SP_EMP를 만들어 검색을 하면

EXEC SP_EMP @EMP_NO = 20;