Mysql Procedure

소개

procedure 를 사용하면 반복적으로 사용되는 쿼리문을 함수 형태로 관리할 수 있습니다.
함수의 형식을 취하기 때문에 input arguments 를 통한 동적인 처리도 가능합니다.
생성된 procedure 는 CALL procedure_name 형태로 호출할 수 있습니다.

아래와 같은 형식으로 Procedure 를 생성할 수 있습니다.

DELIMITER $$
CREATE PROCEDURE test_procedure (
    IN param1 int,
    OUT param2 int
)
BEGIN
    SELECT NOW();
end $$
DELIMITER ;

------------------------------------

SHOW PROCEDURE STATUS
WHERE Name = 'test_procedure'

+-----+--------------+---------+-------+-------------------+-------------------+-------------+-------+--------------------+--------------------+------------------+
|Db   |Name          |Type     |Definer|Modified           |Created            |Security_type|Comment|character_set_client|collation_connection|Database Collation|
+-----+--------------+---------+-------+-------------------+-------------------+-------------+-------+--------------------+--------------------+------------------+
|mysql|test_procedure|PROCEDURE|root@% |2023-05-14 22:31:19|2023-05-14 22:31:19|DEFINER      |       |utf8mb4             |utf8mb4_0900_ai_ci  |utf8mb4_0900_ai_ci|
+-----+--------------+---------+-------+-------------------+-------------------+-------------+-------+--------------------+--------------------+------------------+


------------------------------------

SET @out_params = 1;
CALL test_procedure(1, @out_params)

+-------------------+
|NOW()              |
+-------------------+
|2023-05-14 22:34:34|
+-------------------+

 

Function 과의 차이

MySQL 에는 user defined function 이 존재합니다.
이는 procedure 와 유사한데요.
function 기능을 통하여 now(), from_unixtime() 등과 같은 built-in function 을 직접 제작할 수 있습니다.
procedure 와 function 은 유사하여 서로 호환되는 면이 많은데, 분명한 차이점이 존재합니다.
 
먼저 Function 의 활용 예시는 아래와 같습니다.
 

DELIMITER ||
CREATE FUNCTION test_function(param1 int) RETURNS DATETIME
DETERMINISTIC
BEGIN
    RETURN NOW();
end ||
DELIMITER ;

------------------------------------------------------

select test_function(1)

+-------------------+
|test_function(1)   |
+-------------------+
|2023-05-14 22:38:03|
+-------------------+

1. 호출 방식이 다릅니다.
procedure 는 call statement 를 통하여 호출됩니다.
그리고 query 내부에서 활용될 수 없습니다.
반면, function 은 쿼리 내부에서 자연스럽게 사용됩니다.
그래서 function 은 단순한 내용으로 구성하여 쿼리 내부에서 활용되는 것이 좋습니다.
procedure 는 복잡하거나 반복적인 쿼리로 구성하는 방식이 선호됩니다.

2. 파라미터의 활용 방식이 다릅니다.
프로시저는 input, output 파라미터를 가집니다.
변수를 레퍼런스 형태로 넘겨주어 output 파라미터로 활용할 수 있습니다.
반면 함수는 input 파라미터만이 존재합니다.
함수에선 return 문을 활용하여 output 를 처리합니다.

 

procedure 조회

“show procedure status” query 로 생성된 프로시저들을 조회할 수 있습니다.

<관련 예시>

mysql> SHOW PROCEDURE STATUS LIKE 'sp1'\G
*************************** 1. row ***************************
                  Db: test
                Name: sp1
                Type: PROCEDURE
             Definer: testuser@localhost
            Modified: 2018-08-08 13:54:11
             Created: 2018-08-08 13:54:11
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

 

procedure 생성

주어진 횟수만큼 for-loop 를 반복하며서 insert 를 하는 프로시저를 만들어보겠습니다.

<create procedure example>

create table test_table (
    id int AUTO_INCREMENT primary key,
    name varchar(32)
);

delimiter $$
DROP PROCEDURE IF EXISTS insert_bulk_data$$
create procedure insert_bulk_data()
begin
declare i int default 1;
while(i<10000) DO
insert into test_table (name) value (concat('name',i));
set i=i+1;
end while;
end $$

call insert_bulk_data();

select *
from test_table
order by id desc
limit 10


+----+--------+
|id  |name    |
+----+--------+
|9999|name9999|
|9998|name9998|
|9997|name9997|
|9996|name9996|
|9995|name9995|
|9994|name9994|
|9993|name9993|
|9992|name9992|
|9991|name9991|
|9990|name9990|
+----+--------+

위 프로시저 생성문에서 2개의 delimeter가 사용되었습니다.
각각 $$ 와 ; 가 사용되었는데요.
Create procedure 와 insert 쿼리문을 완료하기 위하여 사용되었습니다.
mysql 에서는 delimeter 의 문자의 변경이 가능한데요.
procedure 의 body 에 해당하는 insert 쿼리를 끝맺는 ; 와 create procedure 문을 끝맺는 $$ 가 사용됩니다.

procedure 삭제

drop 키워드로 procedure 를 삭제할 수 있습니다.

drop procedure procedure_name