mysql多个游标存储过程例子

最新推荐文章于 2024-07-03 03:40:17 发布

原创最新推荐文章于 2024-07-03 03:40:17 发布 · 2.5k 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个存储过程，用于从personinfo和employee表中获取主键并插入到personConnect表中，分别作为系统人员和员工人员记录。该过程通过游标遍历两个表的所有记录，并将数据插入personConnect表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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