假設(shè)當(dāng)前我們使用一個(gè)名為“query”的數(shù)據(jù)庫(kù)，其中包含以下表格 –
mysql> Show tables in query; +-----------------+ | Tables_in_query | +-----------------+ | student_detail | | student_info | +-----------------+ 2 rows in set (0.00 sec)
登錄后復(fù)制
現(xiàn)在，以下是一個(gè)存儲(chǔ)過(guò)程，它將接受數(shù)據(jù)庫(kù)的名稱作為其參數(shù)，并為我們提供包含詳細(xì)信息的表列表 –
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)
登錄后復(fù)制
現(xiàn)在通過(guò)提供數(shù)據(jù)庫(kù)名稱作為其參數(shù)來(lái)調(diào)用此存儲(chǔ)過(guò)程 –
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)
登錄后復(fù)制
以上就是創(chuàng)建一個(gè)MySQL存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程以數(shù)據(jù)庫(kù)名稱作為參數(shù)，列出特定數(shù)據(jù)庫(kù)中包含詳細(xì)信息的表。的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注www.92cms.cn其它相關(guān)文章！
