我在存储过程中添加了个临时表 存储过程可以正常建立,但是在调用时显示没有发现数据表,根据执行过程看,是提示我创建的临时表不存在,大致语句如下,相关部分我用红色标明了,(另:临时表的建立不支持text模式么?我在表格里面创建text字段可以执行,我删除了所有非varchar字段再执行还是报不存在该临时表)
create procedure venetostatistics(in dateBegin date,in dateEnd date) begin declare softwareamount1 decimal;
declare salesman1,saletype1,contractnum1 varchar(128);
declare countmeeting1 smallint(6);
declare customername1 varchar(255);
declare re1 text;
declare collectiontime1 date;
declare int;begin
drop table if exists tmp_table_a1;
create temporary table tmp_table_a1(contractnum VARCHAR(128) NOT NULL,salesman
varchar(128),saletype varchar(128),countmeeting smallint(6),softwareamount
decimal(24,4),customername VARCHAR(255) NOT NULL,re text,collectiontime DATE); insert into tmb_table1 select
skf587,skf589,skf591,skf593,skf596,skf602,skf604,skf616,skf611 from chenkuserdb3.skt42,chenkuserdb3.skt43
where skf589=skf616 and skf611<dateEnd;end;
delete from chenkuserdb3.skt105;
set =0; begin
declare curdeal_1 cursor for
select * from chenkuserdb3.tmp_table_a1 as tb1 left join (select * from chenkuserdb3.tmp_table_a1 where
skf611<dateBegin ) tb2 on tb1.customername=tb2.customername and tb2.customername
is null;
declare continue handler for not found
set =1;
open curdeal_1;
repeat
fetch curdeal_1 into
contractnum1,salesman1,saletype1,countmeeting1,softwareamount1,customername1,re1,collectiontime1;
if(<1)then
begin
insert into chenkuserdb3.skt105
(skf1343,skf1336,skf1338,skf1342,skf1339,skf1337,skf1348,skf1341) select tblemployee.name,contractnum1,saletype1,countmeeting1,softwareamount1,customername1,re1,collectiontime1 from chenkuserdb3.tblemployee where tblemployee.ID=salesman1;
end;
end if;
until =1
end repeat;
close curdeal_1;
end; end;
create procedure venetostatistics(in dateBegin date,in dateEnd date) begin declare softwareamount1 decimal;
declare salesman1,saletype1,contractnum1 varchar(128);
declare countmeeting1 smallint(6);
declare customername1 varchar(255);
declare re1 text;
declare collectiontime1 date;
declare int;begin
drop table if exists tmp_table_a1;
create temporary table tmp_table_a1(contractnum VARCHAR(128) NOT NULL,salesman
varchar(128),saletype varchar(128),countmeeting smallint(6),softwareamount
decimal(24,4),customername VARCHAR(255) NOT NULL,re text,collectiontime DATE); insert into tmb_table1 select
skf587,skf589,skf591,skf593,skf596,skf602,skf604,skf616,skf611 from chenkuserdb3.skt42,chenkuserdb3.skt43
where skf589=skf616 and skf611<dateEnd;end;
delete from chenkuserdb3.skt105;
set =0; begin
declare curdeal_1 cursor for
select * from chenkuserdb3.tmp_table_a1 as tb1 left join (select * from chenkuserdb3.tmp_table_a1 where
skf611<dateBegin ) tb2 on tb1.customername=tb2.customername and tb2.customername
is null;
declare continue handler for not found
set =1;
open curdeal_1;
repeat
fetch curdeal_1 into
contractnum1,salesman1,saletype1,countmeeting1,softwareamount1,customername1,re1,collectiontime1;
if(<1)then
begin
insert into chenkuserdb3.skt105
(skf1343,skf1336,skf1338,skf1342,skf1339,skf1337,skf1348,skf1341) select tblemployee.name,contractnum1,saletype1,countmeeting1,softwareamount1,customername1,re1,collectiontime1 from chenkuserdb3.tblemployee where tblemployee.ID=salesman1;
end;
end if;
until =1
end repeat;
close curdeal_1;
end; end;
我自己建立的临时表是可以创建的 但是这样子查询的时候就提示不存在
A连上数据库,建了个临时表,可以看到,可以操作 。B又连上,是看不到这个临时表的。
临时表只能查询一次
SELECT * FROM temp_table, temp_table AS t2; 会报错