mysql存储过程，函数，触发器例子

1，分割一个用"|||"间隔的字符串的存储过程．

create PROCEDURE  sp_InsertJoKe(lastindex int)
begin
declare i int;
set i=1;
while (i<lastindex) do
 begin
  declare a varchar(4000);
  declare p int;
  declare part1 varchar(4000);
  set a='';
  select a=TitleContent  from content where ID=i ;
  set p=position('|||' in a);
  while (p>0) do
   begin
    set part1=mid(a,1,p-1);
    insert MyJoke(Content) values(part1);
    set a=SUBSTRING(a,p+3);
    set p=position('|||' in a);
   end;
  end while;
  insert MyJoke(Content) values(a);  
  set i=i+1;
 end;
end while;
end;

2，网上一个存储过程分页的例子．（转自http://www.yiluo.net/?p=35），其中把字符串当语句执行是prepare

CREATE  PROCEDURE ClassList(
 IN ID int,
    fldName varchar(100),
    pageSize int,
    pageIndex int,
    orderType int,
    strWhere varchar(2000),
 OUT cou int
)
begin
 declare beginRow int;
 declare sqlStr varchar(1000);
 declare limitTemp varchar(1000);
 declare orderTemp varchar(1000);
 declare v_classp int;
 declare oo int;
 declare done int default 0;
 declare sql1 varchar(500) default ” “;
 declare sql2 varchar(200);
 declare cur cursor for select shop_classid from shop_class where shop_parentclassid=id;
 DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done = 1;

set sql2=’select*from shop_class left join shop_main on shop_classid=shop_type_id where’;
select shop_parentclassid into v_classp from shop_class where shop_classid=id;

if(v_classp=0) then
 open cur;
  repeat
   fetch cur into oo;
   IF NOT done THEN
    set sql1=concat(’ or shop_classid=’,oo,sql1);
   END IF;
  UNTIL done END REPEAT;
 close cur;
 set @sql=concat(sql2,substring(sql1,4,length(sql1)-3));
else
 set @sql=concat(’select * from shop_main where shop_type_id=’,id);
end if;

 set beginRow = (pageIndex-1)*pageSize;
 set limitTemp = CONCAT(’ limit ‘,beginRow,’,',pageSize);
 set orderTemp = CONCAT(’ order by ‘,fldName);
 if orderType = 0 then
  set orderTemp = CONCAT(orderTemp,’ ASC ‘);
 else
  set orderTemp = CONCAT(orderTemp,’ DESC ‘);
 end if;

 set @sqlString = CONCAT(@sql,’ ‘,strWhere,orderTemp,limitTemp);

 prepare sqlstmt from @sqlString;
 execute sqlstmt;

 deallocate prepare sqlstmt;
end

3，创建函数时必须有返回类型．

4，触发器．

语法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name  
FOR EACH ROW   
BEGIN  
 trigger_stmt  
END； 

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
FOR EACH ROW
BEGIN
 trigger_stmt
END；

大写的为关键字
trigger_name:触发器的名字，我常用的命名规则t_name_tableName_(b|a)(i|u|d),t:触发器标识，name:英文名，tableName:表名，b(BEFORE):标识是触发事件之前，a(AFTER):标识触发事件之后,i(insert):标识insert事件，u(update):标识update事件，d(delete):标识delete事件;
trigger_time:触发时间(BEFORE或AFTER)
trigger_event:事件名(insert或update或delete)
tbl_name:表名(必须是永久性表)
trigger_stmt：执行语句(可以是复合语名)，使用别名OLD和NEW，能够引用与触发程序相关的表中的列。

例：

create trigger tr_socre after insert on tal_name

for each row

begin

      sql语句

end