插件的db脚本,创建存储过程失败
回帖数
3
阅读数
1573
发表时间
2017-09-15 18:20:34
你好,自己开发的一个插件,需要创建存储过程,但是报失败,请问有没有解决办法?以下sql在MySQL的管理工具里执行是成功的。
delimiter $$
create procedure `update_boco_product_project`()
begin
begin
declare v_work_type varchar(100);
declare v_work_id bigint;
declare v_project bigint;
declare v_product bigint;
declare done int default false ;
declare cur cursor for
(
select
pt.work_type,
pt.work_id,
pt.project,
pt.product
from
boco_product_project_temp pt
inner join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
pt.project != p.project
or pt.product != p.product
);
declare exit handler for not found set done = true;
create procedure `update_boco_product_project`()
begin
begin
declare v_work_type varchar(100);
declare v_work_id bigint;
declare v_project bigint;
declare v_product bigint;
declare done int default false ;
declare cur cursor for
(
select
pt.work_type,
pt.work_id,
pt.project,
pt.product
from
boco_product_project_temp pt
inner join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
pt.project != p.project
or pt.product != p.product
);
declare exit handler for not found set done = true;
open cur;
cur:loop
fetch cur into v_work_type,v_work_id,v_project,v_product;
if done then
leave cur;
end if;
update boco_product_project set project=v_project,product=v_product where work_type = v_work_type and work_id = v_work_id;
commit;
end loop;
close cur ;
end;
begin
insert into boco_product_project
select
pt.*
from
boco_product_project_temp pt
left join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
p.work_id is null
and p.work_type is null;
commit;
end;
begin
delete p
from
boco_product_project_temp pt
right join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
pt.work_id is null
and pt.work_type is null;
commit;
end;
end$$
delimiter ;
cur:loop
fetch cur into v_work_type,v_work_id,v_project,v_product;
if done then
leave cur;
end if;
update boco_product_project set project=v_project,product=v_product where work_type = v_work_type and work_id = v_work_id;
commit;
end loop;
close cur ;
end;
begin
insert into boco_product_project
select
pt.*
from
boco_product_project_temp pt
left join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
p.work_id is null
and p.work_type is null;
commit;
end;
begin
delete p
from
boco_product_project_temp pt
right join boco_product_project p on p.work_type = pt.work_type and p.work_id = pt.work_id
where
pt.work_id is null
and pt.work_type is null;
commit;
end;
end$$
delimiter ;
创建存储过程的脚本的正确性是已经在mysql客户端验证过的。
需要咨询您,插件的db脚本中,创建存储过程的脚本如何嵌入?还是目前版本的插件的db脚本不支持创建存储过程?
2017-09-18 16:13:04 杨金莲 回帖
可以下载一个官方插件 里面有一个 db 文件夹 存放了需要执行的sql。可以参考:http://www.zentao.net/book/zentaopmshelp/144.html
2017-09-18 17:51:09 石洋洋 最后编辑
2017-09-18 17:51:09 石洋洋 回帖
联系人
刘璐/高级客户经理
电话(微信)
18562550650
QQ号码
2845263372
联系邮箱
liulu@chandao.com