[DATABASE] 스토어드 프로시저

스토어드 프로시저

• 미리 작성해두고 데이터베이스에 저장해 놓은 SQL 문들의 집합
• 필요할 때마다 호출하여 실행할 수 있는 사용자 정의 함수 같은 개념
• 반복적인 SQL 작업을 캡슐화해서 재사용이 가능
• 실행 계획이 캐싱되어 성능 향상
• 보안, 권한 제어에 유리
• 트랜잭션 및 오류 처리 가능

기본 문법

CREATE PROCEDURE 프로시저명
    @[매개변수명] [데이터타입]
AS
BEGIN
    SQL 문
END

-- 실행시
EXEC [프로시저명] @[매개변수명]

-- 외부에서 값을 받고 싶을 때
CREATE PROCEDURE 프로시저명
    @[입력값] [데이터타입],
    @[출력값] [데이터타입] OUTPUT
AS
BEGIN
    SET @[출력값] = @[입력값]
END

-- 실행시
DECLARE @[결과값] [데이터타입]
EXEC [프로시저명] @[입력값], @[출력값] = @[결과값] OUTPUT
SELECT @[결과값]

예시

• 학생테이블에서 특정 학교의 학생 수를 확인하는 프로시저를 만들려고 한다.

쿼리문

스토어드 프로시저 생성

-- 결과 값으로 딱히 활용 안하는 경우
CREATE PROCEDURE usp_get_student_count
	@schoolSeq INT
AS
BEGIN
	SELECT COUNT(*)
	FROM STUDENT
	WHERE STUDENT_SCHOOL = @schoolSeq
END

-- 결과 값을 가지고 뭔가 활용하려는 경우
CREATE PROCEDURE usp_get_student_count
	@schoolSeq INT,
	@studentCount INT OUTPUT
AS
BEGIN
	SELECT @studentCount = COUNT(*)
	FROM STUDENT
	WHERE STUDENT_SCHOOL = @schoolSeq
END

스토어드 프로시저 호출

-- 프로시저 결과 값만 보려는 경우
EXEC usp_get_student_count @schoolSeq = 5

-- 프로시저 결과 값으로 뭔가 활용 하려는 경우
DECLARE @count INT
EXEC usp_get_student_count @schoolSeq = 5, @studentCount = @count OUTPUT
SELECT @count

---

스토어드 프로시저에서 주로 사용하는 문법들

1. 변수선언 및 값 할당

DECLARE @변수명 데이터타입	-- 변수선언
SET @변수명 = 값			-- 값 할당

• 프로시저 만들 때 전역변수와는 다른 로컬변수이다

예시)

CREATE PROCEDURE usp_Example
    @value INT -- 전역변수
AS
BEGIN
    DECLARE @count INT -- 로컬변수
    SET @count = 10    -- 값 할당
END

---

2. 조건문

IF 조건식
    BEGIN
        -- 조건이 참일 때 실행
    END
ELSE
    BEGIN
        -- 거짓일 때 실행
    END

---

3. 반복문

WHILE 조건식
BEGIN
    -- 반복 실행 블록
END

---

4. 트랜잭션

BEGIN TRANSACTION
-- 실행할 SQL
COMMIT
-- 또는
ROLLBACK

 

---

5. 예외처리

BEGIN TRY
    -- 정상 실행
END TRY
BEGIN CATCH
    -- 오류 발생 시 실행
    SELECT ERROR_MESSAGE()
END CATCH