create procedure sp_delete_probe
(
p_id int
)
begin
drop table if exists temp_port_ids;
create temporary table temp_port_ids(
port_id int default 0
);
drop table if exists temp_group_ids;
create temporary table temp_group_ids(
group_id int default 0
);
drop table if exists temp_device_ids;
create temporary table temp_device_ids(
device_id int default 0
);
drop table if exists temp_policy_ids;
create temporary table temp_policy_ids(
policy_id int default 0
);
insert into temp_port_ids(port_id) select id from t_ports where probe_id=p_id;
insert into temp_group_ids(group_id) select id from t_devicegroups
where port_id in(select * from temp_port_ids);
insert into temp_device_ids(device_id) select id from t_devices
where port_id in(select * from temp_port_ids);
insert into temp_policy_ids(policy_id) select policy_id from t_devicegroups
where id in(select * from temp_group_ids);
insert into temp_policy_ids(policy_id) select policy_id from t_devices
where id in(select * from temp_device_ids);
delete from t_probes where id=p_id;
delete from t_ports where id in(select * from temp_port_ids);
delete from t_port_ips where port_id in(select * from temp_port_ids);
delete from t_port_portgroup where port_id in(select * from temp_port_ids);
delete from t_entities where port_id in(select * from temp_port_ids);
delete from t_devicegroups where port_id in(select * from temp_port_ids);
delete from t_devices where port_id in(select * from temp_port_ids);
delete from t_policies where id in(select * from temp_policy_ids);
end业务需要,写了上面这个存储过程,但是写完看起来很别扭,想使用游标,但游标的效率太低了,哪位把游标和临时表给对比下,谢谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货