저장 프로시저(Stored Procedure)

저장 프로시저(Stored Procedure)

DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합
즉, DB에 대한 작업을 정리한 절차를 RDBMS에 저장한 쿼리의 집합으로 영구저장모듈이라고도 불린다.

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

 

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

일반 쿼리문 작동 단계

일반적인 쿼리문을 처음 실행하면 위의 프로세스로 동작을 한다.

예시로 아래의 쿼리문을 실행한다고 가정해보자

SELECT name FROM userTbl;

1. 구문 분석단계 : 구문 자체에 오류가 있는지 분석을 하고, 만약 오타가 있으면 이 단계에서 오류가 발생되어
   에러메세지가 발생
2. 개체 이름 확인 단계 :  userTbl이라는 테이블이 현재 데이터베이스에 있는지 확인하고, 테이블이 있으면 그 안에
   name이라는 열이 있는지 확인
3. 사용권한 확인 단계 :  userTbl을 현재 접근중인 사용자가 접근 권한이 있는지 확인
4. 최적화 단계 :  해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 설정
5. 컴파일 및 실행계획 등록 단계 :  해최적화된 결과를 바탕으로 해당 실행계획 결과를 메모리에 등록
6. 실행 단계 : 컴파일된 결과를 실행

동일한 SQL문을 실행하는 경우 작동 방식

1. 메모리에 동일한 쿼리가 있으면 확인 후 실행
   • 메모리에 동일한 쿼리가 없으면 전체 과정 다시 반복
   • 쿼리 전체가 한글자도 틀리지 않고 같아야 함

 

저장 프로시저 정의 단계

1. 구문 분석 단계 : 구문의 오류를 파악 
2. 지연된 이름 확인 단계 : 저장 프로시저를 정하는 시점에서 테이블과 같은 해당 개체의 존재 여부와 상관 없이 정의 가능 -> 프로시저 실행 시점에 확인하기 때문, 하지만 테이블의 열 이름이 틀리면 오류 발생
3. 생성 권한 확인 단계 : 사용자가 저장 프로시저를 생성할 권한이 있는지를 확인
4. 시스템 테이블에 등록 단계 : 저장 프로시저의 이름과 코드가 관련 시스템 테이블에 등록되는 과정
   -> 관련 내용은 카탈로그뷰 확인 가능

 

저장 프로시저 실행 단계

1. 개체 이름 확인 단계 : 실제로 해당 개체가 유요한지를 확인 
2. 나머지는 일반 SQL문과 같이 동작

 

저장 프로시저 두번째 실행

• 메모리에 있는 것을 그대로 가져와 재사용하게 되어 수행시간이 많이 단축됨

 

일반 쿼리문과 저장 프로시저 차이

일반 쿼리문

SELECT * FROM userTbl WHERE name ='이승기';

SELECT * FROM userTbl WHERE name ='성시경';

SELECT * FROM userTbl WHERE name ='은지원';

일반 쿼리문은 글자 하나로 다르면 다른 쿼리로 인식 -> 매번 최적화와 컴파일을 다시 수행

 

저장 프로시저

CREATE PROC select_by_name

               @Name NVARCHAR(3)

AS

               SELECT*FROM userTbl WHERE name =@Name;

 

EXEC select_by_name @Name = '이승기';

EXEC select_by_name @Name = '성시경';

EXEC select_by_name @Name = '은지원';

 

위처럼 실행하면 첫번째 이승기를 검색하는 과정에서만 최적화 및 컴파일을 수행하고
나머지는 메모리에 있는 것을 사용하게 된다.

 

저장 프로시저의 장/단점

장점

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

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

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

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

보안을 강화할 수 있다

• 사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 줌으로써 테이블의 모든 정보를
  사용자에게 노출하지 않고 프로시저에서 선택한 정보만 사용자에게 보여줄 수 있다.

네트워크 부하를 줄일 수 있다.

• 저장 프로시저를 이용하면 저장 프로시저의 이름, 매개변수 등 몇글자만 전송하면 되기 때문에
  부하를 크게 줄일 수 있다.

단점

DB 확장 어려움

• 서비스 사용자가 많아져서 서버의 수를 늘려야 할 때, DB 수를 늘리는 것이 어렵다.
  • 여러 서버에 분산된 데이터베이스에서는 데이터 일관성을 유지하고 동기화하는 작업이 어려울 수 있다.
    저장 프로시저 내에서의 데이터 일관성 유지 및 동기화 처리는 복잡한 작업이 될 수 있다.
• DB 교체는 거의 불가능 하다.
  • 저장 프로시저는 주로 특정 데이터베이스 벤더의 확장 기능이나 문법을 사용하여 작성되기 때문에
    다른 데이터베이스로의 이전이나 교체가 어렵다.

낮은 처리 성능

• 문자, 숫자열 연산에 SP를 사용하면 오히려 느린 성능을 보일 수 있다.
  • 데이터베이스 엔진은 주로 데이터 조작과 관련된 작업에 최적화되어 있기 때문에 문자열이나 숫자열 연산을
    처리하는데 다소 제한된 성능을 보일 수 있다.

 

저장 프로시저 사용 예시

상품을 새로 등록하는 저장 프로시저 생성

-- DELIMITER를 사용하여 SQL 문의 구분자를 임시로 변경합니다.
-- 이는 나중에 DELIMITER ;로 복원됩니다.
DELIMITER //

-- AddProduct라는 이름의 저장 프로시저를 생성합니다.
-- 이 프로시저는 새로운 제품을 products 테이블에 추가합니다.
CREATE PROCEDURE AddProduct(IN productName VARCHAR(255), IN price DECIMAL(10, 2))
AS
BEGIN
    -- DECLARE 키워드를 사용하여 임시 변수를 선언합니다.
    DECLARE productID INT;

    -- INSERT 문을 사용하여 제품의 이름과 가격을 products 테이블에 삽입합니다.
    -- 입력받은 productName과 price 매개변수를 사용하여 값을 삽입합니다.
    INSERT INTO products (name, price) VALUES (productName, price);

    -- SET 키워드를 사용하여 변수에 값을 할당합니다.
    -- INSERT 문으로 삽입된 제품의 ID를 가져와 productID 변수에 할당합니다.
    SET productID = LAST_INSERT_ID();

    -- SELECT 문을 사용하여 새로 추가된 제품의 정보를 출력합니다.
    SELECT * FROM products WHERE id = productID;
END //

-- DELIMITER를 다시 세미콜론으로 복원하여 기본 구분자로 돌아갑니다.
DELIMITER ;

• IN : 저장 프로시저 생성 시 매개변수 앞에 IN을 붙이면 저장 프로시저 호출 시에 값을 전달하기 위한 용도로
  사용된다는 의미이다.
  • OUT : 저장 프로시저가 호출되고 난 후에 파라미터가 값을 반환하는 역할을 수행한다는 의미이다.
• DELIMITER : SQL문에서 사용되는 구문자를 변경하는 명령어, 여러 SQL문을 하나의 블록으로 묶어야 할 때
                        세미콜론을 바로 사용하면 SQL문의 끝을 나타내기 때문에 사용
• BEGIN ~ END : BEGIN은 저장 프로시저의 본문의 시작을 나타내고, END는 저장 프로시저의 본문의 끝을 나타낸다.
                            이 사이에 SQL문과 제어 흐름 관련 로직을 작성한다.
  • AS : BEGIN ~ END처럼 같은 기능을 할 수는 있지만, 저장 프로시저나 함수 등을 정의할 때 정의의 시작을 나타내고, BEGIN~END는 저장 프로시저나 함수의 본문을 정의하는 코드이다.
  • 둘 다 사용하거나 둘 중 하나만 사용해도 되지만 가독성을 위해 둘 다 사용하는 것이 좋다.
• DECLARE : 변수를 선언하는 키워드로 저장 프로시저 내에서 사용할 변수를 선언할 때 사용한다.
  • DECLARE 대신에 변수명 앞에 @를 붙여서 선언하는 방식도 있고, 둘 다 동시에 사용해도 문제는 없다.
    단, @를 사용하여 변수를 선언한 경우 참조시 변수명 앞에 @를 붙여서 사용해야 한다.
    @productID INT;   /   DECLARE @productID INT;
    
• SET : 변수에 값을 할당하기 위해 사용되는 키워드, 저장 프로시저 내에서 변수에 값을 저장할 때 사용한다.

 

상품 등록 저장 프로시저 호출

-- CALL 문을 사용하여 AddProduct 저장 프로시저를 호출하고 매개변수를 전달합니다.
-- '새로운제품', 29.99라는 값을 전달하여 제품을 추가하고 결과를 출력합니다.
CALL AddProduct('새로운제품', 29.99);

• CALL : 데이터베이스에서 저장 프로시저, 함수 또는 사용자 정의 프로시저를 호출하는 명령어 

 

OUT 사용한 예시

-- 구분자를 $$로 변경합니다.
DELIMITER $$

-- 'getEmployeeNameAndSales'라는 저장 프로시저를 생성합니다.
CREATE PROCEDURE getEmployeeNameAndSales (
    -- 입력용 매개변수: employee_id
    IN employee_id INT,
    -- 출력용 매개변수: employee_name
    OUT employee_name VARCHAR(255),
    -- 출력용 매개변수: employee_sales
    OUT employee_sales DECIMAL(10, 2)
)
BEGIN
    -- employees 테이블에서 특정 employee_id에 해당하는 이름(name)과 판매량(sales)을 선택합니다.
    SELECT name, sales INTO employee_name, employee_sales
    FROM employees
    WHERE id = employee_id;
END$$

-- 구분자를 기본값인 ;로 복원합니다.
DELIMITER ;

-- 저장 프로시저를 호출하고 결과를 출력합니다.
CALL getEmployeeNameAndSales(1, @name, @sales);

-- 호출된 저장 프로시저에서 반환된 값을 출력합니다.
SELECT @name, @sales;

 

---

참고 블로그

저장 프로시저 (Stored Procedure)

[MSSQL] 저장 프로시저 (Stored Procedure) 란?

 

---

발표 ppt

저장 프로시저(Stored Procedure).pptx

0.15MB