[Database] MySQL. Procedure 기본 활용

SQL에서 procedure란 자바의 메소드와 여러모로 유사해보인다.

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

한번의 구문으로 여러 기능들을 처리할 수 있기에 시간이 단축되고 유지보수에 편리하다는 장점이 있지만

처리성능이 떨어지고 재사용성 면이 좋지않다는 단점도 있다. 

 

procedure를 생성하는 구문은 trigger와 비슷하나 매개변수가 있다는 것이 특징이다. 

delimiter //

create procedure [프로시저 명](in_변수명, 리턴타입(in_mode) 1: 값이 있을 경우 | 0: 값이 없을 경우)

begin

쿼리문 실행

end//

delimiter ;

 

사용 시엔 call [프로시저 명](매개변수 값); 으로 호출한다.

 

바로 활용해보자.

 

전에 만들었던 product와 purchase 테이블을 활용하여 purchase에 데이터를 손쉽게 넣을 수 있는 procedure를 만들어보겠다.

매개변수는 customer, item number, purchase_amount를 받고, price와 product_name은 매개변수로 받은 item number를 통해 select 해와서 넣어주면 될 것이다.

 

이번에도 오타로 인한 스트레스를 줄이기 위해 커맨드라인 대신 워크벤치에 쿼리를 작성한다.

2~13 라인까지 길고 복잡해보이지만 이것도 역시 한줄씩 뜯어보면 복잡할거 없다.

아래는 라인1부터 17까지 각각의 라인들이 대한 해석이다.

1) insert_purchase라는 procedure가 기존에 존재할 시 삭제해준다.

2) procedure 쿼리문 내에서 구분문을 자유롭게 사용하기 위해 delimiter를 넣어준다.

3) insert_purchase라는 이름의 procedure를 생성하는데,

4) 매개변수로 varchar(20)의 in_customer 변수, int의 in_product_num 변수, int의 in_amount 변수를 받는 procedure가 된다.

5) 쿼리문 시작

6) _price 변수를 int로 선언해준다.

7) _product_name 변수를 varchar(20)으로 선언해준다.

8) product 테이블 내에서 매개변수의 in_product_num과 일치하는 아이템번호의 아이템 이름을 _product_name 변수에 대입해준다.

9) product 테이블 내에서 매개변수의 in_product_num과 일치하는 아이템번호의 아이템 단가를 _price 변수에 대입해준다.

10~11) purchase 데이터를 입력해주는데, customer에는 매개변수로 받은 in_customer를, product_name에는 _product_name을, purchase_amount에는 _in_amount를, purchase_price에는 _price를 넣어서 생성한다.

12) 쿼리문 마침

13) 구분문 정상사용화를 위해 delimiter 삽입.

 

15~17 라인은 테스트를 위해 insert_purchase를 세번 호출한 것이다.

 

참고로, 8~9 라인은 통합하여 한줄의 쿼리로 작성 가능하다.

select name, price into _product_name, _price from product where num = in_product_num;

이렇게. 

 

테스트문까지 돌리고나서 확인해보면 purchase 테이블에 데이터가 잘 추가된 것을 볼 수 있다.

이전에 product에 영향을 주는 trigger를 세팅해뒀기에 product 테이블에도 잘 반영되었다.