[DB] 저장 프로시저

저장 프로시저란?

• 여러 쿼리문을 힌번에 사용하기 위해 모듈화하여 저장해둔 집합
• 함수처럼 사용됨

특징

• 재사용성
  • 여러 응용 프로그램이나 사용자가 동일한 로직을 재사용할 수 있습니다.
• 성능 향상
  • 한 번 컴파일된 후 저장되므로 실행 속도가 빠릅니다.
• 보안 강화
  • 직접 SQL 쿼리를 노출하지 않고 프로시저를 통해 접근할 수 있어 보안이 강화됩니다.
• 유지보수 용이
  • 로직이 데이터베이스 내에 존재하므로, 변경이 필요할 때 저장 프로시저만 수정하면 됩니다.
• 트랜잭션 관리
  • 여러 SQL 문장을 하나의 트랜잭션으로 묶어 처리할 수 있습니다.

---

뷰, 함수, 저장 프로시저의 차이

• 뷰
  • 여러번 사용할 가상 테이블을 미리 만들어두는 느낌
  • 실제로 새로운 테이블이 생성 되거나 데이터가 저장되진 않음
  • 데이터를 직접 수정할 수 없으며, 데이터를 조회하는 데 사용
• 함수
  • 단일 값을 반환하며, SELECT 문에서 호출하여 사용할 수 있습니다.
  • 데이터베이스 상태를 변경할 수 없습니다.
  • 프로시저 내에서 함수를 호출할 순 있지만 함수 내에서 프로시저 호출 불가능
• 저장 프로시저
  • 데이터뿐만 아니라 처리 로직을 db에 저장하여 반복호출하는 형태
  • 인자를 받아서 상태값을 돌려주는 프로시저

함수 vs 저장 프로시저

• 함수
  • 특정 계산을 수행
  • 클라이언트에서 실행
• 저장 프로시저
  • 특정 작업을 수행
  • 서버에서 실행 → 클라이언트에서 실행되는 함수보다 속도가 빠름
• 함수가 여러 작업을 위한 기능이라면 저장 프로시저는 일련의 작업들을 정리한 절차
• 매개변수
  • 프로시저
    • 입력, 출력, 입출력 형식 가능
  • 함수
    • 입력 형식만 가능
• 반환값
  • 프로시저
    • 상관없음
  • 함수
    • 반환값이 반드시 있어야함
• 쿼리문 내 사용
  • 프로시저
    • select, where 문 등에서 사용 불가
  • 함수
    • select, where 문 등에서 사용 가능

---

작동 방식

일반 쿼리문은?

1. 일반 쿼리문에서는 sql문을 데이터베이스 서버로 보낸 후
2. sql문을 파싱(구문 분석, 개체 이름 확인, 사용권한 확인 절차)한 후
3. 실행 계획을 최적화하여 생성하고
4. 이때 생성한 실행 계획을 컴파일한 후 캐시에 저장하며 실행한다.

저장 프로시저는?

• 저장 프로시저는 미리 컴파일되고 최적화된 상태로 데이터베이스에 저장
  • 호출 시 초기 구문 분석과 최적화 단계를 생략하고 바로 실행 계획을 사용할 수 있음
  • 반복적으로 사용하는 로직은 저장 프로시저로 작성하는 것이 효율적

• Callablestatement
  • 저장 프로시저를 실행시키기 위해 사용되는 인터페이스
  • 쿼리문을 하나의 파일 형태로 만듣거나 데이터베이스에 저장해두고 함수처럼 호출해서 사용하는 것
  • ++
    • 저장 프로시저를 쓰면 좋지만 db에 디팬던시가 걸리게 되니깐 고려해봐야한다.
      • 각 db는 저장 프로시저를 정의하고 호출하는 문법이 다르기때문에
      • 또, 데이터베이스를 이전하려면 새 시스템에 맞게 저장 프로시저를 재작성해야할 수 있음.
    • 하지만, 자바 내부에 sql문장이 안들어가도 되어서 자바 코드가 sql에 독립적이게 된다.
  • +++
    • 오라클은 프로시저 속도가 잘나는데
    • postgreSQL → 오라클이랑 프로시저 호환이 잘됨
    • 그래서 오라클 쓰던 기업들이 postgreSQL로 갈아타려고 한다~~
    • postgreSQL은 함수가 프로시저를 포함하고 있음

쿼리문

• 저장 프로시저 생성

Delimiter //
CREATE PROCEDURE GetEmployeeInfo (IN empno INT)
BEGIN
    SELECT * FROM emp WHERE emp.empno = empno;
    End //
    
Delimiter ;

• 저장 프로시저 호출

CALL GetEmployeeInfo(7369);