Mysql 프로시저 그게 뭔데?

• 실제로 프로시저와 함수는 실무에서 많이 쓰고 있다.
• 하지만 둘의 차이가 무엇이냐고 물어보면 모른다. 애초에 함수랑 프로시저가 뭔지도 잘 모른다.
• 일단 이 중 하나인 프로시저부터 알아보자

SQL 프로시저

• 각 SQL마다 약간의 차이가 있지만, MySQL 에서는 Store Procedure 라고 한다.
  

• 만들면 아래와 같이 생성된다.
  • 여기서 프로시저 이름은 작은따옴표(’)가 아닌 백틱이다()

• 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

저장 프로시저 구조

CREATE OR REPLACE PROCEDURE `프로시저명`(
		 IN 변수명1 데이터타입, 
		 OUT 변수명2 데이터타입
) -- 인자 값은 필수 아님

BEGIN
 필요한 기능; -- 인자값 활용 가능
END;

--- 쿼리문에서 사용 --- 

CALL 프로시저명; -- 호출

• 실습내용

프로시저 파헤치기

• DEFINER
  • 프로시저를 만든 사람을 뜻함.(권한포함)
  • 더 자세히들어가면, Security 범위라 생략
• IN
  • 프로시저의 파라미터로 매개변수의 복사본을 만든다.
  • 프로시저 내부에서만 사용할 수 있는 값이다.
• OUT
  • 값을 호출자에게 다시 전달
    • 즉, 집어넣은 파라미터가 변경되는 것
  • 초기값은 프로시저 내에서 NULL값이며 프로시저가 반환될 때 ****새로운 값이 호출자에게 리턴
• INOUT
  • IN 과 OUT 의 기능을 합친 기능
• BEGIN~ END 사이
  • 여러 쿼리문 작성 (조회, 수정, 정렬등 사용가능)
  • 위에 선언한 변수들 사용가능
• CALL
  • 프로시저를 호출하는 명령어
  • 참고 공식문서

정리

• 예를 들어 , 특정 테이블에 한글로 된 이름이있는 컬럼이 있는 테이블에서 항상 조회할때, 이름의 오름차순으로 조회된 결과를 받고 싶다면
  • 프로시저로 구현해놓고
  • 쿼리문 요청은 CALL [프로시저명] 으로 시켜버릴 수 있다.

장점

• 최적화 & 캐시 → 미리만들어 놓을 수 있기때문에 가능
• 유지보수 → 프로시저 내부만 수정하면 됨
• 트래픽감소 → 요청 데이터가 축소됨(프로시저의 파라미터만 잘 넘겨주면됨.)
  • 네트워크 소요시간 절감
  • 하나의 요청으로 여러 쿼리문을 실행시킬 수 있음
• 보안 → 프로시저 내의 데이터를 반환할지 안할지를 정할 수 있다.

단점

• 성능저하
  • 프로시저는 별도의 Mysql 엔진에서 해석되어 실행되기 때문에 상대적으로 떨어진다.(최적화 부족인 엔진)
  • 이 기능도, 문자열 조작, 숫자연산에만 해당
• 에러출처 파악
  • 여러개의 쿼리문을 하나의 요청에 의해 처리하기때문에, DB 서버의 Log를 들여다 봐야한다.
• 낮은 호환성
  • Mysql 의 프로시저와 Oracle의 프로시저만 잠깐 보더라도 문법자체가 많이 상이하다.
  • 즉, DB을 전환하게되면 코드 재사용성이 나쁘다.

예상 모의면접 질문

1. sql 프로시저에 대해 설명해주세요.
   • 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
2. 프로시저가 필요한 이유를 말씀해주세요.(선택)
   • 여러줄의 쿼리문을 한번의 요청으로 실행하고 싶을 경우
3. 프로시저의 장점과 단점에 대해 아는 만큼 말씀해주세요.
   • 장점은 사용할 다양한 쿼리문을 미리 만들어놓고, 간단하게 프로시저 호출만으로 해결할 수 있어, 최적화와 유지보수에 좋고, 네트워크 소요시간 감소와 보안적인 장점이 있습니다.
   • 단점으로는 별도의 엔진을 사용하기때문에, 성능저하 및 낮은 호환성의 문제가 있으며, 에러 발생시, 출처를 파악하는데 오래 걸릴 수있습니다.

Mysql 함수

• 다음 포스팅에 정리 할 예정