[MySQL] MySQL Procedure

 

Stored Procedure

정의

- 일련의 SQL 쿼리를 선언하여 MySQL에 저장하고 해당 SQL문을 함수처럼 실행하기 위한 쿼리의 집합

- 만들어 두기만 하면 함수처럼 편하게 사용할 수 있다.

 

사용하는 이유

1. 하나의 요청으로 여러 SQL문을 실행할 수 있어, 네트워크 부하를 줄일 수 있다.
- 50줄 이상의 복잡한 Select 문장이 있고, 각 실행시마다 Where 절 조건문이 조금식 변한다고 가정할때, 이 문장을 저장 프로시저에 넣을 경우 네트워크를 통해 전달되는 데이터 소통량이 상당히 감소 => 해당 프로시저가 자주 실행될수록 성능향상 효과 증대
- 즉 매번 전체 쿼리 텍스트를 네트워크를 통해 전달될 필요가 없는 것!

 

2. 성능 향상 또한 가능하다. 저장 프로시저가 최초로 실행되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성하고 이 실행계획이 캐시에 저장된다.
- 만약 해당 저장 프로시저가 재실행 요청을 받으면, SQL서버는 저장된 실행계획을 재사용한다.

 

3. 출력매개변수의 사용이 가능해진다.
- 단일 행을 반환하는 SQL문장을 실행시키고 싶을 때, SQL문만 사용하면 결과 집합을 레코드셋으로 반환 받아야 한다.
- 하지만 저장 프로시저 같은 경우는 성능이 월등한 출력매개변수의 사용이 가능하다.
- 만약 신규 사용자 등록 같은 단순 insert작업을 SQL 서버에 수만번 해야 한다면, 결과 집합으로 값을 받는 것에서 @key를 출력매개변수로 반환받는 경우의 이점은 엄청나게 커지게 된다.

 

예를 들어 다음과 같이 회원 정보 테이블에서 user_id가 1인 사람의 값을 출력해야 한다고 가정하자.

-- user_id가 1인 name만을 출력
SELECT name FROM users WHERE user_id = 1;

 

해당 python코드를 예시로 보면, 클라이언트는 결과 집합을 받아 따로 parsing해줘야 하는 작업이 필요하다.

result_set = cursor.fetchall()
user_name = result_set[0][0]

 

하지만 출력매개변수를 사용하는 경우는 다음과 같이 할 수 있다.(sql서버에 user_name이라는 변수에 할당)

DELIMITER //

CREATE PROCEDURE GetUserName(
    IN user_id INT,
    OUT user_name VARCHAR(100)
)
BEGIN
    -- 주어진 user_id를 기반으로 사용자 이름을 user_name 출력 매개변수에 할당
    SELECT name INTO user_name FROM users WHERE user_id = user_id;
END //

DELIMITER ;

SET @user_name = '';

-- 저장 프로시저 호출
CALL GetUserName(1, @user_name);

 

즉, 클라이언트 측에서는 parsing작업을 거치지 않아도 된다.

cursor.execute("CALL GetUserName(1, @user_name)")
cursor.execute("SELECT @user_name")
user_name = cursor.fetchone()[0]

 

4. 복잡한 시스템(수백개의 테이블 존재)에서 어디에서 어떤 테이블, 혹은 칼럼이 참조되었는지 보고 싶을 때
- 모든 코드가 저장 프로시저에 보관되어 있다면, 참조된 개체를 찾기 위해 저장 프로시저의 코드만 살펴보면 된다.

- 일련의 SQL 쿼리를 선언하여 MySQL에 저장하고, 해당 SQL문을 함수처럼 사용하는 것으로, 만들어 두기만 하면 함수처럼 호출하여 편하게 사용할 수 있다.

---

사용 방법

프로시저들 목록 확인

show procedure status;

 

프로시저 정의

DELIMITER // 

-- DEFINER은 자신 mysql 계정에 맞추어서 수정하기
CREATE DEFINER=`ssafy`@`localhost` PROCEDURE `proc_user_insert`(
	IN PARAM_ID varchar(5),
    IN PARAM_NAME varchar(10),
    IN PARAM_CAMPUS varchar(3),
    IN PARAM_CLASS int,
	IN PARAM_GI int
)
BEGIN
	-- 실행할 쿼리를 넣어준다.
    INSERT INTO ssafy_user(id, name, campus, class, gi) VALUES
    (PARAM_ID, PARAM_NAME, PARAM_CAMPUS, PARAM_CLASS, PARAM_GI);
END
DELIMITER ;

 

프로시저 호출

-- CALL 프로시저명(매개변수들);
CALL proc_user_insert('ssafy', '홍길동', '서울', 5, 11);

 

프로시저 내용 조회

-- show create procedure 프로시저명;
show create procedure proc_user_insert;

---

문법

DELIMITER

- 프로시저의 trigger에 사용

- 실제로 프로시저를 생성할 때, 여러 쿼리들을 한 번에 실행하기 위한 작업이 많다. 이때, 쿼리는 ;로 끝나는데, 이를 막기 위해 DELIMITER를 활용해 지정한 문자가 나타나기 전까지는 ;를 만나도 실행되지 않게 막아준다.

 

IN / OUT

- IN : 프로시저를 호출하기 위해 필요한 정보들로, 함수의 매개변수(인자)에 해당한다.

- OUT : 프로시저 결과의 반환값으로 생각하면 된다.

 

DECLARE

- 저장 프로시저, 함수, 트리거 내부에서 사용하는 지역 변수를 선언할 때 사용

- SET은 변수를 초기화하거나 값을 할당하는 데 사용되며. 저장 프로시저, 함수, 트리거 내뿐만 아니라 일반 SQL 스크립트에서도 사용 가능하다.

---

출처

- MySQL procedure

- 스토어드 프로시저, 스토어드 함수