mysql7笔记----存储过程实例

mysql创建存储过程

drop procedure if exists getcreatetimes
/*前面要写delimiter $$ 或delimiter // */
delimiter $$ 
create procedure `getcreatetimes`()
 begin
  select usercreatetime from users;

end;

---

mysql 存储过程有参数输入拼接

drop procedure if exists gettest01;
delimiter $$ 
create procedure `gettest01`(
  uname varchar(50),
  upass varchar(50)
)
 begin
 set @sql= 'select * from users where 1=1';
 if uname is not null then
      set @sql=concat(@sql,' and username=',"'",uname,"'");
      end if;
  if upass is not null then
      set @sql=concat(@sql,' and password=',"'",upass,"'");
      end if;
  prepare stmt from @sql;
   execute stmt;    
end;

call gettest01(null,"123");

---

mysql存储过程含输入参数的拼接的分页 

 drop procedure if exists gettest01;
delimiter $$ 
create procedure `gettest01`(
  startpage int,/*第startpage页，从0开始算*/
  pagesize int,/*每页显示的记录数*/
  uname varchar(50),
  upass varchar(50)
)
 begin
 set @sql= 'select * from users where 1=1';
 if uname is not null then
      set @sql=concat(@sql,' and username=',"'",uname,"'");
      end if;
  if upass is not null then
      set @sql=concat(@sql,' and password=',"'",upass,"'");
      end if;
      set @sql=concat(@sql,' limit ',startpage*pagesize,",",pagesize);
  prepare stmt from @sql;
   execute stmt;    
end;

call gettest01(4,2,null,null);