MySQL中的存储过程

41zrol4v 于 2023-10-15 发布在 Mysql

关注(0)|答案(1)|浏览(148)

我有这个代码：

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 ;

执行过程继续进行，不会遇到任何错误;然而，我面临的情况是，尽管没有错误，但临时表无法积累任何数据。令我困惑的难题是，负责获取记录的游标是否确实正确地执行了这项任务，或者问题的根源是否在于旨在填充上述临时表的插入查询的复杂性。
有人能帮帮我吗？

mysql

来源：https://stackoverflow.com/questions/77206469/cursor-in-stored-procedure-in-mysql

1条答案

按热度按时间

4ioopgfo1#

应该避免给参数和声明的变量与表列同名。在您的例子中，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 ;

随意添加调试选择

赞(0）回复(0）举报 2023-10-15

我来回答

MySQL中的存储过程

1条答案

相关问题

热门标签

最新问答