|
功能：从personinfo 和 employee表中得到各自主键成为personConnect表的主键插入
CREATE PROCEDURE insertPersonConnet()
begin
declare personConnetId varchar(20);
declare emplyeeConnetId varchar(20);
declare flag int default 0;
declare personConnetIds cursor for(select personId from personinfo);
declare employeeConnetIds cursor for(select employeeId from employee);
declare continue handler for not found set flag=1;
open personConnetIds;
person_loop:loop
FETCH personConnetIds into personConnetId;
if flag =1 then
leave person_loop;
end if;
INSERT into personconnet(personId,persontype,isconnect,version) values(personConnetId,'系统人员','N',0);
end loop person_loop;
close personConnetIds;
set flag = 0;
open employeeConnetIds;
employee_loop:loop
FETCH employeeConnetIds into emplyeeConnetId;
if flag =1 then
leave employee_loop;
end if;
INSERT into personconnet(personId,persontype,isconnect,version) values(emplyeeConnetId,'员工人员','N',0);
end loop employee_loop;
close employeeConnetIds;
end
调用存储过程：call insertPersonConnet();
(责任编辑：IT) |