저장 프로시저
데이터베이스에서 실행 가능한 SQL 코드의 집합, 재사용 가능한 로직을 캡슐화하여 관리
반복적으로 호출되는 작업이나 복잡한 로직을 수행할 때 성능 및 유지보수 측면에서 이점 제공
저장 프로시저(Stored Procedure, SP)는 데이터베이스 내에 저장된 일련의 SQL 쿼리를 실행하는 프로그래밍 루틴이다.
하나의 단위로 실행된다.
저장 프로시저는 데이터베이스 서버에 미리 컴파일되어 있기 때문에 실행시점에 매번 컴파일할 필요가 없어서 반복적인 쿼리 실행에 효율적이다.
저장 프로시저의 특징
1. 로직의 모듈화 : 특정 작업을 수행하는 SQL쿼리들을 하나의 모듈로 묶어 로직을 모듈화한다.
2. 재사용성 : 동일한 로직이 여러번 필요한 경우, 저장 프로시저를 호출해 사용함으로써 코드의 중복을 줄이고 유지보수성을 높인다.
3. 안정성: 데이터베이스 내에서 실행되므로 네트워크 부하나 보안 문제로 인한 데이터 유출 우려가 줄어든다.
4. 보안 강화 : 사용자는 저장 프로시저를 통해 데이터베이스에 접근하므로, 데이터베이스에 직접 접근하는 것보다 보안이 강화된다.
저장 프로시저의 장점
- 성능 향상 : 미리 컴파일 되어 있어 실행 속도가 빠르다. (여러 번 호출되더라도 컴파일 단게 다시 수행 필요 없음)
- 유지보수 용이: 중복 코드를 줄이고 코드의 모듈화로 유지보수가 용이하다.
- 보안 강화 : 데이터베이스 내에서 실행되므로 보안에 더욱 안전하다.
저장 프로시저의 단점
- DB 확장의 어려움 : 저장 프로시저는 데이터베이스에 종속되어 있어, 다른 데이터베이스로의 이전이나 확장이 어려울 수 있다.
- 낮은 처리 성능 : 문자열, 숫자열 연산에 사용할 경우, 다른 프로그래밍 언어에 비해 처리 속도가 느릴 수 있다.
[DB 확장의 어려움]
저장 프로시저가 특정 데이터베이스 시스템의 문법과 구조에 의존하기 때문 !
프로시저 : 한정된 크기의 주차장 !
주차장에는 정해진 주차 공간이 있고, 차들이 주차되면 공간은 사용된다.
프로시저는 주차 공간에 주차된 차로 생각할 수 있다. 프로시저를 실행하면 데이터베이스 서버에 미리 정의된 작업을 수행하는데,
만약 주차장 모든 공간에 새로운 차가 들어오면, 추가적인 공간을 마련하기가 어렵다.
데이터베이스 확장 : 새로운 주차장 추가 증축
새로운 주차장을 만들면 더 많은 차들이 주차할 수 있게 되고, 트래픽이나 데이터 양이 증가해도 대응할 수 있다.
=> 특정 데이터베이스 시스템에 '잠겨' 있기 때문에, 시스템을 확장하거나 이전하는 과정에서 추가적인 작업이 필요할 수 있다.
일반 함수와 차이점
함수 : 주로 데이터를 처리하거나 연산에 사용된다. 프로그래밍 언어 내에서 실행된다. 반환 값을 가지고 있어야 하며, 주로 계산이나 변환 작업을 수행한다.
저장 프로시저 : 데이터베이스 서버에서 실행되며, 데이터베이스의 데이터를 조작하거나 처리하는 데 사용된다. 주로 데이터베이스의 무결성을 유지하고 비즈니스 로직을 수행한다.
저장 프로시저의 활용
복잡한 쿼리나 데이터 처리 작업을 단일 단위로 묶어 관리할 수 있다.
비즈니스 로직을 데이터베이스 내에서 실행하여 서버 자원을 효율적으로 사용할 수 있다.
보안 측면에서 사용자가 직접 데이터베이스 테이블에 접근하지 않고, 프로시저를 통해 접근하므로 보안을 강화할 수 있다.
저장 프로시저를 사용해 데이터 검증, 로깅, 알림 등의 작업을 자동화할 수 있다.
[검증, 로깅, 알림]
검증(Vaildation) : 검수팀 ! 제품이 완성되고 제품의 품질을 점검하고 결함이 있는지 확인하는 !
데이터가 데이터베이스에 저장되기 전에 입력된 값들을 검증하여 유효성을 확인한다.
ex) 저장 프로시저는 주문 정보의 유효성을 확인하고 올바르지 않은 값이 있다면 주문을 거부한다.
로깅(Logging) : 선박 운항 중 발생하는 사건들을 일지에 기록하는 선원 !
저장 프로시저가 데이터베이스 내에서 발생하는 작업들을 기록하는 것을 의미
로그를 작성하여 나중에 오류 발생 시 디버깅이나 추적에 사용될 수 있다.
ex) 주문이 처리되면 저장 프로시저는 주문 내역을 로그에 남겨 추후 문제 발생 시 참고할 수 있게 함
알림(Notificaiton) : 핸드폰 푸쉬 알림 !
데이터베이스 내에서 특정 이벤트 발생할 때 관련된 사용자들에게 알림 보낼 수 있다.
ex) 주문 담당자에게 이메일이나 메시지 등 형태로 알림을 보내 새 주문에 대응할 수 있도록 도와준다.
저장 프로시저 관련 용어
- 파라미터(Parameter) : 저장 프로시저에 전달되는 값으로 입력(IN), 출력(OUT) 혹은 입력 및 출력(INOUT)
- 결과 집합(Return Set): 실행 결과로 반환되는 레코드 집합을 의미. 일반적으로 SELECT 사용하여 결과 생성
- 트랜잭션(Transaction) : 저장 프로시저가 실행될 때 일관성 있는 데이터 변경을 보장하기 위해 필요한 작업의 논리적인 단위
- 예외 처리(Exception Handling) : 저장 프로시저는 실행 중 발생하는 예외나 오류를 처리하는 기능으로, TRY-CATCH 블록을 사용해 구현할 수 있다.
저장 프로시저의 최적화
- 쿼리의 최적화(Optimization) : 저장 프로시저 내부의 쿼리를 효율적으로 작성하여 성능을 최적화할 수 있다. 쿼리의 실행 계획을 분석하고 인덱스를 적절히 활용하는 등 작업이 필요하다.
- 캐시 활용(Cashing) : 자주 사용되는 저장 프로시저의 실행 결과를 캐시하여 반복적인 실행 시간을 단축할 수 있다.
- 컴파일 및 재사용(Compilation and Reuse): 저장 프로시저는 미리 컴파일 되어 있으며, 동일 저장 프로시저는 반복적으로 실행될 때 재사용되어 성능을 향상시킨다.
[쿼리의 최적화, 캐시 활용]
쿼리의 최적화 : 요리를 할 때 재료를 최대한 효율적으로 사용하여 요리 시간을 단축하고 맛을 높이는 것 !
한 번에 많은 양의 재료를 한꺼번에 손질하고, 필요한 것만 사용하여 요리를 준비하는 것이 효율적이다.
마찬가지로 쿼리의 최적화는 데이터베이스에서 필요한 정보를 효율적으로 추출하기 위해
쿼리를 분석하고 인덱스를 활용하여 작업을 최적화한다.
(+. 쿼리 실행 게획 분석 : 요리할 때 레시피 작성하고 . 각 단계별 순서를 정하는 것과 유사. 쿼리의 실행 계획을 최적화함으로써 데이터베이스는 효율적으로 작업 수행하고 결과를 반환한다.
캐시 활용 : 인기 상품은 입구 근처에 놓는 식료품점 ! 고객이 구매를 원할 때 더 빠르게 제공 !
마찬가지로 자주 사용되는 쿼리 결과를 캐시에 저장해 반복적인 쿼리 실행 시간을 단축한다. 이전에 실행된 쿼리의 결과를 캐시에 저장하여 동일한 쿼리가 실행될 때 다시 계산할 필요 없이 저장된 결과를 반환함으로써 실행 속도를 향상시킨다.
저장 프로시저는 특정 작업에 대한 성능 향상과 유지보수 용이성을 고려하여 사용되어야 한다.
사용 시에는 장단점을 고려하여 적절한 상황에서 사용하는 것이 중요하다.
1. 로직의 모듈화 !
특정 작업을 수행할 쿼리를 하나의 모듈로 만든다! 가독성을 높이고 유지보수를 용이하게 한다.
2. 재사용성 !
동일한 로직 여러번 필요할 때, 저장 프로시저 호출해 사용하면 코드 중복 줄이고 유지보수성 높여 개발 시간 단축하고 효율성 높일 수 있다.
3. 성능 향상 !
저장 프로시저는 데이터베이스 서버에 미리 컴파일되어 있어 실행 속도가 빠르다. 쿼리의 최적화와 캐시 활용을 통해 성능을 더욱 향상시킬 수 있다.
4. 보안 강화 !
사용자가 저장 프로시저를 통해 데이터베이스에 접근하므로 데이터 유출 우려가 줄어들며, 접근을 효과적으로 관리할 수 있다.
5. 관리 용이성 !
저장 프로시저를 사용하여 데이터 검증, 로깅, 알림 등이 작업을 자동화할 수 있다. (하면 일관성 유지하고 관리 용이해진다.)
if 지난주 웹소켓 사용한 프로젝트에서 사용했더라면?
저장 프로시저를 호출하는 java코드를 DAO클래스 내에 통합하고 그를 호출해 데이터베이스 작업 수행할 수 있다.
회원가입,로그인 기능이라면..
회원가입, 기존회원 확인, 로그인 3가지 작업이 연속적으로 한 번에 일어나는 작업이 잦았다면!!!!
3가지 작업을 하나의 요청으로 묶어 처리할 수 있다 !!!
DELIMITER //
CREATE PROCEDURE signup_login_user (
IN p_user_id VARCHAR(255),
IN p_user_pw VARCHAR(255),
IN p_user_name VARCHAR(255),
IN p_course_id VARCHAR(255),
IN p_course_code VARCHAR(255),
OUT p_signup_result INT,
OUT p_login_result INT
)
BEGIN
-- 회원가입 처리
DECLARE existing_user INT DEFAULT 0;
SELECT COUNT(*) INTO existing_user FROM users WHERE user_id = p_user_id;
IF existing_user = 0 THEN
-- 새로운 사용자인 경우 회원가입 진행
INSERT INTO users (user_id, user_pw, user_name, course_id, course_code)
VALUES (p_user_id, p_user_pw, p_user_name, p_course_id, p_course_code);
SET p_signup_result = 1; -- 회원가입 성공
ELSE
-- 이미 존재하는 사용자인 경우
SET p_signup_result = 2; -- 이미 존재하는 사용자
END IF;
-- 로그인 처리
DECLARE user_password VARCHAR(255);
SELECT user_pw INTO user_password FROM users WHERE user_id = p_user_id;
IF user_password = p_user_pw THEN
-- 비밀번호가 일치하는 경우
SET p_login_result = 1; -- 로그인 성공
ELSE
-- 비밀번호가 일치하지 않는 경우
SET p_login_result = 0; -- 로그인 실패
END IF;
END//
DELIMITER ;public class UserDAO {
// 회원가입, 기존 유저 확인, 로그인을 하나의 저장 프로시저로 묶어 처리하는 메서드
public static int signupLoginUser(User user) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
int result = 0;
try {
conn = DBConnection.getConnection();
cstmt = conn.prepareCall("{call signup_login_user(?, ?, ?, ?, ?, ?, ?)}");
// 입력 파라미터 설정
cstmt.setString(1, user.getUser_id());
cstmt.setString(2, user.getUser_pw());
cstmt.setString(3, user.getUser_name());
cstmt.setString(4, user.getCourse_id());
cstmt.setString(5, user.getCourse_code());
// 출력 파라미터 등록
cstmt.registerOutParameter(6, Types.INTEGER); // 회원가입 결과
cstmt.registerOutParameter(7, Types.INTEGER); // 로그인 결과
// 저장 프로시저 실행
cstmt.execute();
// 결과 가져오기
result = cstmt.getInt(6); // 회원가입 결과
int loginResult = cstmt.getInt(7); // 로그인 결과
if (loginResult == 1) {
// 로그인 성공
// 여기서 필요한 작업 수행
} else {
// 로그인 실패
// 여기서 필요한 작업 수행
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cstmt != null) cstmt.close();
if (conn != null) conn.close();
}
return result;
}
}
'CS' 카테고리의 다른 글
| S)DB_07_인덱스 (1) | 2024.03.30 |
|---|---|
| S)DB_06_트리거 (1) | 2024.03.27 |
| CSS)DB_05_Schema (0) | 2024.03.25 |
| S)DB_04_RDBMS vs Nosql (3) | 2024.03.16 |
| S)DB_03_트랜잭션3 (0) | 2024.03.14 |