mysql存储过程简单案例

数据库  /  管理员 发布于 7年前   817

1.多字段

CREATE DEFINER=`root`@`localhost` PROCEDURE `r404`(a int)

begin 

select * from cunzhu ;

SET @count = (select count(*) from cunzhu); 

select @count;

select count(*),name,age,home into @count,@name,@age,@home from cunzhu where id=a;

select @count,@name,@age,@home;

end;

call r404(2);

结果：

2.循环查询

CREATE DEFINER=`root`@`localhost` PROCEDURE `r405`()

begin 

declare i int default 1;

loop_label: loop

            select * from cunzhu where id=i;

            set i=i+1;

            if i>3 then 

               leave loop_label;

            end if;

            end loop;

end;

call r405();

结果：

3.动态拼接

CREATE DEFINER=`root`@`localhost` PROCEDURE `r408`()

begin 

set @aa = 'select name from cunzhu where id=2';

set @sentence = concat(@aa); 

prepare aa from @sentence;

execute aa;  

deallocate prepare aa;  

end;

call r408();

结果：