p1:begin
declare v_did varchar(10);
declare v_dname varchar(10);
declare v_tid varchar(10);
declare v_tname varchar(10);
declare i_seq integer default 0;
declare not_found condition for sqlstate ''02000'';
declare at_endc1 int default 0;
declare at_endc2 int default 0;
p2: begin -- open department
declare cursor1 cursor for
select did,dname from department;
declare continue handler for not_found set at_endc1 = 1;
open cursor1;
--遍歷 cursor1
while(at_endc1 < 1) do
fetch cursor1 into v_did,v_dname;
p3:begin
declare cursor2 cursor for
select ID, NAME from teacher where did=v_did and dname=v_dname;
declare continue handler for not_found set at_endc2 = 1;
open cursor2;
if (at_endc1 = 0) then
set at_endc2 = 0;
end if;
--遍歷 cursor2
while(at_endc2 < 1) do
fetch cursor2 into v_tid,v_tname;
insert into student(ID, NAME, TID, TNAME, DID, DNAME) values(i_seq,char(i_seq),v_tid,v_tname,v_did,v_dname);
set i_seq=i_seq+1;
end while;
close cursor2;
end p3;
end while;
close cursor1;
end p2;
END p1
