MySQL 프로시저 기본적인 사용법, insert 더미데이터 넣기

MySQL에서 프로시저(Procedure)를 사용하여 더미 데이터를 넣는 작업을 해보며 정리한 MySQL 프로시저 기본적인 사용법입니다.

 

프로시저(Prodecure)란,

먼저 프로시저란 하나의 요청으로 여러 SQL문을 사용할 수 있는 방식입니다.

처리 성능과 재사용면에서의 단점은 존재하지만, 하나의 요청으로 여러 개의 SQL문을 실행할 수 있기 때문에 네트워크 소유 시간을 줄일 수 있다는 장점이 있고, 보수성이 뛰어나며, DB 관련 처리를 API처럼 만들어서 제공할 수 있다는 장점이 있습니다.

 

이처럼 프로시저는 사용 방법에 따라 다양한 기능을 적용할 수 있는데요.

아래 예시는 프로시저를 통한 insert문의 반복을 통해 더미 데이터를 넣는 예시입니다.

 

DELIMITER $$
CREATE PROCEDURE insertData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO member (data_a, data_b) VALUES (i, 'TEST');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insertData();

다음 예시는 'CREATE PROCEDURE insertData()'라는 명령어를 통해 'insertData()'라는 프로시저를 만들고, 'CALL insertData()' 명령어를 통해 프로시저의 호출하여 'BEGIN ~ END'까지의 구간을 실행시키는 것입니다.

그리고 'BEGIN ~ END' 구간에서 insert문의 반복을 통해 더미 데이터를 넣고 있습니다.

 

 

 

-- 처음과 끝의 DELIMITER는 감싸고 있는 부분이 연속적으로 실행될 수 있게 하는 역할을 합니다.
DELIMITER $$

...

DELIMITER ;

'DELIMITER'는 구분자 라는 뜻으로, 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 것을 방지하기 위해 사용되며, 기존의 구분자인 ' ; ' 세미콜론을 다른 구분자 '$$'로 바꾼다는 뜻으로 프로시저의 작성이 끝나면 다시 원래대로 되돌리고 있습니다.

 

***

프로시저 내부에서 사용하는 SQL문은 일반 SQL문이기 때문에 세미콜론 ' ; '으로 문장을 끝맺어야 합니다.

 

 

 

-- 기존에 생성된 procedure가 있다면 삭제합니다.
DROP PROCEDURE IF EXISTS insertData $$

insertData라는 이름으로 생성된 프로시드가 있다면 삭제하는 명령어입니다. 

하나의 문장이며, 위에서 구분자를 바꿨기 때문에 실행을 위해 마지막에 '$$'가 붙습니다.

 

 

 

-- procedure 'insertData()'를 생성합니다.
CREATE PROCEDURE insertData()
-- procedure의 내용은 BEGIN과 END로 감쌉니다.
BEGIN
    -- 반복에 사용될 변수 i를 선언합니다.
	DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
		INSERT INTO member (data_a, data_b) VALUES (i, 'TEST');
        SET i = i + 1;
    END WHILE;
END$$

CREATE PROCEDURE 명령어로 프로시저를 생성합니다.

BEGIN과 END 사이에 실행될 프로시드를 정의합니다.

 

 

 

-- 생성된 procedure를 호출합니다.
CALL insertData();

마지막으로 생성된 프로시드를 호출하는 부분입니다.

 

 

 

프로시저 호출 후 SELECT *

 

---

 

 

-- 프로시저 목록을 확인하는 명령어입니다.
SHOW PROCEDURE STATUS;

-- 생성된 프로시저 내용을 확인하는 명령어입니다.
SHOW CREATE PROCEDURE insertData;

해당 명령어를 통해 프로시저 목록과 내용도 확인할 수 있습니다.

 

 

 

DELIMITER $$
CREATE PROCEDURE insertData2(
    IN num1 INT, IN num2 INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO member (data_a, data_b) VALUES (num1 + num2, 'test');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL insertData2(1, 2);

위에서 본 내용은 정말 간단한 프로시저의 사용법이며, 다음과 같이 프로시저에 파라미터를 주거나 내부에서 조건문(IF), 조건문(CASE)등 다양한 활용을 할 수 있습니다.

 

 

***

- IN

파라미터에 사용된 'IN'은 프로시저에 값을 전달하기 위해 사용되며, 프로시저 내부에서 값을 수정할 수는 있지만 프로시저가 반환되고 나서의 수정은 불가능합니다. 

 

- OUT

프로시저의 값을 호출자에게 다시 Return 합니다. 초기값은 Null이며, 프로시저가 반환될 때 내부에서 선언된 값이 Return 되고, 호출자는 OUT파라미터의 값에 접근할 수 있게 됩니다.

 

- INOUT

호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정됩니다. 간단하게 IN + OUT으로 생각할 수 있습니다.

 

 

 

< 참고 자료 >

[MySQL] 스토어드 프로시저(Stored Procedure) 기본