存储过程和函数

函数的创建:
create function wuxiaoxiao(ranran int)
  returns int
begin
  declare vi_count int;
  select count(*) into vi_count from user where user_id=ranran;
  if vi_count is null then
     set vi_count=0;
  end if;
  return vi_count;
end;
$$
调用:select wuxiaoxiao(1);
查看函数:show function status;
查看函数的创建语句:show create function wuxiaoxiao
删除函数:drop function function_name;

存储过程的创建:
create procedure curdemo()
begin
  declare done int default 0;
  declare a char(16);
  declare b,c,d int;
  declare cur1 cursor for select id,data from test.t1;
  declare cur2 cursor for select i from test.t2;
  //declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  open cur2;
  repeat
    fetch cur1 into a, b;
    fetch cur2 into c;
    if not done then
       set d = sign(b - c);
       case d
        when 0 then
              insert into test.t3 values (a,0);
        when 1 then
              insert into test.t3 values (a,b);
        when -1 then
              insert into test.t3 values (a,c);
       end case;
    end if;
  until done end repeat;
  close cur1;
  close cur2;
end;
$$

游标:
声明:declare cursor_name CURSOR for select_statement
open游标:open cursor_name
fetch游标:fetch cursor_name into var_name
close游标:close cursor_name

流程控制语句:
1.if语句
2.case语句:
case
 when i_start_id=2 then
  set @x1=....
 else
  set...
end case;

case i_start_id
when 2 then
  set...
else
 set...
end case;
3.loop语句和leave(用在循环或者begin..end中)语句:
ins:loop
  set ...
  if ..then
  leave ins;//从标注的流程构造函数中退出
  end if;
  insert into...
end loop ins;
4.iterate语句:挑出当前循环的剩下语句,直接进入下一轮循环
5.repeat
6.while语句
  while ..do
     ....
  end while;