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();
마지막으로 생성된 프로시드를 호출하는 부분입니다.
-- 프로시저 목록을 확인하는 명령어입니다.
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으로 생각할 수 있습니다.
< 참고 자료 >
'Programming > MySQL' 카테고리의 다른 글
MySQL JSON 데이터 타입에 활용되는 함수 정리 (0) | 2022.10.29 |
---|---|
MySQL 계정 생성 및 권한 부여 방법 정리 (0) | 2022.10.27 |
MySQL, MariaDB 데이터베이스 백업 및 복구하는 방법 (0) | 2022.10.24 |
MySQL 한글 물음표, 한글 깨짐 해결 방법 (mysql 5.6) (0) | 2021.07.31 |
MySQL 비밀번호 변경 방법, How to change MySQL root password (0) | 2021.07.24 |