mysql 存储过程读书笔记(1)基础

mysql存储过程目前只支持存储过程，存储函数，触发器

存储过程基本结构:

Create procedure ProcedureName(Params[in,out])

Begin

     Declare  attr attrType;

     procedure content;

End;

 

Example:

1. 创建基本存储过程

create procedure exampe1()
begin
      declare l_student_count integer;
      select count(*) into l_student_count from student;
 
       select concat('there are totally ', l_student_count ,' students');
end;

 

2.删除存储过程

   drop procedure example1;

 

3.创建带入参的存储过程

create procedure example2(stu_id int)
begin
    declare stu_name varchar(30);
 
     select name into stu_name from student where student_id = stu_id;
     select stu_name;
end;

 

4.创建函数基本结构

create function example5() returns int
begin
     declare student_count int;
 
      select count(*) into student_count from student;
      return student_count;
end;

 

5.创建触发器基本结构

create trigger example6 before update on t for each row
begin
   If New.percent < 0 then
     set New.percent = 20;
   Else
     set New.percent = 100;
   End If;
End;