저장프로시저(MS-SQL 환경)

정의
저장 프로시저(Stored Procedure)란 SQL Server에서 제공되는 프로그램이 기능.

저장 프로시저 개요
어떠한 동작을 일괄 처리하는 용도로 사용된다. 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때마다 호출한다.

예시

exec usp_users를 통해 해당 프로시저를 호출해 사용할 수 있다.

저장 프로시저의 수정과 삭제
수정 - alter procedure를 사용
삭제 - drop procedure

매개 변수의 사용
입력 매개변수(+ 디폴트 매개변수)
@입력_매개 변수+이름 데이터_형식 [= 디폴트]

디폴트 매개변수

출력 매개변수
@출력_매개 변수_이름 데이터_형식 OUTPUT

리턴과 에러 처리
RETURN 문을 사용해 리턴 값을 받게 되면 실행한 저장 프로시저가 성공적으로 수행되었는지 또는 실패했는지 확인할 수 있다.
오류가 발생했을 경우 @@ERROR 함수를 이용해 오류 처리가 가능. TRY/CATCH 문도 있다.

저장 프로시저 확인

암호화

임시 저장 프로시저
tempdb에 저장되는 프로시저. 재시작하면 사라진다.
#또는 ##을 붙이게 되면 임시 저장 프로시저로 생성이 된다.

저장 프로시저의 특징
1. SQL Server의 성능을 향상시킬 수 있다.
2. 유지 관리가 편하다.
3. 모듈식 프로그래밍이 가능하다.
4. 보안을 강화 할 수 있다.
5. 네트워크 전송량을 줄일 수 있다.


저장 프로시저의 종류
사용자 정의 저장 프로시저
T-SQL 저장 프로시저 : 사용자가 직접 생성한 프로시저를 말하며 Transact SQL문을 사용한다.
CLR 저장 프로시저 : T-SQL 저장 프로시저보다 효율적이고 강력한 프로그래밍을 제공한다.
.NET Framework 어셈블리의 클래스에 공용의 정적 메소드로 구현되며 사용자로부터 
매개변수를 입력 받아 결과를 반환하는 .NET Framework CLR 메소드의 참조로 사용된다.


확장 저장 프로시저 
C언어 등을 이용하여 데이터베이스에서 구현하기 어려운 것들을 구현한 저장 프로시저이다. 
SQL에서 제공하는 API를 이용하여 프로그래밍을 작성한다.(※확장 저장 프로시저는 SQL Server 2012 이후에는 제거될 것이므로, 대신 CLR 저장 프로시저 사용 요망)


시스템 저장 프로시저
SQL Server에서 제공하는 저장 프로시저로 SQL Server의 관리와 관련된 작업에 사용된다. ‘sp_’접두어로 시작한다.


저장 프로시저의 작동
T-SQL의 작동 방식
최초 1회 실행

2회 이상 실행(메모리에 있을 경우)

실행 과정
구문 분석 - SELECT, FROM 등의 단어에 오류가 없는지 분석한다. 구문에 오류가 있을 경우 더 이상 진행되지 않는다.
개체 이름 확인 - 사용하려는 데이터, 테이블이 현재 데이터베이스에 있는지 확인한다.
사용권한 확인 - 테이블에 현재 사용자가 접근할 권한이 있는지 확인한다.
최적화 - T-SQL문이 가장 좋은 성능을 낼 수 있는 경로를 설정한다. 주로 인덱스 사용 여부를 경정하며 테이블 스캔(또는 클러스터 인덱스 스캔)을 하도록 결정한다.
컴파일 및 실행계획 등록 - 최적화된 결과를 컴파일 한다. 그리고 그 결과(실행 계획)를 메모리(캐시)에 등록한다.


저장 프로시저의 작동 방식
저장 프로시저를 정의할 때 작동 순서

처음으로 저장 프로시저를 실행할 때

이후 저장 프로시저를 실행할 때

실행 과정
구문 분석 - 일반 T-SQL과 마찬가지로 해당 구문 오류를 파악한다.
지연된 이름 확인 - 저장 프로시저를 정의하는 시점에서 해당 개체(주로 테이블)가 없어도 문제가 발생하지 않는다. 테이블의 존재 여부는 해당 프로시저가 실행될 때 확인한다.
생성권한 확인 - 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인한다.
시스템 테이블 등록 - 저장 프로시저의 이름 및 코드가 관련 시스템 테이블에 등록된다.(카탈로그 뷰 sys.object 및 sys.sql_modulse 등을 확인하면 확인이 가능)
컴파일 및 실행계획 등록 - 최적화된 결과를 컴파일 한다. 그리고 그 결과(실행 계획)를 메모리(캐시)에 등록한다.


매개 변수 스니핑
정의
저장 프로시저의 최적화 단계에서 매개 변수로 인한 성능 문제(인덱스 관련 문제)
오라클 DBMS에서는 Bind Peeking이라 불린다.


해결
EXECUTE로 저장 프로시저 실행 시 ‘WITH RECOMPILE’옵션 사용한다.
CREATE PROCEDURE문에서 ‘WITH RECOMPILE’옵션 사용한다. 단 메모리에 컴파일 내용을 저장하지 않고항상 실행할 때마다 재 컴파일 한다. 매 실행 시 마다 인덱스 사용 여부를 예측 못할 경우 사용하는 것이 좋다.
‘sp_recompile 테이블이름’ 시스템 저장 프로시저를 사용한다.
‘DBCC_FREEPROCCACHE’를 사용하여 현재 메모리(프로시저 캐시 영역)의 내용을 모두 비운다.
[※ SQL Server 2008부터 저장 프로시저를 재 컴파일 할 때 변경된 부분만 수정 ]


사용자 정의 함수
정의
일반적인 프로그래밍 언어에서 사용되는 함수와 같은 프로그래밍을 지원해준다.


특징
1. RETURN문을 이용해 특정 값을 돌려주는 기능이 있다.
2. SELECT문에 포함 되어서 실행(호출)된다.(스칼라 함수는 EXECUTE로 실행 가능)
3. 함수 내부에서 TRY~CATCH문을 사용할 수 없다.
4. 함수 내부에서 CREATE/ALTER/DROP문을 사용할 수 없다.
5. 오류가 발생하면 즉시 함수 실행을 멈추고 값을 반환하지 않는다.


종류
기본 제공 함수 : SQL Server가 제공해주는 시스템 함수
사용자 정의 스칼라 함수 : RETURN문에 의해서 단일 값을 돌려주는 함수
사용자 정의 테이블 반환 함수 : ‘테이블 함수’라고도 부르며 반환 값이 하나가 아닌 테이블인 함수를 말한다.
인라인 테이블 반환 함수 : 간단히 테이블을 돌려주는 함수로 SELECT문만 사용하여 결과를 돌려주는 함수
다중 문 테이블 반환 함수 : BEGIN~END로 정의되며 내부에 일련의 T-SQL문을 이용해 반환 테이블에 
행 값을 Insert하는 형식을 가진다.
스키마 바운드 함수 : 함수에서 참조하는 테이블, 뷰 등이 수정되지 못하게 설정한 함수생성 및 수정 시
‘WITH SCEMABINDING’을 사용하면 된다.


예시

인라인 테이블 반환 함수

출처 - http://hmjo.tistory.com/255