简单的触发器和存储过程

触发器:

before


create procedure p4()

begin
   declare age int default 18;

   if age >=18 then
      select '';
   else
      select '';
   end if;
end

call p4();


---------------------

[in/out/inout] 参数名 参数类型

create procedure p5(width int,height int)
begin

    select concat('你的面积是:',width*height) as area;

    if width  > height then

       select '你挺胖';    
    elseif width < height then

       select '你挺瘦';
    else

       select '你挺方';
    end if;

end$

create procedure p7()
begin

    declare total int default 0;
    declare num int default 0;  

    while num<100 do

        set num:=num+1;
        set total := total+num; 

    end while;

    select total;
end

create procedure p8(in n int,out total int)
begin

    declare num int default 0;
    set total=0;
    while num <n do 

     set num:=num+1; 

     set total:=total+num;

    end while;

end$


create procedure p9(inout age int)
begin

set age:=age+20;

end$

how to use "case"

case ''

  when '' then  ''
  when '' then  ''

else '' 

end case;

create procedure p10(in pos int)
begin

    case pos 

      when 1 then select 'still flying';

      when 2 then select  'fall in sea';

      when 3 then select  'in the islang';


    end case;                   
end$

repeat 的使用:

create procedure p11()
begin

    declare i int default 0;

    repeat


        select i; 

        set i:=i+1;

    until i>=10 end repeat;

end$

create procedure p13()

    begin
        declare total int default 0;

        declare i int default 0;

        repeat 
            set i:=i+1;

            set total:=total+i;

        until i>100 end repeat;

        select total;

    end$


create procedure p13()

begin

    declare tmp_name varchar(20);

    declare test_cur cursor for select name from student;

    open test_cur;

    fetch test_cur into tmp_name;

    select tmp_name;

    fetch test_cur into tmp_name;

    select tmp_name;

    fetch test_cur into tmp_name;

    select tmp_name;

    close test_cur;

end$


create procedure p14()
begin

    declare cnt int default 0;

    declare i int default 0;

    declare tmp_name varchar(20) ;
    declare getstudent cursor for select name from student;

    select count(*) into cnt from student;          
    open getstudent;
    repeat  
        set i:=i+1;
       fetch  getstudent into tmp_name;  
       select tmp_name;

    until i>=cnt end repeat;
    close getstudent;
end$

create procedure p15()

begin

    declare tmp_name varchar(20);

    declare haiyou int default 1;   

    declare getstudent cursor for select name from student; 

    declare exit handler for not found set haiyou=0;

    open getstudent;    

    repeat 

       fetch  getstudent into tmp_name;  
       select tmp_name;

    until haiyou=0 end repeat;

    close getstudent;

end$


create procedure p16()
begin

    declare tmp_name varchar(20);   

    declare you int default 1;

    declare getstudent cursor for select name from student; 

    declare continue handler for not found set you :=0; 

    open getstudent;

    fetch getstudent into tmp_name;

    repeat 

        select tmp_name;

        fetch getstudent into tmp_name;

    until you=0 end repeat;

    close getstudent;

end$

mysql 数据查询:

mysql> select name,subject,group_concat(id) from student group by subject;
+---------+---------+------------------+
| name    | subject | group_concat(id) |
+---------+---------+------------------+
| lisi    | english | 2,3,4            |
| lisheng | math    | 1                |
+---------+---------+------------------+
2 rows in set (0.01 sec)