저장 프로시저는 보기에도 그냥 프로그래밍 할때 사용하는 함수랑 똑같아 보인다
매겨 변수 받고 리턴값 주고 하는것 처럼 말이다.
ms sql 에서도 사용 되는데 저장 프로시저 란 이름을 가지고 사용된다.
1. 저장 프로시저의 이점
1. 캡슐화
저장 프로시저 내의 로직이 변경돤다 하더라도 매개 변수의 형태에 변화가 없다면 사용자와 어플리케이션은 영향을 받지 않는다.
또한 변경된 로직도 적용받을수 있다.
2. 성능
저장 된 실행계획을 재사용 함으로써 CPU 리소스를 절약해주고 코드의 구문 본석 이름확인 및 최적화에 걸리는 시간을 단축 할수 있다.
3. 네트워크 트래픽 최소화
서버 똑으로 전송하는 코드 문자열을 줄여서 트래픽을 최소화할 수 있다.
저장 프로시저의 이름과 매겨변수만 넘기면 된다.
4. 보안 계층으로 사용
개체에 대한 권한을 직접 부여하는 것이 아니라 저장 프로시저에 대한 실행 권한만을 부여하여 개채에 대한 불필요한 접근을 제한 가능하다.
2. sql 문과 저장 프로시저 차이
1. 일반 sql 문
일반 적으로 쿼리문에 대한 문법 검사 진행후 불필요한 구문을 제거 해서 쿼리문을 표준화 한다.
그리고 사용자가 해당 쿼리문을 실행할 권한 이쓴ㄴ지 확인 한 후에 최적의 실행 계획을 만들어 컴파일 과정을 통해 캐시에 저장한 후 실행
이렇게 한번 실행이 된후에는 캐시에 깅행 계획이 존재 하면 바로 실행하고 없으면 위 과정을 거치는게 기본적이다.
하지만 조금이라도 달라지면 다른 쿼리라고 생각하고 검사 부터 다시 진행 하기때문에 CUP와 메모리에 낭비가 올수있다.
실행과정
구문 분석 - 표준화 - 보안 - 최적화 - 컴파일 - 실행
2. 저장 프로시저
저장 프로시저는 구문의 문법을 검사한 후 사용자가 저장 프로시저를 만들 권한이 있는지 확인한다.
저장 프로시저가 참조한 개체가 없어도 저장 프로시저의 지연된 이름 확인의 특성에 의해 무시된다.
완성된 저장 프로시저의 정보는 시스템 테이블에 저장 된다.
저장과정
구문 분석 - 이름 확인 - 보안 - 결과 저장
그후 만들어지고 나서 처음 실행 될때는 먼저 저장 프로시저가 참조하는 개체가 있는지 확인을 한다.
이어서 사용자가 저장 프로시저를 호출할수 있는 권한이 있는지 확인을 하고 최적의 실행 계획을 만들어
컴파일 과정을 통해 캐시에 등록된 후 컴파일된 구문이 실행된다.
저장 프로시저가 다시 호출 될때 캐시에 실행 계획이있으면 즉시 실행하고 그렇지 않은 경우 처음 실행할 때와 동일한 과정으로
실행 계획이 만들어진다. 프로시저 목적 자체가 동일한 구조로 재사용성이 높은 걸 가지고 만들기 때문에
CPU와 메모리 사용량을 줄일수 있는 이점이 있다.
최초 실행
이름 확인 - 보안 - 최적화 - 컴파일 - 실행
3. 저장 프로시저 구문
CREATE { PROC | PROCEDURE } [schema_name.]procedure_name [; number ]
[ { @parameter [type_schema_name. ] data_type } [ VARYING] [ = default ] [ OUT | OUTPUT ] [READONLY]]
[ ,...n ]
[ WITH <procedure_option> [,...n ]]
[ FOR REPLICATION ]
AS { [BEGIN ] sql_statement [;] [ ...n ] [ END] }
[;] 기본적으로 대부분 작업은 저장 프로시저에 포함될 로직을 작성하는 것이다.
select만 있는 뷰와 달리 여러가지 쿼리 문을 포함 될 수 있기 때문이다.
예시
1. 프로시저 생성
-- 프로시저 만들기
CREATE PROCEDURE dbo.usp_GetEmployee
@DeptID char(3),
@FromDate Date,
@ToDate Date
AS
BEGIN
SET NOCOUNT ON
SELECT EmpID, EmpName, HireDate
FROM dbo.Employee
WHERE DeptID = @DeptID AND HireDate BETWEEN @FromDate AND @ToDate
END
GO
위 코드를 보면 PROCEDURE 로 생성하고 매개 변수 3가지를 받는다.
@DeptID
@FromDate
@ToDate
를 받고 BEGIN END 사이에 있는 구문 을 실행해 주는 것이다.
2. 프로시저 수정
ALTER PROC dbo.usp_GetEmployee
@DeptID char(3),
@FromDate Date,
@ToDate Date
AS
BEGIN
SET NOCOUNT ON
SELECT EmpID, Gender, EmpName, HireDate -- select 할때 Gender 추가
FROM dbo.Employee
WHERE DeptID = @DeptID AND HireDate BETWEEN @FromDate AND @ToDate
END위 내용에서 수정 한다.
3. 프로시저 삭제
DROP PROC dbo.usp_GetEmployee4. 프로 시저 실행
EXEC dbo.usp_GetEmployee
@DeptID = 'SYS',
@FromDate = '2008-01-01',
@ToDate = '2008-12-31'
GO매개변수 값은 형식에만 맞춰서 주면 된다.
5. 유효성 체크
매개 변수 값이 원하는 값이 맞는지 체크 하는 과정이다.
ALTER PROC dbo.usp_GetEmployee
@DeptID char(3) = null,
@FromDate Date = '1900-01-01',
@ToDate Date = '9999-01-01'
AS
BEGIN
SET NOCOUNT ON
-- @DeepID null 이면 에러 리턴
IF @DeptID IS NULL
BEGIN
RAISERROR('에러임', 16, 1) -- 에러 리턴
RETURN
END
-- null 아니면 실행
END
GO6. 출력 매개 변수
매개 변수를 출력 매개변수 하나 둬서 값을 받는것이다. 하지만 조금 복잡하다.
리턴 되는 값이랑 별개로 받기 때문이다.
ALTER PROC dbo.usp_GetEmployee
@DeptID char(3) = null,
@FromDate Date = '1900-01-01',
@ToDate Date = '9999-01-01',
@RowNum int OUTPUT -- 출력 매개 변수
AS
BEGIN
SET NOCOUNT ON
-- @DeepID null 이면 에러 리턴
IF @DeptID IS NULL
BEGIN
RAISERROR('에러임', 16, 1)
RETURN -1 -- -1 리턴
END
-- null 아니면 실행
SELECT EmpID, Gender, EmpName, HireDate
FROM dbo.Employee
WHERE @DeptID = @DeptID AND HireDate BETWEEN @FromDate AND @ToDate
SET @RowNum = @@ROWCOUNT
RETURN 0 -- 0 리턴
END
GO
리턴값 받는법
DECLARE @Return int -- 변수 만들고
DECLARE @Rows int
EXEC @Return = dbo.usp_GetEmployee -- return 값 저장
@DeptID = 'SYS',
@FromDate = '2000-01-01',
@ToDate = '2008-12-31',
@RowNum = @Rows OUTPUT -- Set 저장
SELECT @Return as 'return', @Rows as 'rowsCount' 확인
GO7. 결과 값을 테이블로 보내기
DECLARE @Return int
DECLARE @Rows int
INSERT INTO #Employee EXEC @Return = dbo.usp_GetEmployee
@DeptID = 'SYS',
@FromDate = '2008-01-01',
@ToDate = '2009-01-01',
@RowNum = @Rows OUTPUT
SELECT EmpID, EmpName, EMail
FROM #Employee
GO프로 시저 실행후 리턴값을 바로 INSERT 해준 모습이다.
8. 오류 핸들링
프로그래밍 하다보면 try chtch 문법을 많이 볼수있다 여기서도 똑같이 할수있다.
CREATE PROC usp_InsertDeparment
@DeptID char(3),
@DeptName nvarchar(10),
@StartDate date
AS
BEGIN TRY
INSERT INTO dbo.Department VALUES(@DeptID, @DeptName, @StartDate)
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() AS 'error_line',
ERROR_MESSAGE() AS 'ERRORMESAGE',
ERROR_NUMBER(),
ERROR_PROCEDURE(),
ERROR_SEVERITY(),
ERROR_STATE()
END CATCH
GO4. 문제점
이런 쿼리들을 작성한다는건 뭔가 데이터를 얻으려고 하는경우가 많다.
그래서 무엇을 원하는지 적어두고 내부에서 알아서 처리하고 값을 주는것이 대부분이다.
그래서 이때 속도를 높히기 위해서 실행계획을 캐시에 넣고 재사용한다고 위에 적어두었는데
성능을 높혀주는 효과도 있지만 문제가 하나 발생한다.
만약
비클러스터형 인덱스에서 일반적인 sql문으로 2일치에 기간에 로그를 조사와 1년치를 초사한다고 했을때
2일치면 Rid lookup으로 찾으로 간다
1년치 일경우 테이블 스캔으로 찾을수도 있다.
왜냐면 할때마다 sql 문이 조금씩 달ㄹ라져서 다시 최적화 하기 때문에 새로 실행계획을 만드는거다
그럼 프로 시저의 경우는 그렇지 않다 프로시저 는 안에 코드가 바뀌지 않는한 같은 실행 계호기을 사용하기 때문에
만약 2일치 자료 를 원할때 Rid lookup으로 실행계획이 만들어지고 실행 되었다고 하면
다음에 1년치 원할때는 이미 실행계획이 있어서 같은 Rid lookup 이 발생하는것이다.
또는 반대로 1년치 먼저 발생해서 테이블 스캔으로 발생했는데
1시간치 원한다고 실행할때 역시 테이블 스캔이 발생해 버려서 비용이 너무 크게 낭비 된다.
