'query'라는 이름의 데이터베이스를 사용 중이며, 해당 데이터베이스에는 다음과 같은 테이블들이 존재한다고 가정해 보겠습니다.
현재 데이터베이스의 테이블 확인
mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail | | student_info | +-----------------+ 2 rows in set (0.00 sec)
저장 프로시저 생성하기
다음은 데이터베이스 이름을 매개변수로 전달받아, 해당 데이터베이스에 속한 테이블들의 목록을 상세 정보와 함께 출력하는 저장 프로시저입니다.
mysql> DELIMITER//
mysql> CREATE procedure tb_list(db_name varchar(40))
-> BEGIN
-> SET @z := CONCAT('Select * from information_schema.tables WHERE table_schema = ','\'',db_name,'\'');
-> Prepare stmt from @z;
-> EXECUTE stmt;
-> END //
Query OK, 0 rows affected (0.06 sec)프로시저의 동작 원리
이 저장 프로시저는 다음과 같은 단계로 동작합니다.
- CONCAT(): 매개변수로 받은 데이터베이스 이름을 활용해 information_schema.tables를 조회하는 SELECT 쿼리 문자열을 동적으로 생성합니다.
- PREPARE: 생성된 쿼리 문자열을 실행 가능한 준비된 문장(prepared statement)으로 변환합니다.
- EXECUTE: 준비된 문장을 실제로 실행하여 결과를 반환합니다.
여기서 information_schema.tables는 MySQL 서버 내 모든 테이블의 메타데이터(테이블 이름, 엔진 종류, 행 수, 생성 시각, 콜레이션 등)를 담고 있는 시스템 뷰입니다. 따라서 단순히 테이블 이름만 보여주는 SHOW TABLES와 달리, 각 테이블에 대한 훨씬 자세한 정보를 한 번에 확인할 수 있습니다.
저장 프로시저 호출하기
이제 데이터베이스 이름을 매개변수로 지정하여 저장 프로시저를 호출해 보겠습니다.
mysql> DELIMITER;
mysql> CALL tb_list('query')\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: query
TABLE_NAME: student_detail
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 4
AVG_ROW_LENGTH: 4096
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2017-12-13 16:25:44
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: query
TABLE_NAME: student_info
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 4
AVG_ROW_LENGTH: 4096
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2017-12-12 09:52:51
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
2 rows in set (0.00 sec)실행 결과를 보면 'query' 데이터베이스에 있는 두 개의 테이블(student_detail, student_info)에 대해 사용 중인 스토리지 엔진(InnoDB), 행 개수, 평균 행 길이, 데이터 크기, 생성 시각, 콜레이션 등 다양한 상세 정보가 함께 출력되는 것을 확인할 수 있습니다. 이처럼 저장 프로시저를 활용하면 데이터베이스 이름만 바꿔 전달하면 되므로, 여러 데이터베이스의 테이블 정보를 반복적으로 조회해야 할 때 매우 유용합니다.