빠르게 정리하는 데이터베이스 (2) - MySQL Stored Procedure

Stored Procedure

SQL은 근본적으로는 non-procedure Language이지만, Stored Procedure가 있기 때문에 procedural Language 취급을 받기도 한다.

• 반복되는 작업 수행을 위해 서버에 저장해 둔 일련의 명령을 의미함.
• compiled Language만큼 빠르진 않지만 속도도 나쁘지 않음.
• Application Independent. 애플리케이션 구현 언어에 독립적으로 작동 가능하다.

• 애플리케이션에 필요한 기능을 모아두고,
• 매번 sql문을 생성하고 실행할 때보다 시간 / 돈 절약이 가능함.

애플리케이션은 DB와 통신하는데, 보통 여기가 bottleNeck인 경우가 많음.

• 직접 쿼리를 internet 통해서 DB 서버로 전송할 경우, 인터넷 속도가 곧 애플리케이션 성능과 연결되기에 scale up이 쉽지 않음
• 따라서 stored procedure를 저장해두고, 애플리케이션은 해당 프로시저를 호출하는 식으로 사용한다.

단점?

• 메모리 차지
• MySQL의 경우 procedural language로서의 성능이 좋지 못한 편. if else 조건문만 걸어도 cpu 의존도가 상승한다.
• 디버그가 어려움.

Stored Procedure in MySQL

• Group Multiple sql statements -> execute as a single logical unit.

DELIMITER $$ 
-- Stored Procedure 정의하기 위한 delimiter는 $$.

CREATE PROCEDURE firstProcedure()

BEGIN
    SELECT * FROM PERSON;
END $$

DELIMITER ;
-- stored procedure 정의가 끝나면 sql의 delimiter인 ;로 다시 설정한다.

Call firstProcedure();
-- procedure 호출하는 명령어


show procedure status;
-- 해당 DB에 등록된 모든 procedure 조회하기.

show procedure status like '%first%';
-- procedure 이름으로 조회하기

drop procedure if exists firstProcedure;
-- procedure 삭제.


declare total int default 0;
-- 변수 선언하기.

set total = 10;
-- 변수에 값 할당하기

-- ex) 변수를 사용한 procedure 정의하기.
Delimiter $$

create procedure countCities()
begin
    declare total dec(10,2) default 10.0;
    select count(*) into total from city;
    select total;
end $$

Delimiter ;

call countCities();

애플리케이션이 DB Server와 커넥션을 연결하고 DB에 접근할 때 new Session이 할당된다. Connection 생성 시 Session이 만들어지고, connection이 끝나면 만료되는 식.

이 Session동안 유지되는 변수를 procedure에서 생성하고 사용할 수 있다.

-- Session Variable 선언하기: declare 없이 바로 set으로 선언하고, '@변수명' 형태로 사용한다.
set @val = 12;
set @val = "My name is k"; -- 앞서 정의한 변수를 덮어쓴다.

select @val;

-- Parameters: in, out, inout
Delimiter $$

create procedure countCities(
    in country char(3)
    out total int
)
begin
    select count(*) 
    into total 
    from city 
    where countryCode = country;
end$$

Delimiter ;

-- out으로 리턴될 값을 저장할 session Value을 사용할 수 있다.
call countCitites('USA', @total);
select @total;


-- if / else (Conditional Statement) 사용하기.

Delimiter $$

Create procedure labelCountry(
    in country char(3),
    out label varchar(20)
)

begin
    declare totalPopulation int default 0;
    select populatioin into totalPopulation
    from country 
    where code=country;

    if totalPopulation > 10000000 then
        set label = 'large Country';
    else 
        set label = 'small Country';
    end if;
end $$

Delimiter ;

call labelCountry('USA', @label);
select @label; -- return 'Large Country'

-- 기존에 생성해둔 procedure 삭제: drop procedure '프로시저 이름'


-- Loop 사용하기

Delimiter $$

create procedure loopExample()

begin
    declare a int default 1;
    declare s varchar(255);

    loop_label: loop

        -- break loop 방법 in MySQL.
        if a > 10 then
            leave loop_label;
        end if;

        set a = a + 1;

        -- continue에 해당하는 operation.
        if a = 3 then
            iterate loop_label;
        end if;


        set s = concat(s, a, ' ');
    end loop;

    select a;

end $$

Delimiter ;

call procedure loopExample();
-- return '2 4 5 6 7 8 9 10 11'


--- while

Delimiter $$

create procedure loopExample2()

begin
    declare a int default 0;
    declare s varchar(255) default '';

    while length(s) < 5 do
        set a = a + 1;
        set s = concat(s, a,'');
    end while;

    select s;
end $$

Delimiter ;

call procedure loopExample2();
-- return '12345'



--- How to return multiple values


Delimiter $$

create procedure multipleValues(
    out population_counter int,
    out life_counter int
)

begin
    select count(*) into population_counter
    from country
    where population > 1000000;

    select count(*) into life_counter
    from country
    where life_expectancy > 70;

end $$

Delimiter ;

call multipleValues(@c1, @c2);
select @c1, @c2;