MySQL에서 특정 저장 프로시저가 실제로 생성되어 있는지 확인해야 하는 경우가 자주 있습니다. 이 글에서는 예제 저장 프로시저를 직접 만들어 보고, SHOW CREATE PROCEDURE 명령과 information_schema를 활용해 존재 여부를 확인하는 방법을 단계별로 살펴보겠습니다.
1. 테스트용 저장 프로시저 생성하기
먼저 확인 작업에 사용할 간단한 저장 프로시저를 생성합니다. 아래 프로시저는 입력받은 날짜에 지정한 개월 수를 더한 결과를 반환합니다.
mysql> DELIMITER //
mysql> CREATE PROCEDURE ExtenddatesWithMonthdemo(IN date1 datetime, IN NumberOfMonth int)
-> BEGIN
-> SELECT DATE_ADD(date1, INTERVAL NumberOfMonth MONTH) AS ExtendDate;
-> END;
-> //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;
프로시저 본문을 작성하기 전에 DELIMITER //로 구분 기호를 변경하고, 작성이 끝난 후 DELIMITER ;로 원래대로 되돌리는 점에 유의하세요.
2. SHOW CREATE PROCEDURE로 존재 여부 확인하기
저장 프로시저가 정상적으로 생성되었는지 확인하는 가장 직관적인 방법은 SHOW CREATE PROCEDURE 명령을 사용하는 것입니다. 프로시저가 존재하면 정의 내역이 출력되고, 존재하지 않으면 오류가 발생합니다.
mysql> SHOW CREATE PROCEDURE ExtenddatesWithMonthdemo;
실행 결과는 다음과 같습니다. 위에서 생성한 저장 프로시저의 상세 정보가 표시됩니다.
+--------------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+--------------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| ExtenddatesWithMonthdemo | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `ExtenddatesWithMonthdemo`(IN date1 datetime, IN NumberOfMonth int)
BEGIN
SELECT DATE_ADD(date1,INTERVAL NumberOfMonth MONTH) AS ExtEndDate;
END | utf8 | utf8_general_ci | utf8_general_ci |
+--------------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
출력 결과에는 프로시저 이름, SQL 모드(sql_mode), 전체 정의문(Create Procedure), 문자셋 및 콜레이션 정보가 포함되어 있습니다. 이 정보가 정상적으로 조회된다면 해당 저장 프로시저가 데이터베이스에 존재한다는 뜻입니다.
3. information_schema로 프로그래밍 방식으로 확인하기
스크립트나 애플리케이션 코드 안에서 조건 분기 처리를 하려면 information_schema.ROUTINES 테이블을 조회하는 편이 더 유용합니다.
mysql> SELECT ROUTINE_NAME
-> FROM information_schema.ROUTINES
-> WHERE ROUTINE_TYPE = 'PROCEDURE'
-> AND ROUTINE_SCHEMA = 'your_database_name';
결과 목록에 프로시저 이름이 포함되어 있으면 존재하는 것이고, 없다면 아직 생성되지 않은 것입니다. 필요하다면 DROP PROCEDURE IF EXISTS, CREATE PROCEDURE와 함께 사용해 재생성 로직을 구성할 수도 있습니다.
4. CALL 명령으로 저장 프로시저 실행하기
존재가 확인되었다면 CALL 명령으로 실제 동작까지 검증할 수 있습니다. 아래는 2019년 2월 13일에 6개월을 더하는 예제입니다.
mysql> CALL ExtenddatesWithMonthdemo('2019-02-13', 6);출력 결과
+---------------------+
| ExtEndDate |
+---------------------+
| 2019-08-13 00:00:00 |
+---------------------+
1 row in set (0.10 sec)
Query OK, 0 rows affected (0.12 sec)
입력 날짜에서 정확히 6개월이 더해진 2019-08-13이 반환된 것을 확인할 수 있습니다.
정리
SHOW CREATE PROCEDURE 프로시저명;— 프로시저의 정의 전체를 조회하여 존재 여부와 내용을 한 번에 확인information_schema.ROUTINES조회 — 스크립트·애플리케이션에서 프로그래밍 방식으로 존재 여부 판별CALL명령 — 프로시저가 의도대로 동작하는지 실행까지 검증
이 세 가지 방법을 상황에 맞게 활용하면 MySQL 환경에서 저장 프로시저의 존재 여부와 정상 동작을 손쉽게 관리할 수 있습니다.