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

저장 프로시저 ( Stored Procedure )

---

 

 

DataBase에 대한 일련의 ( Query ) 작업을 하나의 함수처럼 실행하기 위한 쿼리의 집합을

관계형 데이터베이스 관리 시스템에 저장한 것 

 

자주 사용되는 쿼리나 논리를 미리 작성해 두고, 필요할 때 호출하여 실행할 수 있는 데이터베이스의 객체

쿼리문을 하나의 메서드 형식으로 만들고 어떤 동작을 일괄적으로 처리하는 용도

 

저장 프로시저의 동작 방식

프로시저 동작원리

 

 

프로시저 장점

 

1. 최적화 및 캐시

 

프로시저의 최초 실행 시 최적화 상태로 컴파일이 되며, 이후 프로시저 캐시에 저장된다.

해당 프로세스가 여러번 사용될 때, 다시 컴파일 작업을 거치지 않고 캐시에서 가져오게 됨.

 

 

 

 

2. 유지 보수

작업이 변경될때, 다른 작업은 건드리지 않고 프로시저 내부에서 수정만 하면됨

 

 

3. 트래픽 감소

클라이언트가 직접 SQL문을 작성하지 않고, 프로시저명에 매개변수만 담아 전달하면 된다.

SQL문이 서버에 이미 저장되어있기때문에 클라이언트와 서버 간 네트워크 상 트래픽이 감소

 

 

4. 보안

프로시저 내에서 참조 중인 테이블의 접근을 막을 수 있다.

 

 

프로시저 단점

1. 호환성

구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁨

 

2. 성능

문자 또는 숫자 연산에서 프로그래밍 언어인 C, JAVA보다 성능이 느림

 

3. 디버깅

에러가 발생했을 때, 어디서 잘못됐는지 디버깅하는 것이 힘듬

 

 

 

 

저장 프로시저의 매개변수 사용 예

 

1. 입력 매개변수 ( IN )

입력 매개변수는 프로시저가 실행될 때 외부에서 값을 받아 그 값을 기반으로 작업을 수행

CREATE PROCEDURE 부서별 직원 가져오기(IN dept_id INT)
BEGIN
    SELECT emp_name, emp_salary
    FROM employees
    WHERE department_id = dept_id;
END;

 

dept_id 라는 입력 매개변수를 받아 해당 부서에 속한 모든 직원의 이름과 급여를 조회하는 예제

 

 

 

2. 출력 매개변수 ( OUT )

출력 매개변수는 프로시저가 실행된 후 외부로 결과를 반환할 때 사용

CREATE PROCEDURE GetTotalSalaryByDepartment(IN dept_id INT, OUT total_salary DECIMAL(10,2))
BEGIN
    SELECT SUM(emp_salary)
    INTO total_salary
    FROM employees
    WHERE department_id = dept_id;
END;

 

dept_id를 입력 매개변수로 받아 해당 부서의 직원 급여 합계를 total_salary라는 출력 매개변수로 반환시킴

 

 

3. 입출력 매개변수 ( INOUT )

입출력을 모두 수행하는 매개변수이며 프로시저 호출 시 값을 입력받아 작업을 수행한 후 결과를

다시 같은 매개변수로 반환

CREATE PROCEDURE AdjustSalary(INOUT emp_id INT, IN percent_increase DECIMAL(5,2))
BEGIN
    UPDATE employees
    SET emp_salary = emp_salary * (1 + percent_increase / 100)
    WHERE employee_id = emp_id;
END;

특정 직원의 급여를 입력받은 비율만큼 증가시키는 작업 수행, emp_id는 직원의 ID를 받아 동일한 값으로 반환

 

 

함수 ( Function )

---

 

하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합

함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차

 

 

보통 로직을 도와주는 역할이며, 간단한 계산, 수치 등을 나타낼 때 사용됨.

 

 

 

 

저장 프로시저와 함수의 차이점

---

 

1. 반환값의 유무

프로시저는 반환 값이 없을 수도 있으며, 여러 개의 값을 반환하거나 "OUT" 매개변수를 통해 값을 반환시킬수 있음.

 

함수는 반드시 하나의 값을 반환하며, 이 값을 SQL 쿼리 내에서 사용 가능

 

 

2. 호출 방식

저장 프로시저 : "CALL" 문을 사용하여 호출     

예) CALL GetEmploy(1);

 

함수 : SQL 문의 일부분으로 사용되며, "SELECT" 문 등에서 호출할 수 있음.

예 ) SELECT GetEmploy(11);

 

 

3. 사용 목적

저장 프로시저 : 데이터베이스 작업을 일괄 처리하거나, 복잡한 비즈니스 로직 처리하는 데 사용

 

함수 : 값을 계산하거나 특정 작업을 수행한 결과를 반환하는 데 사용

 

 

4. 트랜잭션 처리

저장 프로시저 : 트랜잭션을 시작하고, 커밋하거나 롤백할 수 있음

 

함수 : 트랜잭션을 직접 제어할 수 없으며, 데이터베이스 상태를 조회하거나 계산 결과를 반환하는 용도로 사용

 

 

 

일반 SQL 동작 방식과 저장 프로시저의 차이

---

 

일반 SQL 동작 방식

일반적으로 SQL 쿼리는 단일 작업을 수행합니다.

예를 들면 CRUD ( SELECT, INSERT, UPDATE, DELETE )문을 사용하여 데이터베이스에서

특정 작업을 수행할 수 있지만 작업들이 복잡해지거나 여러 단계의 논리를 포함해야 할 때, 쿼리 문을 여러 개를 작성하고

애플리케이션 코드에서 이들을 순차적으로 실행해야함.

 

이런 방식은 코드가 길어지고 관리가 어려워지며, 성능상으로도 비효율적이다.

 

 

저장 프로시저

복잡한 로직을 데이터베이스 서버 측에서 처리할 수 있음.

여러 개의 SQL 문을 하나의 프로시저로 묶어 복잡한 작업을 처리할 수 있으며,

저장 프로시저는 미리 컴파일되기 때문에 자주 실행되는 쿼리, 반복적인 작업의 경우에서 성능 이점이 있음.

저장 프로시저를 통해 권한을 제한할 수 있어 보안상으로 좋고, 로직을 저장해둠으로써 코드의 중복등 재사용성이 뛰어남.

 

 

 

 

 

 

 

 

이렇게 보면 무조건 저장 프로시저를 사용하는게 이점이 좋지만 

대부분의 경우에는 성능이 향상되긴하지만 항상 그렇지 않음.

 

그리고 저장 프로시저를 실행할 때 최적화 단계를 수행하지만 최적화 단계에서 인덱스를 사용할지 안할지를

결정하게 되는데, 인덱스를 사용한다고 항상 수행결과가 빨라지지 않음.

 

만약에 가져올 데이터가 다량인데 인덱스를 사용하면 오히려 성능이 저하될수 있다..