在MySQL存储过程中创建临时表 [英] Creating temporary tables in MySQL Stored Procedure

问题描述

当我使用CALL语句调用它时，以下过程给我一个错误:

The following procedure gives me an error when I invoke it using the CALL statement:

CREATE DEFINER=`user`@`localhost` PROCEDURE `emp_performance`(id VARCHAR(10))
BEGIN
DROP TEMPORARY TABLE IF EXISTS performance;
CREATE TEMPORARY TABLE performance AS  
    SELECT time_in, time_out, day FROM attendance WHERE employee_id = id;
END

错误显示未知表的性能" .

这是我第一次真正使用存储过程，而我的资源来自Google.我只是不知道我在做什么错.

This is my first time actually using stored procedures and I got my sources from Google. I just cant figure out what I am doing wrong.

推荐答案

我已经为您整理了一点，并添加了示例代码.我始终将参数名与其表示的字段保持相同，但以p_为前缀，以防止出现问题.我对sproc主体中声明的变量执行相同操作，但前缀为v _.

I've tidied it up a little for you and added example code. I always keep my parameter names the same as the fields they represent but prefix with p_ which prevents issues. I do the same with variables declared in the sproc body but prefix with v_.

您可以在这里找到我的另一个示例:

You can find another one of my examples here:

从MySQL中的层次结构数据生成基于深度的树(无CTE)

drop procedure if exists emp_performance;

delimiter #

create procedure emp_performance
(
in p_employee_id varchar(10)
)
begin

declare v_counter int unsigned default 0;

create temporary table tmp engine=memory select time_in, time_out 
 from attendance where employee_id = p_employee_id;

-- do stuff with tmp...

select count(*) into v_counter from tmp;

-- output and cleanup

select * from tmp order by time_in;

drop temporary table if exists tmp;

end#

delimiter ;

call emp_performance('E123456789');

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