SQL기초 프로시저(Stored Procedure)

프로시저는 프로그램 영역이 아닌, DB자체에서 SQL구문을 관리하고자 할 때 사용한다.

 

데이터베이스에 SQL 구문을 객체화하여 호출방식으로만 사용하는 방식이다.

 

procedure 생성 방법은

delimiter //

CREATE PROCEDURE spNoCartMember()

BEGIN 
 	-- 조회할 select 문 생성
	SELECT mem_id , mem_name
	FROM `member` 
	WHERE mem_id NOT IN (SELECT cart_member FROM cart ) ;

END //

delimiter ;

위와 같은데 view에서 사용했던 쿼리문을 그대로 작성하였다.

다른점은 create procedure로 시작하고, 사용할 이름옆에 함수처럼 소괄호()를 붙인다.

 

호출할 때도 함수와 비슷한 형태로 작성한다

CALL spNoCartMember();

 

이를 응용하여 쿼리문을 작성하고, 뷰로 만들고 그것을 프로시저로 만드는 작업을 정리해 보겠다.

 

우선 쿼리문 작성

SELECT buyer_id, buyer_name , sum(cart_qty * prod_sale)
FROM buyer 
	LEFT OUTER JOIN prod
	ON (buyer_id = prod_buyer)
	LEFT OUTER JOIN cart 
	ON (prod_id = cart_prod
	AND cart_no LIKE '2005%')
GROUP BY buyer_id;

거래처별 매출합계를 조회하는 쿼리문을 작성하였다.

이때 조건은 2005년도에 판매되었던 물건이다.

그룹은 거래처아이디로하여 거래처별로 조회할 수 있도록 하였다.

cart_qty컬럼은 판매물품의 갯수이고,

prod_sale은 판매가격이다.

sum함수를 사용하여 합계를 내었다.

 

이제 view로 바꾸어 보자

delimiter //

CREATE VIEW viewGetbuyerAll AS

 	-- 조회할 select 문 생성
SELECT buyer_id, buyer_name , sum(cart_qty*prod_sale)
FROM buyer 
	LEFT OUTER JOIN prod
	ON (buyer_id = prod_buyer)
	LEFT OUTER JOIN cart 
	ON (prod_id = cart_prod
	AND cart_no LIKE '2005%')
GROUP BY buyer_id // 

delimiter ;

위와 같이 작성하여 쿼리문을 view로 변경하였다.

 

이때 조회를 하고 싶다면

SELECT *
FROM  viewGetbuyerAll;

이렇게 작성하여 조회할 수 있다.

똑같은 기능을 하는 코드를 한번 정의하고 조회하고 싶을 때마다 짧은 코드를 작성하여 쉽게 조회할 수 있다.

 

이것을 다시 procedure로 변경해 보겠다.

delimiter //

CREATE PROCEDURE spGetbuyerAll()

BEGIN 
 	SELECT *
 	FROM viewgetbuyerall ;

END //

delimiter ;

 

view자체를 프로시저내부에서 실행되게 하였다.

이제 프로시저를 호출하면 처음 만들었던 기능인 거래처별 판매합계를 조회할 수 있다.

 

프로시저 호출

CALL spGetbuyerAll();

길었던 쿼리문이 한 줄이 되었다.

반복적으로 사용해야 하는 쿼리문을 view나 프로시저로 변경하여 사용해 보자

 

기능을 수정할 때도 간편하다.

 

여러 군데에 작성되어 있는 똑같은 기능들을 하나의 프로시저로 묶고,

수정 시에 프로시저만 수정하여 사용할 수도 있다.