假设当前我们使用一个名为“query”的数据库,其中包含以下表格 -Mysql> Show tables in query; +-----------------+ | Tables_in_query | +-------------
假设当前我们使用一个名为“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)
现在通过提供数据库名称作为其参数来调用此存储过程 -
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)
--结束END--
本文标题: 创建一个MySQL存储过程,该存储过程以数据库名称作为参数,列出特定数据库中包含详细信息的表。
本文链接: https://www.lsjlt.com/news/437715.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-13
2024-05-13
2024-05-13
2024-05-13
2024-05-12
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0