Mysql Procedure(프로시저) 개념과 예제

보통 프로시저는 자바스크립트를 예로들자면 매개변수를 받고 리턴값을 던지는 하나의 function의 집합이라고 보면될거같다

다만 한가지의 값만 리턴할수있는 function에 비해 여러 row를 리턴시킬수있는게 프로시저의 장점이라고 할수있겠다

사용법만 능숙해지면 정말 유용한 기능이고 장점과 단점이 있으니 때에맞게 사용하면 될것같다

일단 장점과 단점을 살펴보자

-장점

>쿼리문이 보호가된다 : DB 툴을 사용하신다면 저장프로시저 항목을 보실수있고 프로시저를 수정하기전까진 프로시저내부의 쿼리를 확인할수 없다

>SQL인젝션 보호 

>세밀한 권한제어 가능

>일괄작업에 유용하다 : 보통 콘솔에서 여러 쿼리문을 실행하면 여러 요청과 여러응답을 실행하는데  프로시저 내에서 선언하고 프로시저를 호출하면        한번의 요청만 있으면됩니다 수행속도가 빠르다

>절차적인 기능 : IF ELSE WHILE  등의 기능을 구현할수 있다

>동적인 쿼리가능 : SQL문을 문자열로 저장할시에 동적인 쿼리 생성이 가능하다

-단점

>디버깅이 어렵다 : 위의 설명과 마찬가지로 수정후 쿼리결과를 확인하기가 까다롭기때문에 디버깅이 어렵다

 

>유지보수가 어렵다 : 디버깅이 어려운 이유와 마찬가지다

>짧은 쿼리에선 비효율 : 길고 복잡한 쿼리에 사용을 권장한다

>낮은 처리성능 : 다른 DBMS 보다는 처리성능이 떨어진다고함

예제를 살펴보자

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`( IN b_code varchar(255))
BEGIN
 
DECLARE _STATEMENT VARCHAR(2500);
 
    
 
    SET _STATEMENT = CONCAT('SELECT * ');
 
    SET _STATEMENT = CONCAT(_STATEMENT, ' FROM ',b_code,' WHERE 1=1 ');
 
 
    SET _STATEMENT = CONCAT(_STATEMENT);       
 
    SET @STATEMENT = _STATEMENT;
 
    PREPARE DYNQUERY FROM @STATEMENT;
 
    EXECUTE DYNQUERY;
 
    DEALLOCATE PREPARE DYNQUERY;
 
 
END

간단하지만 응용 가능성을 가진 코드이다 

보통 SET _SATATMENT 부분을 지우고 select * from table 같은 일반 쿼리를 실행해도 문제없이 돌아간다

하지만 문자열로 저장을 하게해서 매개변수로 테이블명을 넘겨주는 구조인것이다

매개변수에따라 동적인 쿼리를 구현할수있다는게 프로시저의 또다른 장점이다 

경우에따라 프로시저명에따라 쿼리를 구분하기위해 간단한 쿼리문도 전부 프로시저로 처리한 프로젝트도 봤을정도다

mybatis에서 처리하기 까다로운 조건이있을때 쓰면 좋을거같다