时间戳

首页 > 解决方案 > 插入临时表在mysql存储过程中不起作用

mysql - 插入临时表在mysql存储过程中不起作用

问题描述

我有以下存储过程：

create temporary table logData (table_name varchar(50), column_name varchar(50), changed_date date, changed_by char(10), old_value varchar(50), new_value varchar(50))

insert into logData SELECT table_name, column_name, changed_date, changed_by, old_value, new_value FROM log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value FROM reservation_flight_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value FROM ticket_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

UNION

SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value FROM passenger_record_log WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate

select * from logData order by changed_date;

在上面的 sql select 语句中empId，startDate和endDate是这个存储过程的输入参数。这是显示错误请帮助？谢谢你！

标签： mysql

解决方案

我相信您缺少两个分号：一个在第一条语句的末尾，另一个在最后一行的末尾。你可以像下面这样重写它：

CREATE temporary TABLE logData (table_name   varchar(50), 
                                column_name  varchar(50), 
                                changed_date date, 
                                changed_by   char(10), 
                                old_value    varchar(50), 
                                new_value    varchar(50));

INSERT INTO logData 
   SELECT table_name, column_name, changed_date, changed_by, old_value, new_value 
   FROM log 
   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
   SELECT "reservation_flight", column_name, changed_date, changed_by, old_value, new_value 
   FROM reservation_flight_log 
   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
   SELECT "ticket", column_name, changed_date, changed_by, old_value, new_value 
   FROM ticket_log 
   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate
UNION
   SELECT "passenger_record", column_name, changed_date, changed_by, old_value, new_value
   FROM passenger_record_log
   WHERE changed_by=empId AND changed_date BETWEEN startDate AND endDate;

SELECT * FROM logData ORDER BY changed_date;

推荐阅读