功能：从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();