[SQL] 저장프로시저 (Stored Procedure)

저장프로시저

- 저장 프로시저는 하나 이상으로 구성된 Transact-SQL 문을 데이터베이스에 저장한 개체입니다.

저장프로시저 특징

• 모듈 프로그래밍
  자주 반복해서 사용하는 T-SQL문을 DB에 저장해 필요한 시점에만 사용함. 
  매번 같은 구문을 다시 작성할 필요가 없음
  
• 유연한 보완관리
  데이터 조회하는 저장프로시저. 접근권한이 없어도 저장프로시저를 실행할 권한이 있다면 조회가능
  
• 네트워크 트래픽 감소
  쿼리전체를 서버로 전송해서 작업하는 것이 아닌 저장 프로시저와 매개변수값만을 전달함으로 데이터량이 작음
  
• 빠른실행
  저장프로시저는 실행후 쿼리 실행계획을 메모리에 저장 > 저장된 실행계획 사용 > 구문분석이나 최적화 과정 거치지 않아서 더 빠른 실행을 할수 있고 캐시에 없더라도 구문분석, 표준화등의 작업을 하지 않기에 성능이 빠름
  

저장프로저의 구성요소
저장 프로시저명, 매개변수들, SQL문, 결과값 반환의 반환값

저장프로시저의 종류

• 확장프로시저 : C와 같은 언어를 이용해서 구현한 프로시저, master DB에만 추가가능
  
• 사용자 정의 저장 프로시저 : T-SQL문을 이용해 저장 프로시저로 구현
  
• 시스템 저장 프로시저 : SQL서버관리를 위해 시스템에서 제공해주는 저장 프로시저
  sys의 스키마로 나타남, 데이터베이스 명 필요없이 시스템 저장 프로시저명을 통해서 실행가능
  
  Sp_who, sp_who2 : 사용자 정보
  Sp_lock : lock 정보
  Sp_help : 지정한 개체 정보
  Sp_helpdb : 지정한 DB정보
  Sp_configure : SQL 서버 설정변경
  
• 임시 저장프로시저
  T-SQL 문 또는 일괄처리의 실행계획을 재 사용하지 않던 이전 버전의 방식
  사용자 정의 프로시저와 동일하게 작성하지만, 저장프로시저 명을 #으로 하면 임시저장프로시저가 됨
  SQL Server 2005에서는 T-SQL문과 일괄처리의 실행계획을 재 사용할수 있음으로 임시저장프로시저 사용이 거의없음
  
• CLR 저장 프로시저
  T-SQL에서 부족한 프로그래밍 부분을 CLR 저장 프로시저를 통해 T-SQL 저장 프로시저보다는 더 강력한 구조적 프로그래밍이 가능

저장 프로시저의 생성

CREATE PROCEDURE usp_withANumber <- 소문자 : 스키마. 저장 프로시저명
@ EmployeeID INT <- 매개변수, 데이터 형식
AS
SELECT ANumber, AContent, ManagerID
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

EXECUTE usp_withANumber

GO
* 임시 저장프로시저의 생성은
CREATE PROCEDURE #usp_withANumber <- 소문자 : 스키마. 저장 프로시저명앞에 샾
* 저장프로시저 생성시, SP_ 접두사는 시스템저장프로시저와 혼란을 줄수 있음으로 사용금지
* 그룹화면 저장프로시저 삭제시 개별적으로 하나씩 삭제할수 없음으로 가급적 사용하지말것
- 그룹화하기 : 같은 저장 프로시저명에 ; 하고 숫자를 매김

CREATE PROCEDURE usp_withANumber;1
AS
[ T-SQL구문 ]
GO

CREATE PROCEDURE usp_withANumber;2
AS
[ T-SQL구문 ]
GO

* 다른 사용자의 접근막기위해서는 CREATE아래쪽에 WITH ENCRYPTION을 사용함
시스템뷰에서 저장프로시저의 텍스트가 나타나지 않음. 암호화된 저장프로시저의 내용은 다시 확인할수 있는 방법이 없음으로 암호화되기전의 저장프로시저를 잘 보관해야함

저장 프로시저의 수정

ALTER PROCEDURE 스키마. 저장프로시저명.
@ 매개변수 데이터 형식
AS
[ 변경된 SQL문 ]

저장 프로시저의 삭제

DROP PROCEDURE 스키마.저장프로시저명;

기본값을 지니는 매개변수의 사용과 output사용

CREATE PROCEDURE usp_withANumber 
@EmployeeID INT = 10 <- EmployeeID에 기본값을 지정한 것
@outvalue int output <- outvalue에 output 변수를 쓰겠다는것
@currency_cursor CURSOR VARYING OUTPUT <- output 매개변수로 커서를 사용할때
AS
SELECT @outvalue = ManagerID <- 관리자 ID를 output 매개변수에 설정
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

DECLARE @ManagerID INT; <- 저장프로시저 output 매개변수에 반환하는 값저장을 위한 지역변수선언

EXECUTE usp_withANumber 20, @ManagerID output <-반환되는값 조회

GO