저장 프로시저 (Stored Procedure)

유수민·2022년 7월 11일

지식창고

목록 보기

32/64

📌저장 프로시저란?

DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합. 즉, DB에 대한 작업을 정리한 절차를 RDBMS(관계형 데이터 베이스 관리 시스템)에 저장한 쿼리의 집합이다. 영구저장모듈이라고도 불린다. 여러 쿼리를 하나의 함수로 묶은 것이다.

SQL Server에서 제공되는 프로그래밍 기능. 쿼리문의 집합
어떠한 동작을 일괄 처리하기 위한 용도로 사용.
자주 사용되는 일반적인 쿼리를 모듈화시켜서 필요할 때마다 호출
테이블처럼 각 데이터베이스 내부에 저장

📌일반 쿼리문 vs 저장프로시저

📖일반 쿼리문 작동 방식

일반적으로 쿼리문 한줄을 실행하더라도 ‘파싱 → 최적화 → 컴파일 및 실행계획 등록(실행계획 결과를 메모리에 등록) → 실행’ 하는 과정의 많은 절차를 거친다.

🌟 최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 해당 실행계획 결과를 메모리(캐시)에 등록한다.

📖저장 프로시저

📚1. 저장 프로시저 정의 단계

구문분석 : 구문의 오류 파악
지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 개체(ex. 테이블)가 존재하지 않아도 상관없다. 프로시저 실행 당시에 테이블 존재 여부 확인함(개체이름 확인).
생성권한 확인 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인
시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록.

📚2. 처음으로 저장 프로시저를 실행

구문분석 단계가 빠지는 것만 빼면 일반적인 쿼리문 수행단계와 동일하다. 저장프로시저 정의 단계의 지연된 이름확인에서 미루어두었던 해당 개체 존재 유무를 개체 이름 확인을 통해 수행한다.

📚3. 이후의 저장 프로시저 실행

이후에 두번째 실행 부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 많이 단축한다.

📌저장프로시저의 장.단점

📖장점 (저장 프로시저를 왜 사용하는가)

📚SQL Server의 성능을 향상 시킬 수 있다

저장프로시저의 두번째 실행부터는 캐시(메모리)에 있는 것을 가져와서 사용하므로 속도가 빨라진다. 또한, 여러개의 쿼리를 한번에 실행할 수 있다.

📚유지보수 및 재활용 측면에서 좋다.

C#, Java등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수정요건이 발생할때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해진다.( SP 수정으로 조회, 수정, 추가 등의 가벼운 소스 변경 등이 가능)
한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.

📚보안을 강화할 수 있다 - 권한 체계

저장 프로시저는 사용자들에게 데이타에 대한 제한적인 접근을 허용케하는 전통적인 수단이다. 사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 줌으로써 테이블의 모든 정보를 사용자에게 노출하지 않고 프로시저에서 선택한 정보만 사용자에게 보여줄 수 있다.

📚네트워크의 부하(전송량)를 줄일 수 있다.

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