Mysql´æ´¢¹ý³ÌǶÌ×Óαê

SQL code:

create procedure sp_select_probe_port()
begin
declare probeid int;
declare portid int;
declare done1 int default 0;
declare done2 int default 0;
declare cur_1 cursor for select id from t_probes;
declare cur_2 cursor for select id from t_ports where probe_id=probeid;
declare continue handler for not found set done1=1;
declare continue handler for not found set done2=1;
declare v_id varchar(30);
declare v_parentid varchar(30);
declare v_name varchar(50);

drop table if exists temp_probe_port;
create temporary table temp_probe_port(
id varchar(30) default 0,
parent_id varchar(30) default 0,
name varchar(50) default 'N/A'
);

open cur_1;
repeat
fetch cur_1 into probeid;
if not done1 then
set v_id=concat("1",probe_id);
set v_parentid='0';
select name into @v_name from t_probes where id=probeid;
set v_name=@v_name;
insert into temp_probe_port values(v_id,v_parentid,v_name);

open cur_2;
repeat
fetch cur_2 into portid;
if not done2 then
set v_id=concat("2",portid);
set v_parentid=concat("1",probe_id);
select name into @v_name from t_ports where id=porti