日日常札记
返回日记本
iteye

mysql存储过程 游标

#数据库

Sql代码  create table table1(id int,name varchar(100));  create table table2(id int,name varchar(100));  create table table3(name varchar(100));  insert into table1 values(1,'李');  insert into table1 values(2,'张');  insert into table1 values(3,'王');  insert into table1 values(4,'孙');    insert into table2 values(1,'智宇');  insert into table2 values(2,'权');  insert into table2 values(3,'飞');  insert into table2 values(4,'中山');    delimiter $  create procedure pc_test()  begin    declare  i int;    declare vname varchar(100);    declare done int;    declare c1 cursor for select id from table1;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;    open c1;    cursor_loop:loop      FETCH c1 INTO i;    if done=1 then      leave cursor_loop;     end if;    insert into table3 (name)    select name  from table2 where id =i;    end loop cursor_loop;    CLOSE c1;  end  $  delimiter ;  create table table1(id int,name varchar(100));

create table table2(id int,name varchar(100));

create table table3(name varchar(100));

insert into table1 values(1,'李');

insert into table1 values(2,'张');

insert into table1 values(3,'王');

insert into table1 values(4,'孙');


insert into table2 values(1,'智宇');

insert into table2 values(2,'权');

insert into table2 values(3,'飞');

insert into table2 values(4,'中山');


delimiter $

create procedure pc_test()

begin

declare i int;

declare vname varchar(100);

declare done int;

declare c1 cursor for select id from table1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

open c1;

cursor_loop:loop

FETCH c1 INTO i;

if done=1 then

leave cursor_loop;

end if;

insert into table3 (name)

select name from table2 where id =i;

end loop cursor_loop;

CLOSE c1;

end

$

delimiter ;


 




0 顶6 踩


原文章浏览量：2058

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-1565496