在存储过程中创建临时表 [英] Creating temporary table inside stored procedure

问题描述

我正在为 MySQL 编写我的第一个存储过程.

I am working on writing my first stored procedure for MySQL.

存储过程需要创建一个临时表，然后在其中插入一些数据.

The stored procedure needs to create a temporary table and then insert some data into it.

如果我在存储过程之外手动创建一个普通表，那么存储过程不会创建临时表，存储过程是创建的，并且完全符合我的要求.

If I manually create a normal table, outside of the stored proc, so the stored proc isn't creating the temporary table the stored proc is created and does exactly what I'm after.

如果我将创建临时表添加到存储过程中，那么 MySQL 工作台会显示意外的结束和创建语句的 en.

If I then add the create temporary table to the stored proc instead, MySQL workbench then shows unexpected end and the en of the create statement.

下面是我的存储过程

    DELIMITER //
    CREATE PROCEDURE getLast24Hours()
    BEGIN
#End of the below line I get unexpected end without this it works fine
        CREATE TABLE hours (`Hour` VARCHAR(8) NOT NULL, `Value` INT NOT NULL DEFAULT 0, PRIMARY KEY(`Hour`)); 

        DECLARE crt_time VARCHAR(8);
        DECLARE counter INT;
        DECLARE currentTime DATETIME;

        SET currentTime = NOW();

        SET crt_time = DATE_FORMAT(currentTime, '%d-%m %H');
        SET counter = 0;
        WHILE counter < 23 DO
            INSERT INTO hours (`Hour`) VALUES (crt_time);
            SET currentTime = currentTime - INTERVAL 1 HOUR;
            SET crt_time = DATE_FORMAT(currenttime, '%d-%m %H');
            SET counter = counter + 1;
        END WHILE;
    END //
    DELIMITER ; 

我看不到任何可以说创建临时表是不可能的，所以我做错了什么.

I can't see anything to say that creating a temporary table isn't possible so what am I doing wrong.

推荐答案

https://dev.mysql.com/doc/refman/5.7/en/declare.html 说:

DECLARE 只允许在 BEGIN ... END 复合语句中使用，并且必须在其开头，在任何其他语句之前.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

(强调我的)

如下:

CREATE PROCEDURE getLast24Hours()
    BEGIN
        DECLARE crt_time VARCHAR(8);
        DECLARE counter INT;
        DECLARE currentTime DATETIME;

        CREATE TABLE hours (`Hour` VARCHAR(8) NOT NULL, `Value` INT NOT NULL DEFAULT 0, PRIMARY KEY(`Hour`)); 

这篇关于在存储过程中创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！