提问人：user9846973 提问时间：9/30/2023 更新时间：9/30/2023 访问量：38
MySQL中存储过程中的游标
Cursor in stored procedure in MySQL
问：
我有这个代码：
DELIMITER //
CREATE PROCEDURE GetColumnMaxLengths(IN schema_name
VARCHAR(255), IN table_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE column_name VARCHAR(255);
DECLARE data_type VARCHAR(255);
DECLARE column_type VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT
`COLUMN_NAME`,
`DATA_TYPE`,
`COLUMN_TYPE`
FROM
`INFORMATION_SCHEMA`.`COLUMNS`
WHERE
`TABLE_SCHEMA` = schema_name
AND
`TABLE_NAME` = table_name
AND
`DATA_TYPE` NOT IN (
"date","time","year","datetime","timestamp",
"enum","set",
"geometry","point","linestring","polygon",
"multipoint","multilinestring","multipolygon","geometrycollection"
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS `_tmp`;
CREATE TEMPORARY TABLE `_tmp` (
`column_name` VARCHAR(255) NOT NULL,
`data_type` VARCHAR(255) NOT NULL,
`column_type` VARCHAR(255) NOT NULL,
`max_value` VARCHAR(255) NOT NULL,
PRIMARY KEY (`column_name`)
) ENGINE = InnoDB;
OPEN cur;
read_loop:
LOOP
FETCH cur INTO column_name, data_type, column_type;
IF done THEN
LEAVE read_loop;
END IF;
SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', column_name, '`)) INTO @max_value FROM `', schema_name, '`.`', table_name, '`;');
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(column_name, data_type, column_type, @max_value);
END LOOP read_loop;
CLOSE cur;
SELECT `column_name`, `data_type`, `column_type`, `max_value` FROM `_tmp`;
DROP TEMPORARY TABLE IF EXISTS `_tmp`;
END;
//
DELIMITER ;
执行过程继续进行，不会遇到任何错误;但是，我面临的情况是，尽管没有错误，但临时表无法积累任何数据。让我感到困惑的困境是，负责获取记录的游标是否确实正确地执行了这项任务，或者问题的根源是否在于旨在填充上述临时表的插入查询的复杂性。
有人可以帮我吗？
答：
3赞
P.Salmon
9/30/2023
#1
应避免为参数和声明的变量提供与表列相同的名称。在您的情况下，AND = table_name 是一个问题。TABLE_NAME
我建议
DELIMITER //
CREATE PROCEDURE p(IN schema_name
VARCHAR(255), IN ptable_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE vcolumn_name VARCHAR(255);
DECLARE vdata_type VARCHAR(255);
DECLARE vcolumn_type VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT
`COLUMN_NAME`,
`DATA_TYPE`,
`COLUMN_TYPE`
FROM
`INFORMATION_SCHEMA`.`COLUMNS`
WHERE
`TABLE_SCHEMA` = schema_name
AND
`table_name` = ptable_name
AND
`DATA_TYPE` NOT IN (
"date","time","year","datetime","timestamp",
"enum","set",
"geometry","point","linestring","polygon",
"multipoint","multilinestring","multipolygon","geometrycollection"
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS `_tmp`;
CREATE TEMPORARY TABLE `_tmp` (
`column_name` VARCHAR(255) NOT NULL,
`data_type` VARCHAR(255) NOT NULL,
`column_type` VARCHAR(255) NOT NULL,
`max_value` VARCHAR(255) NOT NULL,
PRIMARY KEY (`column_name`)
) ENGINE = InnoDB;
#select schema_name, ptable_name;
OPEN cur;
read_loop:
LOOP
FETCH cur INTO vcolumn_name, vdata_type, vcolumn_type;
#select vcolumn_name,vdata_type,vcolumn_type;
IF done THEN
#select concat (done,' leaving');
LEAVE read_loop;
END IF;
SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', vcolumn_name, '`)) INTO @max_value FROM `', schema_name, '`.`', ptable_name, '`;');
#select @sql_query;
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#select @max_value;
INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(vcolumn_name, vdata_type, vcolumn_type, @max_value);
END LOOP read_loop;
CLOSE cur;
SELECT `column_name`, `data_type`, `column_type`, `max_value` FROM `_tmp`;
DROP TEMPORARY TABLE IF EXISTS `_tmp`;
END;
//
DELIMITER ;
随意添加调试选择
评论